Using a Parameterized Cursor in SQLRPGLE

Using a Parameterized Cursor in SQLRPGLE – A Simple and Clean Example

When working with embedded SQL on IBM i, using a cursor with parameters is a great way to selectively retrieve data from a file based on runtime input. In this post, I’ll walk you through a simple SQLRPGLE program that demonstrates this concept.

This free-format RPG example shows how to:

• Accept an input parameter
• Open a cursor based on that parameter
• Fetch records from the CLANA00F file
• Display basic customer information
• Properly handle SQL errors

What Is CLANA00F?

The file CLANA00F contains customer master data. Here's a simplified layout of its most relevant fields:

Field	Type	Description
CLCCL0	S(7)	Customer numeric ID
CLNOM0	A(50)	Customer name
CLCIB0	A(3)	International code

Our goal is to retrieve only those customers whose CLCIB0 matches a parameter passed when the program is called.

Program Highlights

ctl-opt dftactgrp(*no) actgrp(*caller);

dcl-s CLCCL0 zoned(7);
dcl-s CLNOM0 varchar(50);
dcl-s CLNOM0_10 char(10);

dcl-pi *n;
  CIBIntCode char(3); // Input parameter: International code
end-pi;

We declare a few working fields and accept a 3-character input code that will filter our result set.

Cursor Declaration and Processing

exec sql declare empCur cursor for
    select CLCCL0, CLNOM0 
    from CLANA00F 
    where CLCIB0 = :CIBIntCode;

exec sql open empCur;

The cursor empCur selects only customers that match the input CIBIntCode.

We then use a loop to fetch and display each matching record:

dow sqlCode = 0;
    exec sql fetch empCur into :CLCCL0, :CLNOM0;

    if sqlCode = 0;
        CLNOM0_10 = %trim(CLNOM0) + '...';
        dsply ('Customers Name: ' + CLNOM0_10);
        dsply ('Customers ID: ' + %char(CLCCL0));
    endif;
enddo;

Error Handling and Cleanup

exec sql close empCur;

if sqlCode < 0;
    dsply ('SQL Error: ' + %char(sqlCode));
else;
    dsply ('Cursor closed successfully.');
endif;

*inlr = *on;
return;

Proper error handling is essential, even in small utility programs. Here, we close the cursor and display whether the operation succeeded or failed.

Here is the full source code

**free ctl-opt dftactgrp(*no) actgrp(*caller); // This program demonstrates how to use a cursor with a parameter in SQLRPGLE. // It fetches Customers records from the CLANA00F file based on a specific header code // and displays the Customers ID and name. The cursor is opened, rows are fetched in a // loop, and finally, the cursor is closed. Error handling is included to manage SQL errors // that may occur during the fetch or close operations. // The program uses free-form RPG syntax and SQL embedded in RPGLE. // ********************************************************************** // CLANA00F is a file containing customer data. // The file structure is defined in the comments above. // ********************************************************************** // CLANA00F   Record format: CLANA // COLUMN     | T |  POS. |  POS. | ---FIELD-- | DE | // NAME       | Y |  FROM |    TO | BYTES | LN | CI | //            |   |       |       |       |    |----| // CLANN0     | A |     1 |     1 |     1 |    |    | // CLCCL0     | S |     2 |     8 |     7 |  7 |    | // CLCIB0     | A |     9 |    11 |     3 |    |    | // CLNOM0     | A |    12 |    61 |    50 |    |    | // CLSTS0     | A |    62 |    62 |     1 |    |    | // ********************************************************************** // Call this program with a parameter to specify the customer code. // Example: CALL PGM(CUR_X) PARM(('XXX')) dcl-s CLCCL0 zoned(7); dcl-s CLNOM0 varchar(50); dcl-s CLNOM0_10 char(10);   // Declare input parameter dcl-pi *n;   CIBIntCode char(3);   // Input parameter: International code end-pi;   // Declare cursor with parameter exec sql declare empCur cursor for     select CLCCL0, CLNOM0 from CLANA00F     where CLCIB0 = :CIBIntCode;   // Open the cursor exec sql open empCur;   // Fetch rows one by one dow sqlCode = 0;     exec sql fetch empCur into :CLCCL0, :CLNOM0;       if sqlCode = 0;         // Display Customers details         CLNOM0_10 = %trim(CLNOM0) + '...'; // Truncate name for display         dsply ('Customers Name: ' + CLNOM0_10);         dsply ('Customers ID: ' + %char(CLCCL0));     endif; enddo;   // Close the cursor exec sql close empCur; // Handle SQL errors if sqlCode < 0;     dsply ('SQL Error: ' + %char(sqlCode)); else;     dsply ('Cursor closed successfully.'); endif; // End of program // When using embedded SQL and you don't have any files declared // in the D-spec or F-spec, you could omit *INLR = *ON, // but it's good practice to include it for compatibility // and clarity. *inlr = *on; return;

Sample Call

CALL PGM(CUR_X) PARM('ITA')

This will display all customers with CLCIB0 = 'ITA'.

Conclusion

This example is a great starting point for anyone looking to use parameterized cursors in SQLRPGLE. It’s clean, practical, and highlights how you can integrate SQL logic directly into your RPG programs for dynamic data retrieval.

This approach is just the beginning. In real-world projects, it scales beautifully into more complex logic. I’ll explore those use cases in a future article.