Re: Soft coding SQL based on parameter file

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 06/02/2017
01:52:09 PM:

If you are on V7R1 or forward use %SccanRpl. Will make your life so much
easier.

...snip...

Especially handy for quotes

dcl-c QUOTE '''';

Statement = 'Select <<FIELD1>>,
<<FIELD2>>
From <<File>>
Where <<FIELD3>> = Cast(? As Char(10))' And
<<FIELD4>> = <<Q>>Y<<Q>>)'

Statement = %ScanRpl('<<Q>>',QUOTE: Statement);

I have a service procedure I wrote to handle this so that it can
also handle imbedded quotes at the same time. Handy not just for SQL. ;-)

syscmdtxt = CvtDbfXL_Cmd; // create stream file attachment
syscmdtxt = GenUtl_ScanAndReplace( '&WRKF': syscmdtxt: 'IMLFEXWT' );
syscmdtxt = GenUtl_ScanAndReplace( '&STMF': syscmdtxt
: GenUtl_MakeQuotedString(CM_Path));
if %len(syscmdtxt) = %len(syscmdtxt: *max); // if variable is full
GenUtl_Diagnose( 'WSE9897': 'System command variable is full.'
: 'WSMSGF'); // issue diagnostic msg
endif;
callp IBMAPI_ExecSysCmd(syscmdtxt: %len(syscmdtxt));

Sincerely,

Dave Clark