Re: Dynamic SQL - what am I doing wrong

[Richard Schoen <richard@xxxxxxxxxxxxxxxxx>]

Also DSPJOBLOG is useful to see the error messages. 

Not too long ago someone posted some example code for getting the actual message info into an RPG program. 

Anyone.....

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
------------------------------

message: 3
date: Mon, 29 Jul 2024 08:27:52 -0600
from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: Dynamic SQL - what am I doing wrong

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@totalbizfulfillment.c