Skip to main content
Solved

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

  • July 22, 2020
  • 3 replies
  • 188 views
skime
Contributor
  • skimeContributor
  • Contributor
  • 14 replies

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. 😅

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • July 23, 2020

Would it be possible to see the entire SQL command?

skime
Contributor
  • skimeContributor
  • Author
  • Contributor
  • 14 replies
  • July 23, 2020

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
  • 14 replies
  • 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. 😅

Community Stats

32,070
Posts
121,778
Replies
39,699
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.