Are you using the SQLExecutor, maybe? If so, you are responsible for escaping the statements yourself, depending on the syntax of your database back end, e.g.
select * from "RSF-Aflob"."MyTable"
The regular readers and writers should be able to handle this automatically. If not, I would expect it to be a case for Safe support.
Are you using the SQLExecutor, maybe? If so, you are responsible for escaping the statements yourself, depending on the syntax of your database back end, e.g.
select * from "RSF-Aflob"."MyTable"
The regular readers and writers should be able to handle this automatically. If not, I would expect it to be a case for Safe support.
That's not really proper escaping, as escaping always should be native, since ANSI support can be turned off.
It seems that using or not using "bulk mode" makes a difference.
If turned off, proper escaping is used. Bulk mode seems to be the culprit.
That's not really proper escaping, as escaping always should be native, since ANSI support can be turned off.
I completely agree that it's not a very good solution, which is why I proposed the idea to support bind variables more than 4 years ago: https://knowledge.safe.com/idea/23829/sqlexecutor-support-for-bind-variables.html
Not only would it be much more secure, but it would also come with performance gains.
Still not implemented :-(
It seems that using or not using "bulk mode" makes a difference.
If turned off, proper escaping is used. Bulk mode seems to be the culprit.
That's interesting, thanks for sharing that observation.
@lifalin2016 FME is somewhat inconsistent in the quoting of database table and attribute names - depending on the database. But the FME SQL Server writer does (or should) quote the SQL. I've attached a simple example and below are the table create for bulk mode Off an On:
CREATE TABLE [Special%^&-Two] ([sql] varchar(10), [second] integer, [another-name] integer)
The inserts are slightly different for Bulk Mode = Yes but still quoted:
exec sp_executesql N'INSERT INTO [Special%^&-Two] ([sql], [second], [another-name]) VALUES (@P1, @P2, @P3)',N'@P1 varchar(10),@P2 int,@P3 int','123',1234,9876
I copied these from the event SQL Server Management Studio event log.
As @david_r mentions, for SQLExecutor/Creator and any other custom SQL, it's you're responsibility to quote the values - depending on the database. For SQL Server, FME will quote table names and attributes, if you select them from the lists:
SELECT [another-name],[second],[sql] FROM dbo.[Special%^&-Two];
Can you include a small example workspace where this is not working for you? We can then try and address that.
Example (FME 2020): specialcharacters.fmw
@lifalin2016 FME is somewhat inconsistent in the quoting of database table and attribute names - depending on the database. But the FME SQL Server writer does (or should) quote the SQL. I've attached a simple example and below are the table create for bulk mode Off an On:
CREATE TABLE [Special%^&-Two] ([sql] varchar(10), [second] integer, [another-name] integer)
The inserts are slightly different for Bulk Mode = Yes but still quoted:
exec sp_executesql N'INSERT INTO [Special%^&-Two] ([sql], [second], [another-name]) VALUES (@P1, @P2, @P3)',N'@P1 varchar(10),@P2 int,@P3 int','123',1234,9876
I copied these from the event SQL Server Management Studio event log.
As @david_r mentions, for SQLExecutor/Creator and any other custom SQL, it's you're responsibility to quote the values - depending on the database. For SQL Server, FME will quote table names and attributes, if you select them from the lists:
SELECT [another-name],[second],[sql] FROM dbo.[Special%^&-Two];
Can you include a small example workspace where this is not working for you? We can then try and address that.
Example (FME 2020): specialcharacters.fmw
Hi Mark.
The error occurred in the writer itself, not the SQL-transformers, where setting an unescaped schema name with a dash included in "Table Qualifier" threw an error.
As I mentioned elsewhere, it seems like non-bulk mode does escape it, but bulk mode doesn't.
I'll have a look at submitting some simple proof of concept.
I did a little screen dump story of what I'm seeing in the 2020.0 import schema wizard.
SELECTING A TABLE IN AN UNESCAPED SCHEMA IN THE IMPORT WIZARD.pdf
