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

1. 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.
2. Preparing the SQL Statement – PREPARE compiles the SQL text from SqlString into an executable statement (Sql1).
3. Declaring the Cursor – The cursor (Cursor1) is linked to the prepared SQL for row-by-row processing.
4. Assigning the Parameter – The program assigns 'BBB' to wCLCIB0.
5. Opening the Cursor – OPEN ... USING replaces the ? in the SQL with wCLCIB0.
6. Fetching Data – FETCH retrieves the first matching row into wCLNOM0.
7. Displaying the Result – DSPLY outputs the result for demonstration.
8. 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!