Re: SQL RPG -- RPG400-L

Buck Calabro wrote:
> 
> 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          *
> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Thanks a million Buck for your assistance, It is a good starting point 

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