|
midrange.com
Tony,
Below is a sample embedded SQL pgm. I am not sure if attachments are
supported
so I copied & pasted the code. The formatting might be a little messed
up.
Hope this helps.
Ravi
*************** Beginning of data *************************************
***********************************************************************
** This program retrieves data from remote Oracle table F411WRK and **
** inserts into local AS/400 file F4111LOC.
**-------------------------------------------------------------------**
**- F I L E S -**
**-------------------------------------------------------------------**
**
FF4111LOC O E DISK
**-------------------------------------------------------------------**
**
D* Define Oracle Access Manager fields
**
*************** Beginning of data
*************************************
***********************************************************************
** COPYRIGHT 1998 **
** MEDTRONIC INC. **
** ALL RIGHTS RESERVED **
***********************************************************************
** This program retrieves data from remote Oracle table F411WRK and **
** inserts into local AS/400 file F4111LOC.
**-------------------------------------------------------------------**
**- F I L E S -**
**-------------------------------------------------------------------**
**
FF4111LOC O E DISK
**-------------------------------------------------------------------**
**
D* Define Oracle Access Manager fields
**
*************** Beginning of data *************************************
***********************************************************************
** COPYRIGHT 1998 **
** MEDTRONIC INC. **
** ALL RIGHTS RESERVED **
***********************************************************************
** This program retrieves data from remote Oracle table F411WRK and **
** inserts into local AS/400 file F4111LOC.
**-------------------------------------------------------------------**
**- F I L E S -**
**-------------------------------------------------------------------**
**
FF4111LOC O E DISK
**-------------------------------------------------------------------**
**
D* Define Oracle Access Manager fields
**
C* Declare cursor for ONEWORLD ADDRESS BY DATE TABLE (F0301)
C*
**-------------------------------------------------------------------**
*
* Declare the SQL statement to be used to get data from the remote
* system
* "get_data" is the name of the cursor into which values will be
* retrieved. Cursor can have any name.
* Every SQL statement must be preceded by /EXEC SQL
* Every SQL statement must be followed by /END-EXEC
* This is how the RPG compiler knows that the lines between these
* statements are non-RPG code and can be ignored by the compiler.
C*
C*
C*
C/EXEC SQL
C+ DECLARE get_data CURSOR FOR
C+ select ILITM, ILLITM, ILAITM, ILMCU,
C+ ILLOCN, ILLOTN, ILPLOT, ILSTUN, ILLDSQ,
C+ ILTRNO, ILFRTO, ILLMCX, ILLOTS, ILLOTP,
C+ ILLOTG, ILKIT, ILMMCU, ILDMCT, ILDMCS,
C+ ILBALU, ILKCO, ILDOC, ILDCT, ILSFX,
C+ ILJELN, ILICU, ILDGL, ILGLPT, ILDCTO,
C+ ILDOCO, ILKCOO, ILLNID, ILIPCD, ILTRDJ,
C+ ILTRUM, ILAN8, ILTREX, ILTREF, ILRCD,
C+ ILTRQT, ILUNCS, ILPAID, ILTERM, ILUKID,
C+ ILTDAY, ILUSER, ILPID, ILCRDJ
C+ FROM F4111WRK
C*
C/END-EXEC
*
**-------------------------------------------------------------------**
* * Call QCMDEXC to change CCSID of current job to
37.
* Change job CCSID to one supported by Oracle Access Manager
*
C CALL 'QCMDEXC'
C PARM CMD_TEXT
C PARM CMD_LENGTH
C*
**-------------------------------------------------------------------**
* Connect session to the Remote Database
*
C/EXEC SQL
C+ CONNECT TO :RMT_DB
C+ USER :RMT_USER
C+ USING :RMT_PWD
C*
C/END-EXEC
C*
**-------------------------------------------------------------------**
*
* Open cursor for ORACLE ADDRESS BY DATE TABLE
* Open the cursor that was defined previously in the program
*
C/EXEC SQL
C+ OPEN get_data
C/END-EXEC
*
* SQL errors are returned in variable SQLSTT
* SQLSTT is automatically defined in SQLRPG programs by the
* SQL compiler
*
C EVAL %SUBST(SQLSTT:1:2) = '00'
C*
C DOW %SUBST(SQLSTT:1:2) = '00'
*
**-------------------------------------------------------------------**
*
* Get data from the remote table by "FETCH" ing the cursor
* Data is retrieved into host variables which are fields in the
* output file. If temporary variables need to be used as host
* variables, they must be explicitly defined in the program
*
C*-------------------------------------------------------------------**
C/EXEC SQL
C+ FETCH get_data
C+ INTO :ILITM, :ILLITM, :ILAITM, :ILMCU,
C+ :ILLOCN, :ILLOTN, :ILPLOT, :ILSTUN, :ILLDSQ,
C+ :ILTRNO, :ILFRTO, :ILLMCX, :ILLOTS, :ILLOTP,
C+ :ILLOTG, :ILKIT, :ILMMCU, :ILDMCT, :ILDMCS,
C+ :ILBALU, :ILKCO, :ILDOC, :ILDCT, :ILSFX,
C+ :ILJELN, :ILICU, :ILDGL, :ILGLPT, :ILDCTO,
C+ :ILDOCO, :ILKCOO, :ILLNID, :ILIPCD, :ILTRDJ,
C+ :ILTRUM, :ILAN8, :ILTREX, :ILTREF, :ILRCD,
C+ :ILTRQT, :ILUNCS, :ILPAID, :ILTERM, :ILUKID,
C+ :ILTDAY, :ILUSER, :ILPID, :ILCRDJ
C/END-EXEC
*
* All fields in the output file have values. A RPG "WRITE" op-code
* will insert a record in the output file.
*
C IF %SUBST(SQLSTT:1:2) = '00'
C WRITE I4111
C ENDIF
C*
C ENDDO
C*
C*
C* After desired records have been retrieved and written to the output
* file, the cursor must be
closed.
*
C/EXEC SQL
C+ CLOSE get_data
C/END-EXEC
*
*
* Release (drop) connection to the remote database.
C/EXEC SQL
C+ RELEASE CURRENT
C/END-EXEC
*
* Commit all transactions. Not required on AS/400 unless
* commitment control is being used.
*
C/EXEC SQL
C+ COMMIT
C/END-EXEC
SETON
LR
Anthony Mallozzi wrote:
>
> Hello to all!
> I would like to see an example of using embedded SQL
> in RPG. Sample code would be appreciated.
> Thanks,
> Tony Mallozzi
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
