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 –
wCLCIB0holds the customer type code used in the query;wCLNOM0stores the retrieved customer name;SqlStringcontains the SQL text with a?placeholder. - Preparing the SQL Statement –
PREPAREcompiles the SQL text fromSqlStringinto 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 ... USINGreplaces the?in the SQL withwCLCIB0. - Fetching Data –
FETCHretrieves the first matching row intowCLNOM0. - Displaying the Result –
DSPLYoutputs 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 ❤️
DeleteThank you, Gaurav!
I’m really glad to hear that this example helped you discover something new.
I’ll be happy to hear back from you once you try it in your code — feel free to share your experience or improvements!
Hi Aldo,
ReplyDeleteNice example and explanation...
I've not seen the "Using" to replace a "?" placeholder before... Very useful...
Can you tell me if it is possible to have multiple "?" Placeholders in one statement...?
If so, how would the substitution work...?
Many thanks for your generous contribution and sharing your knowledge and expertise...
Yes, it is possible to use multiple ? placeholders in a single dynamic SQL statement.
DeleteFor examle:
dcl-s SqlString char(500) inz(
'SELECT CLNOM0
FROM CLANA00F
WHERE CLCIB0 = ?
AND CLCIT0 = ?');
Welcome to AI and what it can do.
ReplyDeleteThanks for sharing the code! Your dynamic SQL example in RPG IV (ILE RPG) is well-structured and demonstrates a clean use of parameterized queries with cursors. Here's a breakdown of its strengths and a few suggestions for simplification or modernization:
✅ Strengths
Parameterized SQL: Using ? with USING :wCLCIB0 is great for security and performance.
Clear variable declarations: Using dcl-s makes the code easy to read and maintain.
Proper cursor handling: You prepare, declare, open, fetch, and close the cursor correctly.
Good commenting: The comments are thorough and helpful for understanding the flow.
🔧 Suggestions for Simplification or Improvement
1. Use Embedded SQL Directly (if only one row is expected)
If you're only expecting one row, you can skip the cursor entirely and use a simple SELECT INTO:
✅ Benefits:
No need to prepare, declare, open, fetch, or close a cursor.
Cleaner and faster for single-row retrieval.
2. Use VARCHAR Instead of Fixed-Length CHAR
If the fields can vary in length, using VARCHAR can reduce unnecessary padding and improve performance:
3. Error Handling
Consider adding basic SQL error handling to catch issues like no data found or SQL errors:
🧠Summary
Your original code is solid and demonstrates good practices for dynamic SQL in RPG. However, if you're only retrieving a single row, simplifying to a direct SELECT INTO is cleaner and more efficient. If you expect multiple rows or need dynamic SQL for other reasons (e.g., building queries at runtime), your current approach is appropriate.