RE: SQL Problem

[Buck Calabro <Buck.Calabro@xxxxxxxxxxxx>]

>There aren't any indicator variables in the table.

Indicator variables is how SQL is passing NULL to your program.  Look up
indicator variable or null map.   Here's an example of how to use indicator
variables:
h debug indent(*None) alwnull(*usrctl) dftactgrp(*no) actgrp('QILE')

h option(*srcstmt: *nodebugio)



 * dbgview(*source) commit(*none)



FQsysprt   O    f  198        Printer Oflind(*InOF)

D NULL            s              5i 0

dtemplateDS     e ds                  extname(sqlsample)



d* I/O buffer

dioBuffer         ds                  occurs(10)                           1
per record
d field1Data                          like(field1)



d* I/O buffer null map

dioBufferNull     ds                  occurs(10)

d nullMap                             dim(1) like(NULL)                    1
per field


d* Null map for retrieved variables - named instead of anonymous array

dioNamedNull      ds

d field1Null                          like(NULL)



d* Program status data structure contains any error message text

d                SDS
d  PSDSMG                91    170

C* Declare the cursor
C/EXEC SQL
C+ DECLARE NULLTEST CURSOR FOR
C+         SELECT field1
C+         FROM sqlsample
C/END-EXEC

C* Open the access path
C/EXEC SQL
C+ OPEN NULLTEST
C/END-EXEC
C*   If an error occurs, dump and exit.
C*   Since this is a test-bed, this is all the error recovery we want
C*
C                   If        SQLCOD <> 0
C     'Open Error'  Dump
C                   Eval      *InLR = *On
C                   Return
C                   ENDIF

C* Loop until EOF
C                   DoW       1=1
C* For the SQL access path, FETCH is the equivalent of READ
C/EXEC SQL
C+ FETCH NEXT FROM NULLTEST FOR 10 ROWS
C+       INTO :ioBuffer :ioBufferNull
C/END-EXEC

 * End of file
C                   If        SQLCOD = 100
C                   Leave
C                   EndIf

 * Unknown error
C                   If        SQLCOD <> 0
C     'Fetch'       Dump
C                   Leave
C                   EndIf

 * Print the details
C     1             Do        SQLER3        OccurNum         10 0
C     OccurNum      Occur     ioBuffer
C     OccurNum      Occur     ioBufferNull

 * Deal with the nulls

C                   Move      ioBufferNull  ioNamedNull

C                   If        field1Null = -1
C                   Eval      field1Data = *Blanks
C                   EndIf

C                   Except    PrtDtl
C                   EndDo

 * Less records read than requested
C                   If        SQLER3 < 10
C                   Leave
C                   EndIf

C                   EndDo

C* Close the SQL access path
C/EXEC SQL
C+ CLOSE NULLTEST
C/END-EXEC

c* Write records
 *   load record 1
c     1             occur     ioBuffer
c     1             occur     ioBufferNull
c                   clear                   ioBuffer
c                   clear                   ioBufferNull
c                   clear                   ioNamedNull
c                   eval      field1Data = 'f'
c                   eval      field1Null = -1
c                   move      ioNamedNull   ioBufferNull
 *   load record 2
c     2             occur     ioBuffer
c     2             occur     ioBufferNull
c                   clear                   ioBuffer
c                   clear                   ioBufferNull
c                   clear                   ioNamedNull
c                   eval      field1Data = 'g'
c                   eval      field1Null = 0
c                   move      ioNamedNull   ioBufferNull

c/exec sql
c+ insert into sqlsample 2 rows
c+        values(:ioBuffer :ioBufferNull)
c/end-exec

C                   Eval      *InLR = *On
C                   Return

C* Program error
C*
C* This is not really an error handler, so much as a "graceful"
C* death.  If an unexpected error happens, this will do a DUMP,
C* then terminate.
C     *PSSR         BEGSR
C     '*PSSR'       Dump
C                   MOVE      '*CANCL'      ACTION            6
C                   ENDSR     ACTION

OQsysprt   Ef           PrtDtl         1
O                       occurNum      z     10
O                       field1Data       +   2

DSPFFD SQLSAMPLE (I did a create table on it long ago):
            Data        Field  Buffer    Buffer        Field    Column
 Field      Type       Length  Length  Position        Usage    Heading
 FIELD1     CHAR            1       1         1        Both     FIELD1
   Allows the null value
   Coded Character Set Identifier  . . . . . :     37

If you don't want to use indicator variables, look at COALESCE.
--buck