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…
Substitute parameters
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.
Execute immediate
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.
Good article! Thank you! One suggestion. I always use CAST expression in place of a single substitution question mark. Using CAST expression allows you to be sure which data type is going to used on the DB side instead of relying on automatic casting from your language data type (RPG, Java, whatever) to the database data type. For example 111 as a substitution value supplied by the program might be casted to NUMERIC on the DB side while you really want it to be a string. Here is an example of how to get this. In your SQL you specify:
ReplyDeleteSelect * from table1 where key1 = CAST (? as char(3))
Thank you!
Alex Gordon
Simon like always good article just one simple things, instead of using quote I declare a constant like this
DeleteDcl-C Quote Const(''') and use it anywhere I need a quote for example instead of this
String = 'DELETE FROM ' + %trimr(wkFile) +
' WHERE ACCTSTATE = ''' + wkState + ''' ' ;
I would do this
String = 'DELETE FROM ' + %trimr(wkFile) +
' WHERE ACCTSTATE = ' + Quote + wkState + Quote ;
if you mess Those quotes they can make you pull your hair.
Haha. I had this problem 37 years ago when I programmed in Cobol on IBM/370. I hated staring at quotes to be accurate so I defined them as a variable and used it instead. Good one Jose.
DeleteThe method you push to get a value from a row is way too complex and too many statements and steps, but since one of the dynamic values is the file you are forced to do it this way. We need to get away from the PREPARE statement as much as possible because there is a better way to use dynamic declared cursors. As you have shown can just specify your parms directly in the DECLARE statement: DECLARE C1 CURSOR FOR SELECT CUST_NAME FROM CUSTMAS WHERE CUST# = :customer#; however this will not work if the file is dynamic. This will allow you to cut out the SQL string and the PREPARE. But even this can be greatly improved by the VALUES command: VALUES (SELECT CUST_NAME FROM CUSTOMS WHERE CUST# = :customer#) INTO :custName :NullID; You can test NullID for -1 if no row was found. This last method uses just 1 statement to do the same thing, but again if one of the dynamic parms is file, this will not work either, so we are stuck using a PREPARE if the file is one of the dynamic parms.
ReplyDeleteThank you Simon. This is actually very useful. I was always using the "just the variables" way of doing it but now I think the first one with the substitution variables is more flexible. This way the String can be reused with different variables. Alex's comment makes it even better... Thank you Alex.
ReplyDeleteInna Golovan.
excelente!!!
ReplyDeleteNice article, Simon. It's good to show different ways to do things as one size doesn't fit all. I rarely use the Prepare method, though, while it's useful when I don't know which library and/or file I need, DSPPGMREF doesn't know/show the file(s) being used by the program.
ReplyDeleteFrom my point of view a nice way not to bother with the qoutes:
ReplyDeleteDefine the SQL statement as a string (either as varchar or as a constant) and then use the %SCANRPL function (7.1) or write your own one, to replace the variable informations.
SqlStm = 'DELETE FROM &FILE WHERE ACCTSTATE = &STATE' ;
Sql = %ScanRpl('&FILE':wkFile:SqlStm);
Sql = %ScanRpl('&STATE':wkState:SqlStm);
The %ScanRpl replace the information in the whole string.
I was struggling with a complex (for me) variable SQL select and this post helped greatly. Program is now up and running in production and the users are very happy. Thanks
ReplyDeleteMike Brand
Excellent examples Simon and a number of scenarios covered.
ReplyDeleteThe one answer I can't seem to find anywhere is how to code a select statement where the number of WHERE conditions vary depending on input parameters for example.
Rather than code different SELECT and PREPARE statements for each combination of selected conditions, it would be nice if there was a way to "easily" code a single SELECT which catered for varying numbers of conditions.
Example 1:
SELECT field1, field2... from file where field1=:value1 and field2=:value2;
Example 2:
SELECT field1, field2... from file where field1=:value1 and field2=:value2 and field3=:value3;
If using PREPARE statements, the first example would need 2 values to be passed in the OPEN CURSOR, but the second example would need 3 values.
I know how I would do this in PHP, but I can't think of an obvious simple solution in RPG.
If I have a scenario like that I build the SQL statements in parts.
DeleteIf (Value 2 <> ' ') ;
SQL_statment = %trimr(SQL_statement) + ' FIELD2 = :VALUE2' ;
endif;
This comment has been removed by the author.
DeleteSpelling corrections: There is a more efficient way. Its best to put parms in the SELECT statement it's self than to be too dynamic via PREPARE statement. Static usually runs faster than dynamic, and with static the program knows a lot more about the SELECT at compile time than if it were too variable (prepared). Lets say you want to filter by CustName and/or sometimes by customer_Zipcode but not always. You don't want to code two WHERE clauses or two SELECTS. Then code a WHERE clause in your DECLARE or SELECT INTO or VALUES statements (no prepare), the code for the WHERE clause is like this "WHERE (:pCustName = ' ' OR CustName = :pCustName) AND (:pCustZip = 0 OR CustZip = :pCustZip). In this way, you can use or not use the Customer Name argument and/or the CustZipcode argument. In this way no reason to use PREPARE. Static is better than dynamic if you can help it. I'm not anti-PREPARE, but its best to have a bias against PREPARE and dynamic if you can help it. They do have their uses however.
DeleteI use the ? parameter markers because it prevents SQL injection. Else if someone enters string value such as gotcha' or 1=1 -- this could short circuit your query. Everything after the -- becomes a comment.
ReplyDeleteRinger