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.
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:
R CUSMASR CMCUST 6 0 CMNAME 50 CMHOLD 1 CMEMAL 50 ALWNULL
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.
fQPRINT O F 132 PRINTER OFLIND(OverFlow)
This is the file specification for the report. It contains a named indicator (Overflow) for the overflow indicator.
*** * Data structure for Customer Master fields *** dCUSMAS e ds
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.
*** * Data structure for cursor C1 *** d dsC1 ds qualified d CMCUST like(CMCUST) d CMNAME like(CMNAME) d CMEMAL like(CMEMAL) d Status 10 d anC1 s 3i 0 dim(4)
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 less 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").
d wTime s 6 0
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.
*** * Program code *** /free // Run the mainline within a monitor block // If an error occurs, dump the program and return // an error monitor; exsr mainline; on-error; dump(a); endmon;
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.
// Exit program *inlr = *on; return;
With the above followed by the standard exit, that is the complete skeleton. Next, the actual business logic:
// Report mainline begsr mainline; // initialize time and print report heading wTime = %dec(%time); except RPTHDG;
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 canmodify the code accordingly.
// Create cursor // Create a list of orders exec sql declare C1 cursor for select CMCUST, CMNAME, CMEMAL, case when CMHOLD = 'H' then 'HELD' when CMHOLD = ' ' then 'ACTIVE' else 'UNKNOWN' end as STATUS from CUSMAS;
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.
// Loop through the orders exec sql open C1; exec sql fetch next from C1 into :dsC1 :anC1; dow SQLCOD = *zeros;
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.
// Print overflow if needed if OverFlow; except RPTHDG; OverFlow = *off; endif; // Print Order except RPTDTL;
Next, the instructions for if the overflow indicator is on, telling it to reprint the heading and turn off the overflow indicator. Then print the report detail line.
// Get next record exec sql fetch next from C1 into :dsC1 :anC1; enddo;
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.
exec sql close C1; // Print end of report except RPTEND; endsr; /end-free
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.
OQPRINT E RPTHDG 2 02 O 7 'CUSLST' O 61 'List Customers' O UDATE Y 90 O wTime 100 ' : : ' O 128 'PAGE:' O PAGE Z 132
Here we make the report headings:
* O E RPTHDG 1 O +1 'CustNo' O +2 'Status ' O +2 'Name ' O ' ' O +2 'Email Address ' O ' ' * O E RPTHDG 1 O +1 '------' O +2 '-------------------------' O '-------------------------' O +2 '----------' O +2 '-------------------------' O '-------------------------'
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.
* O E RPTDTL 1 O dsC1.CMCUST +1 O dsC1.Status +2 O dsC1.CMNAME +2 O dsC1.CMEMAL +2
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 of reports are indeed relatively simple.
* O E RPTEND 1 2 O 40 '** END OF REPORT **'
This is it, the end.
The report result
I thought I'd include some of the report to give you an idea of what you get:
CUSLST List Customers CustNo Status Name Email ------ ---------- -------------------------------------------------- ----- 100000 ACTIVE Costco - Corporate info@ 100100 ACTIVE Costco - Lake Zurich 100150 ACTIVE Costco - Glenview 100200 ACTIVE Costco - Schaumburg info- 100250 ACTIVE Costco - Bloomingdale 100300 ACTIVE Costco - Niles 301001 ACTIVE Dean Foods Company
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..