I am trying to EXPORT the following SelectStmt SelectStmt = 'SELECT 'GRANT '||rolename||' TO USERDAMIN WITH ADMIN OPTION;' FROM dbc.roleinfo;',
this is failing because the inclusion of the single quote within the statement is not recognised by the syntax. How can this be achieved?
We strip newlines from within the requests so it should not be a problem.
(I have seen SELECT requests in TPT scripts that span pages.)
I'm using TPT 14.00.00.03, both on Windows and AIX. I am exporting data using $EXPORT template.
It seems that escaping single quotes by doubling them does not work for SelectStmt. I am not able to provide any string literal in SELECT.
I am specyfying in a script (just a sample, that anyone can exercise):
Set SelectStmt = 'SELECT DatabaseName,TableName,TableKind from dbc.tables where databasename=''DBC'';';
and the TPT gives me an error message coming from RDBMS:
TPT_INFRA: TPT05014: RDBMS error 3706: Syntax error: expected something between a string or a Unicode character literal and the word 'DBC'.
When I look into DBQL, I see the same error message and the SQL that I see in QueryText column contains doubled single quotes:
SELECT DatabaseName,TableName,TableKind from dbc.tables where databasename=''DBC'';
From old forum posts it seems that it worked for previous versions.
I have never seen the escaping fail.
Therefore, please upgrade to the latest version of 14.00 (efix #3 is a very old version of the 14.0 release) and see if that fixes your problem.