Stored Procedure escape character 1

[markgargan]

Hey folks,


I've created a cursor before for a select statement.
However the sql in the select statement never had to have a 'where' clause with it and as such I'm having terrible trouble
escaping the single-quote (') character

e.g. Before :-

SET countSQL ='SELECT Count(*) FROM ' || vTableName;

This is fine as it creates a string variable called countSQL
with the following SQL
SELECT Count(*) from myTable
for example

however now I need to perform the following sql

set cardSQL ='SELECT CARD from syscat.tables where tabname=' || vTableName;

but this produces
SELECT CARD from syscat.tables where tabname=myTable

however this fails in the procedure because there's no single-quotes surrounding myTable

I've tried escaping this using the backslash (\) char but it won't even compile for me.

i.e.
set cardSQL ='SELECT CARD from syscat.tables where tabname=\' ' || vTableName || ' \' ';

but all I get is the following error

DB2ADMIN.DBCHANGE: 60: [IBM][CLI Driver][DB2/NT] SQL0007N The character "\" following "|| vTableName || '" is not valid. LINE NUMBER=60. SQLSTATE=42601

Does anyone know how to escape a string in a stored procedure?

Thanks,
Mark

 

[MarcLodge]

Mark,
I've not tried this, but howabout something like:
Set vQuote = '''
set cardSQL ='SELECT CARD from syscat.tables where tabname=' || vQuote || vTableName || vQuote;

 

[markgargan]

Hey Marc,

Thanks for getting back to me.

I got it to work by putting two single quotes back to back within the original string hence

SET cardSQL ='SELECT CARD FROM syscat.tables where tabname=''' || vTableName || '''' ;

gives my back
SELECT CARD FROM syscat.tables where tabname='myTable'

I scoured the help for this and nothing. It was just pure chance that I thought of it. Kinda po'ed with that.

Thanks for your help though.

I'll give it a try... Hold on...
Afraid i couldn't even compile it cos the unbalanced quotes
i.e. there' being only three in
Set vQuote = '''
meant it regarded the rest of the procedure as a string down to the next single quote.

Thanks Marc,
Mark.

 

[MarcLodge]

Glad to see you've got it working.

 

[markgargan]

THanks Marc,

Could I ask you maybe to have a quick look at this problem if
you have the chance?
I'm trying to call a RUNSTATS command from within a stored procedure.
Do you konw if it's possible?

The command works fine from the command editor but not so good from the stored procedure.

SET runstatsSQL ='RUNSTATS ON TABLE myTable';
PREPARE runstatsStmt from runstatsSQL;
EXECUTE runstatsStmt;

Thanks,
Mark.

 

[stevexff]

SQL standard for escaping quotes in a string literal is to double them, e.g.

...SET Name = 'O''Reilly'