Re: SQLRPGLE Update Dynamic Filename using WHERE CURRENT OF

Can't have a variable table name with static SQL...

DCL-S UpdateSQL Like(StdLTxtVar)
Inz('UPDATE &1 set token = ? where current of
C1');

UpdateSQL = %ScanRpl('&1' :@FileName :UpdateSQL);

exec SQL
prepare U1 from :updateSQL;

exec SQL
execute U1 using :wrkToken;


Side note, S1, C1, and U1 are a poor choice for production code....

You can run into trouble as you do more with SQL....you might end up with
multiple programs using the same name intermingled...

Best practice is to have a reasonably unique name.

Charles




On Wed, Apr 18, 2018 at 11:49 AM, Stephen Coyle <stephenfcoyle@xxxxxxxxx>
wrote:

HI All,

Need some help with this as I cannot find an example.

I have a filename that is input as a parm.
I prepare the sql statement with the variable filename, then declare, open,
fetch/update loop and close.
Now when I update the current record all samples I have seen are using a
hard coded filename.
I believe I could get around with a sql execute immediate but would like to
stick with WHERE CURRENT OF.

Any help appreciated.
Here are the relevant bits....It's the 'Update @FileName' below that throws
me off.

DCL-S SelectSQL Like(StdLTxtVar)
Inz('Select keyValues from &1 FOR UPDATE OF
TOKEN');

// Replace marker with file name
SelectSQL = %ScanRpl('&1' :@FileName :SelectSQL);

exec sql
Prepare S1 from :SelectSQL;
exec sql
declare C1 cursor for S1;
exec sql
open C1;
exec sql
fetch next from C1 into :KeyValues;

WrkToken = xmlTransaction(keyValues :Mode :@LoadName :groupID);

exec sql
Update @FileName
Set Token = :WrkToken
WHERE CURRENT OF C1;

Thanks in advance...
- Steve
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD