Embedded SQL has been around for a long time. It dates back to before the turn of the century and can be found in ancient RPG III programs. Okay, I'm being a little silly but the original SQL precompiler was indeed written for RPG III. Today, the best language for embedded SQL is ILE RPG, in particular, free-format ILE RPG. If you haven't become acquainted with this great tool, it's time. I'm going to walk you through a complete, albeit simple, example.
SQL reporting
In this tip, I will show you how to use SQL for one of its most basic functions: reporting. I'm going to write a simple report that prints a list of customers from a customer master file, using embedded RPG in a free-format ILE program. Althought it might seem a bit contrary, I'm going to use an internally described print file. While externally described print files have their place (especially with overlays), I often find myself using internally described files because they're more straightforward.
First, the customer master file. Here are the relevant bits for this report:
In this example, CMCUST is the customer number, CMNAME is the name. CMHOLD is a flag that identifies the hold status (blank for active, H for held) and CMEMAL is the email address. Note that CMEMAL can be null. The program's logic is very simple: print all the records in the file including a column that displays either ACTIVE or HELD depending on the CMHOLD field.
Writing the SQLRPGLE program
The program is simple. The code is less than 100 lines long and of that 30 or so are the output specifications for the report. Add another 10 or 15 lines for data definitions and 20 for comments and you're left with about 20 lines of actual code.
The H specification is a standard one I use -- it doesn't make me step through every field on an I/O operation and it also makes sure that errors are reported using the original source line number, not the magic number generated by the RPG compiler...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
.
This is the file specification for the report. It contains a named indicator (Overflow) for the overflow indicator.
I like to make sure that every field that I extract from the database goes into a work variable of the same size and type as the original field. I use LIKE when defining the work fields, but to do that, I need definitions of the database fields. Because these programs have no file specifications, the next best thing is an externally described data structure.
The next step is to define the data structure. I usually call my cursors by creative names such as "C1." While a bit sarcastic, you can see it also makes it a little easier to name things in a consistent manner. Next, I define the fields in the data structure. Because the data structure is qualified, I can name them whatever I want, and what I want is to use the same names as in the database. The single exception is the Status field that I named to make it stand out from the database fields.
You may have noticed that in this example my database field names still use the old six-character naming. That's a throwback to my RPG II heritage; nowadays I often use eight-character names. Using no more than eight characters is key, because by combining a relatively short database field name with an extremely short qualified data structure name, I can still fit the qualified field in the field name of the output specification. Four for the data structure, a dot and up to eight characters for the field fits (just barely) into the 13-character field name limit in the O-spec.
The last part is the array of four signed integers. If you want to use null-capable fields (like the CMEMAL field), you have to define an array to hold the null indicators, and it must have at least as many entries as the number of fields in your cursor. I named mine anC1(a personal convention where "an" stands for "Array of Nulls").
A time field. O-specs support date and page special values, but not time. The answer is: make our own. Now we move on to the program.
This routine is my error catch-all. If any errors occur, the program auto-dumps and ends without halting. Is this the right way for every program? No; some programs need attention when they bomb. But for simple reports this method is usually perfectly sufficient.
With the above followed by the standard exit, that is the complete skeleton. Next, the actual business logic:
Set the time, print the heading. Because I force the heading this report will always print something even if no data is selected. You might not want an empty report, so you can modify the code accordingly.
Finally, some SQL – but this is not an SQL primer. With the exception of the status field, the cursor definition is pretty straightforward: select customer number, name and email address from the customer master. The fields must be in the same order in the SELECT as they are in the data structure, and the types must be compatible. Personally, I prefer the variables to be exact matches, which is why I like to use LIKE definitions in the data structure.
There is also an example of a derived field. The STATUS field is the result of a CASE statement that turns the single character CMHOLD field into one of three more readable status codes: HELD, ACTIVE or UNKNOWN. BecauseI don't have a corresponding database field, I defined it explicitly in the data structure.
This is the standard loop. To keep it simple, I open the cursor then begin a fetch loop by fetching the data from the first row of the result set into the dsC1 data structure. I have also specified the anC1 indicator array immediately after the data structure. The FETCH statement will load this array with indicators indicating the null status of each field.
If I needed special logic conditioned on whether or not a particular field was null, I could test the corresponding entry in the array (here anC1(3) is the null indicator for the email field CMEMAL).
Finally, I use the simplest termination of an SQL loop -- just check if the SQLCOD is not zero. This will end the loop if there's an end of file or any unexpected condition. Not exactly robust, but it works.
Here we close the loop: fetch the next record and check for end of cursor (this isn't the only way nor even the best way to do this). In more complex programs I have only one fetch at the very top of the loop. This allows me to use the ITER opcode to skip a record and the LEAVE opcode to prematurely end the loop entirely.
Finally, time to clean up: close the cursor and print the end of the report, then return back up to the main procedure. At this point all that's left is the report.
Here we make the report headings:
Next are column headings. For a simple report like this, we print the report title and the column heading all in the same pass. You might note that I pad my column headings with extra spaces -- with enough spaces to completely fill the column. I then do the same thing with my dashes. This allows me to use the +1 / +2 syntax for placing the columns and that leads to a powerful advantage.
I match the dashes exactly in my detail line. I also use the qualified data structure subfield naming. My fields fit with just a couple of spaces to spare. More important, though, notice that the +1 / +2 values are the same. This makes it very easy to move a column. I simply move it, making sure to move the corresponding column heading and dashes. No having to recalculate end positions. This works best on relatively simple reports, but really, a whole lot of reports are indeed relatively simple.
This is it, the end.
The resulting report
I thought I'd include some of the report to give you an idea of what you get:
Adding another field is as simple as changing the SQL SELECT statement, adding the field to the data structure and then adding it to the report. How's that for your first SQLRPGLE report?
ABOUT THE AUTHOR: Joe Pluta is the founder and chief architect of Pluta Brothers Design Inc. Joe uses the Rational tool suite extensively, and is on the beta teams for most of its i-related products. His new book Developing Web 2.0 Applications with EGL for IBM i is due out in November 2009..
