Dynamic Selection with Embedded SQL

by Kevin Vandever

In "Empower Users with Embedded SQL," I showed you how to dynamically sort subfiles on any column in a subfile by embedding SQL into your RPG code and then allowing the user to select the column, or columns, by which to sort. In this article I am going to build on that application by showing you a couple of ways to dynamically filter or select the data that you want to display in your subfile.

Parameter Markers

Let's start small and work from there. First, I am going to allow the user to key a date range to shorten our Web hit list from the last article. The source code that contains this modification is webhits2.txt. Adding this to the dynamic sort doesn't seem like a big deal at first. I can use a logical view sorted by time stamp to set a lower limit at from date/time and read through the file until I reach to date/time. That's great if I always want the data sorted by time stamp, but what if I still want it sorted by user or browser? Now I'm back to using multiple logical files and negotiating my way though them to get my desired data. However, with fixed-list select statements--that is, select statements that contain a fixed number of parameters of the same data type--I can use one or more place holders (parameter markers) inside my select statement and substitute them with data at a later time, like when a user enters a date range. To do this, simply include a question mark (?) inside your select statement where your data would normally go. The following is a modified version of the SelectOne D-spec from my previous program, in the last article:

  D SelectOne    S         500A   INZ('SELECT requser, reqts,  - 
  D                               browser, UCASE(reqfile)  -     
  D                               FROM webtemp2 -                
  D                               WHERE reqts >= ?  And  -       
  D                               reqts <= ? ')

Notice that the WHERE clause states that the time stamp reqts, inside webtemp2 (to create the webtemp2 file, follow the instructions in the webtemp2_install.txt file), should be greater than or equal to some data to be determined later, and less than or equal to some other data to be determined earlier. The data to be determined later will come from the entry screen that allows the user to key in a date range. I have set defaults in the time stamps so that even if the user presses Enter without entering anything, the SQL will still contain valid from and to time stamps.

Now that the user has entered time stamps, or taken the defaults, these entries can be inserted into the select statement shown in the webhits2 source code. I do this during the opening of the cursor. Remember the PREP subroutine from my last article? This subroutine will contain a minor modification, and that modification will take place in the OPEN statement. Instead of simply opening MYCSR, I will now provide two host variables; you got it: one for each question mark (?), which will be used to insert data into my select statement. The following code snippet shows the modified OPEN statement employing the USING parameter with the from (date1) and to (date2) time stamps:

       * Open the SQL cursor.                
       *                                     
      C                                      
      C/EXEC SQL                             
      C+ OPEN MYCSR USING :date1, :date2   
      C/END-EXEC                             
       *

This minor modification to my original dynamic sorting subfile can provide your users with added flexibility without adding complicated logic to your program. For a more complicated--though more flexible--solution, let's move on.

The Where Clause

You won't always be able to use placeholders with fixed-list select statements. There may be times when you allow for optional data to be keyed. Adding to my last example a little, what if I provided capabilities to filter by user, and downloaded files but made them optional; that is, a blank entry in user field would include all users, just as a blank entry in downloaded files would include all downloaded files? In this example, webhits3.txt, depending on what was entered, your select statement will change, specifically in the WHERE clause portion of it.

The D-spec that defines my select statement, SelectOne, becomes much simpler and is built only to the lowest common denominator. The rest of the statement will be built based on what is entered on the display screen. I used this technique in the WEBHITS file from my last article to add the Order By clause, but this is a little more complex. Here is the basic select statement:

   D SelectOne    S         500A   INZ('SELECT requser, reqts,  -   
   D                               browser, UCASE(reqfile)  -       
   D                               FROM webtemp2')                    

The WHERE and ORDER BY clauses will be added to the select statement as the user enters data on the screen. It is the WHERE clause that may take many forms. If no user or downloaded file data is entered, then neither will be included in the WHERE clause. However, if either one or both were entered, they would be included. Check out the webhits3 source code, specifically the FILTER subroutine, to understand what happens whenever the Enter key is pressed on the selection screen.

The first thing the routine does is clear the WhereClause variable. Then it always performs the time stamp filter evaluation, because I always set from and to time stamp defaults. It's too much work to leave those fields blank and try to figure out if none, one, or both were entered; so I default the from time stamp to *loval and set the to time stamp to *hival. Next, the routine checks to see if something was entered in the user field. If so, then that entry is added to the WHERE clause and the list will only include user IDs matching that entry. The last thing the routine does is check to see if something was entered in the downloaded file field. If so, I want to retrieve downloaded files that contain the wildcard entered in the field. For example, if AS400 were keyed into this field, I only want to see downloaded files containing AS400 somewhere in the name.

You will notice that you have to be very specific when building a select statement this way. Everything must be exact. That is why you see the %TrimR function used to trim leading and trailing blanks, as well as the quote constant, which is declared in the D-specs, used to surround the data keyed on the screen. For flexibility, I also defined constants to hold my file field names instead of hard-coding them into the WHERE clause. Those of you who are accustomed to building complicated OPNQRYFs (Open Query Files) should have no problems with this stuff.

Filter Away

There you have it, a robust application that allows the user to both select and sort data dynamically, at their whim. Of course, there are potential performance issues if you are attacking a very large database and performing complicated selection and sorting criteria. So play with it a little and do some testing. Also, run your programs under debug and check the joblog for recommendations on permanent access path creation provided by the DB2 optimizer. This will come in handy when trying to maximize flexibility but minimize performance issues.