October 28

3 comments

Getting a count with SQLRPGLE Variable File Name

By NickLitten

October 28, 2021

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

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; 
enddo;

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

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';
endif;

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;

/end-free

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.
    thanks

  • 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

    >