Dynamic SQL in RPG
Simple Example of Using Dynamic SQL in RPG
Dynamic SQL is a powerful technique on IBM i that allows you to build SQL statements at runtime instead of hardcoding them. This flexibility is essential when your SQL needs to adapt based on user input, configuration, or variable conditions.
In this post, I’ll walk you through a simple RPG example that uses a parameterized dynamic SQL statement to retrieve data from a file.
The Program
**FREE
// ************************************************************* // * Author.......: ALDO SUCCI * // * Description..: Simple example of using dynamic SQL in RPG * // ************************************************************* // Declare variable to hold the customer type (3 characters) dcl-s wCLCIB0 CHAR(3); // Declare variable to hold the customer name retrieved from the database dcl-s wCLNOM0 CHAR(50); // Declare a character variable to hold the SQL statement. // INZ(...) initializes the variable with a parameterized SQL query. // The "?" acts as a placeholder for values supplied at runtime. dcl-s SqlString CHAR(500) INZ('SELECT CLNOM0 FROM CLANA00F WHERE CLCIB0 = ?'); //***************************************************************** //* Prepare the SQL statement stored in SqlString. * //* PREPARE converts the text in SqlString into an executable SQL * //* statement identified here as 'Sql1'. * //***************************************************************** Exec SQL PREPARE Sql1 FROM :SqlString; // Associate the prepared SQL statement with a cursor. // A cursor allows row-by-row retrieval of query results. Exec SQL DECLARE Cursor1 CURSOR FOR Sql1; //*************************************************** //* Assign the customer type to be used as input * //* for the parameterized query. * //*************************************************** wCLCIB0 = 'BBB'; // Open the cursor, supplying the parameter value (wCLCIB0). // The value replaces the "?" placeholder in the SQL statement. Exec SQL OPEN Cursor1 USING :wCLCIB0; // Retrieve the first (and in this case, only) row from the result set. // The value of CLNOM0 from the query is stored into wCLNOM0. Exec SQL FETCH Cursor1 INTO :wCLNOM0; // For demonstration purposes, simply display the retrieved value. // In a real-world program, this could be written to a display file, // printed, or processed further. Dsply wCLNOM0; //****************************************** //* Close the cursor to release resources. * //****************************************** Exec SQL CLOSE Cursor1; //****************** //* End of program * //****************** *inlr = *on; // Set the last record indicator to ON, ending the program. return; // Return control to the caller or operating system. |
How It Works
- Variable Declarations –
wCLCIB0
holds the customer type code used in the query;wCLNOM0
stores the retrieved customer name;SqlString
contains the SQL text with a?
placeholder. - Preparing the SQL Statement –
PREPARE
compiles the SQL text fromSqlString
into an executable statement (Sql1
). - Declaring the Cursor – The cursor (
Cursor1
) is linked to the prepared SQL for row-by-row processing. - Assigning the Parameter – The program assigns
'BBB'
towCLCIB0
. - Opening the Cursor –
OPEN ... USING
replaces the?
in the SQL withwCLCIB0
. - Fetching Data –
FETCH
retrieves the first matching row intowCLNOM0
. - Displaying the Result –
DSPLY
outputs the result for demonstration. - Closing the Cursor – Always close cursors to free resources.
Why Use Parameterized Dynamic SQL?
- Security – Using
?
prevents SQL injection. - Flexibility – Easily change SQL clauses at runtime.
- Performance – Prepare once, execute multiple times with different parameters.
Tip: In real-world applications, instead of DSPLY
, you could send the data to a display file, write it to a report, or process it further.
This is the simplest possible example of “Dynamic SQL in RPG” — just a starting point.
You are invited to share your thoughts, suggest improvements, and propose more refined implementations.
I will greatly appreciate everyone’s collaboration!
With this I learnt, something new today, I never declared sql statement as
ReplyDeletedcl-s SqlString CHAR(500) INZ('SELECT CLNOM0 FROM CLANA00F WHERE CLCIB0 = ?');
Will use it in my code soon. Thanks ❤️