October 28


Getting a count with SQLRPGLE Variable File Name

By NickLitten

October 28, 2021

#SQLRPGLE, #count, #file, #RPGLE, #variable, cursor, sql

Count with SQLRPGLE Variable File Name

Been having fun with SQL and RPG ILE this afternoon… the question was “How do I find out if and how many policies exist in one of the reservation files for a given customer code?”

Obviously, we could write a little piece of RPGLE doing a READ LOOP and incrementing the count value for each loop something like this:

clear  countX ;

setll ( mypolicy ) policyfilename;
reade ( mypolicy ) policyfilename;
dow not %eof(filename);
  countX += 1;
  reade ( mypolicy ) policyfilename; 

if countX = 0;
  errormsg = 'Policy value ' + myPolicy + ' not found';

But, for this little blog let’s look at a very simple way of doing this in the SQL RPG.

In it’s simplest form this SQLRPGLE might look like this:

exec sql
select count(*) into :countX from policyfilename where policyFieldName = :myPolicy;

if sqlcode < 0 or sqlcode > 100;
  errormsg = 'Policy value ' + myPolicy + ' not found';

What if we want to build that select statement in a variable?

We sometimes want to build more complex SELECT statements, with longer more complex selection fields.

Here is the same code block using a variable statements and declaring the select statement as a cursor so we can read the cursor (the constructed select statement) to get the count value for the number of policy lines found.

This is just a code example to show you how to use the cursor function with a variable

The constant value singleQuote simply contains the character to ease variable construction

Code is just an example of how to do it – not the best way of doing it

A Dynamic SQL statement with a variable file name in SQLRPGLE

RPG Code Snippet SQLRPGLE Variable File Namedcl-c singleQuote const(''''); // single quote 

SelectStatmnt = 'Select Count(*) from '
 + %trim(policyFileName) + ' where policyFieldName = '
 + singleQuote + %Trim(myPolicy) + singleQuote;

exec sql prepare stmt from :SelectStatmnt ;

exec sql declare myCursor cursor with return to client for stmt;

exec sql open myCursor;

exec sql fetch myCursor into :numberOfClaims;

// Variable "numberOfClaims" now contains the count value
Dsply %char(numberOfClaims); 

exec sql Close C1;


Which just goes to show… there is… in fact… more than one way to skin a cat.

  • direct concatenation of variable mypolicy in last example can lead to sql injection and require unnecessary validation efforts. It’s bad habit.
    just use prepared statement parameters.

  • What do you think of initializing the variable countX?
    Do you think it is good practice to set it to zero?
    Or is that a waste of code?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips