Question

SQLCreator generates false warnings in log, Query failed possibly due to a malformed statement

P
Badge

I think this has crept in since the first release of FME 2018.

I have a SQLCreator with a perfectly valid MS SQL query, however when I run the workspace (either in Workbench or in FME Server) it reports warnings in the log file: "Query failed possibly due to a malformed statement", which is not true. So here is the SQLCreator with the query. If I press the Run button to check syntax it reports Successful.

And here is the same query in SQL Server Management Studio, it also reports successful.

However if I run the workspace I always get a warning that the query failed due to being malformed.

You will also notice in the log, FME injects its additional SQL around my original SQL statement (perhaps this the the cause of the failure warning)? The last line in the screenshots reports that the data read was successful.

So the issue I have is this: even though the workspace runs OK, when examining log files I'm never really sure if there is indeed a genuine syntax error with the SQL statement, or if it's a bogus error being reported by FME. Hope it can be fixed in a future release.

10 replies

david_r
Rank
Userlevel 4

Try removing the comments from the SQL code and see if that improves the situation.

There has been a known issue with this in the past, not sure if it's been fixed yet.

G
Badge +3

@peterz

yes, I sometimes experience that comments with some type of characters inside the comment result in "malformed statements".

The second and or the third comment might cause this.

P
Badge

Thanks for your suggestions @gio and @david_r, but the problem still persisted even after removing the comments from the SQLCreator.

However I did manage to narrow it down to a very strange Workbench peculiarity. If you have a combination of SQLCreator and a DataReader in your workspace and both target the same database, this is what causes the false error/warning in the log file.

Here is a simplified workspace illustrating this very fact:

Now, if I replace the DataReader in above example with another SQLCreator, the problem goes away, no more false warnings in log file! (and yes, comments are OK in the SQL statement, it runs fine with them)

The takeaway form all of this:

It appears there's some incompatibility/oddity in using a SQLCreator and DataReader in the same workspace that target the same database, which manifests in false warnings in the log file. The workaround is to use either SQLCreators or DataReaders but not a combination of both (that target the same database).

david_r
Rank
Userlevel 4

Thanks for your suggestions @gio and @david_r, but the problem still persisted even after removing the comments from the SQLCreator.

However I did manage to narrow it down to a very strange Workbench peculiarity. If you have a combination of SQLCreator and a DataReader in your workspace and both target the same database, this is what causes the false error/warning in the log file.

Here is a simplified workspace illustrating this very fact:

Now, if I replace the DataReader in above example with another SQLCreator, the problem goes away, no more false warnings in log file! (and yes, comments are OK in the SQL statement, it runs fine with them)

The takeaway form all of this:

It appears there's some incompatibility/oddity in using a SQLCreator and DataReader in the same workspace that target the same database, which manifests in false warnings in the log file. The workaround is to use either SQLCreators or DataReaders but not a combination of both (that target the same database).

That's strange, but thanks for sharing. I suggest you forward this to Safe support, with a link to this thread. Sounds like something they might want to look into.
G
Badge +3

@peterz

Yes, that does make sense.

It is not peculiar, you have no control over who does what when.

You can either read all the data from one sql creator, for logic dictates that you can do the merging within a single sql script (for instance make the selections through 2 WITH statements and then join them by selecting those 2, or you can use a sql-executor (instead of the reader)triggered by the sql creator I ussualy take a single sample to trigger it (using the Sampler).

P
Badge

@peterz

Yes, that does make sense.

It is not peculiar, you have no control over who does what when.

You can either read all the data from one sql creator, for logic dictates that you can do the merging within a single sql script (for instance make the selections through 2 WITH statements and then join them by selecting those 2, or you can use a sql-executor (instead of the reader)triggered by the sql creator I ussualy take a single sample to trigger it (using the Sampler).

No, that's not correct. The issue I raised has to do specifically with false error/warnings in log file, not with the functioning of the workspace itself. So I still stand by the fact that it's most likely a bug: the warning in log is false.

 

 

Also I don't recall seeing any advice against using a SQLCreator and DataReader in the same workspace (that target the same database).

 

T
Rank
Badge +6

Hello @peterz,

What is occurring here, is when the SQL is sent to the database, FME wraps some additional SQL with the statement. This is done in an attempt to get finalized schema, with the modern data types such as datetime2. Sometimes these additional queries will fail, in your case, it appears the temporary table that FME tries to create fails due to permissions.

 

I hope that helps explain the behavior you are seeing!
fmelizard
Rank
Userlevel 4
Badge +13

Thanks for your suggestions @gio and @david_r, but the problem still persisted even after removing the comments from the SQLCreator.

However I did manage to narrow it down to a very strange Workbench peculiarity. If you have a combination of SQLCreator and a DataReader in your workspace and both target the same database, this is what causes the false error/warning in the log file.

Here is a simplified workspace illustrating this very fact:

Now, if I replace the DataReader in above example with another SQLCreator, the problem goes away, no more false warnings in log file! (and yes, comments are OK in the SQL statement, it runs fine with them)

The takeaway form all of this:

It appears there's some incompatibility/oddity in using a SQLCreator and DataReader in the same workspace that target the same database, which manifests in false warnings in the log file. The workaround is to use either SQLCreators or DataReaders but not a combination of both (that target the same database).

@peterz I confirmed your findings! It was a tough one to track down. I had narrowed it down to a spot where I was using a FeatureMerger between the DataReader and SQLCreator. I was suspicious of the FeatureMerger, but now I know the true culprit. Thanks!

milo89
Rank
Userlevel 2
Badge +11

Hi @peterz,

Have you tried this in a version of 2019 by chance? I noticed the same issue with an SQL creator (only) running in 2019 but I don't recall seeing it previously. I may have to install FME 2018 again to test this.

s.jager
Rank
Badge +8

I am using 

Edition: FME Desktop Oracle Edition (floating)

Version: FME(R) 2020.0.0.0 (20200309 - Build 20200 - WIN64)

 

and also the accompanying FME Server, and I can confirm that this is still an issue. The SQL Creator still generates it's features, and everything keeps working, but there are warnings in the logs:

2021-08-05 14:44:43| 11.8| 0.0|WARN |Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `IF OBJECT_ID('fme_tempAdoDataTypeTable_spatialReader', 'U') IS NOT NULL            DROP TABLE fme_tempAdoDataTypeTable_spatialReader; SELECT * INTO fme_tempAdoDataTypeTable_spatialReader FROM ( select a.column1,
       a.column2,
       los.column3
from Table1 los
join Table2 a on a.ParentColumn=los.ChildColumn ) AS customQuery WHERE 1=0;'. Provider error `(-2147217900) CREATE TABLE permission denied in database '[databasename redacted]'.'
 
2021-08-05 14:44:43| 11.8| 0.0|WARN |Microsoft SQL Server Non-Spatial Reader: Getting Custom SQL schema failed. Make sure all columns are named (any ambiguous columns, e.g. GEOM.STBuffer(1), must have an AS clause). If there is an ORDER BY clause, either remove it or add a TOP clause

I redacted the column- and tablenames in the sql statement, but rest assured the sql is perfectly valid and works, returning 165024 rows. There are no function calls or anything like that, it is just selecting three columns from two existing tables.

FME, either desktop or server, or any of the database connections available to us, will never get create table permissions. And it most definitely and assuredly will never even come close to getting a whiff of the drop table permission!

I am not entirely sure what FME is trying to achieve here (there are better ways of getting the result set schema than writing a temporary table - that is something that should never happen), but the resultant warnings in the log files are annoying (especially if you have workspaces analyzing logfiles from Server and all of a sudden start yelling and screaming about warnings - it plays havoc with my dashboards ;-) ).

 

The weird thing is that it indeed changes when I disable the regular Readers in that workspace that access the same database: the message indeed goes away.

 

Reply


Community Stats

28,717
Posts
109,495
Replies
36,560
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.