× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



My mistake. That approach can be a good one but it means building a empty
table for every query. Lots of work there but you are only bringing in what
you need.


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

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


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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.