Re:SQL RPG

[Buck Calabro <mcalabro@xxxxxxxxxxxx>]

I would like to have more information about the use of embedded SQL
statements in the RPG programs, as I understand RPG program written with
the RPG OR RPGLE extension doesn't support embedded SQL statement. Is
there an IBM course or a manual which could be used for guidance

Jan

=========
Jan,
        Here is a very early effort on my part to implement embedded SQL.
Don't think of this as a great example; just a "barely working" example!
The SQL-type statements need to be in this order:
DECLARE
PREPARE
OPEN
FETCH

ps - it's in RPG, not LE

FDSPTTIFMCF  E                    WORKSTN                          
F                                        RRN   KSFILE SFL          

-snip-

C* Declare the cursor (sorta like an F spec...) 
C/EXEC SQL                                      
C+ DECLARE DYNFIL SCROLL CURSOR FOR DYNSTM      
C/END-EXEC                                      

-snip-

C* Interpret the SQL statement and check for errors
C* note: WWHERE contains the entire SQL statement
C* example: SELECT KWD,TT,INIT,TTDATE,TTDESC              
C*          FROM TTIND K,TTHDR H                         
C*          WHERE (K.TT=H.TTNUM AND K.INIT=H.TTINIT) AND 
C*          KWD='BILLING'
C*          ORDER BY KWD,TT DESC                         
C*          FOR READ ONLY                                
C/EXEC SQL                                         
C+ PREPARE DYNSTM FROM :WWHERE                     
C/END-EXEC                                         
C*         

-snip-

C/EXEC SQL                                                    
C+ OPEN DYNFIL USING :WWHERE                                  
C/END-EXEC                                                    
C* Error OPENing SQL dynamic file                             
C*                                                            
C*   If an error occurs, display the message from CPF and let 
C* the user decide what to do.                                
C*                                                            
C           SQLCOD    IFNE 0                                  
C                     SETON                     44            
C                     MOVELPSDSMG    MESSAG                   
C                     GOTO SEATOP                             
C                     ENDIF                                                     
                      

-snip-

C* For the SQL access path, FETCH is the equivalent of READ  
C/EXEC SQL                                                   
C+ FETCH NEXT FROM DYNFIL INTO :KWD,:TT,:INIT,:TTDATE,:TTDESC
C/END-EXEC                                                   
C*
C* EOF                                                       
C           SQLSTT    CABNE'00000'   PAGEND                  
C*
C* Have a record, put it in the subfile
C                     WRITESFL                    
C                     ADD  1         RRN          
C                     SETON                     40
C* Loop back if more records avail this page      
C                     ADD  1         PRRN         
C           PRRN      CABLE15        PAG001       
C                     SETOF                     42
C                     Z-ADDRRN       SAVRRN       

-snip-

I hope this helps!

The manuals DB2 for OS/400 SQL Programming and SQL Reference
should be a place to start looking...

Buck Calabro
Commsoft, Albany, NY


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