Dennis is an independent consultant who specializes in workflow- and document-management technologies. He is currently working with the XSoft division of Xerox on their InConcert workflow product. He can be contacted at [email protected].
The Open Database Connectivity (ODBC) specification is intended to provide a standard interface between applications and data sources. When an application developer accesses data via ODBC, the application is easily portable to multiple data sources. When a data-source vendor provides an ODBC driver, the data source is instantly integrated with a wide variety of ODBC-enabled applications and tools.
InConcert, a workflow manager from Xerox's XSoft division, is a software system that facilitates the execution of business processes by office workers and computers. As such, it stores business-process definitions and the state of processes currently being executed. Additionally, InConcert provides an API through which applications can retrieve and update this information.
However, this API does not support the emerging visual paradigm of developing client/server applications. Using tools like PowerBuilder and Access, you build applications visually by drawing controls onto windows, setting control properties, and writing code in a scripting language. Calling a C or C++ function in a DLL is "out of paradigm." To better integrate InConcert with visual tools (and thus reduce development costs), we're investigating a "componentware" strategy. The idea is to enable users of client/server development tools to access InConcert services within the tool's programming paradigm by delivering these services through components that are "plug compatible" with those tools. An ODBC driver is one such component.
Developing an ODBC driver can be an expensive proposition. Before committing resources, management wanted to know the costs and benefits of having an ODBC driver for InConcert. We decided to develop a proof-of-concept prototype on a limited budget, taking only two weeks for the driver and demos. The goal was to demonstrate the benefits of an ODBC driver, and to gain experience on which we could base a credible estimate of the cost of developing a product component. After the prototype was complete, a decision could be made regarding its inclusion in the product plans.
We considered three approaches to developing our prototype ODBC driver:
- Microsoft's ODBC SDK.
- PageAhead's Simba Engine.
- Syware's Dr. DeeBee ODBC Driver Kit.
Dr. DeeBee ODBC Driver Kit
The Dr. DeeBee ODBC Driver Kit consists of a single diskette, printed technical documentation, and brochures. The diskette contains ODBC driver source code, a 16-bit driver and installer, and a 32-bit driver and installer. The drivers are compiled versions of the source code. The kit comes with one hour of telephone support (additional support is available for a fee). The driver that you create using the kit is redistributable royalty free (subject to limitations specified in the license agreement).
The technical documentation is a brief 17 pages. It contains an architecture diagram, a listing of function names by source file, the supported SQL grammar, and an implementation strategy. You are expected to refer to the ODBC SDK documentation for explanations of ODBC concepts and functions. The appendix on implementation strategy is the most useful part of the documentation. It provides a 12-step outline for implementing your driver.
To build the driver from the source code, you need a C development environment and the Microsoft ODBC SDK. XSoft uses Microsoft Visual C++ (Version 1.5), and has a Level 2 subscription to the Microsoft Developer Network. The set of CD-ROMs that come with this subscription includes the ODBC SDK and an online copy of the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide.
To install the kit, you simply copy all of the files from the diskette to a directory on your hard drive. (A setup program has since been added.) I was able to build and install the driver from the source code using nmake without any problems.
Designing Your Driver
The source code that comes with the Dr. DeeBee ODBC Driver Kit implements an ODBC driver for dBase files. All of the low-level file- and record-access functions are in one source file: ISAM.C. To implement your ODBC driver, you essentially replace the implementations of the 27 functions in that file. In my case, that meant implementing these functions with calls to the InConcert API.
My first step was to express the objects and operations defined by the InConcert API in terms of SQL tables and statements. The API is object oriented and defines its workflow services in terms of 14 object classes. Each class defines operations that can be invoked through API functions. Some of these operations get or set properties and relationships, while others change the state of objects. An ODBC driver for InConcert must express objects as rows in tables, and operations on those objects as SQL statements.
I chose to limit the scope of my prototype driver to two InConcert object classes: users and pools. An InConcert pool is a group of users who share a work queue. There is a many-to-many membership relationship between users and pools that is expressed as three tables in the relational model: users, members, and pools. My driver translates SQL statements on these tables into calls to the InConcert API. For example, when an application inserts a row into the Users table, the driver translates this into the API call that creates an InConcert user. Table 1 summarizes the mapping from SQL tables and statements to InConcert objects and operations.
As mentioned previously, the documentation for the Dr. DeeBee ODBC Driver Kit includes a 12-step plan for implementing your driver. Each step specifies the ISAM functions to implement and the ODBC functions to call to test your implementation. The Microsoft ODBC SDK includes an ODBC Test program that enables you to call ODBC functions without writing code. Before starting, I annotated each step with the InConcert API calls that would be needed to implement the ISAM functions. This helped to identify the fields that I needed to add to the ISAM data structures for InConcert objects.
Implementing Your Driver
I followed the implementation strategy outlined in the driver-kit documentation (skipping steps that did not apply to my driver). Table 2 lists the steps with the functions implemented in each step and the InConcert API functions called.
The first step was to implement the ISAMOpen and ISAMClose functions and test them using the ODBC Test application (Full Connect and Full Disconnect commands in the Connect menu). The functions call the InConcert API to open and close an InConcert server session. I ran into my first problem when I tried to compile and link my driver after adding calls to the InConcert API. The C binding for the InConcert API wants compiler and linker settings that are different than those used by the makefile that comes with the driver kit. It took some experimentation and a few calls to the Dr. DeeBee support line to find a combination of settings that satisfied both the ODBC driver and the InConcert API. This took more time than writing the code.
The bulk of the logic in the driver consists of mapping SQL data-manipulation statements (which manipulate rows and columns) to InConcert API functions (which operate on objects). The ISAM component of the driver is expected to implement the notion of the "current record." The ISAMGetData, ISAMPutData, ISAMUpdateRecord, and ISAMDeleteRecord functions all act on the current record. The ISAMNextRecord function advances the current record through a table.
The InConcert API defines three data types for each object class: object, set, and iterator. The API functions that retrieve users and pools return them as a set. You use an iterator to successively get the elements from the set. The iterator provides the notion of a "current element" in the set. When you call the iterator function to get the current element, it is returned as a user or pool object.
The first call to ISAMNextRecord calls the InConcert API to retrieve a set of users or pools (depending on which table is opened), creates an iterator for that set, and uses the iterator to get the first user or pool in the set. Subsequent calls to ISAMNextRecord advance the iterator to the next element of the set and get that user or pool. When there are no more elements in the set for the iterator to return, the ISAMNextRecord function returns EOF.
Things are a bit more complicated for the MEMBERS table. InConcert has API functions for retrieving all pools and retrieving all members of one pool. The ISAMNextRecord function has a nested loop for the MEMBERS table. The outer loop uses an iterator for the set of all users, and the inner loop uses an iterator for the set of pools to which the current user from the user-set iterator belongs.
The ISAMGetData function retrieves one column value from the current record; see Listing One. This function is implemented by nested switch statements, with a case for each column in each table. Each case contains a call to an InConcert API function that retrieves a property of the current object (user or pool). ISAMGetData is called in processing a SELECT statement and in processing DELETE or UPDATE statements that have WHERE clauses.
The ISAMDeleteRecord function is called to delete the current record when processing a DELETE statement. It contains a switch statement with a case for each table. For the USERS and POOLS table, it deletes the current user or pool object. For the MEMBERS table, it removes the current user object from the current pool object.
The ISAMPutData function updates one column value from the current record. It is also implemented by nested switch statements, with a case for each column in each table. Id columns cannot be updated, and the UPDATE statement is not allowed on the MEMBERS table. The cases for non-id columns of the USERS and POOLS tables each contain a call to an InConcert API function that updates a property of the current object (user or pool). ISAMPutData is called in processing both UPDATE and INSERT statements.
When processing an INSERT statement, the driver first calls ISAMInsertRecord, then calls ISAMPutData for each column value specified in the INSERT statement, and finally calls ISAMUpdateRecord. When rows are being inserted into the USERS or POOLS table, the ISAMInsertRecord calls the InConcert API to create a new user or pool (using default property values defined by the driver) and makes the new user or pool the current object. Subsequent calls to ISAMPutData change the property values for the new user or pool, and the call to ISAMUpdateRecord does nothing. INSERT statements on the MEMBERS table are handled differently, since the API call to add a user to a pool takes the user and pool objects as inputs. The call to ISAMInsertRecord clears the current objects. The calls to ISAMPutData retrieve the user and pool identified by the column values (names or ids). The call to ISAMUpdateRecord calls the InConcert API to add the user to the pool.
Coding the InConcert API function calls revealed a deficiency in the Dr. DeeBee ODBC Driver Kit. The InConcert API functions return a status object. Every call to these functions is followed by a test of the status returned. In case of an error, a numeric code and text message can be obtained from the status object. However, the ISAM interface provided no means to return this information to higher levels of the driver. Syware agreed this is a problem, and it sent me instructions for changing the ISAM interface and the driver's error-handling logic. In the interest of time, I decided to defer making these changes until the driver was working.
At this point, the driver did what I wanted, at least when called from the ODBC Test application. Comparing my ISAM.C file to the original that came with the kit showed that I had written 750 lines of code, most of which was produced by copying and pasting similar code. At each step, I first wrote and tested only the code necessary to implement the functionality for one table (or one column in a table). Once that code was working properly, I copied and modified it to handle the other tables and their columns. For example, in the ISAMGetData function, I first implemented and tested the cases for the Id and Name columns of the Users table. The remaining cases were implemented by copying one of these two cases and changing the InConcert API function called.
ODBC Driver Costs and Benefits
To demonstrate the benefits of having an ODBC driver for InConcert, I used Crystal Reports and Microsoft Access (primarily because they were already installed on my PC). The Crystal Reports demo showed how to create a report (pools and their members) with a graphical user interface (GUI), without programming. The Microsoft Access demo showed how to create a GUI for managing users and pools, again without programming. This demo consists of attaching the driver's USERS and MEMBERS tables to an Access database, running the Access Form Wizard to create a master/subform form for the USERS and MEMBERS tables, setting the join fields in the subform's property sheet, running the form, creating a user (by inserting a row into the USERS table), and adding the user to pools (by inserting rows into the MEMBERS tables).
The Access demo revealed a deficiency in my driver. Access does not allow you to update an attached table unless it has a key. My driver was not reporting any keys to Access, so Access did not allow me to insert records into the Users or Members tables. ODBC drivers list indexes through the SQLStatistics function. Dr. DeeBee support explained how to modify the source code in RESULTS.C to list the primary key indexes for my tables, and then Access allowed me to update the USERS and MEMBERS tables. (The driver kit has since been upgraded so that SQLStatistics reports indexes based on the ISAM data structures that define your tables.)
The demos communicated to my audience the benefits of having an ODBC driver for InConcert. Seeing was believing when it came to developing applications and reports without programming. Management's next question, however, was, What's the level of effort needed to make this a product? The prototyping effort gave me a solid basis for estimating: one week of labor spent on the prototype driver multiplied by the inverse of the proportion of the InConcert API the prototype covered, multiplied by the usual fudge factor for going from prototype to production software.
Conclusions
My goal was to produce a prototype of an ODBC driver for InConcert in a limited amount of time. I was not an ODBC expert, and the project budget didn't include the time to become one or the money to hire one. I could have bought off-the-shelf tools if the price weren't too high, but contract negotiations over royalties were beyond the scope of the project. The Dr. DeeBee ODBC Driver Kit was a perfect match for my situation at the business level.
At the technical level, I was able to follow Dr. DeeBee's recipe for developing my driver. The bulk of my work was in implementing the ISAM functions over the InConcert API. The kit is not perfect: In two cases I had to make changes in other components of the source code (error reporting, listing indexes), but I was able to do this within my time budget. It would be better if future releases of the driver kit handled these functions through the ISAM interface. Will we use the Dr. DeeBee ODBC Driver Kit to implement the product version of the InConcert ODBC driver? This is still an open question. PageAhead's Simba Engine is an attractive alternative for developing production quality ODBC drivers. But even if we choose that route, using the Dr. DeeBee driver kit was still worthwhile. It definitely makes driver development feasible for those who have not been initiated into the mysteries of ODBC.
For More Information
Dr. DeeBee ODBC Driver Kit
Syware Inc.
P.O. Box 91 Kendall
Cambridge, MA 02142
617-497-1376
Table 1: Expressing the InConcert Model as SQL tables and statements.
Copyright © 1995, Dr. Dobb's Journal
Users Table Members Table Pools Table
Rows One per user One for each member of each pool One per pool
Columns Id, Name, Address UserId, PoolId Id, Name
SELECT Retrieve users Retrieve pools and their members Retrieve pools
INSERT Create a user Add a user to a pool Create a pool
UPDATE Change user's -- Change a pool's
name/address name
DELETE Delete a user Remove a user from a pool Delete a pool
Table 2: Implementation steps.
Step ISAM Function InConcert Function Called
Implemented
Connect ISAMOpen CIcClient_beginApplication
CIcClient_directInitialize
ISAMClose CIcClient_terminate
CIcClient_endApplication
List Tables ISAMGetTableList
ISAMGetNextTableName
ISAMFreeTableList
List Columns ISAMOpenTable CIcUser_init, CIcUserSet_init
ISAMCloseTable CIcPool_init, CIcPoolSet_init
SELECT ISAMRewind CIcUser_list
ISAMNextRecord CIcPool_list
ISAMGetData CIcUser_getPools
CIcUser_getName
CIcUser_getEMailAddress
CIcPool_getName
UPDATE ISAMPutData CIcUser_setName
ISAMUpdateRecord CIcUser_setEMailAddress
CIcPool_setName
CIcPool_addUser
INSERT ISAMInsertRecord CIcUser_create
CIcPool_create
DELETE ISAMDeleteRecord CIcUser_deleteUser
CIcPool_deletePool
CIcPool_removeUser
Listing One
SWORD INTFUNC ISAMGetData(LPISAMTABLEDEF lpISAMTableDef,
UWORD icol,
SDWORD cbOffset,
SWORD fCType,
PTR rgbValue,
SDWORD cbValueMax,
SDWORD FAR *pcbValue)
{
CIcString icString = CIcString_initNullString();
CIcStatus icStatus = CIcStatus_init();
CIcStatusSeverity severity;
CIcPool icPool = CIcPool_init();
CIcUser icUser = CIcUser_init();
switch (lpISAMTableDef->tableId) {
case USER_TABLE:
severity = CIcUserSetIterator_getElement(lpISAMTableDef->icIterator,
icUser,icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
switch (icol+1) {
case USER_ID_COLUMN:
CIcObject_export (icUser, rgbValue);
*pcbValue = 32;
break;
case USER_NAME_COLUMN:
severity = CIcUser_getName (icUser, icString, icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcString_copy (icString, rgbValue);
*pcbValue = CIcString_length(icString);
break;
case USER_ADDRESS_COLUMN:
severity = CIcUser_getEMailAddress (icUser, icString,
icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcString_copy (icString, rgbValue);
*pcbValue = CIcString_length(icString);
break;
default:
return ISAM_ERROR;
}
break;
case MEMBER_TABLE:
switch (icol+1) {
case MEMBER_USER_ID_COLUMN:
severity = CIcUserSetIterator_getElement(lpISAMTableDef->
icIterator,icUser,icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcObject_export (icUser, rgbValue);
*pcbValue = 32;
break;
case MEMBER_USER_NAME_COLUMN:
severity = CIcUserSetIterator_getElement(lpISAMTableDef->
icIterator,icUser,icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
severity = CIcUser_getName (icUser, icString, icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcString_copy (icString, rgbValue);
*pcbValue = CIcString_length(icString);
break;
case MEMBER_POOL_ID_COLUMN:
severity = CIcPoolSetIterator_getElement(lpISAMTableDef->
icIterator2,icPool,icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcObject_export (icPool, rgbValue);
*pcbValue = 32;
break;
case MEMBER_POOL_NAME_COLUMN:
severity = CIcPoolSetIterator_getElement(lpISAMTableDef->
icIterator2,icPool,icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
severity = CIcPool_getName (icPool, icString, icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcString_copy (icString, rgbValue);
*pcbValue = CIcString_length(icString);
break;
default:
return ISAM_ERROR;
}
break;
case POOL_TABLE:
severity = CIcPoolSetIterator_getElement(lpISAMTableDef->
icIterator,icPool,icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
switch (icol+1) {
case POOL_ID_COLUMN:
CIcObject_export (icPool, rgbValue);
*pcbValue = 32;
break;
case POOL_NAME_COLUMN:
severity = CIcPool_getName (icPool, icString, icStatus);
if (severity != CIC_SUCCESS)
return ISAM_ERROR;
CIcString_copy (icString, rgbValue);
*pcbValue = CIcString_length(icString);
break;
default:
return ISAM_ERROR;
}
break;
default:
return ISAM_ERROR;
}
CIcString_free(icString);
CIcStatus_free(icStatus);
CIcPool_free(icPool);
CIcUser_free(icUser);
return NO_ISAM_ERR;
}
