Let's explore the nuts and bolts of using embedded SQL: file type, syntax, and compiling.

In a previous article, "Why Use Embedded SQL Within RPG?," I talked about why you would use embedded SQL in RPG. In this article, I will talk about how you could use embedded SQL within RPG. We'll start with an existing RPG program that is written with CHAIN and READ, and then we'll convert it to use embedded SQL.

Source Member Type SQLRPGLE

First things first. We need to make sure we are using the new type for your source member, which is SQLRPGLE.

Figure 1: Here's a list of my SQLRPGLE and RPGLE source member types. (Click images to enlarge.)

Why does the type of member matter? Well, it doesn't matter to the compiler; you can keep it as RPGLE, and it will compile just fine, but PDM won't be happy about it when you're trying to create the code. Here is what will happen if you try to put embedded SQL in a member of type RPGLE.

Figure 2: This is an example of what you'll see in PDM if you attempt SQL in an RPGLE source member.

PDM thinks it's an invalid EVAL operation. What if I'm using IBM Rational Developer for Power Systems Software?

Figure 3: And this is what you'll see in IBM Rational Developer for Power Systems Software if you attempt SQL in an RPGLE source member.

With IBM Rational Developer for Power Systems Software, you will get the same results as you would using an RPGLE source member type. So, in order to be able to code your embedded SQL using either of these tools, you will want to use the SQLRPGLE source member type to keep your editor happy.

Physical File Descriptions

For our example, we will determine the year-to-date amount that was paid to employee 400. We will be using two simple files for our example.

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 4: Here's a DBU view of an employee record for account 400.

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 5: 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 our RPG code without embedded SQL, we will chain into the EMPLOYEE file to retrieve the employee name, and then we will loop through the EMPHIST file using the account number key to accumulate the year-to-date total for the employee.

     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

Fixed-Formatted RPG with Embedded SQL

Now we will write an embedded SQL program to generate the same results:

     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

     C                   eval      currentKey = 400

     C                   eval      ytdNet = *ZEROS

     C                   eval      displayBytes = 'Acct('

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

     C                             + ')'

     C*// Run the Query

     C/EXEC SQL

     C+ select MCACCT,  MCFNAME,  MCLNAME,  sum(mhnet)

     C+        into :MCACCT, :MCFNAME, :MCLNAME, :ytdNet

     C+  from EMPLOYEE

     C+    join EMPHIST on MCACCT = MHACCT

     C+      where MCACCT = :currentKey

     C+  group by MCACCT, MCFNAME, MCLNAME

     C/END-EXEC

     C*// Display the Results

     C                   if        sqlState = *ZEROS

     C                   eval      displayBytes = %trim(displayBytes)

     C                                  + ' ' + %trim(MCLNAME)

     C                                  + ', ' + %trim(MCFNAME)

     C                                  + ' Net: '

     C                                  + %trim(%editc(ytdNet: '1'))

     C                   else

     C                   eval      displayBytes = %trim(displayBytes)

     C                                  + ': ' + ' NOT FOUND!'

     C                   endif

     C     displayBytes  dsply

     C                   eval      *inlr = *ON

Free-Formatted RPG with Embedded SQL

And here is the free-formatted version of the same code:

     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 sqlState = *ZEROS;

         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

Now let's review the differences.

No File Specifications

I discussed this in my previous article, and you can see that we are not using any F-specs in this program. This is because we are using SQL, and we are specifying the file to use in the SQL statement.

Using Both Files at the Same Time

There is no need to CHAIN into the EMPLOYEE file and then READ through the EMPHIST file. You can JOIN the two files together and use them to create the results. This might seem nice here, but it is a simple example and is only being used for one account. This gets to be really nice when you are processing all of the accounts in the file.

/EXEC SQL, /END-EXEC, and exec sql

/EXEC SQL indicates that some SQL code is about to begin, and /END-EXEC marks the end. This is similar to /free and /end-free to indicate the beginning and ending of segments of code that are free-formatted RPG.

"exec sql" is the free-formatted alternative to /EXEC SQL and /END-EXEC.

Colons in Front of the Variables

The colons (:) indicate variables inside of the SQL statement, which are referred to as host variables in SQL. These are used for both input and output in this example. The "into :MCACCT, :MCFNAME, :MCLNAME, :ytdNet" portion is used as output for the results to be put into when the statement is executed. The "where mcacct = :currentKey" is the input version that allows a variable value to be used to filter the data.

No Loop?

You don't need to loop through the records when you are using "group by" in your SQL statement. SQL will automatically "group" all of the records specified to be grouped together, and the "sum" used on the MHNET field will sum the total for the group.

Where Did sqlState Come From?

You can see the complete code above, and there are no variables declared for sqlState. This is made available automatically by the compiler. If you look at your compiler spool file, you will see the SQLCA data structure, which contains the SQLSTATE/SQLSTT variable.

Figure 6: You'll see the SQLCA data structure in the compiler spool file.

In my quest for simplistic code, and in the process of breaking my own bad habits, my previous version of this program was using sqlCode, which is discouraged from usage for the purpose of portability, and I don't want to pass along my own bad habits, so I updated this version of the code to use SQLSTATE instead. You can get a lot more resolution by expanding your code and looking for more codes that are available for SQLSTATE.

You can find a list of SQLSTATE values on the IBM site by clicking here.

Compiling the Embedded SQL Program/Module

To compile RPG programs using embedded SQL, you'll need to use a new command. You can choose either CRTSQLRPG or CRTSQLRPGI if you are creating a program. The difference between the two is that you do not have the option to determine the compile type when using CRTSQLRPG because it creates only *PGM objects. With CRTSQLRPGI, you can create *PGM, *SRVPGM, or *MODULE. I usually just use CRTSQLRPGI because you can do everything with it.

Here is the command I used to compile this program:

CRTSQLRPGI OBJ(MyLib/MCP035SQL) SRCFILE(MyLib/MySrc) COMMIT(*NONE) DBGVIEW(*SOURCE)

You options may vary, depending upon whether or not you are using commitment control and what your debugging preferences are.

The Output

There is no special way to call an SQLRPGLE program. You call it just the same as you would call any other RPG program, using the CALL command. 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 November.

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

And that's all you need to do to create an RPG program with embedded SQL. You can download the code, create the files, compile the code, and run it.

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,