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