Re: SQLRPGLE Update Dynamic Filename using WHERE CURRENT OF

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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