Let's evaluate the reasons you might want to take this approach.

You've heard about embedded SQL and maybe you've tried embedded SQL, but you may be asking yourself, "Why would I use embedded SQL?" That's the course that I have taken. Of course, when a new capability comes out, I am usually eager to start digging into it and figuring out how it works. And I have done that with SQL, but I couldn't easily find a reason to justify its use until recently.

In several previous articles, I have discussed ways that you could use SQL outside of RPG. I have found various practical applications for this power in such things as ad hoc queries to produce detailed data to provide numbers that are represented on RPG reports, and I have also used it to create patch programs for changes that needed to be performed over large amounts of data. And if you have the need to export data from your system, you could run an interactive query for a one-time export or you could save your query in a source file to be reused later on. But in this article, I will talk about the reasons that you may want to consider using SQL embedded right inside your RPG programs.

Dynamic File Source

When I started developing with SQL, I immediately found a strong reason to use it: the dynamic usage of files within an RPG program without the need for F-specs. We had an existing program that was screaming for this capability because we were legally obligated to use physically separated files that would not be updated once they were archived out of the current year's updateable file.

At the end of each year, we would copy the currently used file into a new physical file that would have the year included in the name. For example, the file for the current year would be named EMPLOYEE, and at the closing of the 2010 year, the data from 2010 would be copied into a newly created file named EMPL2010 and the current EMPLOYEE file would be cleared to prepare for the new data in 2011.

This caused the yearly process to also include program maintenance to add the newly created file to the F-specs and make any necessary changes to use the files within the historical programs.

So, to support this without the yearly maintenance, I rewrote the historical applications to use embedded SQL. The program was able to dynamically determine the file name within the application, and it was able to determine the appropriate file name because I implemented a naming convention for all the historical data.

EXTFILE

Then I discovered EXTFILE. Using the EXTFILE keyword in your file specification allows you to specify the name of the file to be referenced at compile time, but it doesn't specify the file until run time. I wrote an article awhile back, "Reusing a Single File Description on Multiple Files," that shows you how to do this. So that alternative shot my justification for embedded SQL out of the water. I thought I was using SQL for something that RPG alone couldn't do, and then I found out that wasn't the case.

Dynamic Data Sorting

Even though embedded SQL lost the title for doing the impossible with RPG alone, I still found another good reason for using embedded SQL. You can specify the way that the files are sorted during run time.  And you can do this without the need to create a logical file. Not only that, you can also dynamically specify the way it is sorted within the program; so you're eliminating multiple logicals.  With these capabilities, I put dynamic files back on the list for reasons that I would use it. And then there are unions and joins and grouping too!

Performance

The next thing I was looking for was performance. I thought there was a different mechanism that was automatically used when I coded in SQL, but that's not true. You don't get better performance simply by using SQL coding style. You get the performance increase in the way that you create the files, which I'll discuss in an upcoming article.

This is a subjective topic; you could potentially get better performance based upon your coding style simply because you wrote it a second time. But, then again, any time you get the chance to have a second pass at the way you write a program, you will most likely find something that could be done better.

Readability

Embedded SQL programs are easier to read. "Yeah, right" was my initial response. I found it much easier to read an RPG program without SQL than one with SQL. That was my first impression. Why?  Because I was more familiar with RPG than SQL, that's why. But the fact is that SQL is easier to read once you become familiar with it. You don't believe me? Write a program in standard RPG, and then convert it to use embedded SQL. Then hand it to a Java programmer, a .NET programmer, a database administrator, or even a non-programmer for that matter, and see what they say is the easier one to figure out.

Getting a Jump Start

As you become familiar with SQL, it increases your skill set for other projects that have been hanging over your head for awhile, such as putting a GUI on your IBM i. You've looked at all the options out there, and you may still be in the process of deciding which way to go. But the fact is that almost every option you could consider involves SQL. So you could start using SQL in your RPG today and become familiar with seeing it and experiencing the things to do and not do, while still snuggled comfortably within your RPG code.

Portability

As I just mentioned, learning SQL now means that it will be one less thing to learn when you start to integrate with other environments. And not only that, when you start coding in different languages, you could practically copy and paste your SQL code into another language and have it run! Embedded SQL may be a stranger to you now, but it may be the comfort zone that you live in with another language.

I have recently seen this with fellow members of my staff. A Microsoft SQL guru was using ODBC to connect to our DB2 database to put together SQL queries over our data. He then handed the statement to one of our RPG programmers to copy and paste it into the RPG program in order to provide the data to a business intelligence system using embedded SQL.

Where the Rubber Meets the Road

Well, that's enough talk. Let's look at some code. For our example, we'll display the year-to-date net pay for employee number 400. We'll display information from the employee master file (EMPLOYEE) and gather the net pay from the employee history file (EMPHIST). We'll write a program that will gather the information for account number 400 and display the total net dollars paid for the current year in two different ways:

• Straight-up RPG without SQL
• RPG with embedded SQL

For our example, we will use two simple files. Here's the DDS for those:

EMPLOYEE contains account number and name.

     A          R MCFMT

     A            MCACCT         6S 0       COLHDG('Account Number')

     A            MCFNAME       32A         COLHDG('First Name')

     A            MCLNAME       32A         COLHDG('Last Name')

     A          K MCACCT

Figure 1: Here's a DBU view of an employee record for account 400. (Click images to enlarge.)

EMPHIST contains account number, date of pay, and net amount of pay.

     A          R MHFMT

     A            MHACCT         6S 0       COLHDG('Account Number')

     A            MHDATE          L         COLHDG('Pay Date')

     A            MHNET          9S 2       COLHDG('Pay Net Amount')

     A          K MHACCT

     A          K MHDATE

Figure 2: Here's the DBU view of the employee history for account 400 for the first day of the year.

For our data, the account, Eibeamma Ausfurhundrid, will have an account key of 400, and her net pay will be $1,000 per week.

Straight-up RPG Without Embedded SQL

For this code, you need to specify the file in the F-specs. The code will use chain and reade to move through the file:

     F* F Specs Required (Using RPG)

     FEMPLOYEE  IF   E           K DISK

     FEMPHIST   IF   E           K DISK

     F*

     D xEMPLOYEE     E DS                  ExtName(EMPLOYEE)

     D xEMPHIST      E DS                  ExtName(EMPHIST)

     D currentKey      S              6S 0

     D ytdNet          S              9S 2

     D displayBytes    S             52A

      /free

       currentKey = 400;

       ytdNet = *ZEROS;

       chain currentKey EMPLOYEE;

       displayBytes = 'Acct('

                    + %trim(%editc(currentKey: '3')) + ')';

       if %found();

         chain currentKey EMPHIST;

         dou %eof();

           ytdNet = ytdNet + MHNET;

           reade currentKey EMPHIST;

         enddo;

         displayBytes = %trim(displayBytes) + ' '

                      + %trim(MCLNAME) + ', '

                      + %trim(MCFNAME)

                      + ' Net: ' + %trim(%editc(ytdNet: '1'));

       else;

         displayBytes = %trim(displayBytes) + ': '

                      + ' NOT FOUND!';

       endif;

       // Display the Results

       dsply displayBytes;

       *inlr = *ON;

      /end-free

RPG with Embedded SQL

When using embedded SQL, there is no need for F-specs: 

     F* No F Specs Required (Using Embedded SQL)

     D xEMPLOYEE     E DS                  ExtName(EMPLOYEE)

     D xEMPHIST      E DS                  ExtName(EMPHIST)

     D currentKey      S              6S 0

     D ytdNet          S              9S 2

     D displayBytes    S             52A

      /free

       currentKey = 400;

       ytdNet = *ZEROS;

       displayBytes = 'Acct('

                    + %trim(%editc(currentKey: '3')) + ')';

       // Run the Query

       exec sql select MCACCT,  MCFNAME,  MCLNAME,  sum(mhnet)

                 into :MCACCT, :MCFNAME, :MCLNAME, :ytdNet

         from employee

           join emphist on mcacct = mhacct

             where mcacct = :currentKey

         group by mcacct, mcfname, mclname;

       // Display the Results

       if sqlCode = 0;

         displayBytes = %trim(displayBytes) + ' '

                      + %trim(MCLNAME) + ', '

                      + %trim(MCFNAME)

                      + ' Net: ' + %trim(%editc(ytdNet: '1'));

       else;

         displayBytes = %trim(displayBytes) + ': '

                      + ' NOT FOUND!';

       endif;

       dsply displayBytes;

       *inlr = *ON;

      /end-free

Whether you run the straight RPG program or the one using embedded SQL, you will get the same results. This would be a year-to-date amount into October.

Figure 3: This output is generated when calling both versions of the program.

You may or may not agree that the embedded SQL looks more intuitive than the straight RPG. I could drive the point home by running the query over multiple files that represent different years, but we'll save that for another day. Today, let's focus on the portability by looking at the same code in different languages. We'll start with a segment from PHP.

PHP Code

<?php

// Put Connection Code here...

$sqlString = "SELECT mcacct, mcfname, mclname, SUM(mhnet)

               FROM employee

                 JOIN emphist ON mcacct = mhacct

               WHERE mcacct = 400

               GROUP BY mcacct, mcfname, mclname";

$result = mysql_query($sqlString, $conn) or die(mysql_error());

while ($dataArray = mysql_fetch_array($result))

  {

  // Get Data from Fields here...

  }

?>

For the sake of staying focused on the topic, I hard-coded the account key into the strings and showed only the relevant code for comparison for the PHP and Java examples.

Now, let's look at a Java sample of doing the same thing.

Java Code

// Put Connection Code here...

String sqlString ="SELECT mcacct, mcfname, mclname, SUM(mhnet)

                     FROM employee

                       JOIN emphist ON mcacct = mhacct

                     WHERE mcacct = 400

                     GROUP BY mcacct, mcfname, mclname";

PreparedStatement stmt=conn.prepareStatement(sqlString);

ResultSet result=stmt.executeQuery();

while (result.next())

  {

  // Get Data from Fields here...

  }

Looking over all four code segments, can you tell me which one of these doesn't look like the other? 

Planning for the Future

In summary, dynamic data sorting, readability, and portability are the reasons that you would want to start using embedded SQL in your RPG programs. There are a lot of things that you may want your system to do, and looking ahead, you may see other languages in your future. This can seem overwhelming, so it doesn't hurt to get a head start. And once you start crossing over into other languages, you'll appreciate similar syntax when changing mind-set on the fly.

Download the Code

You can download the RPG and DDS source used in this article by clicking here.

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7,