Empower Users with Embedded SQL

by Kevin Vandever

Do you want to provide your users with maximum subfile flexibility, but are tired of open query files, multiple logical files, and placing all your selection logic inside RPG? Whether you're just a considerate programmer, you want the users off your back, or you're simply interested in learning cool technology, it might be time for you to give embedded SQL a whirl and give the power back to the users, by letting them sort and select the data how they want, dynamically.

What Is Embedded SQL? And Why Use It?

Basically, embedded SQL allows you to place an SQL statement directly into the logic of your program. When coding a traditional AS/400 program, you concern yourself with opening physical files or logical files and positioning your file pointer, with the chain or read commands, to access the records that interest your application. When using embedded SQL, instead of concerning yourself with opening logical and physical files, you craft an SQL statement that will return the records you desire. You then move through the returned recordset, using a construct called a cursor. Moving the cursor is like using a chain or a read: It moves the file pointer from one record to another. In other words, instead of opening an actual file, with embedded SQL you are opening a virtual file (a table) that can act like a physical file, except it doesn't really exist.

The primary difference between embedded SQL and record level access is that, when using RLA, you have to worry about the physical layout of the database. What logical files and physical files you have is very important. Using embedded SQL, you are more concerned with what records you want and what order you read them in, not how they are retrieved from the physical database.

Let's say, for example, that multiple users from different departments are going to use your subfile program, but, as is usually the case with users, they each want to see the data in a different order. You can certainly accomplish this by including enough logical files in your program to cover every possible sorting criterion, but this may require many logical files, especially if the user wants secondary and tertiary sorts. This may also create unnecessary access path administration by the system if all the logical files are created for this one application. It also makes for an ugly subfile-build routine in your RPG program. Another method is to use open query files (OPNQRYFs). You could create one open query for each possible sort or dynamically create the open query based on what the user wants to do. Either way, you would be passing parameters back and forth between the RPG and CL programs to either build the OPNQRYF command or select which specific OPNQRYF command to use. It works, but it's not very clean. What about selection criteria? I have seen many a subfile program filled with selection logic. The code ends up looking like a nested IF party, and somewhere stuck in the corner of this party is the actual business logic you want to accomplish. I love users, but we programmers have a hard enough time keeping our code neat and tidy without trying to provide added flexibility.

By embedding SQL, you can dynamically build your SELECT statement based on how the user wants to sort and select the data, and you can do so in nice little routines that are separated from the subfile and business logic. You can also use techniques that allow you to provide this capability very efficiently and, with a couple of minor changes, for data residing on other AS/400s.

The DDS

WEBHITSDF is an AS/400 display file that contains a self-extending subfile--meaning that I control the paging for more records, and OS/400 handles paging back through records already loaded. I am going to assume you have a basic understanding of subfiles and will therefore jump into the SQL- related stuff. However, before I end the DDS discussion, I want to mention that each subfile record takes up two rows per subfile record on the screen. I coded it this way so that I could fit all the data I wanted to show onto the same subfile screen. Also, I have coded a window record format to allow the user to press function key 4 (F4) and select a column by which to sort. This can be expanded to allow the user to select more than one column to sort by, but for the purpose of this example, I kept it to one column.

Where Are the F-specs?

Notice in the RPG program WEBHITS that there is one F-spec, but it is used for the display file. There are no F-specs for the data files; I will introduce them in the embedded SQL. The program will list the names from a database file and allow the user to display a window by pressing the F4 key and selecting a field by which to sort. When the user presses the Enter key after selecting a field, the data is sorted by that field and redisplayed on the screen. Cool, huh? Take a look at the code, and I will explain what the embedded SQL is doing.

The D-specs

Now that you know how to compile an RPG program containing embedded SQL, you would probably like to know how to code one. First, you create the basis for your dynamic selection capability. This is done with the help of D-specs. You define a 500-byte stand-alone field--SelectOne, in this example--to hold your initial SQL statement. (The number 500 was selected at random.) You then need a variable large enough to hold your SQL statement; initialize this field with your SQL statement. Define a second 500-byte stand- alone field, SelectTwo, to hold the user-defined SQL statement, which will be made up of the initial SQL statement from SelectOne plus the selection criteria assigned by the user. I also defined a stand-alone field called OrderBy and initialized it to the field I want my subfile first sorted by. The OrderBy variable will be used again once the subfile is built and the user wants to sort the list.

How to Embed SQL

I've set up the D-specs, but I still have not embedded any SQL. Before I do, I should explain the rules. All blocks of SQL code must begin with a slash (/) in position 7, followed by the EXEC SQL statement, and they must end with a slash in position 7, followed by the END-EXEC command. In between EXEC and END-EXEC, you place all of your SQL code. Each line is signified by a plus (+) sign in position 7. For example, the first embedded SQL statement you come across is the CONNECT statement. The CONNECT statement with the RESET parameter connects to the local DB2 database. If you were going to connect to a database on another machine, you would connect to that remote database by replacing the RESET parameter with the name of the remote database.

Declare, Prepare, and Open

Now take a look at the rest of the mainline routine. First, you execute the PREP subroutine; this is where the dynamic SQL statement will be built. Create the dynamic SQL statement from what the user selected. Since this is the first time through, I use the default value for the OrderBy variable. I set this in the D-specs. Subsequent times through this subroutine, the user will have selected a field on which to sort, and that data will be placed in the OrderBy variable. Once I have appended the data in OrderBy to my original SELECT statement defined in my D-specs, I now have a completed SELECT statement that will retrieve my data and order it appropriately.

Now I am going to use that SELECT statement to retrieve my data. I use the PREPARE statement to prepare a statement called SEL using the new SelectTwo variable I just created. The PREPARE statement will validate the SQL statement contained in the SelectTwo variable. Next, I declare a cursor called MYCSR using the DECLARE statement. I am going to allow this cursor to scroll (through the returned recordset) and create the cursor from the SEL statement I prepared in the previous statement. Finally, I will open the cursor using the OPEN command.

Clear as spaghetti code, right? OK, before I go on, let me explain cursors a little. When SQL runs a select statement, the resulting rows create a result table. A cursor provides a way to access that result table. It's kind of like a subfile in its own right. The cursor is used within an SQL program to maintain a position in the result table. SQL uses the cursor to work with the data in the result table and to make it available to your program. Your program may contain several cursors, although each must have a unique name.

There are two types of cursors: serial and scrollable. A serial cursor is defined without using the SCROLL keyword. This type of cursor allows you to fetch each row once, and only once. If you want to retrieve a row more than once, you must close the cursor and re-open it.

A scrollable cursor is what I have defined in this demo program. The advantage of a scrollable cursor is that you can move back and forth throughout the rows of data. Using different parameters in a FETCH statement, you can read the next or prior row, navigate to the first or last row, read the same row over again, or position any number of rows forward or backward from the current row. This type of cursor can be used if you are building your subfile one page at a time, or self-extending, as in this example. The data remains in the cursor and is loaded into your subfile only one page at a time.

You have successfully validated the SELECT statement contained in your variable using the PREPARE command, created a scrollable cursor that will contain the result table from your SELECT statement using the DECLARE command, and opened the cursor using the OPEN command. You are now ready to load the subfile from the cursor.

Here, Boy! Fetch!

Check out the SFLBLD routine. Instead of loading the subfile directly from the database file, load it from the cursor by using the SQL FETCH command. Notice that the FETCH command is inside your DO loop. Use the FETCH NEXT command and place the results in your display file field names. Because this is a scrollable cursor, you should use the NEXT parameter.

Use SQLCOD to determine whether or not to write to the subfile record format. If you are astute, and I know you are, you may have noticed that SQLCOD is not defined anywhere in the program or the display file. So where did it come from? Well, when you embed SQL in your program and use the Create SQL RPG ILE (CRTSQLRPGI) command to create the program, the SQL Data Structure, which is not unlike the file information data structure, is included in your program. It is filled with all sorts of information on the SQL statements embedded inside your program. One example of that information is the error code returned when an SQL statement is run. It is contained in the SQLCOD variable and is set to zero upon successful execution of an SQL statement. There are all kinds of good stuff in the SQLDA, but, for this program, the only data you will use from the SQLDA is the SQLCOD variable. I will talk more about the SQLDA in a future article. Once your subfile is loaded, you are ready to display it. Now go back to the mainline.

Dynamic Sorting

When displaying the subfile the first time, you will see the data sorted by user. That is because I initialized the OrderBy variable to the user field, requser. Now that the person running this program has the data in his control, he may press F4 to sort the data another way.

When F4 is selected, a number of subroutines will be executed. First the SORT subroutine is executed, which will determine what to place in the OrderBy variable. Then the CLEAN subroutine is executed. This simply closes the cursor, MYCSR, using the CLOSE command. Once the sort criterion is determined and the cursor is closed, the PREP and SFLBLD routines are executed again. We have already seen what they do.

Let's take a closer look at the SORT subroutine. There is no new, exciting embedded SQL code in this routine. However, there is some code in here that may interest you.

It is in this subroutine that I will determine what field to append to the ORDER BY clause in my dynamic SQL statement. A window is displayed that lists the fields contained in the subfile. The user can select one of these fields and press Enter. The program then determines which field was selected and places that field in the OrderBy variable. After the user makes his choice and presses Enter, the file will be redisplayed, now resorted based on his selection.

Pretty simple, huh? Well, if you really want to separate this technique from what's easily implemented using logical files and open query files, you can allow the user to select more than one field. If the user wants to sort by user ID, browser, and time stamp, for example, you allow him to enter a 1 next to user, a 2 next to browser, and a 3 next to time stamp, after he presses F4. Then place logic in your SORT routine to interrogate all the fields selected, place them in the correct order, and place that information in the OrderBy variable. So instead of the OrderBy variable containing one field to append to the ORDER BY clause, it would now contain 3. Go ahead, give it a try. I'll wait.

Behind Option 14

Before I conclude, I want to discuss the compile options you need to make all of this work. First of all, this source member is type SQLRPGLE, not RPGLE. This tells the compiler that there are going to be embedded SQL statements in this RPG code. The command used to create a program of type SQLRPGLE is the CRTSQLRPGI program. This command verifies, validates, and, if you choose, prepares the embedded SQL statements during compile time.

When using the CRTSQLRPGI command, there are some compile options you need to take into consideration. You may want to tinker with some of the parameters when compiling your SQLRPGLE source code. The Relational Database (RDB) parameter tells you which database you are using. It can be the name of your local DB2 database, or, if you are networked using Distributed Relational Database Architecture, it can contain the name of a remote AS/400 database. If you use a remote database in the RDB parameter, you need to tell the compiler where to create the SQL package. Entering a library and package name in the SQLPKG parameter does this. The Delay Preparation (DLYPRP) parameter asks you if you want to delay preparation of your SQL statement until the program is run. Answer *YES to this parameter, because you do not want to perform redundant access path validation. If you enter *NO in this parameter, access path validation is performed at compile time and again when the cursor is opened later during run time. By entering *YES, the validation is done only at run time.

There are many other parameters in the CRTSQLRPGI command. I have mentioned a few here that I commonly deal with; however, I suggest you peruse them for yourself to see if there is anything of interest to your specific implementation of this technique.

Give It a Try!

The user can now sort on any column in the subfile, and you can modify the program to perform secondary and tertiary sorts. And you did all this with no open query files and no access paths to maintain. You have now seen how to use embedded SQL and subfiles to select and sort data dynamically, but that's only the beginning. In future articles I will discuss more about the error handling, the SQLDA, storage allocation, optimization techniques, parameter markers, and dynamic selection techniques. For now, I've given you a simple example that you can build on. Combining embedded SQL and subfile programming allows you to take your applications to new heights. Enjoy.