Retrieve Column Descriptions in your ADO Client/Server Applications

Note: The code accompanying this article is available for download here.

Using the IBMDASQL provider, I want to find the field/column description/label for fields in a record set. However, all my scenarios return null. I am using IBM iSeries Access for Windows V5R3M0 service level SI30707.

–Andy

Here is the Excel VBA code snippet that Andy sent in.

Sub testit()
   Dim con, com, rs
   Set con = CreateObject("ADODB.Connection")
   con.Open "provider=IBMDASQL;data source=AS400;", "", ""
   Set rs = CreateObject("ADODB.Recordset")
   Set com = CreateObject("ADODB.Command")
   Set com.ActiveConnection = con
   com.CommandText = "select * from QIWS.QCUSTCDT for read only"
   Set rs = com.Execute()
   Debug.Print rs.fields(0).Properties.Item(2).Value, _
               rs.fields(0).Properties.Item(2).Name
End Sub

The result of the print statement is:

Null          IBMCOLUMN_DESCRIPTION

Andy was expecting the value “Customer number field” to be returned.

Unfortunately, I confirmed with IBM that the IBMDASQL provider will not return the column descriptions. Let me temporarily digress with the other useful info IBM supplied:

• The provider will return column headings if you query the IBMCOLUMN_HEADING property.
• Only the record level access provider (IBMDARLA) will return the column descriptions via the IBMCOLUMN_DESCRIPTION property (using the adCmdTableDirect option of the Recordset object’s Open method). Unfortunately, IBMDARLA is designed to access rows from a single table, so its usefulness can be limited in client/server applications where multi-table retrievals are needed.

There are a few other ways to approach the problem of returning column descriptions.

Approach 1: Query the System Catalog.

You can query the SYSCOLUMNS catalog view for every column in every table that the query returns. Unfortunately, this means that you have to separately maintain a list of tables and columns apart from the query itself. Also, to minimize trips to the server, you’ll probably want to request all columns per table in a single statement.

So, if your client app submits a query like this:

SELECT CUSTOMER_ID,CUSTOMER_NAME,SALES_ORDER_ID,SALES_AMOUNT
FROM ADVWORKS.SALES_ORDER S
JOIN ADVWORKS.CUSTOMER C ON C.CUSTOMER_ID=S.CUSTOMER_ID

This query against the SYSCOLUMNS catalog returns the descriptions for each column:

SELECT COLUMN_NAME, COLUMN_LABEL
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_NAME = 'SALES_ORDER'
AND TABLE_SCHEMA = 'ADVWORKS'
AND COLUMN_NAME IN ('SALES_ORDER_ID', 'SALES_AMOUNT')
UNION ALL
SELECT COLUMN_NAME, COLUMN_LABEL
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_NAME = 'CUSTOMER'
AND TABLE_SCHEMA = 'ADVWORKS'
AND COLUMN_NAME IN ('CUSTOMER_ID', 'CUSTOMER_NAME')

Approach 2: Use the SQL PREPARE and DESCRIBE statements and the catalog view.

If you have the ability to work with stored procedures and have a host OS of V5R4 or later, using the PREPARE and DESCRIBE SQL statements are a good way to go about retrieving this information from the SQL query itself. Sample RPG program GETCOLDSCR can be downloaded here. This RPG program was designed to be used as an external stored procedure. It accepts a SQL SELECT statement as a string parameter and returns a result set that contains information about the columns in the SELECT statement. Note that this program does not execute the query (although it could be modified to do so.)

Call the program in this manner:

CALL GETCOLDSCR ('SELECT * FROM QIWS/QCUSTCDT')

That call will return metadata about the query’s columns in a result set like this:

I chose to write this as an RPG procedure because it can easily return a data structure array as a result set. However, an external table function could be designed for this task as well or even a SQL stored procedure. Now let’s briefly peek inside the program.

The first major task of the program is to pass the query to DB2 using the PREPARE statement. PREPARE is a marvelous statement. It is a workhorse that dynamically translates the text of a SQL statement into a plan that the database engine can execute. (This is no small programming feat!) When you pass it a SELECT statement, PREPARE will (among other things) verify that the statement is syntactically correct, and that the referenced column names and table names are valid. If the SQL statement is invalid, the PREPARE statement will fail.

PREPARE is so versatile, it even accepts parameterized queries, so if your client side queries contain parameter markers (as indicated by a question mark) PREPARE will be able to handle them as shown here in this example:

CALL GETCOLDSCR ('SELECT * FROM QIWS/QCUSTCDT WHERE CUSNUM = ?')

This is good news because it means the client won’t have to maintain two separate query statements: one with parameters and one without.

Once a SQL statement has been prepared, the DESCRIBE statement can be used to collect metadata from the statement such as the attributes for the columns specified in a query. This information from DB2 is available to the RPG developer via a special storage area called the SQL Descriptor area. This area is allocated by using the ALLOCATE DESCRIPTOR statement.

Once the descriptor area has been allocated and populated (by DESCRIBE) the GET DESCRIPTOR statement can be used to retrieve information about each column in the query. When using GET DESCRIPTOR, you will need to request information about each column individually. In other words, if you have 10 columns in your query, you will call GET DESCRIPTOR 10 times (passing the ordinal column number as an index) to get the desired information for each column.

While GET DESCRIPTOR can tell us much about a query’s columns, including data type, nullability attribute, CCSID, etc., unfortunately, the column description is not available. However, the base column information (i.e., underlying table name and column name) for each column in the query is available, and the program can use this information to query the SYSCOLUMNS table to get the column description (just like was done in the example shown above).

To summarize the program’s steps:

1. The program passes the SQL statement parameter to DB2 via the PREPARE statement.
2. Once the query is successfully prepared, DESCRIBE returns information about each column to an SQL Descriptor Area.
3. The SQL Descriptor area will be interrogated for column information including the underlying table or view name and column name. The program will also retrieve each column’s data type, nullability attribute, CCSID, etc.
4. Once the based table and column names are known the label is retrieved from the SYSCOLUMNS catalog (column COLUMN_TEXT).
5. Each column’s metadata is placed into an array.
6. When finished, the array is returned to the caller as a result set.

Miscellaneous Program Notes

Any column expressions (such as QTY * PRICE) contained in the query statement will not have an underlying table and column name and therefore will simply return the column name in the description column of the result set. One way to simulate a meaningful description is to use a long column name as the column’s alias so that the column name appears to be a description.

For instance, the following column expression will return both the column name and description as Extended Price:

Qty * Price AS "Extended Price"

The embedded SQL RPG program should be compiled with the same naming convention (*SQL or *SYS) that your client/server environment uses.

Unless your schema (a.k.a., library) names are hard-coded within the SQL statement, bear in mind that the connection job’s library list will need to be set correctly in order for PREPARE to work correctly in your client/server environment.

If you don’t have column descriptions on your tables or views, you can use SQL or DDS to create them. In SQL, use the LABEL ON statement as shown here:

LABEL ON COLUMN ADVWORKS/SalesOrderHeader
(TotalDue     TEXT IS 'Total Due',
 SalesOrderId TEXT IS 'Sales Order Identifier')

So, there are multiple alternatives to using IBM’s OLE DB providers to retrieve this information. Making column metadata available to users in an application can be a great aid to understanding the nature of the database they’re working with. Not limited to VBA and ADO, this approach of calling a stored procedure to get column metadata can be used in other client/server environments as well.

With either of the approaches I presented, it will require another round trip to the server and more time to get the column description information, but that’s the compromise we in the IT world continually live with.

–Mike

Author’s Note: Special thanks to Lorie Dubois of IBM, who explained the limitations and ability of the OLE DB providers.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot