You do not get an error, but a warning, because you selected more columns
then you want to receive.
Each column you select should have its own place holder (assumed you want to
receive single values).
I'm not quite sure about the indicator variables, but IMHO you also should
include a place holder for each of them.
If you want to receive the data in a data structure a single place holder
would be enough (may be followed by a second one for an array with indicator
variables.
Your statement should look like this
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 ? ?, ? ?';
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 19:45
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: 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
--
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.
midrange.com
