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

[Buck Calabro <mcalabro@xxxxxxxxxxxx>]

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