I was recently asked was it possible to "soft code" values into SQL statements, i.e. pass different parameters to the same SELECT statement. This is something I do on a regular basis. I looked in this website and could not find an example, therefore, I decided to write this post to show three methods I have used.
All of these examples are in SQLRPGLE programs using fully free RPG, but these examples can be easily changed to be compatible with any of the earlier forms of the RPG language.
So let's get started with the file I will be using in these examples. The file, called TESTFILE, has only three fields:
- ACCTNBR – Account number
- ACCTNAME – Name of the account
- ACCTSTATE – State when the account resides
ACCTNBR ACCTNAME ACCTSTATE 1 FIRST NATIONAL BANK CA 2 DATASTREAM TECHNOLOGIES GA 3 FRONTIER CAFE CA 4 AMERICAN AIRLINES NY 5 MICROSOFT WA 6 TOGO COLUMBIA AZ 7 AZTECA CERVEZA NM 7 LIGHTROOM SOLUTIONS IL 8 HEALTH CARE SERVICES INT'L GA 9 WARNER BUSINESS TELEPHONE DC 10 RED SHOES LLC XX
In the examples I want to select the same information: an account in Georgia (state code = GA) with an account number of greater than 5.
All of these programs start the with the same code, even if the example program does not use the variables defined:
01 **free 02 dcl-ds InputDs extname('TESTFILE') qualified ; 03 end-ds ; 04 dcl-s String char(1024) ; 05 dcl-s wkFile char(10) ; 06 dcl-s wkAccount like(InputDs.AcctNbr) ; 07 dcl-s wkState like(InputDs.AcctState) ; 08 dcl-s RowsDeleted int(10) ; 09 wkFile = 'TESTFILE' ; 10 wkAccount = '5' ; 11 wkState = 'GA' ;
Line 1: This is "fully free" so I need my **FREE.
Lines 2 and 3: This data structure is "externally defined" to be the same as my file TESTFILE. I am going to use this to contain the data I get from performing a FETCH in SQL. If you are unfamiliar with them you can learn about externally defined data structures.
Lines 4 - 8: These are the various variables I will be using in the examples.
Lines 9 – 11: As these are just example programs I move values into the variables defined previous. In a real life situation this lines would not exist, as the values used by the SQL statements would probably passed to the program.
All my examples end the same way too:
19 dsply (%trimr(InputDs.AcctNbr) + ' = ' + %trimr(InputDs.AcctName) + ',' + InputDs.AcctState) ; 20 *inlr = *on ;
Line 19: As this is an example I want to see the information I had fetched from the file. The Display operation combines the three fields from the file in a single line that is easily readable.
Line 20: Do I really need to explain this line?
And now onto the interesting part…
12 String = 'SELECT * FROM TESTFILE WHERE ACCTNBR > ? + AND ACCTSTATE = ?' ; 13 exec sql PREPARE S0 FROM :String ; 14 exec sql DECLARE C0 CURSOR FOR S0 ; 15 exec sql OPEN C0 USING :wkAccount,:wkState ; 16 exec sql FETCH C0 INTO :InputDs ; 17 exec sql CLOSE C0 ;
Line 12: When I build the string that contains the SQL statement I place a question mark ( ? ) in the places where I want the "soft coded" parameter to be.
Line 13: The PREPARE statement is used to convert a character string to an executable form of a SQL statement.
Line 14: The declaration of the cursor has to have the extra FOR S0 as the SQL statement is in S0.
Line 15: When I open the cursor I need to give the values to replace the question marks. If the is more than one question mark the variables are separated by a comma.
Line 16: I fetch the first row that matches my select statement's criteria into the InputDs data structure.
Line 17: As I have an open cursor I need to close it.
When I run the program I get the following output displayed:
DSPLY 8 = HEALTH CARE SERVICES INT'L,GA
Just the variables
12 exec sql DECLARE C0 CURSOR FOR SELECT * FROM TESTFILE WHERE ACCTNBR > :wkAccount AND ACCTSTATE = :wkState ; 13 exec sql OPEN C0 ; 14 exec sql FETCH C0 INTO :InputDs ; 15 exec sql CLOSE C0 ;
Line 12: In the select statement I can use the RPG variable names with a starting colon ( : ). The colon indicates to the SQL precompiler that this is a RPG variable, which it then includes in the generated code.
Line 13: This time when I open the cursor I do not need the extra FOR code.
Lines 14 and 15: These are the same as lines 16 and 17 in the previous example.
Completely free text
This uses the same logic as the first example, except there are no substitution parameters. I just insert the values into the string where I want them:
12 String = 'SELECT * FROM ' + %trimr(wkFile) + ' WHERE ACCTNBR > ''' + %trimr(wkAccount) + ''' AND ACCTSTATE = ''' + wkState + ''' ' ; 13 exec sql PREPARE S0 FROM :String ; 14 exec sql DECLARE C0 CURSOR FOR S0 ; 15 exec sql OPEN C0 ; 16 exec sql FETCH C0 INTO :InputDs ; 17 exec sql CLOSE C0 ;
Line 12: I just insert the values of the variables I want to use, including the file name, into the string.
Lines 13 – 17: These are identical to the same numbered lines in the first example.
All of the previous examples have been selects. But what if I wanted to do something else, like delete rows/records from a table/file?
There is a SQL statement EXECUTE IMMEDIATE that executes whatever is in the string it is given. For example, I want to delete all records from TESTFILE where the state code is "XX":
12 wkState = 'XX' ; 13 String = 'DELETE FROM ' + %trimr(wkFile) + ' WHERE ACCTSTATE = ''' + wkState + ''' ' ; 14 exec sql EXECUTE IMMEDIATE :String ; 15 if (SQLCOD < 0) ; 16 dsply ('SQLCOD = ' + %char(SQLCOD)) ; 17 endif ; 18 exec sql GET DIAGNOSTICS :RowsDeleted = ROW_COUNT ; 19 dsply ('No. of records deleted = ' + %char(RowsDeleted)) ;
Line 13: I am building my SQL statement to delete the records.
Line 14: I can just use the EXECUTE IMMEDIATE to execute my statement without any preparation.
Lines 15 – 17: I always include some code to do with if the SQL statement could not be executed because of an error. If there is an error the value of SQLCOD will be less than zero. The value of SQLCOD will then be displayed.
Line 18: I want to display the number of records I have deleted. I can do this by using GET DIAGNOSTICS to retrieve the number of rows (records) that were affected by the previous SQL statement. For more information about this you should read Getting the number of rows affected by SQL statement.
With this example data when this program is run the following is displayed:
DSPLY No. of records deleted = 1
Many releases ago I wrote a command that could be used to execute simple SQL statements in a CL program. The program that was called by the command was just a SQLRPG program that used EXECUTE IMMEDIATE.
Hopefully these simple examples have shown you how you can "soft code" the parameters you use with SQL and will give you the basis to make more complex examples for yourself.
You can learn more about this from the IBM website:
This article was written for IBM i 7.2, and should work for earlier releases too.