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

["Nelson C. Smith" <ncsmith@xxxxxxxx>]

Wow!  That's exactly what I'm trying to do, Buck.  But I can't get the
RtnNullRaw DS past the precompiler.  I keep getting the dreaded SQL5011
error.  Did you ever get this error?  It occurs right on the Fetch statement
in the precompiler.  When I star-out the null-capable fields and thus the
need for the indicator host variable, the procedure works fine.

I have tried defining the DS  the way you show it as well as with 4B 0
elements.  And as both an array and as individual fields.  If the way you
have it defined below works, then the IBM manuals (as well as Paul Conte and
several other SQL writers) are flat-out wrong.  They all show individual
data structure fields of 4B 0, rather than the array of 5 I 0 elements.
Also, some manuals say you have to have the same number as the total number
of fields, and other manuals say the same number as null-capable fields.
>From your example below, it seems to be All fields.

I have gotten the same error at other times which made absolutely no sense
at all.  For example, in one procedure where I am returning multiple
records, I get the SQL5011 if I don't have a prefix keyword on the multiple
occurrance ds.  With the prefix keyword, the precompiler accepts it and it
runs fine.  Of course, then I have to go prefix all the field names to make
it work.  On other procedures just like it, no problem at all without a
prefix.  Do you know of any PTF's required for the precompiler (V4R3)?  The
only thing I can figure is something flaky about the precompiler.

At least I'm glad to find out it actually does work for someone.  You're the
first one I've found actually doing multiple record Fetch's.  Now I just
have to find out what's different on my machine.  Thanks for your examples.
I'll keep trying.

-----Original Message-----
From: Buck Calabro <mcalabro@commsoft.net>
To: 'RPG400-L@midrange.com' <RPG400-L@midrange.com>
Date: Tuesday, July 27, 1999 1:16 PM
Subject: Null indicator with FETCH for N ROWS WAS: SQL pre-compiler


>Nelson,
>I have a program that does this.  Here are some snippets:
>
>     FQsysprt   O    f  198        Printer Oflind(*InOF)
>
>     D NULL            s              5i 0
>     D ColScalePrt     s             10    inz
>
>     D* Retrieved variables
>     DRtnData          ds                  occurs(10)
>     D TblLib                        10
>     D TblName                       10
>     D TblType                        1
>     D TblText                       50
>     D ColName                       10
>     D ColType                       10
>     D ColLen                         9  0
>     D ColScale                       9  0
>     D ColText                       50
>
>     D* Null map for retrieved variables
>     DRtnNullRaw       ds                  occurs(10)
>     D NullMap                        5i 0 dim(9)
>
>     D* Null map for retrieved variables - named instead of anonymous array
>     DRtnNullNamed     ds
>     D TblLibNull                          like(NULL)
>     D TblNameNull                         like(NULL)
>     D TblTypeNull                         like(NULL)
>     D TblTextNull                         like(NULL)
>     D ColNameNull                         like(NULL)
>     D ColTypeNull                         like(NULL)
>     D ColLenNull                          like(NULL)
>     D ColScaleNull                        like(NULL)
>     D ColTextNull                         like(NULL)
>
>     C* Declare the cursor (sorta like an F spec...)
>     C/EXEC SQL
>     C+ DECLARE DYNFIL SCROLL CURSOR FOR
>     C+         SELECT t.dbname, t.name, t.type, t.label,
>     C+                c.name, c.coltype, c.length, c.scale, c.label
>     C+         FROM systables t join syscolumns c on
>     C+         t.name = c.tbname and t.dbname = c.dbname
>     C+         WHERE t.name = 'QRPGSRC' and t.dbname = 'QGPL'
>     C/END-EXEC
>
>     C* Open the access path
>     C/EXEC SQL
>     C+ OPEN DYNFIL
>     C/END-EXEC
>
>     C* For the SQL access path, FETCH is the equivalent of READ
>     C/EXEC SQL
>     C+ FETCH NEXT FROM DYNFIL FOR 10 ROWS
>     C+       INTO :RtnData :RtnNullRaw
>     C/END-EXEC
>
>      * Print the details
>      * SQLER3 is the number of rows actually returned by the FETCH
>     C     1             Do        SQLER3        OccurNum          5 0
>     C     OccurNum      Occur     RtnData
>     C     OccurNum      Occur     RtnNullRaw
>
>      * Deal with the nulls
>     C                   Move      RtnNullRaw    RtnNullNamed
>
>     C                   If        ColScaleNull = -1
>     C                   Eval      ColScalePrt = *Blanks
>     C                   Else
>     C                   Eval      ColScalePrt = %editc(ColScale:'L')
>     C                   EndIf
>
>     C                   If        ColTextNull = -1
>     C                   Eval      ColText = '*null'
>     C                   EndIf
>     C                   Except    PrtDtl
>     C                   EndDo
>
>Buck Calabro
>Billing Concepts Albany, NY
>mailto:mcalabro@commsoft.net
>
>> -----Original Message-----
>> From: Nelson C. Smith
>> Sent: Saturday, July 24, 1999 5:12 PM
>> To: RPG400-L@midrange.com
>> Subject: Re: SQL pre-compiler
>>
>> I have procedures which fetch multiple records into a multiple occurrance
>> data structure used as a host variable array using the Define Cursor and
>> Fetch Next For xx Rows method.  The procedures work fine until I include
>> some null-capable fields in what I'm fetching.
>> Then the system REQUIRES an associated host variable array for the null
>> indicators.  Here's where I run into trouble.
>>
>>  I cannot get the precompiler to accept anything for an indicator host
>> variable except individual fields.  However, all the manuals say it has
to
>> be another multiple occurrance data structure with the same number of
>> occurrances as the base MODS has.  Also, (and there is some disagreement
>> between manuals on this point) it should have one 4B 0 field for each
>> field
>> in the base MODS (one manual says for each null-capable field and another
>> says for every field).  No matter how I try to define it, the precompiler
>> says it is "not defined or useable" whatever that means.  Some of the
>> reasons given are nonsensical, such as "an indicator array cannot have
>> more
>> than one element".
>>
>* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
>* 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          *
>* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
>

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