Skip to main content
Solved

SQLExecutor error DECLARE CURSOR must not contain data-modifying statements in WITH? in FME 2020.1

skime
Contributor
  • skimeContributor
  • Contributor

Hello,

I've a question about SQLExecutor and SQL statement. I'm upgrading workspace from FME 2017.1 to 2020.1 and in 2020.1 I'm getting an error in SQL WITH statement. In FME 2017.1 this code is not rejected and works.

Error with example

2020-07-22 14:35:49|   3.1|  0.0|WARN  |Error executing SQL command ('declare "public_custom_query_crsr" cursor for WITH something AS  (...)
INSERT INTO table (a...'): 'ERROR:  DECLARE CURSOR must not contain data-modifying statements in WITH

 

What has changed in SQL Executor from 2017 to 2020.1? The transformer version is the same as I see (version number 8). I use the same database

How can I change my SQL, is there a need to modify mine WITH statements?

 

Best answer by skime

I have a solution given by my SQL mastermind colleague (thank him!).

You need to close the cursor so... adding ";" before WITH is getting job done. 😅

View original
Did this help you find an answer to your question?

3 replies

david_r
Evangelist
  • david_rEvangelist
  • July 23, 2020

Would it be possible to see the entire SQL command?

skime
Contributor
  • skimeContributor
  • Author
  • Contributor
  • July 23, 2020
david_r wrote:

Would it be possible to see the entire SQL command?

Here you are, code example:

with insert_something as (
insert into table (attribute1, attribute2)
    select nextval(pg_get_serial_sequence('table', 'attribute1') ), $(FME_JOB_ID)
        returning attribute1
)

select attribute1 as attribute_old
    from insert_something order by attribute1 desc limit 1
skime
Contributor
  • skimeContributor
  • Author
  • Contributor
  • Best Answer
  • November 3, 2020

I have a solution given by my SQL mastermind colleague (thank him!).

You need to close the cursor so... adding ";" before WITH is getting job done. 😅

Reply


Helpful Members This Week

Community Stats

30,942
Posts
117,252
Replies
38,742
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.