Hi
I keep little snippets of examples and build from them. Maybe, this will help you. (And hopefully, the smarter people on this list can review the code and point out how to improve it. That would be greatly appreciated.)
* ********************************************************************
* 24.) Read & Write Null-Capable Fields
*******************************************************************
* Stand Alone Fields Used To 'Flag" fields with null values. *
*******************************************************************
D NullCdBudgetDt S 5i 0
/FREE
// To Read a Null Capable field, cdBudgetDT BE SURE TO use
// an indicator variable.
Exec Sql Declare cursorCd CURSOR For
Select cdOrg1, cdOrg2, cdOrg3, cdBudgetDt, cdAdptd
From CaDist
Where CdAgid# = :Agid#;
Exec Sql Open cursorCd;
// Read the Null Capable field, cdBudgetDT
// (NB there is no comma betweem cdBudgetdt & nullCdBudgetDt)
Exec SQL Fetch from cursorCd Into
:cdOrg1, :cdOrg2, :cdOrg3,
:CdBudgetDt :nullCdBudgetDt, :cdAdptd;
// **********************************************************
// To Write a Null Capable field, cdBudgetDT BE SURE TO use
// an indicator variable & set it to -1 for null or 1 for not null.
%nullind(CdBudgetDt) = *on;
nullCdBudgetDt = -1;
Exec Sql Update CaDist
set cdBudgetDt = :CdBudgetDt :nullCdBudgetDt,
where CdAgid# = :Agid# ;
From: <Rick.Chevalier@xxxxxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Date: Wednesday, April 14, 2010 9:46 AM
Subject: 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
