Set-based processing in SQL is very powerful, but sometimes you have to do a little extra work to use it.

I've used various features of SQL for a long time, but the one that has always given me a little trouble is the PREPARE statement. I use it rarely enough that I usually have to remind myself how to do it (and why!). I thought I'd write this article as a way to remind myself and you of how and when to use the PREPARE statement and its close cousins, the EXECUTE and EXECUTE IMMEDIATE statements.

Progressing Through Set-Based Processing

The focus of this article is updating a set of records. The story is simple: I created an extract file of orders, and I want to process them. The extract file has a status that keeps track of the processing state of the records. The requirement for this particular business process is to reset some or all of those records to a status of 00, which will allow them to be processed again. Simple enough, right? So here's a simple SQL statement that updates them all:

      exec sql update ORDERS set ORSTAT = 00;

Every record in the file will get its status field (ORSTAT) set to zero. Since this is the kind of thing that SQL is designed for, it's no surprise the statement is easy to write. But let's add a quick wrinkle; let's say we want to update only the records for a specific customer. Well, that's still easy to do with embedded SQL. We simply add a WHERE clause and use a "host variable" (a fancy name for any variable in our RPG program). The syntax is still simple:

      exec sql update ORDERS set ORSTAT = 00 where iCust = :iCust;

Let's say that we have this file in multiple libraries and, instead of specifying the customer of the records we want to clear, we want to specify the library. We'd like to do this:

      exec sql update :iLib.ORDERS set ORSTAT = 00;

Unfortunately, the SQL precompiler doesn't allow that particular syntax. You can't use host variables to specify schema, table, or column names (better known as library, file, and field names to those of us with old-school mentalities). Instead, we have to build the SQL string using traditional RPG character/variable manipulation. Once we've done that, we can then use the special EXECUTE IMMEDIATE statement to run that statement. Here's the code:

      wSQL = 'update ' + %trim(iLib) + '.ORDERS set ORSTAT = 00';

     exec sql execute immediate :wSQL;

You can see that I'm taking a variable, iLib, and using it to build a string. If you work through the EVAL statement, assuming that iLib contains the value "MYLIB", you'll see that wSQL ends up starting with "update MYLIB.ORDERS," which is exactly what we want. The EXECUTE IMMEDIATE statement then runs that statement, updating all the records in ORDERS in library MYLIB.

So let's combine these! Let's do this:

      wSQL = 'update ' + %trim(iLib) +

             '.ORDERS set ORSTAT = 00 where ORCUST = :iCust';

     exec sql execute immediate :wSQL;

Unfortunately, the precompiler doesn't like that syntax either. The EXECUTE IMMEDIATE statement does not allow the use of host variables. You'd have to do something like this (which I actually see all the time):

      wSQL = 'update ' + %trim(iLib) +

             '.ORDERS set ORSTAT = 00' +

             'where ORCUST = ''' + iCust + '''';

Note that since the ORCUST field is alphanumeric, you have to enclose the iCust field in single quotes, but since you're creating a string from a string literal, you have to double the single quotes. This coding can get quite messy, especially when you're doing many variables, and it can be error-prone. There are some advantages to the technique, especially when the selection criteria change dynamically in response to other data conditions. I plan to write another article showing a more comprehensive way to build these select clauses, but today I'm going to demonstrate the PREPARE and EXECUTE statements using parameter markers.

To use parameter markers, you build your statement the way we tried to do earlier using the iCust variable. But instead of using host variable syntax, we simply place a marker (a question mark) in each position of the SQL where we plan to substitute data at run time. Then, we do two SQL actions: a PREPARE statement tells the SQL run time that we're ready to execute a parameterized command, and the EXECUTE statement actually runs the statement, substituting run time data. It looks like this:

      wSQL = 'update ' + %trim(iLib) +

            '.ORDERS set ORSTAT = 00 where ORCUST = ?';

     exec sql prepare s1 from :wSQL;

     exec sql execute s1 using :iCust;

This code will update a file named ORDERS in a library specified in iLib, setting ORSTAT to 00 where ORCUST is equal to the value in iCust. Exactly what we needed!

An Alternate Approach to Dynamic Files

The entire reason I went this route was that I was updating a file in a library whose name was not known at compile time. This is one of the shortcomings of embedded SQL; you can't use a variable to qualify the file name. The prepared statement outlined here is a good approach only if you need to do a single function. If, though, you were going to do a lot of SQL statements on this dynamically specified file, you'd have to repeat this sort of logic over and over. In that particular case, you might consider a different approach: using an ALIAS. An ALIAS is sort of the SQL version of an OVRDBF.

Since you're using a dynamically defined library, you'll still need to use the EXECUTE IMMEDIATE statement, but you use it only once, to define the file that you're planning to access. After that, the rest of your SQL is more traditional. Here's the statement that defines the ALIAS:

      wSQL = 'create alias QTEMP.MYORDERS for ' + %trim(iLib) + '.ORDERS';

     exec sql execute immediate :wSQL;

At this point, every time we access the alias QTEMP.MYORDERS, we're actually accessing the file ORDERS in the library specified. The statements become very simple:

      exec sql update QTEMP.MYORDERS set ORSTAT = 30 where ORCUST = :iCust;

You can then execute any number of statements. When you're done, you can get rid of the alias using the DROP ALIAS statement as follows:

      exec sql drop alias QTEMP.MYORDERS;

Since the ALIAS was created in QTEMP, you don't strictly have to do this. The ALIAS will disappear when the job ends. But I usually code the drop for the sake of tidiness.

PREPARE, EXECUTE, EXECUTE IMMEDIATE, and ALIAS

So there are our concepts for the day: what to use PREPARE for, the difference between EXECUTE and EXECUTE IMMEDIATE, and how to use an ALIAS as an override. I'll have more on these and other related topics in an upcoming article.

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Joe Pluta available now on the MC Press Bookstore.

		Developing Web 2.0 Applications with EGL for IBM i Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform. List Price $39.95 Now On Sale
		WDSC: Step by Step Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction. List Price $74.95 Now On Sale
		Eclipse: Step by Step Quickly get up to speed and productivity using Eclipse. List Price $59.00 Now On Sale

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