I couldn't get it to work either.
Data structures and indicator arrays work with single row fetch.
Array Data structures and MODs work with multiple row fetches, but I didn't
succeed in using either Array Data Structures or MODS for indicator
variables with multiple row fetches.
IMHO it is a bug and should be reported.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"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!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Rick.Chevalier@xxxxxxxxxxxxxxx
Gesendet: Wednesday, 14. April 2010 15:17
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL null indicators when not all columns are null capable
I'm working on a program to fetch records from a table where some of the
fields are defined as null and others defined as not null. I am doing a
multiple row fetch and what happens is the first row returned is fine but
every row after that is shifted four bytes as if there are four bytes
missing from the buffer. I don't think it is a coincidence that there are
two non-null capable fields in the result row and the indicator size is two
bytes for a total of four bytes. I have tried changing the size of the
indicator array but it didn't help. Before the table was changed to allow
nulls this same fetch was returning the records correctly.
I have read several posts in the archives but none seemed to address
returning a mixture of null capable fields and non-null capable fields. I
am still working through the manuals looking for information on mixed return
sets but haven't found anything yet. RPG examples would help.
I'm posting the relevant code for reference. Any help would be greatly
appreciated. We are at V5R4 and PTF's should be current.
-- Create table
Create table #830/PSTFBCHR (
Shipping_Number for ShipNbr Char(20) not null,
Batch_Number for BatchNbr Numeric(9,0) not null,
Batch_Status for BatchSts Char(1) not null with default 'A'
Constraint PSTFBCH_Record_Status
Check(Batch_Status in ('A', 'D')),
Document_Type for DocType Numeric(3,0),
Created_By for CreatedBy Char(18),
Submitted_By for SubmitedBy Char(10),
Received_Back for RcvdBack Date,
Add_User for AddUser Char(18) not null with default
USER,
Add_Timestamp for AddTMSP Timestamp not null
with default CURRENT_TIMESTAMP,
Add_Procedure for AddProc Char(64) not null,
Chg_User for ChgUser Char(18),
Chg_Timestamp for ChgTMSP Timestamp,
Chg_Procedure for ChgProc Char(64),
Dlt_User for DltUser Char(18),
Dlt_Timestamp for DltTMSP Timestamp,
Dlt_Procedure for DltProc Char(64),
// Format of returned SQL record.
d ResultSet ds Dim(340) Qualified
d BatchNumber Like(BatchNbr) Not null
capable
d DocumentType Like(DocType)
d CreateTmsp Like(AddTmsp) Not null
capable
d CreatedByUser Like(CreatedBy)
d SubmittedByUser...
d Like(SubmitedBy)
d ReceivedBack Like(RcvdBack)
// Null indicator array for result set
d NullIndDS ds Dim(340) Qualified
d Indicator 5i 0 Dim(6)
// Declare cursor for SQL statement
Exec SQL
Declare C1 Cursor for
Select BatchNbr, DocType, AddTMSP, CreatedBy, SubmitedBy, RcvdBack
From PSTFBCH
Where ShipNbr = :ShippingBox;
// Retrieve first set of records
Exec SQL
Fetch from C1 for :NbrRows rows into :ResultSet :NullIndDS;
Rick Chevalier
IT Software Solutions - Loan Servicing
[
http://www.americredit.com/images/acf_email_sig.gif]
817-525-7178 (w)
rick.chevalier@xxxxxxxxxxxxxxx<mailto:rick.chevalier@xxxxxxxxxxxxxxx>
________________________________
Privileged and Confidential. This e-mail, and any attachments there to, is
intended only for use by the addressee(s) named herein and may contain
privileged or confidential information. If you have received this e-mail in
error, please notify me immediately by a return e-mail and delete this
e-mail. You are hereby notified that any dissemination, distribution or
copying of this e-mail and/or any attachments thereto, is strictly
prohibited.
midrange.com
