When sorting data to be displayed in a subfile, the Open Query File (OPNQRYF) command is often used. This comes in handy when you are providing the user with the ability to change how he displays the data in the subfile. I agree that OPNQRYF is a powerful way to select and sort data before loading your subfile, but its real strength is sorting and selecting data in a batch environment. I have used it with subfile programs, and, although
it’s a bit of a pain, it does work, especially if you use optimizing techniques. Another method for providing multiple sorting criteria in your subfiles is to use logical files. One problem with logical files is that you are required to maintain an access path for every sort criterion required by your users. Another problem is the complexity of your subfile load routine when using multiple logical files to provide the users with different looks at the data. That gets ugly.

What if you had a tool that allowed you the same select and sort capabilities as logical files and OPNQRYF but did so in a flexible and efficient manner, while allowing you to produce a well-structured, easily maintainable program? Wouldn’t that be awesome? Well, with embedded SQL, that’s just what you get. SQL embedded into your RPG program allows you to sort and select the data as you want, but it also provides the user with some added power.

What if 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 for 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 to allow dynamically sorting subfiles is to use OPNQRYF. 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. I’ve messed around with the latter method, and the CL can get cumbersome if you try to give the users complete flexibility by allowing them to sort by more than one field at a time. I love users, but that’s a lot of work just to provide them with a different view of the same data.

By embedding SQL, you can dynamically build your SELECT statement based on how the user wants to sort the data, all in one neat little subroutine. You can also use techniques that allow you to provide this capability very efficiently and, with a couple of minor changes, for data on other AS/400s. I’ll tell you how it’s done.

There’s Nothing to See Here, Folks

The DDS in Figure 1 is a standard load-all subfile. I have coded a window record format to allow the user to select fields by which to sort. I could have made this window a subfile window, but there are only four fields from which to choose, so I decided to make the selection window a regular format.

Look, Mom. No F-specs

Well, that’s not exactly true. In Figure 2 (page 96), there is one F-spec in the RPG program, 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 is a basic load-all subfile program to list the names from a database file. This program allows the user to display a window by using 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.

Behind Option 14

Before I dig into the RPG code itself, I want to discuss the compile options you need to make this work. First of all, this source member is type SQLRPGLE, not RPGLE. The SQL tells the compiler that there are embedded SQL statements in this RPG code. The command used to create a program using an SQLRPGLE-type source member is Create SQL RPG ILE Program (CRTSQLRPGI). This command verifies, validates, and, if you choose, prepares the embedded SQL statements during compile time. To use this command, you must have the SQL Development Kit licensed program.

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 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 (DRDA), 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. This is done by entering a library and package name in the SQLPKG parameter. 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 runtime. By entering *YES, the validation is done only at runtime.

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.

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. Define a 500-byte standalone field, SELCT1, to hold your initial SQL statement. (The number 500 was selected at random. You need a variable large enough to hold your SQL statement.) Initialize this field with your SQL statement. Define a second 500-byte standalone field, SELCT2, to hold the user-defined SQL statement, which will be made up of the initial SQL statement from

SELCT1 plus the selection criteria assigned by the user. Also define a standalone field called ORDER and initialize it to DBLNAM, which is the last-name field in the database file. The ORDER variable will be used to append the field selected by the user to the ORDER BY clause in the SELCT1 variable.

Rules and Regulations

All blocks of SQL code must begin with a slash (/) in position 7 followed by the EXEC SQL statement and must end with a slash in position 7 followed by the END-EXEC command. In between EXEC and END-EXEC is where you place all of your SQL code. Each line is signified by a plus sign (+) in position 7. The CONNECT RESET statement connects to the local DB2 database. If you were going to connect to a database on another machine, you would do so by replacing the RESET parameter with the name of the remote database.

Prepare, Declare, 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. Because this is the first time through, use the default value for the ORDER variable, setting it to DBLNAM in the D-specs. On subsequent times through this subroutine, the user will have selected a field by which to sort, and that data will be placed in the ORDER variable. Once you have appended the data in ORDER to the original SELECT statement defined in your D-specs, you have a complete SELECT statement that will retrieve your data and order it appropriately.

Now use that SELECT statement and retrieve your data. Then use the PREPARE statement to prepare a statement called SEL, using the new SELECT2 variable you just created. The PREPARE statement will validate the SQL statement contained in the SELECT2 variable. Next, declare a cursor called MYCSR by using the DECLARE statement. You should allow this cursor the ability to scroll; create it from the SEL statement you just prepared in the previous line. Finally, open the cursor by 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 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 one time, you must close the cursor and reopen it. This kind of cursor is perfect for a load-all subfile because you want to see the data only once before loading it into your subfile. The subfile provides the scrolling of the data in this case.

The scrollable cursor is what you have defined in your program, even though it is a load-all subfile. The advantage of a scrollable cursor is that you can move back and forth throughout the rows of data. By 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. The data remains in the cursor and is loaded into your subfile only one page at a time.

OK. You have successfully validated the SELECT statement contained in your variable by using the PREPARE command; you’ve created a scrollable cursor that will contain the result table from your SELECT statement by using the DECLARE command;

and you’ve opened the cursor by using the OPEN command. Now you are 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, but, as I mentioned before, you don’t have to use a scrollable cursor when using a load-all subfile. I had you do this simply because I wanted to show you some of the parameters you can use with a scrollable cursor. Figure 3 shows that, in addition to NEXT, there are other parameters with the FETCH command.

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 CRTSQLRPGI command to create the program, a data structure 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 0 upon successful execution of an SQL statement. For more information on the SQL Data Area (SQLDA), look in IBM’s DB2 for AS/400 SQL Programming manual. For this program, the only data you should use from the SQLDA is the SQLCOD variable.

Once your subfile is loaded, you are ready to display it. Now go back to the mainline.

Let’s Kick It Up a Few Notches

When displaying the subfile the first time, the data is sorted by last name because you initialized the ORDER variable to the last-name field. Now that the user has the data in his control, he can press F4 to sort the data another way. If F4 is selected, a number of subroutines are executed. First, the SORT subroutine is executed, which determines what to place in the ORDER variable. Then, the CLEAN subroutine is executed. This simply closes the cursor, MYCSR. Once the ORDER variable is given its proper value and the cursor is closed, the PREP and SFLBLD routines are executed again. You have already seen what they do.

The last thing I want to take a look at is the SORT subroutine. There is no new, exciting subfile code in this routine, nor is there any SQL. However, there is some code that may interest you. It is here that you determine what field to append to the ORDER BY clause in your 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 ORDER variable. Pretty simple, huh? Well, if you really want to separate this technique from what’s easily implemented by 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 first name, last name, and nickname, you allow him to enter a 1 next to first name, a 2 next to last name, and a 3 next to nickname. 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 ORDER variable. So instead of the ORDER variable containing one field to append to the ORDER BY clause, it now contains three.

Now that the user has selected a field to sort by and pressed the Enter key, the subfile is redisplayed in the appropriate order, with no open query files and no access paths to

Voila!

maintain. You have now seen how to use embedded SQL and subfiles to select and sort data dynamically. If you are interested in learning more about SQL programming, I suggest you dig into IBM’s DB2 for AS/400 SQL Programming manual. There you will learn all sorts of advanced techniques, such as allocating storage and loading the SQL data area (SQLDA) by using the SQL DESCRIBE command as well as implementing optimizing techniques to make your programs scream even more. Combining embedded SQL and subfile programming allows you to take your applications to new heights. Enjoy.

REFERENCES AND RELATED MATERIALS

• DB2 for AS/400 SQL Programming (SC41-5611-03, CD-ROM QB3AQ803)

A DSPSIZ(24 80 *DS3)

A PRINT ERRSFL

A CA03 CA12 CF04

A*

A R SFL1 SFL

A DBLNAM R O 5 2REFFLD(DBLNAM SFL001PF)

A DBFNAM R O 5 26REFFLD(DBFNAM SFL001PF)

A DBMINI R O 5 50REFFLD(DBMINI SFL001PF)

A DBNNAM R O 5 55REFFLD(DBNNAM SFL001PF)

A*

A R SF1CTL SFLCTL(SFL1)

A SFLSIZ(0050) SFLPAG(0017)

A OVERLAY

A N32 SFLDSP

A N31 SFLDSPCTL

A 31 SFLCLR

A 90 SFLEND(*MORE)

A RRN1 4S 0H SFLRCDNBR

A 4 2'Last Name' DSPATR(HI)

A 4 26'First Name' DSPATR(HI)

A 4 50'MI' DSPATR(HI)

A 4 55'Nick Name' DSPATR(HI)

A 1 2'SFL013RG'

A 1 26'Dynamic Sort with Embedded SQL'

A DSPATR(HI)

A 1 71DATE EDTCDE(Y)

A 2 71TIME

A*

A R FKEY1

A 23 2'F3=Exit' COLOR(BLU)

A +3'F4=Prompt' COLOR(BLU)

A +3'F12=Cancel' COLOR(BLU)

A R WINDOW1

A WINDOW(*DFT 11 25)

A TAB1 1 B 3 2

A 3 5'Last Name'

A TAB2 1 B 4 2

A 4 5'First Name'

A TAB3 1 B 5 2

A 5 5'Middle Initial'

A TAB4 1 B 6 2

A 6 5'Nick Name'

A 10 1'F3=Exit' COLOR(BLU)

A 10 11'F12=Cancel' COLOR(BLU)

A 1 3'Select a sort field'

A DSPATR(HI)

Figure 1: Using SQL to load a subfile requires no special DDS.

*****************************************************************

* To compile:

* CRTSQLRPGI OBJ(xxx/SFL013RG) SRCFILE(xxx/QRPGLESRC) +

* SRCMBR(SFL013RG) RDB(*LOCAL) OBJTYPE(*PGM) +

* DLYPRP(*YES) SQLPKG(*OBJ)

*****************************************************************

Fsfl013df2 cf e workstn sfile(sfl1:rrn1)

F infds(info)

*

* Information data structure to hold attention indicator byte.

Dinfo ds

D cfkey 369 369

* Constants for attention indicator byte

Dexit C const(X'33')

Dprompt C const(X'34')

Dcancel C const(X'3C')

Denter C const(X'F1')

Dorder S 8 INZ('dblnam')

Dselct1 S 500A INZ('SELECT dblnam, dbfnam,D dbmini, dbnnamD FROM sfl001pfD ORDER BY ')

Dselct2 S 500A INZ(' ')

* Establish the connection to the remote machine. The -842 return

* code indicates that the connection is already established. If

* you want to connect to the local machine, use CONNECT.

C/EXEC SQL

C+ CONNECT RESET

C/END-EXEC

C exsr prep

C exsr sflbld

*

C dou (cfkey = exit)

C write fkey1

C exfmt sf1ctl

C select

* prompt to selection sorting criteria

C when cfkey = prompt

C exsr sort

C exsr clean

C exsr prep

C exsr sflbld

C when cfkey = cancel

C leave

C endsl

C enddo

*

C exsr clean

* Disconnect the current connection.

C/EXEC SQL

C+ DISCONNECT CURRENT

C/END-EXEC

C eval *inlr = *on

*****************************************************************

* Prepare SQL cursor

*****************************************************************

C prep begsr

*

* Prepare the SQL statement for validation, since the program was

* compiled with DLYPRP (*YES), it will wait until it is used before

* it prepares the cursor.

*

C eval selct2 = %trimr(selct1) + ' ' + order

*

C/EXEC SQL

C+ PREPARE sel FROM :selct2

C/END-EXEC

*

* Declare the SQL cursor to hold the data retrieved from the SELECT

C/EXEC SQL

C+ DECLARE MYCSR SCROLL CURSOR FOR SEL

C/END-EXEC

*

* Open the SQL cursor.

C/EXEC SQL

C+ OPEN MYCSR

C/END-EXEC

*

C endsr

*****************************************************************

* Clean up before exiting

*****************************************************************

C clean begsr

*

* Close the SQL cursor after all processing is complete.

C/EXEC SQL

C+ CLOSE MYCSR

C/END-EXEC

*

C endsr

*****************************************************************

* Build the subfile

*****************************************************************

C sflbld begsr

*

* Clear the subfile

C eval *in31 = *on

C write sf1ctl

C eval *in31 = *off

C eval rrn1 = 0

*

* Process the records in the SQL cursor until the return not = 0

C dou sqlcod 0

* Get the next row from the SQL cursor.

C/EXEC SQL

C+ FETCH NEXT FROM MYCSR

C+ INTO :dblnam, :dbfnam, :dbmini, :dbnnam

C/END-EXEC

C if sqlcod = 0

C eval rrn1 = rrn1 + 1

C write sfl1

C endif

C enddo

*

C if rrn1 = 0

C eval *in32 = *on

C else

C eval rrn1 = 1

C endif

*

C eval *in90 = *on

*

C endsr

*****************************************************************

* SORT - prompt to select sort criteria

*****************************************************************

C sort begsr

*

C exfmt window1

*

C select

C when tab1 *blank

C movel(p) 'dblnam' order

C clear tab1

C when tab2 *blank

C movel(p) 'dbfnam' order

C clear tab2

C when tab3 *blank

C movel(p) 'dbmini' order

C clear tab3

C when tab4 *blank

C movel(p) 'dbnnam' order

C clear tab4

C endsl

*

C endsr

Figure 2: Embedded SQL makes powerful RPG.

NEXT PRIOR FIRST LAST BEFORE AFTER CURRENT RELATIVE--+-host-variable-+--integer Figure 3: Additional parameters for scrollable cursors allow easy access to a database.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions