Skip to main content
  • EN

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?

 

Would it be possible to see the entire SQL command?

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

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


Community Stats

30,186
Posts
114,572
Replies
37,849
Members
Terms & ConditionsCookie 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.