You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > RPG

Bringing the Power of SQL to Your RPG Program


Many RPG programmers have used interactive SQL as a tool to quickly browse data or create test data scenarios but have stopped short of embedding it into their RPG programs. If you fall into this category, this article will introduce you to the basics. Embedding SQL into your programs allows you to perform more powerful data selection, sequencing and updating than is possible with either logical files or Open Query File (OPNQRYF).

You can embed many kinds of SQL statements into your programs: SELECT, UPDATE and DELETE, of course, for manipulating data from files, as well as CREATE, DROP and even ALTER SQL statements for creating and managing database objects such as physical files.

The basic syntax for embedding SQL into RPG looks like this:

C/EXEC SQL                                                                                                 

C+     SELECT  CusNam, City, State

C+       INTO   :CustName, :City, :State                                                                       

C+       FROM  CustMast                                                                                      

C+       WHERE  CusID = :CustInput                                                                          

C/END-EXEC        

The /EXEC SQL and /END-EXEC statements surrounding the SQL statement itself and the + signs in column seven for each line of the SQL statement are required entries. The SQL statement itself is completely free format and can be typed anywhere between columns eight and 80, so the following is also perfectly valid syntax:

C/EXEC SQL                                                                                                 

C+ SELECT CusNam, City, State INTO :CustName, :City, :State                                                                       

C+   FROM  CustMast WHERE  CusID = :CustInput                                                                          

C/END-EXEC        

Those experienced with interactive SQL may be wondering about two aspects of the preceding SELECT statement:

1. What is the INTO clause?

2. Why are there colons (:) in front of some of the field names?

In interactive SQL, when you enter an SQL SELECT statement, the selected data typically appears on the screen. When you embed a SELECT statement into a program, the data must go into RPG fields in your program. That's what the INTO clause does-it identifies the RPG fields for the values selected from the database file. The names immediately following the SELECT verb are the database names; those following the INTO clause are the names of the program variables that will receive the values. The two sets of names are mapped one-to-one by position in the list. The colons in front of the names identify them as "host variables" (i.e., they represent RPG program variable names). (Note: As this example illustrates, the RPG variable names may match the external database field names but arent required to do so.)

Since the SQL statement provides the name of the file from which the data is retrieved, no F specification is used for files that will be accessed by SQL statements. Therefore, it's important to remember that the external field names from the file wont automatically be defined in the RPG program as they would if an F specification were included. Therefore, you must define the fields in the RPG program, typically on the D specifications. You may find the use of externally described Data Structures (DS) as a useful way of getting the appropriate field definitions into your program. This is particularly true if you're fond of using the "SELECT *" syntax. Those of you who hate typing may want to take note of the following example which uses an externally described DS in the SQL statement:

D CustRec       E DS                ExtName(Customer)

                       

C/EXEC SQL

C+  SELECT * INTO :CustRec               

C+  FROM CUSTMAST WHERE CUSTID = :CustInput

C/END-EXEC

Embedding SQL into your programs allows you to perform more powerful data selection, sequencing and updating than is possible with either logical files or Open Query File (OPNQRYF).

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters