Re: Embedded SQL and host variables...

OPNQRYF And the cycle baby! ! ! !

Best tool for the job...

Mark Walter
Paragon Consulting Services, Inc.
IBM Certified System i Specialist
717-764-7909 Ext. 26
mwalter@xxxxxxxxxxxxxxx
www.paragon-csi.com



"Roger Vicker, CCP" <rv-tech@xxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/20/2008 11:43 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG400 <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Embedded SQL and host variables...






I thought I was being "modern" and making a new report app use embedded SQL but I am about to junk it and go to OPNQRYF that I know works.

I have a program that has one file only and that is the external DDS printer file.

When I coded the FETCH Into phrase listing the host variables the compiler gave me an SQL0312 for each stating that they were not defined or not usable.

After some archive plundering I found a hint that the precompiler didn't understand printer files so I tried putting the fields in a named DS and using that on the FETCH. The program then compiled but upon execution I get an SQLSTT of '01503' and the job log has a SQL0030 about not enough host variables for the result values.

Another archive dive hinted that I might need the host indicators. So I added the DS with five integer fields and added it to the FETCH. Back to the SQL0312 and complaining about the host indicator DS.

I even went back to naming the fields on the FETCH (the form I prefer and with the DS still in place to declare them) and adding individual indicator fields with them. That returned to the SQL0312 complaining about the variables (but not the indicators).

Next archive dive found someone that used an array for the indicators instead of the DS. Tried that and it would compile but then on execution I would get the ole MCH1210 target too small. BTW the indicators are all filed with 16448 and SQLSTT is '01503'.


<Background>
V5R4 all PTF as of 2/11/2008

CustNum happens to be the only numeric field and is the same as in the database.

d RecIn ds

d CustNum

d CFNameRSVP

d CLNameRSVP

d CDelegate

d VoteDstDsc

d RecInNullMap ds

d RecInNullAr 5i 0 dim(5) inz


EXEC SQL
DECLARE C1 SCROLL CURSOR FOR
SELECT UBD014P.CUSTNUM, UBD014P.CFNameRSVP, UBD014P.CLNameRSVP,
UBD014P.CDelegate, UBD716P.VoteDstDsc
FROM UBD001P AS UBD001P, UBD014P AS UBD014P,
UBD716P AS UBD716P
WHERE UBD014P.CUSTNUM = UBD001P.CUSTNUM
AND UBD001P.CVOTEDST = UBD716P.VOTEDST
and UBD001P.CUSSTS = 'A'
and UBD014P.CRSVP = 'Y'
ORDER BY UBD716P.VoteDstDsc ASC, UBD014P.CLNameRSVP ASC,
UBD014P.CFNameRSVP ASC
FOR READ ONLY;

exec SQL
Open C1;

<snipped>

exec SQL
Fetch next from c1 into :RecIn;
// this compiles

<alternate>

exec SQL
Fetch next from c1 into :RecIn :RecInNullMap;
// this complains the RecInNullMap is not usable

<alternate>

exec SQL
Fetch next from c1 into :RecIn :RecInNullAr;
// this compiles but dies on execution

</Background>

There has GOT to be a simpler way that WORKS! Oh yeah, OPNQRYF.

Roger Vicker, CCP