21
Thu, Nov
1 New Articles

Why Use Embedded SQL Within RPG?

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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

 

102010TomSnyder_WhyEmbeddedSQL_figure1 

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

 

102010TomSnyder_WhyEmbeddedSQL_figure2

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.

 

102010TomSnyder_WhyEmbeddedSQL_figure3

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,

Thomas Snyder

Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.

Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.

Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.

Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.


MC Press books written by Thomas Snyder available now on the MC Press Bookstore.

Advanced, Integrated RPG Advanced, Integrated RPG
See how to take advantage of the latest technologies from within existing RPG applications.
List Price $79.95

Now On Sale

Extract, Transform, and Load with SQL Server Integration Services Extract, Transform, and Load with SQL Server Integration Services
Learn how to implement Microsoft’s SQL Server Integration Services for business applications.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: