Last week I heard about an amazing job opportunity from my second cousin's ex-girlfriend. She told me to apply right away, or I would miss the opportunity of a lifetime. So I showed up in the lobby, but was stopped by the receptionist. I didn't have an appointment. I didn't even have a contact name or a job description. But if I could just get a little information from this guardian of the front door, I'd be on my way to employment nirvana.

ODBC is a lot like that. There are a trillion bytes of data out there on the LAN, files full of valuable information that are just out of reach of RPG programmers. Before ODBC support was added to IBM's VisualAge for RPG, or VARPG, you had little choice but to use some file transfer method to duplicate the data on an iSeries server and then write code in good old RPG II. Now there is a better way.

This article will begin to explain how to write a VARPG program that acts as an information server. The VARPG program will monitor a data queue for commands to execute or commands to retrieve data. The retrieved information, or result code, will be placed on another data queue, which is monitored by the requesting program.

ODBC stands for "Open Database Connectivity." It is a standardized method for retrieving information from many other database systems, such as DB2, Oracle, Informix, SQL Server, and Microsoft Access. JDBC stands for Java Database Connectivity. JDBC provides a common method for accessing databases from Java programs. In VARPG, the ODBC/JDBC part supports both standards of database access. A program written in this way can easily be pointed to any database that supports ODBC/JDBC. ODBC is like a receptionist. If you can learn to have a nice conversation with the receptionist, you can find out almost anything.

Here is your chance to begin a meaningful conversation with the receptionist. If you mess this part up, you won't get any information or go anywhere, except out the door. You are expected to introduce yourself, present some qualifications, and state your business. The ODBC part in VARPG has an attribute that provides this function. It's called the connect string. The connect string provides the database manager with the information it needs to decide whether to honor subsequent requests. The bad news is that each database has its own connect string. The good news is that we can write a short VARPG program to capture the connect string. Our first program example will demonstrate how to retrieve the connect string for the Microsoft Access ODBC driver, but any ODBC driver will work. Let's get started.

1. Create a new GUI project by clicking the new GUI project icon in the VARPG projects folder.

2. When the new project appears, double-click the window with canvas icon. This will open the design window where we will place all of our parts.

3. Select the window part, and open the properties window by right-clicking the Properties option on the pop-up menu.

Now that we've created a utility to capture ODBC connect strings, let's take a moment to look at the three lines of code and see how it all works:

When you press the Connect to DB button, the PBCONNECT/PRESS action subroutine is executed.

Line 40 instructs the ODBC part to connect to the ODBC database. Since the Connect String attribute is blank, the ODBC driver will prompt us through all the parameters necessary to connect to the data source of our choosing.

Line 42 retrieves the derived Connect String from the ODBC part and displays it in the Multiline edit part.

When the validated Connect String is displayed, copy the field value to your clipboard and paste it wherever you need it.

The ODBC/JDBC part is like the F-spec in RPG. In the same way that you need a separate F-spec for each file that you intend to access, you will need an ODBC/JDBC part. Care should be exercised when naming these parts, so that the references to them are clear and meaningful. (A word of warning, if you change the name of a part after you have attached VARPG code to it, the connection between the part and the code will be lost.) Each ODBC/JDBC part must be "connected" to the database before being used and may have a unique connect string. This feature opens all sorts of possibilities. You can now write a program using RPG syntax to copy data from SQL Server to a Microsoft Excel spreadsheet or an Oracle database.

Well, we've made it past the receptionist and are ready to speak with the department manager. An interview is all about asking the right questions. Now that we know how to connect to the database via ODBC/JDBC, we can begin to get information from the database manager. There is a long list of attributes for the ODBC/JDBC part. Here, we will focus on the bare necessities. To retrieve data using the ODBC/JDBC part, follow these steps.

Connecting to the database involves setting up the Connect string and using the Connect attribute to perform the action. The connection can be verified by checking the contents of the Connected attribute.

In this sample, we demonstrate connecting to the database and verifying our connection.

Line 18 retrieves the status of the connection attempt. If the connection was successful, the number 1 is returned. If the connection failed, a 0 is returned.

To bring requested data from the ODBC/JDBC driver into your VARPG program, you must provide the ODBC/JDBC part with information about each column that is returned. This information allows the ODBC/JDBC part to transform and map the data into VARPG variables. For each column that you want to bring back into your program, you must specify the following attributes:

In this sample code, there are two techniques for setting the BufferPtr attribute.

Line 11 uses the built-in function %ADDR to retrieve the memory pointer for the field Item.

Line 4 uses the INZ keyword to initialize the value of Total_Ptr to the correct value when the program is started. Functionally, the results are the same. Your preference will determine the method you use. Please note as well that there is no free-form method available to set the BufferPtr attribute. You will need to use the SETATR op-code.

[Note: If you intend to generate Java code from your project, you will need to link the fields to the ODBC/JDBC part using a different method. Since Java does not support the use of pointers, set the BindPart attribute to the name of the field that you want the data placed into. Do not place data in the BufferLen, BufferDec, BufferType, or BufferPtr attributes.]

The SQLQuery attribute is where the SQL statement to be executed is stored. The stored SQL statement is not executed until the ExecuteSQL attribute is set to 1. This example is a static query. The value of field SQL is always the same. The value of SQL can also be dynamic. You can construct your SQL statement at runtime and place variable data in the SQL field. Set the SQLQuery attribute and then execute the statement.

When a Select statement is executed, the database manager gathers the requested record set and places it into a cursor. A cursor is like a temporary file containing just the records that were selected. Retrieving these records is as simple as setting the Fetch, FetchNext, or FetchPrior attributes to 1. Fetch will retrieve a specific record, FetchNext will return the next record, and FetchPrior will return the previous record in the set.

IBM has provided a good example of ODBC/JDBC part usage in the Sample folder, in your VARPG projects folder. It is named Odbcceld.ivg. The program comes complete with an Access database file and demonstrates the techniques described in this article. Build, tweak, and run this project to see just how powerful the ODBC/JDBC part is. There is much more that can be done with ODBC/JDBC access to other file systems. Records can be added, changed, and deleted. Tables can be created ad hoc. Stored procedures can be called. A whole new world is now open to RPG developers. What are you waiting for? Get started today. In my next article, we will explore the use of data queues for communication between VARPG and RPG programs.

Raymond Everhart is an independent programmer/consultant in the Dallas/Fort Worth area, and has 17 years' experience with IBM midrange servers. He can be reached at reverhart@raecodesign.com.