SQL isn't just for queries; this article shows you one way to use its update capabilities.

I like to use SQL primarily as a query tool. Its very name—Structured Query Language—sort of leans you in that direction. It's also an excellent tool for set-based updates, as its many proponents will tell you. But there are circumstances under which it would be good to be able to update a file one record at a time via SQL. This is how you do it.

Future-Proofing Your Code

The initial impetus for the concepts in this article came from an ongoing attempt to future-proof the development in a production shop. This shop, like so many, works on a combination of modern programming techniques, including free-format ILE RPG and what can charitably be called long-in-the-tooth packaged code. The goal is to be able to make changes in this code the most productive way possible.

While we've developed processes that allow us to implement changes rapidly, we still run up against one of the most difficult issues in all of RPG development: the dreaded database change. This is a package with thousands of programs, many of which share several major master files. Item master, anyone, or customer order? Transaction history, perhaps? Well, in order to handle database changes in packaged software, you really have only two options: modify the existing database or add extension files. Yes, there are a few other more-involved options, such as converting all existing code to SQL access. Those sorts of architectural overhauls might make sense in a software development shop, but in most production environments, those approaches aren't easy to get approved by the folks who pay the bills. So instead you'll have to choose between modifying the existing files or creating your own extension files.

Modifying Existing Files

Modifying the existing files is difficult, especially in a living, breathing production shop. You can't change the file during normal business operations because every program that has the file open has a lock on it. And it's bad enough in a simple 9-to-5 environment; the complexity simply increases as you throw in multiple shifts and international locations. Pretty soon you get to a spot where you have to set aside a specific window, say two hours in the wee hours on Sunday morning, when the system is unavailable to users. You may be able to schedule that once a week or, in even more complicated cases, once a month. Because of this, a database change requires someone to work in the off hours, installing not only the database changes but also recompiled versions of every affected program. And woe be to you if the database change fails in any way; backing it out is every bit as resource-sensitive, and it's usually an emergency requiring kicking people off during production. This isn't a scenario I recommend.

Creating Extension Files

Enter the extension file. In case you haven’t created one before, an extension file is simply a custom file that you build with the same unique keys as an existing master file. You put all your custom data points in that file. Then, programs that need those data points simply retrieve the appropriate record from the extension file.

When implemented using traditional record-level access (RLA), the extension file suffers from most of the same problems as a normal database file. If you define the file using a traditional F-spec (or a free-format dcl-f statement), the program will lock the file upon startup and hold it until you exit. You can circumvent the lock by using the USROPN keyword on the file definition and opening and closing the file as needed, but even in that scenario, if you change the file, the program will get a level-check error the next time it tries to open the file.

SQL Unbound

SQL provides a real benefit here because of its unbound nature. By that, I mean that SQL works with a more dynamic view of the data than native I/O. The names of the fields are specified in the SQL statement and passed to the SQL engine, which then accesses the data in the file dynamically. If the layout of the table changes, the SQL engine recognizes those changes and processes the new structure accordingly. As long as the changes don't conflict with the basic syntax of the SQL statement, there is no problem.

Now, most people when they think of using SQL to update data think of the traditional SQL UPDATE statement:

      exec sql update ORDEXT

     set OXSTAT = '40' where OXSTAT = '10';

This is a perfectly acceptable way of updating data. Set-based updates are in fact one of the features that SQL evangelists like to point to when they stress the benefits of SQL. And while I agree that updating large numbers of records with a single statement has a certain appeal, it's rare that I have the opportunity to do that. Instead, I typically have to slog through a whole raft of records, testing each case and processing only those that meet certain criteria. Since that selection process often involves multiple files and a lot of conditional logic, it's not very well suited to SQL's version of conditional logic, the adequate but somewhat verbose CASE statement.

That's where the cursor comes in. In RPG's embedded SQL, you can declare a cursor (if you've written an SQLRPGLE program you've almost certainly been down this path). You specify the SELECT criteria and the columns you want to retrieve, and then you loop through the cursor one row at a time using the FETCH NEXT statement.

That's for a read-only cursor. But what if you want to update the data? Well, that only requires one little extra clause on the DECLARE statement and then suddenly you can now update the data as well. Take a look at the following code.

      exec sql declare Orders cursor for

     select OXORDR from ORDEXT where OXSTAT = '10'

        order by OXCUST, OXORDR

        for update of OXSTAT;

     exec sql open Orders;

      dow (1=1);

     exec sql fetch next from Orders into :wOrder;

     if SQLCOD <> *zeros;

        leave;

     endif;

     if Process(wOrder);

        exec sql update ORDEXT

             set OXSTAT = '40' where current of Orders;

     endif;

      enddo;

     exec sql close Orders;

Here I've defined a cursor named Orders, and all that cursor does is retrieve the order number from the extension file. Most of the statement is vanilla SQL syntax, including a WHERE clause and an ORDER BY clause. Note that I use the same condition (OXSTAT = '10') as the original UPDATE clause. But it isn't until the very end that things get a little unusual. The FOR UPDATE clause indicates to the SQL engine not only that the program intends to update the file, but also which field (or fields) we expect it to update.

Next, I use one of my standard programming constructs: an infinite loop using the FETCH NEXT to get the next record; I exit the loop on EOF. But here's where we see a little extra logic. I execute a custom subprocedure called Process, which does some additional processing. If Process is successful, it will return *ON, which will in turn direct the program to execute the UPDATE statement.

And here's the tiny detail that makes all the difference. The UPDATE statement doesn't use a traditional WHERE clause that updates every record in the file in which some field or fields match the selection criteria. Instead, the Process routine adds additional checks, and only if those are passed does it return *ON. But the beauty of this technique is that it will work no matter how much you change the file (with obvious exceptions).

Note that this holds as long as you don't use the dreaded "SELECT *" syntax against the physical table. In another article, I'll spend a little more time on the pros and cons of using data structures and views for your SQL access, but that will have to wait. For now, take your time and see if an updatable cursor might just solve one of your business needs.

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