FAQ-SQLRPGLE et SQLRPG

Bonjour,
je souhaite savoir comment integrer une requete sql a du gapIII(rpg400)
et par quelle commande compiler le prog...Merci

Code:

C/EXEC SQL

Code:

C/END-EXEC

Code:

C+

Code:

C/EXEC SQL
C+ update table1 set vnom = :nom, vprenom = :prenom
C/END-EXEC

Code:


Brief Overview of Using Embedded SQL with RPG
A cursor is defined in the RPG or RPGLE source code. Then FETCH is used in a loop to access the data from the file. In this loop to process the fetched data, RPG/RPGLE code can be used and the output can be sent to the spool or to the display.
The following are the steps in sequence.
1) Declare the SQL Communication Area. This is a must.
C/EXEC SQL
C+ INCLUDE SQLCA
C/END-EXEC
2) SQL Cursor Declaration
C/EXEC SQL
C+ DECLARE GETREC CURSOR FOR SELECT XABCDE, XFGHIJ, XKLMN, FROM
C+ XFILE WHERE
C+ XSTRIN LIKE :STRING
C/END-EXEC
:STRING IS A VARIABLE
3) SQL FETCH Subroutine Declaration
C Fetch begsr
C
C/EXEC SQL
C+ FETCH NEXT FROM GETREC INTO :XABCD, :XEFGH
C/END-EXEC
ENDSR
4) SQL Close Subroutine Declaration (To Close the Cursor)
C Close begsr
C
C/EXEC SQL
C+ CLOSE GETREC
C/END-EXEC
C endsr
5) * SQL Open Subroutine Declaration (To Open the Cursor)
C Open begsr
C
C/EXEC SQL
C+ OPEN GETREC
C/END-EXEC
C endsr
First step in main process would be to open the cursor

EXSR OPEN
sqlcod doune 0
exsr Fetch
rpg code
rpg code
enddo
exsr close
The source type for RPG/400 with embedded SQL is SQLRPG, with ILE it is SQLRPGLE.

Figure 1 One-at-a-time record retrieval with SQL
Figure 1: One-at-a-time Record Retrieval With SQL
DECLARE cursor_name CURSOR FOR sql_statement
OPEN cursor_name
FETCH cursor_name INTO program_variable/s
CLOSE cursor_name
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 10 Dynamic SQL stmt with multiple program variable
Figure 10: Dynamic SQL Statement With Multiple Program Variable
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
EXECUTE SQLSTM USING :PGMVRI, :PGMVR2
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 2 Example of retrieving a record with embedded SQL
Figure 2: Example of Retrieving a Record With Embedded SQL
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
C/EXEC SQL
C+ DECLARE REC_PTR CURSOR FOR
C+ SELECT CMNBR, CMNAME FROM CSTMST
C+ WHERE CMSTAT = 'A'
C+ ORDER BY CMNAME
C/END-EXEC
*
C/EXEC SQL
C+ OPEN REC_PTR
C/END-EXEC
*
C/EXEC SQL
C+ FETCH REC_PTR
C+ INTO :@CNBR, :@CNAME
C/END-EXEC
*
C/EXEC SQL
C+ CLOSE REC_PTR
C/END-EXEC
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 3 Static SQL statements in RPG/400
Figure 3: Static SQL Statements in RPG/400
SQLCOD
0: Record found
by FETCH
100: Record not
found
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
FLIST O E PRINTER
IINREC E DSCSTMST
*===========================================
* Declare cursor for SQL statement
C/EXEC SQL
C+ DECLARE REC_PTR CURSOR FOR
C+ SELECT * FROM CSTMST
C+ WHERE CMADR1 LIKE :STR
C+ ORDER BY CMNAME
C/END-EXEC
*===========================================
* Open cursor
C/EXEC SQL
C+ OPEN REC_PTR
C/END-EXEC
*===========================================
* Loop until no more records
C SQLCOD DOWEQ0
* Load current record into data structure INREC
C/EXEC SQL
C+ FETCH REC_PTR
C+ INTO :INREC
C/END-EXEC
C SQLCOD IFNE 0
C LEAVE
C ENDIF
*
C WRITECMREC
C ENDDO
*===========================================
* Close cursor
C/EXEC SQL
C+ CLOSE REC_PTR
C/END-EXEC
C SETON LR
*===========================================
C *INZSR BEGSR
C *ENTRY PLIST
C PARM STR 10
C ENDSR
*===========================================
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 4 SQL statements not allowed with dynamic execution
Figure 4: SQL Statements Not Allowed or Limited With Dynamic Execution
BEGIN DECLARE SECTION
CLOSE
CONNECT
* DECLARE CURSOR
DECLARE STATEMENT
DECLARE VARIABLE
* DESCRIBE
DESCRIBE TABLE
END DECLARE SECTION
* EXECUTE
* EXECUTE IMMEDIATE
FETCH
INCLUDE
OPEN
* PREPARE
* SELECT INTO
* SELECT-statement
* WHENEVER
* Limited usage: Refer to the Dynamic SQL chapter of the AS/400 SQL/400
Programmer's Guide (SC41-9609)
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 5 User-submitted SQL statement
Figure 5: User-submitted SQL Statement
DELETE FROM CSTMEM WHERE CMDATE < 850101
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 6 Embedded non-SELECT dynamic SQL statement
Figure 6: Embedded Non-SELECT Dynamic SQL Statement
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
*===========================================
* Prepare SQL statement from user input
C/EXEC SQL
C+ PREPARE SQLSTM FROM :USRINP
C/END-EXEC
* Execute SQL statement
C/EXEC SQL
C+ EXECUTE SQLSTM
C/END-EXEC
*===========================================
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 7 RPG/400 program with dynamic fixed-list SQL stmt
Figure 7: RPG/400 Program With Dynamic Fixed-list SQL Statement
SQLCOD
0: Record found
by FETCH
100: Record not
found
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
IINREC E DSTESTFILE
*===========================================
*
C EXSR EXC
*===========================================
C MOVE *ON *INLR
*===========================================
C EXC BEGSR
*
C/EXEC SQL
C+ PREPARE SQLSTM FROM :USRINP
C/END-EXEC
*
C SQLCOD IFGT 0
* statement is not executable
C CLEARUSRINP
C MOVEL'*ERROR' USRINP
C RETRN
C ENDIF
*
C/EXEC SQL
C+ DECLARE REC_PTR CURSOR FOR SQLSTM
C/END-EXEC
*
C/EXEC SQL
C+ OPEN REC_PTR
C/END-EXEC
*
*===========================================
*
C SQLCOD DOWEQ0
*
C/EXEC SQL
C+ FETCH REC_PTR INTO :INREC
C/END-EXEC
*
C SQLCOD IFNE 0
C LEAVE
C ENDIF
*
.
(Process record)
.
*
C ENDDO
*
C/EXEC SQL
C+ CLOSE REC_PTR
C/END-EXEC
*
C ENDSR
*===========================================
C *INZSR BEGSR
C *ENTRY PLIST
C PARM USRINP 80
C ENDSR
*===========================================
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 8 User-submitted SQL stmt with parameter marker
Figure 8: User-submitted SQL Statement With Parameter Marker
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
DELETE FROM CSTMEM WHERE CMDATE < ?
--------------------------------------------------------------------------------
Add Power to RPG/400 With Embedded SQL
Figure 9 Dynamic SQL stmt with single program variable
Figure 9: Dynamic SQL Statement With Single Program Variable
... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
*===========================================
* Execute SQL statement
C/EXEC SQL
C+ EXECUTE SQLSTM USING :INPDTE
C/END-EXEC
*===========================================

Code:

14 + F4 + commit *none

Code:

SQLRPG

Code:

RPG