Drivers that conform to Microsoft's Open Database Connectivity (ODBC) standard bring new client/server capabilities to the AS/400. But what exactly is ODBC and how does it work?

ODBC allows any PC (client) with an ODBC driver to access data from almost any database server (including the AS/400). ODBC is used primarily in a client/server environment, where the server can hold large amounts of data and the client, very often a PC, offers inexpensive processing power but limited storage capacity. The client application is not aware of how the data is retrieved; it simply issues a request for data to the ODBC driver and receives its requested data in reply. PC Support/400 provides the link between your PC and the AS/400.

The purpose of this article is to show you how easily you can access your AS/400 database through the IBM ODBC driver using Microsoft's Visual Basic. An elementary example of a client/server application created with Visual Basic is provided to illustrate how simple ODBC makes accessing AS/400 data from a PC.

Alternatives to ODBC

You do have other client/server programming options on the AS/400, but many of them could have restrictions or limitations which impact your development efforts. For example, developers attempting to create client/server applications without ODBC might choose to use Dynamic Data Exchange (DDE) between their applications and a PC Support program called DQSERVER (Data Queue Server), which in turn communicates with the AS/400 through data queues. However, unlike files, data queues can't be saved. If the link between the PC and the AS/400 fails during an update, there's a good chance that data will be lost in transit with no prospect of recovery.

Another alternative is to use Remote SQL to access data on the AS/400. This method allows PC applications to access AS/400 data directly; but it requires a tedious, database-specific application program interface (API). An interface developed to access data on the AS/400 won't necessarily work on another database system. This is where ODBC enters the picture.

The advantage of ODBC is that your application can access not only the AS/400 but any database that allows ODBC access. The benefit of this approach isn't really apparent with a single application for a single platform. It becomes strikingly obvious if you're developing products that provide more generic functions. ODBC gives you the option to change either the client or the server, often without rewriting the application.

On the Market

Currently, three AS/400-oriented ODBC drivers are on the market: Born Software's ODBC/400, ShowCase Corporation's ODBC Dynamic Link Library (DLL), and IBM's ODBC driver. This article focuses on the IBM offering.

In some cases, ODBC drivers work hand-in-hand with ODBC-compliant software; the driver acts as middleware between the client- and server-based software. In other cases, ODBC drivers are built into the component software.

At present, at least two AS/400-friendly database products utilize ODBC. Microsoft's Access can-upon proper setup of an ODBC driver on your PC- retrieve data from your AS/400 directly. Lotus Corporation's Approach product employs ODBC but, as of this writing, the AS/400 has problems understanding it. This is because ODBC has three levels of compliance: Core level, Level 1, and Level 2. Core level allows the ODBC connection and simple SQL statements to be executed at the server level. Level 1 allows data "GET" and "PUT" commands. Level 2 uses foreign keys and assign privileges. Approach uses Level 2 to converse. Unfortunately, IBM's ODBC driver isn't fully Level 2-compatible yet.

As for programming languages, Microsoft's Visual C++ version 1.5 is ODBC- compatible, as is Computer Associates' CA-Realizer. If you use the ODBC driver with these products, you'll probably encounter some frustration because you'll be programming close to the metal. When working with these languages, you need Microsoft's ODBC Software Development Kit (SDK) and Microsoft's Developer's Network CD (known in the trade as MSDNCD), totaling around $200. In addition, you must be conversant with API-oriented Windows programming.

The application presented in this article requires Visual Basic Professional Edition 3.0 (the standard edition doesn't support ODBC). Unlike the previously mentioned languages, Visual Basic allows you to program at a higher level. It is a relatively easy language to learn and use, which accounts for its popularity. If you plan to use Visual Basic in a client/server environment, you can use this article as your stepping stone to application development that utilizes ODBC.

Installing IBM's ODBC Driver

IBM's ODBC is free, but it doesn't come on tapes-not even cumulative tapes. You must order it electronically as Program Temporary Fix (PTF) SF15426. When you download PTF SF15426, you'll notice three other PTFs tagging along: SF15199, SF15200, and SF15201. Fortunately, you can load (LODPTF) and apply (APYPTF) these PTFs on the fly-and no IPL is required. However, make sure that PC Support/400 is inactive during the application process.

You'll be disappointed if you're counting on the ODBC documentation to be a part of IBM's PTF cover letters-it's not. However, the good news is that the documentation comes in Windows' help format, a more readable format than an SEU source file. To access the documentation, run the following command from Windows:

WINHELP I:QIWSFL2EHNODBC.HLP

Since the documentation is in Windows' help format, it has hypertext capabilities and it prints out nicely too. Once the PTFs are installed, go to the PC on which you intend to run ODBC and bring up Windows.

From the Windows program manager, click on File and then on Run, and type:

I:QIWSFL2EHNSTP.EXE

You should install the ODBC driver and the Administration utility. (By default, both options will be selected.) During the installation process, you are asked to select from a list of available drivers. Select the AS/400 PC Support ODBC Driver. Then click on Install and Continue. When asked to add a new data source, bypass this window by selecting Continue. (The addition of data sources is covered in detail in a moment.)

Upon completion of this installation process, you'll find a new directory called ODBC on your PC and a new program group within Windows called Microsoft ODBC. Open the group (if it's not opened already) and you'll find an icon that looks like 1. At this point, you're on your way to configuring your database for ODBC access.

Upon completion of this installation process, you'll find a new directory called ODBC on your PC and a new program group within Windows called Microsoft ODBC. Open the group (if it's not opened already) and you'll find an icon that looks like Figure 1. At this point, you're on your way to configuring your database for ODBC access.

Database Configuration and ODBC

In order to access data from a server, you must make ODBC aware of the specific files that contain the data. The ODBC Administrator utility provides a configuration function that allows you to add and configure files you plan to access through ODBC. The following material describes this process.

Bring up the ODBC Administrator by double-clicking on its icon. In ODBC parlance, a file (physical or logical) is called a data source. The example in this article uses physical file QIWS/QCUSTCDT, which should be on every AS/400 that has PC Support/400 installed. ODBC refers to this file (data source) as a data source name (DSN). The Microsoft ODBC Administrator window appears as shown in 2.

Bring up the ODBC Administrator by double-clicking on its icon. In ODBC parlance, a file (physical or logical) is called a data source. The example in this article uses physical file QIWS/QCUSTCDT, which should be on every AS/400 that has PC Support/400 installed. ODBC refers to this file (data source) as a data source name (DSN). The Microsoft ODBC Administrator window appears as shown in Figure 2.

As you install ODBC, you'll see an entry in the Installed Drivers box. Click on the AS/400 PC Support ODBC Driver entry in this box. Then click on the Add New Name button and fill out the ODBC Driver Setup window as shown in 3. Use the default local location name displayed by the OS/400 Display Network Attributes (DSPNETA) command in place of MyAS400.

As you install ODBC, you'll see an entry in the Installed Drivers box. Click on the AS/400 PC Support ODBC Driver entry in this box. Then click on the Add New Name button and fill out the ODBC Driver Setup window as shown in Figure 3. Use the default local location name displayed by the OS/400 Display Network Attributes (DSPNETA) command in place of MyAS400.

The library/owner/file format specified in the Default Library prompt doesn't conform to the traditional library/file format on the AS/400. However, it's important to follow this syntax correctly; otherwise, subsequent requests for an ODBC connection will fail. Use QIWS for the library, since this is where the file resides. Use the Display Object Authority (DSPOBJAUT) command to determine the owner of the file (probably QSECOFR). Use QCUSTCDT for the file name. Now, as soon as you hit the OK button, your PC is ready for ODBC communications.

You can repeat this process to put in other data sources. For example, if five of your users each need access to 10 files on your AS/400, you'll have to repeat the aforementioned process 50 times. As an alternative, you could configure one PC manually and copy the data sources to the other four PCs. The data sources are stored in the ODBC.INI file within the Windows subdirectory on the PC.

The Sample Application

Our sample application consists of a screen (a form in Visual Basic terminology) that displays the customer's last name, first initial, and customer number from the QCUSTCDT file in library QIWS. (You may recall that we defined this file as a data source to ODBC in the previous section.) 4 illustrates this form.

Our sample application consists of a screen (a form in Visual Basic terminology) that displays the customer's last name, first initial, and customer number from the QCUSTCDT file in library QIWS. (You may recall that we defined this file as a data source to ODBC in the previous section.) Figure 4 illustrates this form.

Through the use of buttons, the user can connect to the file and retrieve the first, next, previous, or last record. These buttons, as well as the text box to the left of the buttons, are referred to as controls. In event-driven programming, a control typically has associated code that performs an action. (For anyone who is unfamiliar with event-driven GUI programming, "Visual Development Tools for RPG," MC, May 1994, covers many of the basic concepts.)

In 4, we have labeled each control of our form with a letter that relates to a portion of the Visual Basic program code in 5. For simplicity, we have listed all of the code associated with the form in one place. The code for each control is actually created separately as you build a form in Visual Basic. The code that performs an action for a control is defined within a Sub procedure. As you can see, 5 includes a number of Sub/End Sub groups.

In Figure 4, we have labeled each control of our form with a letter that relates to a portion of the Visual Basic program code in Figure 5. For simplicity, we have listed all of the code associated with the form in one place. The code for each control is actually created separately as you build a form in Visual Basic. The code that performs an action for a control is defined within a Sub procedure. As you can see, Figure 5 includes a number of Sub/End Sub groups.

Once the form in 4 is displayed, the user can click on the Connect button. This step, in a real application, should be done during the initialization phase. The first message, "Connecting to database," will appear.

Once the form in Figure 4 is displayed, the user can click on the Connect button. This step, in a real application, should be done during the initialization phase. The first message, "Connecting to database," will appear.

At this point, let's take a look under the hood. The code that executes at the onset of the program is labeled A in 5.

At this point, let's take a look under the hood. The code that executes at the onset of the program is labeled A in Figure 5.

The Dim statements define global variables. In this example, they define "db" as your database, "ds" as a view of the database, and "readout" as the string that contains the data record you're interested in.

Since these Visual Basic terms may be unfamiliar to AS/400 programmers, let's translate them into standard AS/400 terminology. A physical or logical file that is updatable is called a dynaset; and one that is not updatable is called a snapshot. You're using dynasets here.

The code inside the Connect button is shown as label B in 5. In the third statement, "QCUSTCDT" is hard-coded as the data source name. Had the next statement (the one commented out with a single, preceding quote) been used, the user would be prompted with a list of all preconfigured ODBC data sources.

The code inside the Connect button is shown as label B in Figure 5. In the third statement, "QCUSTCDT" is hard-coded as the data source name. Had the next statement (the one commented out with a single, preceding quote) been used, the user would be prompted with a list of all preconfigured ODBC data sources.

The task is to specify that all records from file QCUSTCDT are made available. This is accomplished in the fifth statement of section B's code with the SQL statement:

"select * from QCUSTCDT"

If you want to retrieve the first record, click on the First Record button. The first record appears in the text box (see lable G in 4).

If you want to retrieve the first record, click on the First Record button. The first record appears in the text box (see lable G in Figure 4).

The code associated with the First Record button is shown as label C in 5. The MoveFirst line forces the file pointer to the first record of the dynaset. In RPG parlance, the equivalent is SETLL with *LOVAL used as the search argument.

The code associated with the First Record button is shown as label C in Figure 5. The MoveFirst line forces the file pointer to the first record of the dynaset. In RPG parlance, the equivalent is SETLL with *LOVAL used as the search argument.

After the MoveFirst, we concatenate three fields: LSTNAM, INIT, and CUSNUM. We use the variable readout to store the result and then ask the text box to display it (using the SetFocus method). For the Next, Previous, and Last buttons (see labels D, E, and F in 5), we use the MoveNext, MovePrevious, and MoveLast methods in lieu of MoveFirst. The MoveNext and MovePrevious methods are equivalent to READ and READP in RPG.

After the MoveFirst, we concatenate three fields: LSTNAM, INIT, and CUSNUM. We use the variable readout to store the result and then ask the text box to display it (using the SetFocus method). For the Next, Previous, and Last buttons (see labels D, E, and F in Figure 5), we use the MoveNext, MovePrevious, and MoveLast methods in lieu of MoveFirst. The MoveNext and MovePrevious methods are equivalent to READ and READP in RPG.

I hope this sounds simple enough for you to try. The Visual Basic program presented here is shorter than a DDS/RPG program with similar functionality.

For the sake of illustration and readability, the Visual Basic code is not robust, because we've stripped out all the error traps. For example, once the user is at the beginning of file (BOF), he shouldn't be able to click the Previous Record button. Similarly, while at end of file (EOF), he shouldn't have access to the Next Record button. You can easily implement these rules by inserting code like this:

btnPrev.Enabled = False btnNext.Enabled = False

The code to check for EOF and BOF looks like the following:

If ds.BOF Then ..... If ds.EOF Then .....

What About Subfiles?

The use of subfiles is extremely popular in the AS/400 world; the GUI equivalent is a list box. Several varieties of list boxes exist: combo boxes, dropdown list boxes, and plain list boxes (e.g., file/directory listings). Filling a list box is much the same as filling a subfile. You have to read (or MoveNext in Visual Basic) enough records to allow the user to scroll up or down for selection. Side-by-side subfiles, which are sources of headaches and divorces, can be replaced by two list boxes in Visual Basic. What could be simpler and more user-friendly?

One thing that's missing (if you haven't noticed already) is the ability to run a query against more than one ODBC data source. With that capability you'd have tremendous power at your fingertips. For example, you'd be able to run a query that does a join between a mainframe Oracle data source, an AS/400 data source, and a PC database file created with Ashton-Tate's dBASE IV software! This capability would allow your application to access and act on data from sources that are fully transparent to it. One other point to keep in mind: at this stage no details have yet been released on how ODBC performs in a complex, multiuser environment. Perhaps you can contribute some information on this by using ODBC in your environment.

Go the Distance

Event-driven, GUI-based, client/server applications-like the one described in this article-are created every day. Some are more sophisticated than others, of course. Many are in daily production; some are used for on-demand jobs.

This article is intended to furnish the AS/400 programmer with information about an easy-to-use tool for building AS/400-oriented, client/server applications utilizing ODBC. RPG programmers should not find Visual Basic difficult to learn. In fact, it should be an exciting experience. I hope I've convinced you to take a hard look at AS/400 client/server programming with Visual Basic and ODBC.

Ignatius Wong is an MIS manager in Kingston, Ontario. He's a former services specialist with IBM.

Creating Client/Server Applications with ODBS

Figure 1 The ODBC program group

 UNABLE TO REPRODUCE GRAPHICS 
 
 Creating Client/Server Applications with ODBS
 
 Figure 2 The ODBC Administrator main dialog box
 
 UNABLE TO REPRODUCE GRAPHICS 
 
 Creating Client/Server Applications with ODBS
 
 Figure 3 Configuring an ODBC data source
 
 UNABLE TO REPRODUCE GRAPHICS 
 
 Creating Client/Server Applications with ODBS
 
 Figure 4 ODBC test form
 
 UNABLE TO REPRODUCE GRAPHICS 
 
 Creating Client/Server Applications with ODBS
 
 Figure 5 Visual Basic code for test form.
 
 Section A: Dim db As Database Dim ds As Snapshot Dim readout As String Section B: Sub btnConnect_Click () Print "Connecting to database ...." Set db = OpenDatabase("QCUSTCDT", False, True) 'Set db = OpenDatabase("", False, False, "ODBC") Set ds = db.CreateSnapshot("select * from QCUSTCDT") Print "Connected" End Sub Section C: Sub btnFirst_Click () ds.MoveFirst readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section D: Sub btnNext_Click () ds.MoveNext readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section E: Sub btnPrev_Click () ds.MovePrevious readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section F: Sub btnLast_Click () ds.MoveLast readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section G: Sub txtRecord_GotFocus () txtRecord.Text = readout End Sub