Question

SQLExecutor comments

  • 5 August 2015
  • 6 replies
  • 8 views
nic_ran
Rank
Badge +11
It appears that the behaviour of the SQLExecutor has changed for FME 2015. I cut & pasted a valid SQLExecutor transformer from an old Workspace (developed using FME 2014 sp4) into a new 2015.1 Workspace and it came up as invalid.

 

 

The SQL Statement had comments in it, of the form: /* comment */

 

 

Taking those comments out made the Transformer valid again.

 

 

Does anyone know if this is an intentional or documented change?

 

 

thanks,

 

Nic

6 replies

takashi
Rank
Userlevel 2
Badge +17
Hi Nic,

 

 

How to interpret SQL statements including comments depends on the database engine. In my observation, for example, PostgreSQL and Microsoft SQL Server allow comments, but MS Access doesn't.

 

I checked it with FME 2015.1.1.0 build 15515.

 

 

Takashi
david_r
Rank
Userlevel 4
Hi

 

 

As Takashi says, it depends more on the underlying database than FME. You could, however, try using the single-line comment form, that seems to work fine for me:

 

 

select *  -- This is a comment

 

from myTable -- Here is another comment

 

 

David
nic_ran
Rank
Badge +11
Thanks for your responses.

 

 

I have not changed the database type or the SQL statement, I simply opened an existing workspace (originally created in FME 2014sp4) in FME2015 and the existing transformer became invalid due to the embedded comments.

 

 

Of course, I can remove the comments, or possibly use a different comment character, but it appears that FME behaviour has changed and workspaces will need to be modified if upgrading to FME 2015.
takashi
Rank
Userlevel 2
Badge +17
In my quick test using Microsoft SQL Server, I was not able to reproduce the same issue as yours. My test is as below:

 

- with FME 2014 SP5, create a workspace which contains a SQLExcutor with a SQL statement containing comments, and save it.

 

- open the workspace with FME 2015.1, and run.

 

- result, it worked fine without any problem.

 

 

But there could be a special case where comments would be rejected. I would recommend you to send a request to Safe support to investigate the reason for the issue in your case.
nic_ran
Rank
Badge +11
Thanks Takashi. I'll contact Safe. At first this seemed to be just an annoyance but with some of our scheduled jobs failing last night it might become more of an issue...
nic_ran
Rank
Badge +11
Another update... It appears that the issue might not be specific to 2015.

 

 

The text that is commented in my SQL statement is an older version of the query. The older version uses some attributes as replacement values in the query (e.g. @Value(myAttr)). Those attributes did not exist on the initiator feature to the SQLExecutor and, despite the /* */ style comments being recognised in the editor, FME was still trying to expand out the attribute values, so the transformer became invalid.

 

 

Reply


Community Stats

28,687
Posts
109,421
Replies
36,543
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.