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
dcl-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.
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?
Thank you very much, this information has been very helpful.