RE: Dynamic SQL - what am I doing wrong

I'm not sure I follow...
Are you saying I should have "INTO ??" instead of "INTO ?"
In that case, is it best to use a DS instead?

It is working (retrieving two values). My issue was that the variable SQLSTMT was not a valid statement due to a single-quote in the data.

Now I'm doing a %scanrepl() to change a single quote to 2 single quotes

I think Charles was had the answer... I'm checking the SQLCOD after the PREPARE statement.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Monday, July 29, 2024 12:09 PM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Dynamic SQL - what am I doing wrong

You want to return 2 values, but you have only a single placeholder (?)!
You need a place holder for each variable

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization - Education - Consulting on IBM i Database and Software Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson) "Learning is experience ... everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Monday, 29 July 2024 16:01
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Dynamic SQL - what am I doing wrong

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.


--
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.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231