04
Mon, Nov
5 New Articles

Introduction to Embedded SQL

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

You should consider inserting SQL statements into your RPG-centered applications for many reasons.

 

Editor's note: This article is excerpted from Programming in ILE RPG, Fifth Edition (MC Press, 2015), chapter 10, "Processing Files Using SQL."

 

By now, you probably realize that an RPG program depends upon several components other than the program itself to do its work and that the tools used with those components use languages other than RPG: database files, whether created with SQL or DDS; printer files created with DDS; perhaps Control Language (CL) commands to set up the environment in which the program will run; and so on. Even within the program itself, some sections can exploit other languages' capabilities and features. In recent years, it has become common practice to embed SQL statements within an RPG program to access and manage its database processing. Generally, these statements are DML statements, but they can include DDL as well as a few SQL statements specifically designed for use in a program.

 

These SQL statements can complement or entirely replace native file operations (Read, Write, Update, Delete, etc.). You should consider inserting SQL statements into your RPG-centered applications for many reasons. Embedded SQL is especially useful for set-at-a-time processing, enabling an RPG program to act upon many records by using a single SQL statement instead of an iterative file loop. SQL provides database features (e.g., security at the field, or column, level) that are unavailable with DDS or through native RPG file operations. SQL has a rich set of functions (e.g., Avg, Count, Sum) that RPG operations or functions cannot readily duplicate. RPG supports the dynamic creation of SQL statements at runtime, allowing a great deal of flexibility in the way your program processes the database. As an industry standard, SQL provides consistency across software platforms, pervasive training and documentation, and organized collaboration. In some cases, SQL can offer performance benefits over native operations.

 

10.4. Exec SQL

To insert SQL statements into an RPG program, you must use the Exec SQL directive to signal that the rest of the line is an SQL statement, not an RPG operation:

 

Exec SQL sql-statement;

 

For example, the following line in an RPG program will run the SQL Delete statement:

 

Exec SQL Delete From Customers Where Czip = '60606';

 

Exec SQL is not technically an RPG operation. It is, instead, a compiler directive—an instruction that is executed once when the program is compiled, not each time the program runs. It controls what the compiler does, not what the program will do. In this case, the Exec SQL statement signals to automatically convert the SQL statement in the program source into appropriate RPG operations before the program is created. Essentially, though, you can consider Exec SQL to be an RPG operation. Later in this chapter, we examine the steps in creating a program with embedded SQL statements.

 

The Exec SQL directive must be on a single line, but the SQL statement itself can span multiple lines. Just as with other RPG operations, you end the SQL statement with a semicolon (;):

 

Exec SQL Update Customers (Cphone, Cemail)

 

                         Values ('3095559753', This email address is being protected from spambots. You need JavaScript enabled to view it.')

 

                         Where Custno = 'AB0097532';

 

While you can insert most SQL statements by using Exec SQL, not all statements are allowed, and some statements require modification to work. Yet other SQL statements are allowed only as embedded statements in a program.

 

Tip: If a program is to process a file by using SQL statements, you need not declare that file with a Dcl-f instruction. Explicit file declaration is required only if the program processes a file by using native operations (Read, Write, etc.).

 

10.5. Using Host Variables

The previous SQL Update statement specifies explicit values to use in the table update. But this form is not productive if you have to write a new program, or modify a program, every time the values for Cphone and Cemail change. To make the SQL statement more flexible, you can replace the explicit values with host variables. A host variable is a data item that an SQL statement uses as the instrument to synchronize RPG program variables with data from tables and views. So, instead of coding explicit values in the example Update statement, you can substitute host variables:

 

Exec SQL Update Customers (Cphone, Cemail) Values (:Cphone, :Cemail)

 

                         Where Custno = :Custno;

 

The host variable name is preceded by a colon (:). The name corresponds to an RPG program variable. You need not define the host variable separately from the RPG variable. It is simply a means to allow the SQL statement to refer to an RPG variable. So, if the RPG variables Cphone and Cemail have respective values of 3095559753 and This email address is being protected from spambots. You need JavaScript enabled to view it., the end result of executing either of these two Update examples is identical. You should declare the RPG variable with the same data type and size as its associated database column.

 

The Insert and Delete statements can also substitute host variables for explicit column values:

 

Exec SQL Insert Into Customers

 

               (Custno, Cfname, Clname, Caddr, Czip,

 

                 Cphone, Cemail, Cdob, Cgender)

 

               Values (:Custno, :Cfname, :Clname, :Caddr, :Czip,

 

                 :Cphone, :Cemail, :Cdob, :Cgender);

 

 

 

Exec SQL Delete From Customers Where Czip = :Czip;

 

 

Host variable names cannot begin with SQ, SQL, RDI, or DSN. Those names are reserved for database use.

 

10.5.1. Select Into

While the Insert, Update, and Delete statements can be inserted into an RPG program and can use host variables, largely without changes, embedded SQL requires a modified form of the Select statement to retrieve a result set into host variables. The Select Into variation retrieves a single row and places the result set into RPG host variables:

 

Exec SQL Select columns Into :host-variables From table {Where conditions};

 

The Into clause lists the host variables into which the result set will be placed. The result set columns and the list of host variables share a one-to-one correspondence; that is, you must list an associated host variable, in order, for each column in the result set. The result set can have only one row. Consequently, the Where clause often refers to the table's primary key. If the result set includes more than one row, SQL returns an exception code, discussed later in this chapter.

 

The following example illustrates the use of Select Into:

 

Dcl-s Custno Char(9);

 

Dcl-s Cfname Char(15);

 

Dcl-s Clname Char(20);

 

 

                         // Program will provide a value for Custno.

 

Exec SQL Select Cfname, Clname

 

           Into :Cfname, :Clname

 

           From Customers

 

           Where Custno = :Custno;

 

                         // Cfname and Clname will contain result set values.

 

 

10.5.2. Using Host Structures

Instead of listing individual host variables in an SQL statement, you may find it useful to name a single host structure instead. A host structure is a data structure that you can use with the Into clause:

 

Dcl-s Custno Char(9);

 

 

 

Dcl-ds Custdata;

 

Cfname Char(15);

 

Clname Char(20);

 

End-ds;

 

 

                       // Program will provide a value for Custno.

 

Exec SQL Select Cfname, Clname

 

           Into :Custdata

 

           From Customers

 

           Where Custno = :Custno;

 

                       // Custdata subfields will contain result set values.

 

 

As was the case before, a one-to-one correspondence must exist between the result set columns and the subfields in the host structure.

 

A host data structure is especially useful for retrieving all the columns from a record layout. In this case, an externally described data structure is appropriate. Examine the following example:

 

 

Dcl-s Custno Char(9);

 

 

 

Dcl-ds Customers Ext Qualified End-ds;

 

 

                       // Program will provide a value for Custno.

 

Exec SQL Select * Into :Customers From Customers

 

         Where Custno = :Custno;

 

                     // Customers subfields will contain result set values.

 

Here, Customers is an externally described data structure, patterned after the record layout in the Customers file. By selecting all the columns into the :Customers host structure, this code segment has effectively accomplished the same result as the native Chain operation. The data structure is a qualified data structure. While not required, making it a qualified data structure reduces the possibility that variable names in the data structure will conflict with names elsewhere in the program. The program refers to the data structure subfields as Customers.Custno, Customers.Cfname, Customers.Clname, and so forth.

 

10.5.3. Handling Null Values

Recall from Chapter 3 that the Null constraint allows a column to contain a null value. A null value represents the absence of any data for a column; it is an unknown value—not a zero or blank. A null-capable column can be set to null instead of an actual value. Most IBM i tables specify Not Null for each column, forcing that column to always have a value. If, however, a column is null capable, your RPG program can detect a null value in a column retrieved via SQL.

 

Exec SQL Select Cfname, Clname

 

           Into :Cfname, :Clname

 

           From Customers

 

           Where Custno = :Custno And Cfname is Not Null;

 

This statement does not retrieve any row with a null Cfname value even if the Custno value matches the condition.

 

If you want the program to retrieve null-capable columns, you can use an indicator variable to detect whether or not a column value is null. An indicator variable is similar to a host variable, except that you use an indicator variable to detect a null value in a retrieved column or to set a column to a null value. Wherever an embedded SQL statement allows a host variable, you can optionally include an indicator variable immediately following the host variable (with no comma between the host variable and the indicator variable):

 

Dcl-s Custno     Char(9);

 

Dcl-s Cfname     Char(15);

 

Dcl-s Clname     Char(20);

 

Dcl-s NullCfname Int(5);

 

Dcl-s NullClname Int(5);

 

 

Exec SQL Select Cfname, Clname

 

           Into :Cfname :NullCfname,

 

                 :Clname :NullClname

 

           From Customers

 

           Where Custno = :Custno;

 

In this example, :NullCfname and :NullClname are indicator variables. Even though they are named the same, an SQL indicator variable is not the same data type as an RPG indicator variable. While RPG indicators are a true/false data type, SQL indicator variables are integers (five digits, signed). In the previous example, if :Cfname has a null value, then :NullCfname has a negative value (-1). But if :Cfname is not null, then :NullCfname is positive or zero.

 

To set a column to a null value, you can use the SQL Update statement, setting the indicator variable to -1:

 

Cfname = *Blanks

 

Clname = *Blanks

 

NullCfname = -1;

 

NullClname = -1;

 

Exec SQL Update Customers

 

           Set   Cfname = :Cfname :NullCfname,

 

                 Clname = :Clname :NullClname

 

           Where Custno = :Custno;

 

If you are retrieving the result set into a host structure, you can also organize indicator variables into an indicator structure, which is simply a data structure that uses the indicator variables as subfields:

 

Dcl-s Custno Char(9);

 

 

 

Dcl-ds Custdata;

 

Cfname Char(15);

 

Clname Char(20);

 

End-ds;

 

 

 

Dcl-ds Custnulls;

 

NullCfname Int(5);

 

NullClname Int(5);

 

End-ds;

 

 

                     // Program will provide a value for Custno.

 

Exec SQL Select Cfname, Clname

 

           Into :Custdata :Custnulls

 

           From Customers

 

           Where Custno = :Custno;

 

                     // Custdata subfields will contain result set values.

 

                     // Custnulls subfields will contain indicator variables.

 

 

The indicator structure must contain a corresponding subfield for each column in the result set even if it is not null capable.

 

Learn more by ordering Programming in ILE RPG, Fifth Edition from the MC Press bookstore.

 

James Buck
Jim Buck's career in IT has spanned more than 35 years, primarily in the college education, manufacturing, and healthcare industries. Past president (13 years) of the Wisconsin Midrange Computer Professional Association, he has served on several teams developing IBM and COMMON certification tests. Jim has co-authored several IBM i textbooks with Bryan Meyers that are used by many companies and in colleges worldwide. Other accomplishments include: recipient of the 2007 IBM System i Innovation - Education Excellence Award, 2014 COMMON President's Award, and 2013/2016/2017 IBM Champion - Power Systems.

Jim is the president and founder of imPower Technologies, where he provides professional IBM i training and consulting services. He is active in the IBM i community, working to help companies train their employees in the latest IBM technologies and develop the next generation of IBM i professionals.

MC Press books written by Jim Buck available now on the MC Press Bookstore.

Control Language Programming for IBM i Control Language Programming for IBM i
Master the A-Z of CL, including features such as structured programming, file processing enhancements, and ILE.
List Price $79.95

Now On Sale

Mastering IBM i Mastering IBM i
Get the must-have guide to the tools and concepts needed to work with today's IBM i.
List Price $85.95

Now On Sale

Programming in ILE RPG Programming in ILE RPG
Get the definitive guide to the RPG programming language.
List Price $95.95

Now On Sale

Programming in RPG IV Programming in RPG IV
Understand the essentials of business programming using RPG IV.
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: