What's going on? Nothing except SQL syntax requirements...
From memory, the ONLY way to access null flags is to use TWO host fields for EACH COLUMN being referenced. In SQL, we use comma to separate column list items, so that means your fetch needs to be one of two formats:
Fetch Csr1 into :FIELD1 :ISNULL1 , :FIELD2 :ISNULL2 , :FIELD3 :ISNULL3 ...
- or -
Fetch Csr1 into :FieldsDS :FieldNull
Style one gets very verbose if many columns are selected, whereas style two is concise and readable. In both cases, the null field(s) must be specified AFTER the column field, no comma until AFTER the null flag field...
-Eric DeLong
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck Calabro
Sent: Friday, May 09, 2014 1:22 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL null indicator variable in DS
On 5/9/2014 1:25 PM, Alan Campin wrote:
Sorry Trevor, that is not correct. the array element is not based on
position in the database. It is based on the position in the query.
For example, you have a table with 100 fields but you only put the
names of three of them in the SQL Select statement. If you want to
check the second element of the null array, you would check the 2
position no matter what the position of the field in the physical
table. In the document that I referenced, you can avoid having to use
the array. You can reference to the array elements by name.
I advised Steve to share so I might as well take my own advice. This is probably not the best example; it was a very early proof of concept of an RPG external stored procedure that could handle nulls. The takeaway of this example is that I can't easily remember if NULLMAP(2) refers to the customer name or address or whatever. So I create a set of meaningful names that I overlay over the null map. Full confession: I stole the idea from Jon Paris who demonstrated a similar train of thought regarding RPG indicators.
* CREATE PROCEDURE buck/sp_test
* (in text varchar(30),
* out message varchar(30))
* LANGUAGE RPGLE
* NOT DETERMINISTIC READS SQL DATA
* CALLED ON NULL INPUT
* EXTERNAL NAME buck/sp_test
* PARAMETER STYLE GENERAL WITH NULLS
* call buck/sp_test('some message', '')
dsp_test pr
d text 30 varying
d message 30 varying
d nullMap_in like(nullMapIn)
dsp_test pi
d text 30 varying
d message 30 varying
d nullMap_in like(nullMapIn)
// null map is a structure that corresponds one-to-one
// to each input parm
dnullMapIn ds
d text_null like(NULLIND)
d message_null like(NULLIND)
d NULLIND s 5i 0 inz(-1)
c/free
nullMapIn = nullMap_in;
message = '';
if message_null <> NULLIND;
if text_null <> NULLIND;
message = text;
else;
// message = 'input is null';
message = 'is null';
message_null = NULLIND;
endif;
endif;
nullMap_in = nullMapIn;
*inlr = *on;
/end-free
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
