I posted this to the RPG list also as it relates to RPG but because it is SQL related I am posting here because there are more SQL related questions on this list.
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
