Re: Dynamic SQL - what am I doing wrong

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Check SqlState/SqlCode after the PREPARE...

Charles



On Mon, Jul 29, 2024 at 8:01 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> I have the following statement defined in a procedure.  It works just fine
> when the statement is valid.
>
> However, when there's an error in the statement the SqlCod (and SqlState)
> are 0 and "00000" respectively.
> (the "tagValues" contained an apostrophe)
> To make things worse, the values in my output parameters ouEntNbr and
> ouShprNbr are from the last time the procedure was called.
> I noticed in debug that SqlErr3 is 0
>
> What is the proper way to check this?
>
>
>     ... stuff here
>     Clear ouEntNbr;
>     Clear ouShprNbr;
>     sqlStmt = 'Values(Select ent_nbr, trim(shpr_nbr) from SHOPTAGS where
> site_id = ' +
>                sq + inSiteId + sq + ' and tag_val in (' + tagValues + ')' +
>                ' and active = ' + sq + 'Y' + sq +
>                ' Fetch First Row Only) Into ?';
>
>     Exec Sql Prepare dyn_sql From :sqlStmt;
>     Exec Sql Execute dyn_sql using :ouEntNbr :entInd, :ouShprNbr :shprInd;
>
>     if SqlCod <> 0;
>       return *off;
>     endif;
>
>     return *on;
>
>
>
>
>
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.