All Forums Tools
butchec 10 posts Joined 07/11
03 Oct 2011
TPT SelectStmt

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?

logc 34 posts Joined 09/05
04 Oct 2011

Use two single quotes as follows:

SelectStmt = 'SELECT ''GRANT'' ||rolename|| ''TO USERDAMIN WITH ADMIN OPTION;'' FROM dbc.roleinfo;'

Dhana 1 post Joined 11/09
11 Dec 2013

Has anyone tried multi line sqls in SELECTSTMT.? an working example would be grateful.

feinholz 1234 posts Joined 05/08
11 Dec 2013

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.)


jacek.adamowicz 5 posts Joined 04/11
28 Nov 2014

I'm using TPT, 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. 

feinholz 1234 posts Joined 05/08
01 Dec 2014

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.


You must sign in to leave a comment.