Three Simple Embedded SQL Tips--Select, Insert, and Update

[Note: This article was brought over from another CMS I own since it had a relatively large number of hits. I will continue to update it over the next few weeks.]

I  had to create a new public-facing website and decided from the start that I would use SQL DDL to define the database files (I suppose I should call them "tables" but you get my meaning). While I did cheat in creating the SQL DDL (I had used DDS to define the file initially and then retrieved the SQL source and modified it) normally I only use the SQL CREATE TABLE statement create new files/tables. Any future modificaitons will be made to the SQL source.

The one thing I did in all the files was is to include the primary key by adding the IDENTITY keyword. This capability, which I believe originated with Oracle, allows you to define a key field that is automatically incremented during an insert (write) operation. This avoids the traditional RPG IV approach which is:

• Issue a SETLL *END to get to the bottom of the file
• Issue a READP to retrieve the last record in the file
• Add 1 to the key field (assuming its numeric)
• Write the new record to the file

As you can imagine, if several simultaneous users are hitting a website and consequently the database, this generating the next key could be problematic. But with an IDENTITY column, the database does it all for you, very cool!

Here's a simple example of an SQL IDENTITY column (field) and the originally DDS I used to define the file:

CREATE TABLE MYLIB/CONTACTS (                   
  CSTNBR INTEGER PRIMARY KEY                          
          GENERATED ALWAYS AS IDENTITY             
            (START WITH 1000, INCREMENT BY 1),           
  FNAME    CHAR(32) CCSID 37 NOT NULL DEFAULT ' , 
  LNAME    CHAR(32) CCSID 37 NOT NULL DEFAULT ' , 
  TITLE    CHAR(64) CCSID 37 NOT NULL DEFAULT ' , 
  EMAIL    CHAR(64) CCSID 37 NOT NULL DEFAULT ' );

Now the original DDS:

A          R CONTACTS                                                  
     A            CSTNBR         9B 0       COLHDG('Client ID')            
     A            FNAME         32A         COLHDG('First' 'Name')         
     A            LNAME         32A         COLHDG('Last' 'Name')          
     A            TITLE         64A         COLHDG('Professional' 'Title') 
     A            EMAIL         64A         COLHDG('email')
     A          K CSTNBR              

Note the 2 differences in the SQL from the DDS. (1) The CSTNBR field has been enhanced with the IDENTITY attribute and (2) the Column Headings aren't specified. To specify column headings, use the SQL LABEL ON statement, but I'm not going to cover those in this article. Also note that the CSTNBR is now a real INTEGER data-type and not that goofy "9B0" legacy thing.

I prefer to use SQL to define my database files because RPG IV doesn't care if you use DDS or SQL to define them, it treats all types of files identically. You can use File specs and native I/O opcodes to get at your SQL tables from RPG IV and you can use embedded SQL to get at native database files (created with DDS); or go the other way around.

Today there's no advantage to using DDS for database definition. In fact, my RPG World colleague, Bruce Hoffman suggests that database files created with SQL may perform better than those created with DDS. Granted however, that statement does not imply that using embedded SQL to access the data is faster than native RPG IV opcodes for single record I/O.

RPG Database Access

There are so many cool features in native RPG IV File I/O support that it's rather disappointing to consider using embedded SQL as an alternative. This may be why so many people continue to use, and rightly so, native File I/O opcodes. RPG IV has the EXTFILE and EXTMBR keywords and there's the %FIELDS keyword on the UPDATE opcode. Then there's the ability to pass file handles between subprocedures; oh, and the ability to program-describe a file. With the exception of the %FIELDS keyword, and to a lesser extent, file handles (called "cursors" in SQL) SQL really doesn't have these features. For example, you can't really change the library or file name you're accessing with SQL at runtime without effectively eliminating the advantages static embedded SQL. You could issue an EXECUTE IMMEDIATE, but it is troublesome at best to change a file or library name in static SQL even with Parameter Markers.

But let's look at some of the good things you can do easily with embedded SQL that you can also do in RPG IV:

Let's look at an example of each one of these operations in SQL. I assume you already know how to use the RPG IV opcodes, so I'll spare us both from reviewing that syntax. Likewise, I won't bother discussing every component of these SQL statements since most of us have used them at one time or another.

SQL SELECT in Place of CHAIN

With SELECT you can access any record in the database file using the sophisticated WHERE clause. When using SELECT in RPG IV, you specify the fields you want to extract. This way you only retrieve the data you actually want, not the entire record as is required when doing the native CHAIN opcode. SQL SELECT also allows you to grab the entire record if you desire, but "SELECT *" is frowned upon by virtually every SQL professional as a sign of "an amateur wrote this".

The WHERE clause allows you to specify dynamic select and omit parameters. When applied to the database file's key field, this is similar to using the CHAIN opcode with a key or partial key. When applied to non-keyed fields, it's similar to using the OPNQRYF CL command. For example, to "chain out" to the Contact file with customer number 1547 stored in the CLIENT field and return the First and Last Name fields as well as the EMAIL address, the following SELECT statement could be used.

/FREE
           exec SQL  SELECT FName, LName, email INTO :fstname,:lstname,:email
                       FROM  contacts
                       WHERE cstnbr = :client
                       FETCH FIRST ROW ONLY;
     /end-free

In this example, the fields FNAME, LNAME, and EMAIL are read into the program and are stored in the FSTNAME, LSTNAME and EMAIL variables (specified on the INTO clause of the SELECT statement). The record being retrieved is identified on the WHERE clause, where the RPG IV variable CLIENT is used to identify the key value. I've included the "FETCH FIRST ROW ONLY" clause after the WHERE to insure that only one record is returned. If more than one record is required, the SELECT would fail at runtime.

This process is similar to the following simple CHAIN opcode, produces input of only the fields we actually need instead of all of them.

/free
          CHAIN (client) contacts;
      /end-free

One of the cool things about RPG IV is that the %FOUND() built-in function can be used to verify that the CHAIN opcode found a record that matches the key criteria. As it turns out, SQL also has this capability. Here's how:

/FREE
           exec SQL  SELECT FName, LName, email INTO :fstname,:lstname,:email
                       FROM contacts
                       WHERE cstnbr = :client
                       FETCH FIRST ROW ONLY;
           if (SQLSTATE >= '02000'); // Record not found?
              // NOT FOUND processing here.
           else;  We got it!
              // FOUND processing here.
           endif;

     /end-free

The SQLSTATE variable is automatically generated by the SQL preprocessor and stored in your program. After an SQL operation is performed, the state is always updated. This is similar to %STATUS() in RPG IV. An SQL State value >='02000' means the statement failed. So in this context, we are using it just like the %FOUND() built-in function after a CHAIN operation.

Oh, but you say you want to use it like a SETLL where you can test to see if the record exists before bothering with the processing itself? Fine. A little used feature of SQL allows you to search only the index of the file, and avoid the data portion entirely. Here's the modification to do just that:

/FREE
           EXEC SQL select cstnbr INTO :client                        
                 FROM contacts  WHERE cstnbr = :client
                 FETCH FIRST ROW ONLY; 
            if (SQLSTATE> = '02000'); // Record not found
              // NOT FOUND processing here.
           else;  We got it!
              // FOUND processing here.
           endif;

     /end-free

By selecting only the key field on the SELECT clause as well as the WHERE clause, only the index is searched, thus improving performance and causing the SELECT to act more like a SETLL than a CHAIN opcode. Pretty cool, huh? And the really interesting thing is that if you've created other indexes or logical views of the file, and the SELECT and WHERE clauses match those indexes, guess what? That's right, SQL will use those, effectively allowing you to use whatever logical view you want without first defining that logical view to the program. Now that's cool!

SQL INSERT vs. Write a Record

I'm sorry SQL fanboys, but somebody was asleep at the wheel when it comes to consistency in this one. The INSERT operation is so different from the UPDATE and DELETE operations that it is confusing. Fortunately INSERT is simple enough that you'll eventually get used to it.

To write a record to a database file, you specify a value for each field you want to populate. In the following example, assume you want to populate all the fields in the file--making it similar to a traditional RPG IV WRITE opcode.

/FREE
           exec sql insert into contacts                     
                values(DEFAULT, 'Bob', 'Cozzi', DEFAULT, :email); 
     /end-free

The CONTACTS file has 5 fields in it and I've specified 5 values. The 2nd, 3rd and 4th values are literals while the 5th value is a host variable. So far, so good. But then there's that IDENTITY column thing (the 1st column). To allow the system to generate an IDENTITY value for the CSTNBR field, you specify the special value of DEFAULT for the field's value. I also used DEFAULT for the value of the TITLE field. You may specify the DEFAULT keyword in place of any field that has a DEFAULT value. Go back to review the CREATE TABLE statement, all fields were defined with a DEFAULT value. I chose blanks and zeros values, but you could specify any value you want. That default value is inserted when the DEFAULT keyword is specified in place of a real value or when the field is not included on the list of fields for an INSERT statement.

But now you ask "How do I know what that new record's key value is?" Oh sure, you want everything don't you? Okay, it's a bit goofy, but it does work, here's how:

/FREE
            exec sql INSERT INTO contacts                     
                       values(DEFAULT, 'Bob', 'Cozzi','Author', :email); 

            exec sql VALUES IDENTITY_VAL_LOCAL() INTO :client; 
     /end-free

Do you see it? The VALUES IDENTITY_VAL_LOCAL() statement returns the most recently generated IDENTITY column value. The INTO clause identifies the host variable into which we want to store that identity value (the CLIENT field in our example). Like I said, it is a bit goofy looking, but it works. Now you could follow this up with, for example, an UPDATE statement that modifies some of the values of the record just inserted by specifying "WHERE CLIENT = :client" or perhaps use the IDENTITY value to access another file; whatever works for you.

If you are going to specify values for a subset of fields in the record during an INSERT, then you need to list the field names that will be populated. These Field names must be listed in the same order as they are on the VALUES keyword. They do not, however, need to be in the order they appear in the database file. I'm sure many of you have already used the INSERT statement in Interactive SQL (via the STRSQL command) and prompted the INSERT statement to insert data into specific fields. Here's what it would look like as embedded SQL in RPG IV.

FREE
           exec sql insert into contacts (fname, lname, email)                    
                      values( 'Bob', 'Cozzi', :email); 
           exec sql values IDENTITY_VAL_LOCAL() into :client; 
     /end-free

I've specified only 3 field names and their 3 corresponding values. Note that since I did not specify the CSTNBR or the AUTHOR columns, I do not include the DEFAULT keyword as placeholders for their default values.

UPDATE vs. Update

What would the world be like without updating data? The SQL UPDATE statement is pretty powerful and pretty cool. Not only can you replace the RPG IV UPDATE opcode, you can actually update more than one record at a time! Sure you long-time SQL pro's already know this, but I always get looks of astonishment when I tell people this for the first time. Here's a great example of UPDATE. It's a simple example that matches what can be done with the RPG IV UPDATE opcode:

/FREE
           exec sql update contacts set FName = 'Bob', LName = 'Cozzi', email = :email                    
                      where cstnbr = :client;
     /end-free

The SET clause identifies each field that is to be updated along with its value. If more than one field is being updated, you separate each field from the previous value with a comma. This is sort of like the old EXCEPT Opcode with specific output fields specified, or the more contemporary UPDATE with the %FIELDS keyword specified. Unlike the RPG IV UPDATE opcode, however, the SQL UPDATE only support updates with a subset of fields. That is if you want to update all the fields in the record, you must specify each and every field name and its corresponding value on the SET clause of the UPDATE statement.

Now suppose you want to update a group of records, or perhaps all the records in a file. To do this in RPG IV requires a DO loop with a READ or READE opcode and perhaps some IF conditions. But SQL really stands out here as the best choice. This is called set-at-a-time processing. For example, to update every record in our file that contains the letters "IT" in the DEPT field, the following could be performed:

/FREE
           exec sql update contacts set salary = salary*1.20 where dept = 'IT';
     /end-free

In this example, I've increased the salary for everyone in the 'IT' department by 20%. Not bad in this economy. Whether 1 or 1000 or 1,000,000 records matching the WHERE clause, they are all updated to the new value. This works for SQL created tables or regular old DDS created database files--doesn't matter to SQL.

And this is much easier than using native RPG IV opcodes.

Follow Bob Cozzi on Twitter at: Twitter.com/bobcozzi