Embedding SQL in /free
March 2, 2005 Joel Cochran
I’ve been hooked on free-format RPG since I first upgraded to V5R1. In fact, I have immersed myself so deeply into /free that I almost feel uncomfortable when I have to maintain fixed-format code. I’ll assume if you are reading this article that you are already aware of the virtues of free-format. In total fairness, I will also acknowledge that working with free-format certainly has some undesirable aspects. Listing and discussing them would be an entire article in its own right, and maybe one I’ll contemplate soon. But in this article I’d like to address the one that personally bugs me the most: embedding SQL in free-format code.
A Quick How-To
Regular readers of Four Hundred Guru already know I’m a big fan of SQL. To embed SQL into an RPG program, the SQL pre-compiler has to go through the source code first and convert the SQL statements to program calls. To do this, we have to signal the pre-compiler that a particular section of source code is in fact SQL by wrapping the code in c/exec sql and c/end-exec compiler directives. On top of that, each line inside these directives must have c+ in positions 6 and 7. Here is an example:
c/exec sql c+ select * c+ into :mainDS c+ from MYLIB/MYFILE c+ where IDFIELD = :KEY_VALUE c/end-exec
For the embedded SQL newcomer, the statement above will get all the fields from a record and place the contents into a data structure named mainDS. This block of code would execute just fine in either fixed- or free-format, but free-format it means first issuing a /end-free statement and then reissueing a /free statement after the SQL. There is lots of room for improvement here, and more than likely we want to deal with a group of records, which means DECLARE CURSOR, OPEN, FETCH, CLOSE, and possibly PREPARE statements. Explanation of these is beyond the scope of this article, but here is a sample bit of code that uses all the above in free-format:
d mainDS e ds extname(MYNAMES)
d sql s 32000a varying
d inz( 'Select * from mynames +
d order by USERNAME' )
d message s 52a inz
c/exec sql
c+ declare mainCursor cursor
c+ for mainStatement
c/end-exec
c/exec sql
c+ prepare mainStatement
c+ from :sql
c/end-exec
c/exec sql
c+ open mainCursor
c/end-exec
c/exec sql
c+ fetch next from mainCursor
c+ into :mainDS
c/end-exec
/free
dow sqlstt = '00000' ;
message = %trimr( m_USERNAME ) + ' ' + %trimr( m_USEREMAIL );
dsply message ;
/end-free
c/exec sql
c+ fetch next from mainCursor
c+ into :mainDS
c/end-exec
/free
enddo ;
/end-free
c/exec sql
c+ close mainCursor
c/end-exec
/free
*inlr = *on ;
/end-free
The Ugly Factor
The issue with embedded SQL in free-format has primarily to do with the need to constantly drop in and out of /free to execute SQL statements. As demonstrated above, this can be quite messy. In fact, this is downright ugly. And it can easily get worse! Multiple cursors, additional select statements, updates, deletes, and inserts can all be interspersed with sections of free-format code. Eventually you have as much time invested in compiler directives as you do in actual code. This approach can be difficult to read, which means it is difficult to manage.
I have personally written enough code with embedded SQL that I can pretty much ignore the ugliness, but many people contend that we shouldn’t have to deal with this in the first place. There is enough discontent with this particular topic that some people refuse to use /free. IBM should be paying attention: If the party line is to adopt free-format RPG, then it needs to be cleaner, especially when dealing with SQL. No matter your opinion on the matter–and there are many–this is the current state of things, so let’s discuss getting rid of the ugliness.
So how do we conquer the ugliness? Embedded SQL requires the c/exec sql and c/end-exec blocks, so there isn’t any way to get rid of those. And we already know that we cannot use embedded SQL statements inside free-format. Our only recourse then is to separate the embedded SQL from the free-format code. Naturally, for such an operation, we are going to turn to our trusty tool: the subprocedure.
Using SQL in Subprocedures
Subprocedures are an excellent mechanism for detaching our ugly SQL statements from our program logic. Ultimately, since we can’t change the rules, the ugliness still remains, but the final affect is much more palatable. Before we begin breaking our program up into smaller chunks, there are some pesky rules to go over.
First, the SQL Precompiler, at least through V5R2, requires that some of the SQL statements occur in the source code in the correct physical order. Specifically, since the DECLARE statement must be logically executed before the related OPEN the precompiler expects them to be in this order in the actual source code. If you try to compile a program that has the OPEN statement physically before the DECLARE statement, you should receive an SQL0504 (“Position xx Cursor %CURSORNAME% not declared.”) compile error. The same rule however is not true for OPEN and FETCH statements, which compile just fine out of order. Honestly, I don’t know if this applies to any others or not, but to avoid confusion and compile problems, I always try to place the subprocedures in the same physical order as their logical execution.
Second, the use of local variables. Since we are moving these statements to subprocedures, we can take advantage of local variables, but with restrictions. Namely, if the variable name is also not a global variable name, an SQL statement should be able to use the local variable as a host variable. To complicate matters, this is not always true. The local name can be the same as a global name, provided that both variables are defined the same. Otherwise, SQL will get confused and not recognize the value of either variable. This is only a problem if the local and global variables are defined differently. But don’t let any of this odd behavior fool you into thinking something is wrong with the SQL precompiler: the manual in V5R3 still states: “The names of host variables must be unique within the program, even if the host variables are in different procedures.” So ultimately, this has nothing to do with global versus local! The solution is to ensure that your host variable names unique no matter what.
To avoid confusion, I’ve adopted the convention that all host variables should be global. For update and insert statements, I like to use an externally defined DS based on the same file. This way the variable definitions are guaranteed to be correct. As a result, no data conversion is ever required so I get a little performance boost. You can populate the variables before calling the procedure or use local variables to populate the global host variables if you like. While it is a little extra work, being cognizant of this issue beforehand can save you time and aggravation.
Modularizing the Program
Now that we know what the rules are, we can transfer the SQL statements out of our mainline and into subprocedures. This really couldn’t be any easier: Simply move the offending statements to a basic procedure shell, add the prototype to the D-Specs, and replace the original code with the new procedure call. Apply this to the code posted above and here is the result:
d mainDS e ds extname(MYNAMES) prefix(m_)
* Prototypes
d declare pr
d openCursor pr
d fetchNext pr
d closeCursor pr
* Standalone Variables
d sql s 32000a varying
d inz( 'Select * from mynames +
d order by USERNAME' )
d message s 52a inz
/free
declare();
openCursor();
fetchNext();
dow sqlstt = '00000' ;
message = %trimr( m_USERNAME ) + ' ' + %trimr( m_USEREMAIL );
dsply message ;
fetchNext();
enddo ;
closeCursor();
*inlr = *on ;
/end-free
*-------------------------------------------------------------------
p declare b
d declare pi
c/exec sql
c+ declare mainCursor cursor
c+ for mainStatement
c/end-exec
c/exec sql
c+ prepare mainStatement
c+ from :sql
c/end-exec
p declare e
*-------------------------------------------------------------------
p openCursor b
d openCursor pi
c/exec sql
c+ open mainCursor
c/end-exec
p openCursor e
*-------------------------------------------------------------------
p fetchNext b
d fetchNext pi
c/exec sql
c+ fetch next from mainCursor
c+ into :mainDS
c/end-exec
p fetchNext e
*-------------------------------------------------------------------
p closeCursor b
d closeCursor pi
c/exec sql
c+ close mainCursor
c/end-exec
p closeCursor e
*-------------------------------------------------------------------
You’ll notice quickly that there isn’t much to any of the procedures. The real gem here is in the main line. Hopefully you’ll agree that this is much more palatable than what we started with. We certainly have a lot less jumping in and out of free-format, even in the subprocedures, since they are primarily SQL.
Obviously this is a very simple example, but let me point out a couple of things. At first glance, it appears that the declare() and open() procedures could be combined. While in this case that may be true, what if the cursor had a variable where clause? To handle this, the SQL statement would include a parameter marker for the where value:
d sql s 32000a varying d inz( 'Select * from mynames + d where USEREMAIL like (?) + d order by USERNAME' )
If the declare() and open() procedures were combined, then the statement would get re-prepared and the cursor re-declared every time the where clause changes. That’s a lot of unnecessary overhead, so most of the time it makes sense to separate these into their own procedures. If you really want to get the declare() statement out of the mainline, try setting a global variable such as isDeclared and checking its state in the open() subprocedure, calling declare() if it the subprocedure hasn’t been executed yet.
|
A Few More Tidbits
For scrollable cursors, is to have a single fetch procedure (instead of our unidirectional fetchNext) that accepts a parameter for the fetch method and some branching logic. Below is a simple example, you could easily add the rest of the scroll types (i.e. FIRST, LAST, CURRENT, etc.) to this code.
In the declare() subprocedure:
c/exec sql c+ declare mainCursor scroll cursor c+ for mainStatement c/end-exec
And the fetch() subprocedure:
p fetch b d fetch pi d scrollType 10a const options(*nopass) /free if %parms() = 0 or scrollType = 'NEXT' ; /end-free c/exec sql c+ fetch next from mainCursor c+ into :mainDS c/end-exec /free elseif scrollType = 'PRIOR' ; /end-free c/exec sql c+ fetch prior from mainCursor c+ into :mainDS c/end-exec /free endif ; /end-free p fetch e
Granted, a bit of the ugliness has returned with this sort of branching. Remember, I said we couldn’t get rid of the ugliness, but we could reduce it. At least we are limiting it to this subprocedure and getting it out of the mainline. And of course, you could have separate subprocedures for each of these instead of one subprocedure.
Another thing you could do is to make your fetch() routine(s) return a boolean value representing whether or not a record was found. I’m not going to demonstrate it, just check the value of SQLSTT and return *ON or *OFF based on your particular needs. The benefit of this is a little less mainline:
/free
declare();
openCursor();
dow fetchNext();
message = %trimr( m_USERNAME ) + ' ' + %trimr( m_USEREMAIL );
dsply message ;
enddo ;
closeCursor();
*inlr = *on ;
/end-free
You also no longer have the “priming” call to fetchNext() and the “continuing” call.
Final Thoughts
I do, as always, have a few items on my wish list. The first is to find a solution to the problems with the local variables discussed above. Second, I’d like to have enhanced cursor support, more like an actual OO-style result set. Looping through a cursor is OK, but sometimes I want to know how many records the cursor has, or maybe dump the contents of a cursor into an array or even an outfile. I can imagine a lot of similar tasks and uses. I’d really like to be able to pass a cursor between programs and to be able to retrieve data from a cursor by referencing field name or ordinal position. Mostly, and more in line with the topic of this article, I’d like to see a set of SQL Built-In Functions (BIFs). For example, %execSQL(), %declareSQL(), %prepareSQL(), %opencursor(), %fetchSQL(), etc. could certainly eliminate the need for the c/exec-sql and c/end-exec directives, which would go a long way to improving the use of SQL in free-format RPG.
In the meantime, while the techniques in this article aren’t perfect, they can simplify and cleanse your mainline code. This is especially true when you have a lot of conditional updating, multiple cursors, and the like. It certainly provides some relief for those of us wishing to take advantage of both SQL and free-format RPG.
Joel Cochran is the director of research and development for a small software firm in Staunton, Virginia, and is the author and publisher of www.RPGNext.com and www.RPGBeans.com. Click here to contact Joel by e-mail.
