Re: Null indicator with FETCH for N ROWS WAS: SQL pre-compiler

It works! Yeah!  I tried your array definition and it works.  All the IBM
manuals and all the SQL books I've consulted are wrong, wrong, wrong, and
you are right!  Oh thank you, thank you, thank you.  You don't how long I've
been beating my head against a wall trying to get these null indicators to
work.  This list is worth it's weight in gold.


-----Original Message-----
From: Buck Calabro <mcalabro@commsoft.net>
To: 'RPG400-L@midrange.com' <RPG400-L@midrange.com>
Date: Wednesday, July 28, 1999 10:28 AM
Subject: RE: Null indicator with FETCH for N ROWS WAS: SQL pre-compiler


>The precompiler seems very fussy regarding the null indicator definitions.


Tell me about it.

>You can't declare the indicator array as anything but an array of type
>SMALLINT, and the number of elements must exactly match the number of
fields
>in your FETCH.  Any discrepancy and the precompiler will reject the null
map
>with the SQL5011.  I use the "I" datatype out of habit nowadays, because
the
>"B" datatype will truncate large numbers.  It should work just fine as 4b
0.
>
>For an example of how strict the precompiler is, this gives an SQL0511:
>DRtnNullRaw       ds                  occurs(10)
>D NullMap                        5i 0 dim(9)
>D TblLibNull                     5i 0 overlay(NullMap:01)
>D TblNameNull                    5i 0 overlay(NullMap:05)
>D TblTypeNull                    5i 0 overlay(NullMap:09)
>D TblTextNull                    5i 0 overlay(NullMap:13)
>D ColNameNull                    5i 0 overlay(NullMap:17)
>D ColTypeNull                    5i 0 overlay(NullMap:21)
>D ColLenNull                     5i 0 overlay(NullMap:25)
>D ColScaleNull                   5i 0 overlay(NullMap:29)
>D ColTextNull                    5i 0 overlay(NullMap:33)
>
>apparently because of the presence of any fields other than the array.
>
>This works:
>DRtnNullRaw       ds                  occurs(10)
>D NullMap                        4b 0 dim(9)


All the reference books I've found, show:

DRtnNullRaw         ds                  occurs(10)
D NullField1                       4b 0
D NullField2                       4b 0
D NullField3                       4b 0
D NullField4                       4b 0

Your way does, indeed, work.


>
>As for PTF's, try the latest database group ptfs: SF99103.  I am on V4R3M0,
>cume 99054 (way back!)


Thanks, I'll check on these.

One other question did come up in my research that I haven't seen mentioned
anywhere else.  In section 9.1.2 of the "DB2 for AS/400 SQL Programming
V4R3, it states " If the value for the result column causes a data mapping
error, SQL sets the indicator variable to -2."  What exactly does this mean?
I'm currently only testing for -1 (nulls).  I've never seen any other
examples of code that tests the null indicator for anything but nulls.  This
makes me think I should also be testing for -2.  Do you do this and if so,
what do you do with the base field when you get a hit?  Clear it?



* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the RPG/400 Discussion Mailing List!  To submit a new         *
* message, send your mail to "RPG400-L@midrange.com".  To unsubscribe   *
* from this list send email to MAJORDOMO@midrange.com and specify       *
* 'unsubscribe RPG400-L' in the body of your message.  Questions should *
* be directed to the list owner / operator: david@midrange.com          *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *