Hi list.
I'm using a SQLCreator to read (!) some data from our administration database.
The query fails, because FME wraps my query into a SELECT-INTO construct, that requires "CREATE TABLE" permission, which my task (of course) doesn't have.
Q1: Why does FME wrap my query into such a construct?
Q2: What other ways do I have to accomplish a guaranteed read-only query?
Using 2020.1
Cheers
Addendum.
The error "Query failed, possibly due to a malformed statement." is only flagged as a warning in the log file.
Really?
Addendum.
The error "Query failed, possibly due to a malformed statement." is only flagged as a warning in the log file.
Really?
Addendum 2.
The SQLExecutor transformer has a setting called "Application Intent", set initially to ReadWrite, but may be set to ReadOnly. This setting is not available in SQLCreator.
Also, the parameter cannot be changed in the GUI. I did manage to change the parameter value via an unpublished parameter. Why is this so cumbersomly implemented?
I've now replaced the SQLCreator with a Creator and a SQLExecutor, but I need to wait for the nightly run to see if it changes anything.
Cheers.
This is SQL Server, I guess? I can confirm the exact same behavior.
I believe the SELECT INTO wrapper is because FME is trying to be smart and induce the schema of the resulting data. But when it fails (e.g. when adding a simple ORDER BY in your query), you get those warnings in the log file. Depending on the number of trigger features, it can really fill up your log, and it tends to make customers question what you're doing ;-)
As far as I know, there is currently no way of disabling this behavior, unfortunately.
I've been thinking about flagging this to Safe for a while now actually, so thanks for the push!
This is SQL Server, I guess? I can confirm the exact same behavior.
I believe the SELECT INTO wrapper is because FME is trying to be smart and induce the schema of the resulting data. But when it fails (e.g. when adding a simple ORDER BY in your query), you get those warnings in the log file. Depending on the number of trigger features, it can really fill up your log, and it tends to make customers question what you're doing ;-)
As far as I know, there is currently no way of disabling this behavior, unfortunately.
I've been thinking about flagging this to Safe for a while now actually, so thanks for the push!
It's actually FME Desktop, although I guess FME Server would exhibit the same problem.
Getting the schema could just as easily be done with a non-intrusive SELECT * FROM (...) x WHERE 0=1
No need to involve an INTO clause, which requires extended permissions.
Or maybe, if a target table is absolutely required, use a normal temporary table like this: SELECT * INTO #tempTableName FROM ...
I haven't verified the latter construct, but it's much more acceptable with the DBA, I'm sure.
It's actually FME Desktop, although I guess FME Server would exhibit the same problem.
Getting the schema could just as easily be done with a non-intrusive SELECT * FROM (...) x WHERE 0=1
No need to involve an INTO clause, which requires extended permissions.
Or maybe, if a target table is absolutely required, use a normal temporary table like this: SELECT * INTO #tempTableName FROM ...
I haven't verified the latter construct, but it's much more acceptable with the DBA, I'm sure.
I'm not talking about FME Desktop or Server, the question was which database backend is being used here.
I trust that the database developers at Safe had their reasons for implementing it that way, back in the day. That said, I'm sure it can be improved upon.
It's actually FME Desktop, although I guess FME Server would exhibit the same problem.
Getting the schema could just as easily be done with a non-intrusive SELECT * FROM (...) x WHERE 0=1
No need to involve an INTO clause, which requires extended permissions.
Or maybe, if a target table is absolutely required, use a normal temporary table like this: SELECT * INTO #tempTableName FROM ...
I haven't verified the latter construct, but it's much more acceptable with the DBA, I'm sure.
Hi David.
Ah, misread that, sorry.
It's indeed SQL Server 2016. And I'm using the MSSQL_ADO reader.
I'm using SQLCreator version 3, which seems to be the latest version.
But I assume that it may be a problem with all database types, unless this particular implementation is specific to this particular reader?
It's actually FME Desktop, although I guess FME Server would exhibit the same problem.
Getting the schema could just as easily be done with a non-intrusive SELECT * FROM (...) x WHERE 0=1
No need to involve an INTO clause, which requires extended permissions.
Or maybe, if a target table is absolutely required, use a normal temporary table like this: SELECT * INTO #tempTableName FROM ...
I haven't verified the latter construct, but it's much more acceptable with the DBA, I'm sure.
Good question, but I'm not sure. I suspect this behavior is specific to SQL Server, I haven't seen it with e.g. Oracle.
Addendum 2.
The SQLExecutor transformer has a setting called "Application Intent", set initially to ReadWrite, but may be set to ReadOnly. This setting is not available in SQLCreator.
Also, the parameter cannot be changed in the GUI. I did manage to change the parameter value via an unpublished parameter. Why is this so cumbersomly implemented?
I've now replaced the SQLCreator with a Creator and a SQLExecutor, but I need to wait for the nightly run to see if it changes anything.
Cheers.
Revisting this issue. Yes, the SQLExecutor worked, so using it and possibly setting the "Application Intent" to ReadOnly seemed to do the trick.