Wednesday, May 11, 2016

Soft coding values in SQL statements

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:

  1. ACCTNBR – Account number
  2. ACCTNAME – Name of the account
  3. 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.

14 comments:

  1. 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:
    Select * from table1 where key1 = CAST (? as char(3))
    Thank you!
    Alex Gordon

    ReplyDelete
    Replies
    1. Simon like always good article just one simple things, instead of using quote I declare a constant like this
      Dcl-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.

      Delete
    2. 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.

      Delete
  2. The 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.

    ReplyDelete
  3. Thank 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.
    Inna Golovan.

    ReplyDelete
  4. Nice 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.

    ReplyDelete
  5. From my point of view a nice way not to bother with the qoutes:
    Define 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.

    ReplyDelete
  6. 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
    Mike Brand

    ReplyDelete
  7. Excellent examples Simon and a number of scenarios covered.

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

    ReplyDelete
    Replies
    1. If I have a scenario like that I build the SQL statements in parts.

      If (Value 2 <> ' ') ;
      SQL_statment = %trimr(SQL_statement) + ' FIELD2 = :VALUE2' ;
      endif;

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Spelling 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.

      Delete
  8. I 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.

    Ringer

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.