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
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.
Page 1 / 1
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
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
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.
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.
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...
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.