Re: RUNSQL and apostrophe problem

Hi Luis,

It is a good suggestion and one I've seen before but it took a while for me
to get it right. I combined Justin's suggestion of using 4 apostrophes and
yours to use a variable and arrived at the following:

DCL VAR(&AP) TYPE(*CHAR) LEN(2) VALUE('''''') +
/* two apostrophes */

RUNSQL SQL('update qclsrc set srcdta = ''PUT ' *CAT +
&AP *CAT %TRIM(&FILE_NAME) *CAT &AP +
*CAT ''' where upper(left(srcdta,3)) = +
''PUT''') COMMIT(*NC) NAMING(*SYS)

Thank you for your help.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 3 February 2016 at 12:07, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Glen,

You could use a CL var that contains your SQL instruction, and build it
inserting the apostrophes where appropiate. Something like:

dcl &SQL_ST *char 256
dcl &ap *char 01 value('''') /* there are four(4) single quotes (') */

And then use an instruction like:

CHGVAR &SQL_ST ('PUT' *bcat &ap *tcat &log_fname *tcat &ap *bcat 'where
upper(left(srcdta,3)) =' *bcat &ap *tcat 'PUT' *tcat &ap)

A little cumbersome, but is should work...

Regards,


Luis Rodriguez

--


On 3 February 2016 at 12:23, Glenn Gundermann <glenn.gundermann@xxxxxxxxx>
wrote:

Hi all,

I'm having a problem with RUNSQL along with apostrophes in my CL program.

I have a CL source member with one record containing PUT. I want to
replace it with PUT followed by the file name surrounded by appostrophes.

OVRDBF FILE(QCLSRC) TOFILE(GLENNLIB/QCLSRC) MBR(FTPSRCMBR) OVRSCOPE(*JOB)

The following will work in a STRSQL session:

update qclsrc
set srcdta = 'PUT ''long file name 2016-02-03 11.40.02.csv'''
where upper(left(srcdta,3)) = 'PUT'

In the CL program, I can get it to work with quotes. It's hard to see

but

I have a quote " before long and after csv. All others are apostrophes

'.

RUNSQL SQL('update qclsrc set srcdta = ''PUT "long file name 2016-02-03
11.40.02.csv"''
where upper(left(srcdta,3)) = ''PUT''')
COMMIT(*NC)
NAMING(*SYS)

I can't get it to work in a CL program replacing the quotes with
apostrophes.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144