There is also a Printer Friendly version of this page.

This month's article covers ODBC data access using a set of VisualAge C++ classes and is once again aimed at programmers. While VisualAge (VAC) C++ already has tools and IOC (IBM Open Class library) classes for getting at data in databases, including ODBC data, those tools and classes are oriented towards programs where the structures of the data and tables are known at compile time. The Data Access tool included in VAC builds C++ classes for each table that you intend to access. The "attributes" of the class represent the columns of the table and the methods of the class are used for retrieving rows and sending updates to the database. This is all well and good if you know in advance what tables you will be working with. But I found myself in many situations where I needed to access tables in a database and I didn't know at the time I was writing the program what those tables were or how they were structured. I wanted something more flexible. And something much easier to use. I guess you could say I had VBA envy.

Microsoft's Visual Basic for Applications (VBA) is a common language used by Microsoft Office applications for writing scripts and macros for those applications. It is somewhat like Rexx on OS/2, except VBA only works with Microsoft applications where Rexx works across multiple vendor's applications. VBA however has a substantial advantage over Rexx: easy data access (hopefully we will address Rexx's data access shortcoming in the next article or two.) Since ODBC is a Microsoft standard it only makes sense that VBA programs can use the data access facilities of VBA to get at any ODBC data. Since VBA is BASIC (on steroids,)e those data access methods are very easy to use - hence you have a very easy to use language that can get at any data in an ODBC database. I longed for some simple, flexible way of getting at ODBC data in C++, something as powerful as the data access facilities of VBA and as easy to use.

Overview

1. Simple and easy to use. I wanted something that only took a few lines of code to get at data, and that was easy to incorporate (headers, libraries, etc.) into a program. My inspiration came from VBA data access methods.
2. Decent performance.
3. Run on OS/2 and Windows NT. While the majority of my work is on OS/2 there are times I have to accept Windows as my customer's platform choice. I want to leverage my OS/2 work to Windows if I have to. Besides VisualAge C++ v 4 is an exceptional cross platform tool in itself, why not take advantage of that capability?
4. Bypass Intersolv nag screen. Since Intersolv won't sell ODBC drivers for less than a $10,000 purchase, I thought it was important to make the classes work with the drivers that are available, i.e. the existing Intersolv drivers - more about this below.
5. Get at Windows NT and Win-OS/2 ODBC data. There are ODBC drivers available for Windows that will never be available for OS/2 that I wanted to use from OS/2 programs.
6. Doesn't require ODBC SDK. This wasn't really a goal but it is a benefit. You don't need the ODBC SDK in order to use these C++ classes.

My intention was not to replace the ODBC API but rather to simplify the portion of the ODBC API that I worked with most often.

PODBC is made up of 7 VisualAge version 4 classes which handle different aspects of working with ODBC data. While the classes are compiled with version 4 IOC components, I tried to not use any version 4 specific classes so that the classes could be compiled with VAC version 3.

Those 7 classes are:

PEnvironment	Establishes an ODBC "environment", i.e. it initializes ODBC for a program. This class is required for all the other classes to work.
PConnect	Handles connecting to an ODBC data source and has methods for sending non-select type SQL statements to the database.
PCursor	Opens a cursor for retrieving data. It has methods for determining the number of columns in the result set, the name, type and size of each result set column, as well as fetching the rows of data.
PTableList	Lists table, views and other objects in a data source.
PTableDef	Lists the columns in a table and the attributes of each column.
PStatement	Prepares a statement for use. Prepared statements are statements that are used repeatedly with only very slight variations - for example an insert statement.
PFormatData	Formats date, time, and numeric type data returned by the PCursor class.

The first two classes, PEnvironment and PConnect, are always required; you can't do any real work without them. The rest of the classes are only required if you need the function provided by the class.

The sequence used in a program that is accessing ODBC data is:

1. Create a PEnvironment object.
2. Create a PConnect object and associate it with the PEnvironment object. Then connect to the data source, i.e. the database.
3. Create a PCursor, PTableList, PStatement, or PTableDef object as needed (if you need them) and associate the object with the PConnect object you created.

In the spirit of quick and dirty programing I am going to jump right to examples of programs that use the classes.

Example 1 - Listing Data Sources Defined in ODBC

Line 6 is where the environmental object is created. The default constructor creates a environment that is linked to the local ODBC Driver Manager. This works fine for VIO type programs, i.e. non-PM type programs; or PM type programs that are going to connect to a non-Intersolv ODBC database driver, such as DB2/2 or PostgreSQL. For PM type programs that are going to connect to Intersolv drivers you need to use a different constructor. (This is because PM type programs that access Intersolv ODBC drivers bundled with an application, such as Lotus SmartSuite, throw up an annoy-ware type of dialog box every time you open a cursor. This alternate constructor opens a VIO session that handles the actual ODBC interface and handles the communications between the class methods and that session.) There are also alternate constructors for creating an environment that is linked to an ODBC Driver Manager on a remote machine, say a Windows NT machine for example.

Line 8 opens the Data Source Name (DSN) list.

Line 10 is a loop that fetches each row from the DSN list. Each row contains two columns: a name and a description. The fetch() method returns false when attempting to fetch past the last row.

Notice that the guts of the program are enclosed within a try block of a try-catch set. Any errors encountered by the classes result in an exception, which is picked up by the catch block. Line 18 outputs the PODBC error number and line 19 outputs the error message; if the error comes from the ODBC Driver Manager or database driver the entire ODBC error will appear in the error text.

The output from the program is shown below.

Compare the output from the example program with the data sources lists in the ODBC Administrator.

To build the example the only files you need are the header file for the PEnvironment class and the library file needed for linking to the PODBC DLL. The figure below shows the VisualAge C++ version 4 configuration file used to build the example.

Line 8 identifies the target of the "build process," in this case xmp01.exe.

Line 10 specifies the header file for the PEnvironment class.

Line 11 specifies the library needed for the PODBC classes.

Line 12 specifies the source code file that contains the example.

The rest of the configuration file is pretty much standard. The settings shown in the rest of the file can be changed either in the IDE or by editing the configuration file. I prefer to edit the file because it is quicker.

To run the program you need the following files somewhere in your LIBPATH

• PODBC.DLL
• PODBCDET.DLL
• PNPIPE.DLL
• STARTPRG.DLL

Example 2 - List Rows from a Table

Line 5 creates an environment object.

Line 7 creates a connection object. Notice that the environment object is passed in as the first parameter to the connection object's constructor. The second parameter is the data source name (DSN) that we want to connect to. In this case we are connecting to a dBase database driver named dbase_xmp. In OS/2 the name specified for the DSN must exactly match, upper and lower case, the name as it is specified in ODBC. This can be a problem if you don't already know the name or if you cannot guarantee the form of the name as it is configured in ODBC. The correctDsnCase() method in the PEnvironment class can help; it takes the name of a data source and returns that name in the correct format so that it matches the entry in ODBC. To use that function in this example line 7 would read

PConnect con(env,env.correctDsnCase("dbase_xmp"));

There are 8 different constructors for the PConnect class. Most, like the constructor used in this example, automatically connect to the data source.

Line 9 creates a cursor object. The PCursor object also has multiple constructors, most of which open the cursor when the object is constructed. The first parameter to the constructor is the connection object, which links the cursor to the connection. The second parameter is the SQL select statement

Line 11 starts a loop that fetches each row of data from the cursor's result set. The fetch() method returns false when fetching past the last row of data.

Line 13 sets up a loop that goes through each column of the result set. The numOfCols() method returns the number of columns in the result set.

Line 15 outputs the data for each column. The data() method retrieves the data for a specific column. The parameter passed to data() can either be a column number (ordinal) or the name of a column; in this case we are using the ordinal since we are looping though the columns for each row.

The results of running this example are show below.

The configuration file for building this example is show below.

Lines 11 and 12 add the headers for the PConnect and PCursor classes.

Line 8 changes the name of the target.

Line 14 specifies the name of the source file.

Example 3 - Creating a Table, Displaying the Table Structure, Listing the Data

Line 5 creates an environment object.

Line 7 creates a connect object. Once again we are connecting to a dBase ODBC driver. Since dBase is what ODBC calls a "file type" of database, a directory in the OS/2 file system represents the database, with each table being a file in that directory. The setup screen for the dBase driver, accessed from the ODBC Administrator, contains an entry where you specify what file system directory is to be the "database". You can use the setQualifier() method of PConnect to point the "database" to some other directory. On my system the default directory for the dbase_xmp data source is f:\temp.

Line 10 uses the executeSql() to send a SQL create table statement to the database. This creates the file bdays.dbf in the directory f:\temp. With most dBase drivers you can also qualify the table name with a path in order to access a table in a directory different from the default database/directory. For example we could have used

con.executeSql("create table c:\mydata\bdays(fname char(10),lname char(10), bday date)");

to create the table in the C:\MYDATA directory instead of creating it in f:\temp.

Line 13 creates a PTableDef object, which is used to list the columns that make up a table.

Line 15 outputs the number of columns in the bdays table with the PTableDef method numOfCols()

Line 17 starts a loop that runs through the columns on the table.

Line 19 outputs the name and type of each column in the table. The dsnType() method returns the name of the column according to the database driver. There are also PTableDef methods for returning the ODBC type of the column, the size and precision of the column, whether the column in nullable, and the remarks associated with the column; dBase "databases" do not support remarks for a column.

Lines 22 - 26 insert rows into our new table. We are using an ODBC escape clause to enter date values. This is because each database has its own format for entering date values. For example some require the date string to be in the form mm/dd/yyy, others require dd-month-yyyy with the month spelled out. The ODBC escape clause lets us enter dates in one known format and the ODBC database driver is responsible for translating that into the format required by the database. There are ODBC escape clauses for specifying date, time, and timestamp values; the three most troublesome data types. On the other hand, dates are always returned by ODBC in the same format, YYY-MM-DD, regardless of how the database stores the data (we will see how to format the output value below.) While this helps greatly in dealing with dates from different databases there are still problems caused by some databases, Oracle and Microsoft Access to name two, that add a time value to date columns. So for those databases the date part of the value is returned by ODBC as YYYY-MMM-DD but some time value is also added to the end.

Line 29 opens a cursor for the bdays table.

Lines 31 - 33 output a heading that appears above the data using one of the PCursor methods that return characteristics of the result set. Once the cursor is open you can use various PCursor methods for finding out: how many columns are in the result set, the name of each column, the data type of each column, it size and precision, and if the database driver supports it, the table that column came from.

Line 35 creates a FormatData object that we will use to format the date strings that are output in line 41.

One of the ways the PODBC classes achieve simplicity is by converting all data output to IStrings - the data() method of the PCursor class always returns an IString value. (This isn't nearly as inefficient as it first seems because 1) all data values have to be converted to text in order to be output anyway, 2) many database data types - date, time, timestamp, decimal, and some numerics - do not have a direct corresponding C or C++ type and so have to be converted anyway.) While this makes working the classes very easy in most situations, it makes formatting date, time and numeric values rather difficult since you cannot use any of the built in facilities of C or C++ for formatting. The FormatData class is designed to solve that problem. It has methods that format date, time, timestamp, and numeric values that are stored in IStrings. However for those situations where you really need a numeric value you can use one of the IString methods -.asInt(), or .asDouble() - to convert the value returned by data() method.

Line 41 uses the formatDate() method of the FormatData object to format the date value returned from the bday column. formatDate() takes the value to be converted and a token specifying the type of format as input parameters, and returns a formatted value as IString.

The following figure shows the output for XMP03.EXE

In order to run the program you must have the FrmData.DLL file somewhere on your LIBPATH, in addition to the PODBC.DLL and PODBCDET.DLL files.

The configuration file for building XMP03.EXE is shown below.

Line 13 adds the header for the FormatData class.

Line 14 specifies the FormatData library.

Line 15 adds the header for the PTableDef class.

Technical Details

The code for these examples is included in the PODBC classes.

The PODBC classes appear on hobbes.nmsu.edu in three files:

PODBC2vv.ZIP (where vv is a version number) contains the library and DLL files needed to use PODBC on OS/ 2

PODBCWvv.ZIP (where vv is a version number) contains the library and DLL files for Win32

Conclusion