Rick,
Your result set has 6 fields, but 2 of them are "not null". That means only
4 fields are null capable.
I think your null indicator array should only have 4 entries, not 6.
// Null indicator array for result set
d NullIndDS ds Dim(340) Qualified
d Indicator 5i 0 Dim(4)
I'm not where I can test this out, so good luck!
Richard
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Wednesday, April 14, 2010 10:15 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL null indicators when not all columns are null capable
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.
****************************************************************************
********
This footnote confirms that this email message has been scanned by PineApp
Mail-SeCure for the presence of malicious code, vandals & computer viruses.
****************************************************************************
********
midrange.com
