RE: SQL null indicator variable in DS -- RPG400-L

I have a VERY strong aversion to using select * in embedded SQL, in ANY language...

I have a very strong preference for application-defined structured data, meaning that when I use SQL to read data, I'm fetching into a data-structure (defined in /copy member) describing the subset of data in context for the procedure.

A data definition might be CustomerBasic DS with subfields like Customer number, Name, billing address, primary contact phone, etc... Data is represented "in business context"... I'd rather my application NEVER depend on the physical organization of the data, so that I have freedom to restructure the physical data with minimal impact to the dependent business logic. SQL work wonderfully for this, and is quickly becoming my preferred design approach.

JMO,
-Eric DeLong

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Briggs, Trevor (TBriggs2)
Sent: Friday, May 09, 2014 12:39 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: SQL null indicator variable in DS

That's a good point, Alan, if you specify columns on the select. But then, surely you have to specify the format of the input data structure and cannot just use extname().

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Friday, May 09, 2014 1:25 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL null indicator variable in DS

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.

On Fri, May 9, 2014 at 11:12 AM, Briggs, Trevor (TBriggs2) < TBriggs2@xxxxxxxxxxx> wrote:

This brings to light the main problem with null fields (IMHO) - you

have

to define the null indicators one way or another, therefore you lose

major advantage of SQL, which is disassociating your program from the
database layout. If you use a null map like Eric described, in your
program you have to test, say, element 6 in your map to see if the

Order

Number is null - so what happens when someone makes a table change and
Order Number isn't the 6th column anymore? Much safer to do a COALESCE
in your view and prevent those nulls from getting to your program.

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of

Gary

Thompson
Sent: Friday, May 09, 2014 12:57 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: SQL null indicator variable in DS

Eric is correct.
Here is an older RPG example showing two DS; one for data and one for
null indicators for those columns which may return null:

* Data structure to accept result from SQL cursor C1
*
I#DSW2 DS
I 1 2 #ZDIV
I 3 4 #ZLOC
I 5 34 #ZLOCN
I 35 36 #ZSEQN
I 37 86 #ZVOUT
I 87 146 #ZSEQP
I 147 186 #ZVART
I 187 189 #ZTIME
I 190 1930#ZCENT
I 194 195 #ZPER
I 196 2104#ZGLUT
I 211 2232#ZNETT
I 224 2412#ZMGNT
I 242 2564#ZGLUL
I 257 2692#ZNETL
I 270 2872#ZMGNL
I*
I* DS DEFINING FIELDNAMES FOR EVERY POSSIBLE NULL COLUMN
I*
I#IS DS
I B 1 120$IS
I B 1 20$ZGLUL
I B 3 40$ZNETL
I B 5 60$ZMGNL
I B 7 80$ZGLUT
I B 9 100$ZNETT
I B 11 120$ZMGNT
I*
C/EXEC SQL OPEN C1
C/END-EXEC
C/EXEC SQL FETCH C1 INTO :#ZDIV ,
C+ :#ZLOC ,
C+ :#ZLOCN,
C+ :#ZSEQN,
C+ :#ZVOUT,
C+ :#ZSEQP,
C+ :#ZVART,
C+ :#ZTIME,
C+ :#ZCENT,
C+ :#ZPER ,
C+ :#ZGLUT: $ZGLUT,
C+ :#ZNETT: $ZNETT,
C+ :#ZMGNT: $ZMGNT,
C+ :#ZGLUL: $ZGLUL,
C+ :#ZNETL: $ZNETL,
C+ :#ZMGNL: $ZMGNL
C/END-EXEC
C SQLCOD DOWEQ*ZERO
C* some code here
C* another FETCH C1 here
*
C ENDDO

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
DeLong, Eric
Sent: Friday, May 09, 2014 10:40 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: SQL null indicator variable in DS

I tend to stay away from null in the database, since it mostly just
confuses the $#!+ out of the developers following after...

However, I seem to think that, for a fetch into DS subfields, that the
null map must be an array of null indicators. The syntax is something
like this:

Exec SQL Fetch Cursor1 Into :myData :myDataNullMap;

I dabbled with null in database a while back and found the level of
effort to develop robust implementations, develop a training course
and/or example code for self-study, modifications to shop standards

and

practices, etc. was above the tolerance levels of those affected...

so it goes.

I'm sure I'll get an earful from the "null is good" evangelists, and
while I can agree in spirit that it can be beneficial or whatever,

I'll

stick with COALESCE to convert null values into the equivalent of
INZ(*DFT)...

Hopefully, someone with more practical experience can correct and
clarify the null map, and how to construct that piece...

Hth,
-Eric DeLong

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Justin Taylor
Sent: Friday, May 09, 2014 10:26 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL null indicator variable in DS

I have an SQLRPGLE program that's accessing a table with a column that
allows nulls. I'm fetching into a DS that's defined via a copybook.
The DS includes a 3i 0 field for the null indicator variable. The

FETCH

NEXT lists each field individually. The SQL precompiler accepts all

the DS fields except the one for the null indicator variable. If I

use

a locally defined field, it'll take that.

Has anyone else seen this? Just a quirk in the SQL precompiler?
--
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.

--
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.

--
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.

************************************************************************
************************************************************************
************************************************************

This message originates from Lincare Holdings Inc. It contains

information

which may be confidential or privileged and is intended only for the
individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use

the

contents of this message.
All personal messages express views solely of the sender, which are

not to

be attributed to Lincare Holdings Inc., and may not be copied or
distributed without this disclaimer.
If you received this message in error, please notify us immediately at
MailAdmin@xxxxxxxxxxx or (800) 284-2006.

--
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.

--
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.

************************************************************************************************************************************************************************************************************
This message originates from Lincare Holdings Inc. It contains information which may be confidential or privileged and is intended only for the individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use the contents of this message.
All personal messages express views solely of the sender, which are not to be attributed to Lincare Holdings Inc., and may not be copied or distributed without this disclaimer.
If you received this message in error, please notify us immediately at MailAdmin@xxxxxxxxxxx or (800) 284-2006.
************************************************************************************************************************************************************************************************************

--
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.