Embedded Dynamic SQL – Simple Select
H/TITLE SQLEX01 Simple Select & Display
h DEBUG DECEDIT(',') DATEDIT(*DMY/)
d customerName s 50
// Demonstrate use of simple SQL select statement
/FREE
Exec Sql
SELECT csnam INTO :customerName
FROM customer
WHERE csnum = 0010001005;
dsply customerName;
Eval *inlr = *on;
/END-FREE
Use of SQLSTATE for FOUND & NOT FOUND
H/TITLE SQLEX04 Simple Select & Display
h DEBUG DECEDIT(',') DATEDIT(*DMY/)
d customerName s 50
// Demonstrate use of simple SQL select statement
/FREE
Exec Sql
SELECT csnam INTO :customerName
FROM customer
WHERE csnum = 0010001005;
if (SQLSTATE >= '02000'); // Record not found?
dsply 'Not Found';
else;
dsply customerName;
endif;
Embedded Dynamic SQL – Simple Update
H/TITLE SQLEX02 Simple Update
h DEBUG DECEDIT(',') DATEDIT(*DMY/)
// Demonstrate simple embedded SQL update statement
/FREE
Exec Sql
UPDATE customer
SET csnam = 'Cliff Hill'
WHERE csnam = 'Cliff Richard';
Eval *inlr = *on;
/END-FREEEmbedded Dynamic SQL – Simple Update using RPG variable in SQL update statement
H/TITLE SQLEX03 Simple Update using RPG variable
h DEBUG DECEDIT(',') DATEDIT(*DMY/)
d customerName s 50
// Demonstrate use of RPG variable in SQL update statement
/FREE
customerName = 'Gareth Williams';
Exec Sql
UPDATE customer
SET csnam = 'Richard Reed'
WHERE csnam = :customerName;
Eval *inlr = *on;
/END-FREEEmbedded SQL Cursors
Cursors are used to handle multiple results.
H/TITLE sqlex10 multiple results using a cursor
h DEBUG DECEDIT(',') DATEDIT(*DMY/)
d customerFile e ds Extname(customer)
// Demonstrate processing of multiple results using a cursor
d ok s 10I 0
/FREE
// Handling multiple results using a cursor
// Declare/Open/Fetch/Close
// Declare SQL cursor
Exec Sql
DECLARE myCursor Cursor For
SELECT *
FROM customer
WHERE csnam like 'Cliff%'
For Fetch Only;
// Open SQL cursor
Exec Sql
OPEN myCursor;
// Fetch and display data
Dow SqlCode = Ok;
Exec Sql
FETCH myCursor Into :customerFile;
If Sqlcode = Ok;
dsply csnam;
EndIf;
EndDo;
// close cursor
Exec Sql
CLOSE myCursor;
Eval *inlr = *on;
/END-FREE
Embedded Dynamic SQL
With embedded dynamic SQL, the script is created at runtime. The HLL program constructs the script according to logic/variables in the program.
H/TITLE sqlex20 simple dynamic SQL select statement
h DEBUG DECEDIT(',') DATEDIT(*DMY/)
h dftactgrp(*no) actgrp(*caller)
d displayResults pr
d closeCursor pr
d openCursor pr
d compareValue 40 Value
d prepareStmt pr
d stmt 200 Value
d customerFile ds
d customerName 30
d customerTelno 15
// Demonstrate use of simple dynamic SQL select statement
d displayBytes s 52
d ok s 10I 0
d stmt s 200
d selectStmt C 'SELECT csnam, cstel +
d FROM customer WHERE '
d forFetch C 'For Fetch Only'
d cityValue C 'ODIJK'
d phoneValue C '035-7721234'
/FREE
Exec SQL
Declare myCursor Cursor
for DynSQLStmt;
// Display list of customers in a certain city
displayBytes = 'CITY SEARCH: ' + cityValue;
dsply displayBytes;
Stmt = selectStmt + 'cscity = ? ' + forFetch;
prepareStmt(stmt);
openCursor(cityValue);
displayResults();
closeCursor();
// Display list of customers with a certain phone nr
displayBytes = 'PHONE SEARCH: ' + phoneValue;
dsply displayBytes;
Stmt = selectStmt + 'cstel = ? ' + forFetch;
prepareStmt(stmt);
openCursor(phoneValue);
displayResults();
closeCursor();
Eval *inlr = *on;
/END-FREE
//-------------------------------------------------------------------------
// Procedure - Display result set
//-------------------------------------------------------------------------
p displayResults b
/free
// Table read loop
Dow sqlCode = Ok;
Exec Sql
Fetch myCursor Into :customerFile;
If sqlCode = Ok;
displayBytes = %trim(customerName);
dsply displayBytes;
Else;
If sqlCode < 0;
displayBytes = 'error ' + %char(sqlCode);
dsply displayBytes;
EndIf;
EndIf;
EndDo;
/end-free
p displayResults e
//-------------------------------------------------------------------------
// Procedure - Close Cursor
//-------------------------------------------------------------------------
p closeCursor b
/free
Exec Sql
Close myCursor;
/end-free
p closeCursor e
//-------------------------------------------------------------------------
// Procedure - Open Cursor
//-------------------------------------------------------------------------
p openCursor b
d openCursor pi
d compareValue 40 Value
/free
Exec SQL
Open myCursor Using :compareValue;
/end-free
p openCursor e
//-------------------------------------------------------------------------
// Procedure - Prepare Statement
//-------------------------------------------------------------------------
p prepareStmt b
d prepareStmt pi
d stmt 200 Value
/free
Exec SQL
Prepare DynSQLStmt from :Stmt;
If SQLState <> '00000';
// ...error checking!
EndIf;
/end-free
p prepareStmt e