Re: SQL processing in RPG programs

[Marco Facchinetti <facchinetti@xxxxxxxxx>]

Thanks John,
and I guess I have to set the "indicator" to -1 if I have
to change data, right?
But maybe I'd better explaine my doubts:

I made this just to test what happen (the field ABDTINIRAP
is null capable filed and is usually null):
H DATFMT(*EUR) ALWNULL(*USRCTL)                            
D DBANA0        E DS                  EXTNAME(DBANA00F)    

D NUL             S              4                         
dind_array        ds                                       

d inds                           4B 0 dim(50)              

D*----------------------------------------------------* 
SQL                
C/EXEC SQL                           
C+ DECLARE CUR CURSOR FOR
C+ SELECT * FROM DBANA02L                              
C+ ORDER BY ABRAGSOC1E                               
C/END-EXEC                                            
c*
C/EXEC SQL                                   
C+ OPEN CUR                                              
C/END-EXEC                                         
 c*                                                        
               
 C     1             DO        11                          

 C/EXEC SQL                                               
 C+ FETCH CUR INTO :DBANA0 :INDS                      
 C/END-EXEC                                             
 c                   except    riga                       
 C                   IF        SQLCOD=100 OR
SQLSTT='020000'
 C                   LEAVE                             
 C                   ENDIF                                
I cannot use this form......
 C*                  IF        %NULLIND(ABDTINIRAP)=*ON 
 C*                  EVAL      NUL ='*ON'               
 C*                  ELSE                            
 C*                  EVAL      NUL ='*OFF'           
 C*                  ENDIF                     
I must write (I know it's INDS(21) from compiler listing )
 C                   IF        inds(21)=-1        
 C                   EVAL      NUL ='*ON'               
 C                   ELSE                            
 C                   EVAL      NUL ='*OFF'           
 C                   ENDIF                     

 C                   ENDDO     
 c*                                      
 C/EXEC SQL                                          
 C+ CLOSE CUR                                          
 C/END-EXEC                                             
 c                   eval      *inlr=*on               
 c                   return                 
 ******************** End ******************************** 

Please do not comment the code, I know it's not perfect. 

I like the way the pre-compiler handles and generate the
code form the "*" syntax and the ds since is working even
if I add or eliminate some fields from the file DBANA00F.
But this is not true anymore since I'm forced to identify
the SQL %nullind whit an array and (of course) a fixed
index.
Any way out?
(please don't tell me to select only the collums I need)
--- jpcarr@TREDEGAR.COM wrote:
> 
> <snip>
> >By the way how do you test if a field resulting from an
> >embebbed sql statement is null? I don't know how to
> handle
> >since is not the same as RPGIV. Somebody know other
> ways?
> Marco Facchinetti
> <snip>
> 
> 
> Here's two examples of  how to handle SQL Null fields in
> RPGIV.
> 
> Note:  The DB field doesn't contain a null character(no
> such thing).
> 
>  You have to test the Indicator Variable associated with
> the field.   In
> the example below,   SalesNull is an Indicator Variable
> that will be a -1
> if it's associated field DSCustSales is null during the
> fetch function.
> On an Insert,   You would say something like  < C     
> Eval     SalesNull
> =  -1  >    Then do an Insert to write DSCustSales as
> Null.
> 
> BTW,   I do in house classes on this stuff if anyone is
> interested.
> 
>    D SalesNull S    4B 0
>     C/Exec SQL
>     C+             Select CustId,
>     C+                        CustNam,
>     C+                        CustSales
>     C+             Into:   :DSCustId,
>     C+                        :DSCustName,
>     C+                        :DSCustSales :SalesNull
>     C+             From Customer  Where CustId = 'ACME'
>     C/End-Exec
> 
> ---------
> Here's how you would do an array of Null capable fields
> into a D/S  You
> have a D/S to put the individual fields you are fetching
> into and (followed
> by a colon) is a structure of an array of binary fields
> to hold the null
> representation of each of those fields.
> 
> D CustomerR    DS
> D    CsCustID         7P  0
> D    CsName         30A
> D    CsShpCity      30A
> D    CsDiscount            5P  3
> 
> D CustomerIDS  DS
> D    CsIDNull         4B  0
> D    CsNameNull            4B  0
> D    CsShpCityNull    4B  0
> D    CsDiscountNull   4B  0
> D*
> D     CustomerI           4B   0  Dim(4)
> overlay(CustomerIDS)
> 
> C/Exec SQL
> C+        Select    CustID,
> C+             Name,
> C+             ShpCity,
> C+             Discount
> C+        Into :CustomerR :CustomerI
> C+        From Customer
> C+        Where     CustID =:SelCst
> C/End-Exec
> 
> 
> John Carr
> 
> 
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to
> RPG400-L@midrange.com.
> | To subscribe to this list send email to
> RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
> RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list
> owner/operator: david@midrange.com
> +---


__________________________________________________
Do You Yahoo!?
Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free!
http://photos.yahoo.com/
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---