Embedded SQL -- Better than Ever!

It was back in the 1980s when I first used SQL embedded in RPG. Embedded SQL, as it is called, has had the same syntax as my old RPG III programs did for more than 20 years, and it's been ugly for all those years. Today with i5/OS in most shops, there's a new embedded SQL in town, but I continue to see shops coding in old-style, or dare I say "traditional," embedded SQL. Let's fix that problem right now.

Traditional Embedded SQL

Here's a basic embedded SQL statement that mimics the CHAIN opcode, more or less.

     D custDS        E DS                  extname(custmast) qualified
     D custno          S                   Like(custds.cstnbr)
     
     C                   EXFMT     AskUser
     C                   if        FKey <> F3
     C                   MOVE      DSPCUST       CUSTNO
     C                   endif
     C/EXEC SQL                                        
     C+       select * into :custDS                    
     C+         from custmast where cstnbr = :custno   
     C/END-EXEC

If you're experienced at using embedded SQL, you probably don't use the SELECT statement in this way too often, but it was the easiest way to illustrate my point. If you're new to embedded SQL, then I've got some explaining to do.

The starting C/EXEC SQL is a compiler directive. Technically it is an SQL Preprocessor directive that lets the preprocessor know you are about to begin an SQL statement.

The second and third Calc specs (in this example) contain the "C+" characters. This tells the preprocessor that you've specified SQL statements on these lines. Some people prefer to begin the SQL statements on the same line as the /EXEC SQL directive, but they and the 15 others who do it that are rare. However, either way works – it's all a matter of style.

The preprocessor was migrated to RPG IV back in the early to mid-1990s and has served those who used it well. But then in i5/OS V5R1, IBM introduced free- format RPG IV, and look what happened to our code:

     D custDS        E DS                  extname(custmast) qualified
     D custno          S                   Like(custds.cstnbr)
     
      /FREE
          EXFMT AskUser;
          if FKey <> F3;
              custno = DspCust;
          endif;
      /END-FREE
     C/EXEC SQL                                        
     C+       select * into :custDS                    
     C+         from custmast where cstnbr = :custno   
     C/END-EXEC

Every time we need to do embedded SQL, we have to

1. Terminate Free Format
2. Start Embedded SQL
3. Code the SQL Statement
4. Terminate Embedded SQL
5. Start Free Format
6. Rinse and Repeat

This made coding with embedded SQL, in a word, ugly!

Enter i5/OS Version 5 Release 4

After years of pleading and begging by IBM Canada (who own RPG IV), it seems IBM Rochester (who owns embedded SQL) realized that they needed a much cleaner syntax for embedded SQL. Well, with V5R4, IBM hit a home run. The embedded SQL preprocessor appears (to the RPG IV programmer) as just another opcode that works in free format. No more /EXEC SQL followed by lines with goofy plus signs, followed by an /END-EXEC statement. Today the SQL preprocessor is as easy to use as the CHAIN opcode, for example:

     D custDS        E DS                  extname(custmast) qualified
     D custno          S                   Like(custds.cstnbr)
     
      /FREE
          EXFMT AskUser;
          if FKey <> F3;
              custno = DspCust;
          endif;
          EXEC SQL select * into :custDS                    
                     from custmast where cstnbr = :custno;
      /END-FREE

RPG TnT: 101 Dynamic Tips 'n Techniques with RPG IV by Bob Cozzi is available now.

The latest book from author Bob Cozzi is 300-pages long and contains 101 example RPG IV Tips and Techniques for everyday programming tasks, from date calculations, to regular express searches, to using APIs. Cozzi wrote down every cool technique he's found over the years, updated them, and consolidated them into this compact book that is a great desktop companion -- and it includes full example source code. Order your copy today.

The big change here is that the "EXEC SQL" directive is more opcode-like and there's no more pesky "/END-EXEC"; instead, you specify a semicolon just like you do to terminate any other RPG IV free-format statement. What about two embedded SQL statements? How about this:

     D custDS        E DS                  extname(custmast) qualified
     D custno          S                   Like(custds.cstnbr)
     
      /FREE
          EXFMT AskUser;
          if FKey <> F3;
              custno = DspCust;
          endif;
          EXEC SQL select * into :custDS                    
                     from custmast where cstnbr = :custno;
          if (FKey = F11);  // Delete customer requested?
             exec sql delete from custmast where cstnbr = :custno;
          endif;
      /END-FREE

The same syntax is used for all embedded SQL statements including but not limited to DECLARE, PREPARE, OPEN, and FETCH. Lastly, if you haven't yet moved to free-format RPG IV, you can still use this syntax by coding (gulp) the following:

     D custDS        E DS                  extname(custmast) qualified
     D custno          S                   Like(custds.cstnbr)
     
     C                   EXFMT     AskUser
     C                   if        FKey <> F3
     C                   MOVE      DSPCUST       CUSTNO
     C                   endif
     C/FREE                                        
          exec sql select * into :custDS                    
                     from custmast where cstnbr = :custno;
      /END-FREE

This example shows using standard, fixed-format RPG IV with V5R4's support for free-format, embedded SQL.

Bob Cozzi is the host of RPG World Live, a live, weekly two-hour show on System i developments. It is aired each Friday from 10:00 AM to Noon, Central time on ustream.tv and on RPGWorld.com. Bob also produces RPG World, the most popular RPG IV developer conference of the year.