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

We don't bring in the "whole database" - every program has a
program-specific view that defines only the data the program needs, so
the extname() references this view, not the base table. BTW, this a
standard that was implemented long before I joined the company, so if
it's a crazy approach, then you can't blame me! :-)

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:52 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL null indicator variable in DS

First of all why would you want to do that (Specify extname) and bring
the
whole data base in? The whole concept of SQL is to bring into the
program
only what you need but even if you use the extname it is still based on
the
position in the data structure at time of compile. The compiler just use
the extname to build the internal information that says what the record
looked like at compile them. So If you went in and added a new field
nothing would happen in your program unless you recompile the program
which
is, of course, a very good reason to avoid using extname.

I have been working on a program to build SQL data structures but have
not
finished it. Bottom line, bring into a program only what you need. Never
anything more. Doing that just make the life of any programmer following
you easier. The know exactly what is being brought in the program. Not
trying to guess which field you are using.

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

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

referenced, you can avoid having to use the array. You can reference

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

a

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

An

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

of

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

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.