The truth about Open Database Connectivity (ODBC) and other APIs, such as QELIB, depends on who you ask.
The benefits of porting database applications by writing to an application programming interface (API) for a virtual DBMS are so appealing to software developers that the computer industry has recently introduced several multidatabase APIs. Developers use these call level interfaces for applications that access multiple databases using a single set of function calls, thereby minimizing differences in application source code. The IDAPI initiative (Borland, Novell, IBM, and WordPerfect) and Oracle Glue are recent solutions to the multidatabase programming problem, but this article focuses on the concepts and feedback from experienced users of APIs from Microsoft Corp. and Q+E Software Inc. Q+E Database Library (QELIB) was the first of these products available to Windows and OS/2 developers. Q+E bundles are available with a variety of products from Microsoft, Computer Associates, IBM, Lotus, Borland, WordPerfect, and other companies. Q+E Software is replacing its earlier QELIB drivers with ODBC-compatible drivers and marketing an ODBC Driver Pack as a separate product. Version 2.0 of QELIB supports ODBC while maintaining backward compatibility with older versions of QELIB. ODBC and the other APIs provide standard database access through a common client side interface. It implements and extends the call level interface from the SQL Access Group (SAG) and X/Open.
ODBC is still a relatively new phenomenon. Several companies (Watcom, Microrim, and Quadbase) shipped early drivers at the end of 1992. Microsoft partnered with PageAhead Software to produce the ODBC Desktop Database Driver Kit in July 1993. Q+E's ODBC Pack and The Ask Group's ODBC driver for Ingres were both summer 1993 releases. ODBC was conceived as an API for Windows clients, but developers such as Q+E and PAL Consulting Ltd. (London, U.K.) are working on ports to OS/2, Macintosh, Unix, and other environments. At the time of this writing, Watcom's implementation for OS/2 is expected to be available in March 1994. (Watcom was recently acquired by Powersoft Corp.) The total number of distributed and downloaded copies of the ODBC SDK was approximately 9100 by September 1993. Microsoft ODBC Marketing Manager Colleen Lambert estimated a monthly shipping rate of 500 copies.
Most early adopters in my feedback survey (see sidebar titled "ODBC Reality and Developer Experiences") have databases in more than one format with combinations of desktop databases and servers. Specialists that view DBMSs solely from the perspective of high-performance, custom OLTP applications must consider the total application mix, including "shrink-wrap" software and desktop databases, when they evaluate interoperability solutions. In simple terms, developers must address requirements for desktop, workgroup, and enterprise solutions. ODBC provides connectivity to many database formats, so developers add it to the toolkit even when they use gateways or routers, which generally provide links only to the more popular DBMSs. This widespread connectivity may be the principal factor that motivated vendors of front-end products, such as Oracle CDE, Powersoft's PowerBuilder, KnowledgeWare's ObjectView, and Gupta's SQLWindows, to add ODBC to their product development plans.
Access and Visual Basic were early ODBC-enabled applications, so many developers evaluated ODBC performance based upon their experience with those products instead of differentiating between the API, driver performance, and client application performance. Access 1.0 was capable of being an ODBC client, and processing sets using an optional SQL passthrough dynamic link library (DLL). Visual Basic 2.0 introduced Object Layer support for ODBC, and VB 3.0 introduced data-aware controls. Among the many client tools that work with ODBC and QELIB are AutoCAD, Pagemaker, PowerBuilder, SAS, ObjectView, Lotus 1-2-3 and Improv, Excel, Crystal Reports, Clear Access, SQLView, WordPerfect, Ami Pro, and Word for Windows. The number of drivers increased as Informix, Oracle, Gupta, IBM, DEC, NCR, The ASK Group, Microsoft, and Sybase developed drivers internally or shipped drivers developed by PageAhead or Q+E. There is at least one ODBC driver for nearly every major SQL DBMS platform. You can also access data on legacy systems by using ODBC drivers for products such as Information Builders' EDA/SQL.
Figures 1 and 2 illustrate the software architecture of two common ODBC implementations. In either scenario, the ODBC Driver Manager DLL processes the application's function calls and directs them to the appropriate loadable driver. It is at this point that implementations vary. Some drivers map the ODBC functions into calls to a library of proprietary functions, that is, a native API (Figure 2). An example of a driver that follows the translation layer architecture of Figure 2 would be a SQL Server driver that maps ODBC functions to DB-Library function calls. Drivers that conform to the Figure 1 model include those that provide SQL access to databases without a native SQL API (such as Btrieve, Paradox, and dBASE), those that implement ODBC/SAG as a native API (for example, Coromandel Industries' Integra and Microrim's R:Base SQL), and those that work directly on databases without using gateways or native API function libraries (such as NCR's Teradata). If a driver follows the translation layer model, its installation will require the proprietary function library to be present.
Many of the concepts supported by the ODBC API are common to SQL programming (fetches, transactions, and cursors), but other APIs don't include functions that address the requirement of matching a client programming interface to a variety of back ends. ODBC applications that use multiple databases require context management logic to manage multiple connections and multiple SQL requests. ODBC uses handles to track the storage and context for the environment, connections, and statements. One of the first steps of an ODBC application is to allocate an environment handle by calling SQLAllocEnv. The application must allocate a handle for a database connection (SQLAllocConnect) before calling connection functions (SQLConnect, SQLDriverConnect, SQLBrowseConnect). To set or retrieve options for the current connection, the application calls SQLGetConnectOption and SQLSetConnectOption. As part of its termination logic, every application should close connections and free the connection and environment handles by calling SQLDisconnect, SQLFreeConnect, and SQLFreeEnv.
To obtain status information about the driver or DBMS, call SQLGetInfo. To get metadata or data-dictionary information, call SQLGetTypeInfo, SQLTablePrivileges, or SQLTables. One weakness of some early drivers was repetitive calls for information such as metadata. Astute developers recognized the performance hit and added caching to their applications. To control transactions and asynchronous queries, an application uses SQLCancel and SQLTransact, respectively. Use SQLTransact in your code to implement commit and rollback. ODBC does not have explicit rules functions but it supports the concept of integrity constraints using SQLForeignKey and SQLPrimaryKey.
ODBC includes several functions to prepare and execute SQL and retrieve results. To process statements, an application must first acquire a handle by calling SQLAllocStmt. There is a function for direct execution of a SQL statement (SQLExecDirect) and functions to prepare and execute statements (SQLPrepare and SQLExecute). A program calls SQLSetParam to use parameters and associate storage for parameter markers in a prepared statement. To free statement handles, a program calls SQLFreeStmt. An application can use named cursors by getting and setting the cursor name for a statement (SQLGetCursorName and SQLSetCursorName).
A program retrieves a row in a result set by calling SQLFetch. It calls SQLBindCol before doing the SQLFetch in order to associate storage and conversions for columns of the result set. This technique of fetching a bound result set applies to data and metadata, such as the information from SQLTables and SQLProcedures. Programs obtain information about columns by calling SQLColAttributes, SQLProcedureColumns, or SQLDescribeCol, and retrieve a column of data by calling SQLGetData. Applications obtain information about statement results by calling SQLRowCount and SQLNumResultCols. ODBC's support for scrollable and named cursors includes a function (SQLSpecialColumns) that permits applications to use custom scrollable cursors. SQLSpecialColumns supports rowids (the best set of columns that uniquely identify a row). Applications use SQLSetPos to track cursor position in a rowset and two new ODBC 2.0 functions (SQLOpenTable and SQLBindKey) to navigate ISAM databases.
ODBC functions can post zero, one, or more errors, so checking for errors after calling SQLError is good programming practice. An application can call SQLError at any time, but most applications call it when ODBC functions return SQL_ERROR or SQL_SUCCESS_WITH_INFO. To get a clear representation of status, a developer should check SQLState and make repetitive calls to SQLError until ODBC returns SQL_NO_DATA_FOUND. Developers using ODBC and QELIB to write for more than one back end will encounter a variety of error messages and error codes. Multidatabase programmers are looking for a magic-bullet solution in this area, so this represents an opportunity for someone to create components such as an error handler with an extensive database of error messages.
The key "interrogating" functions are SQLGetInfo, SQLGetTypeInfo, and SQLGetFunctions. SQLGetInfo provides information such as availability of stored procedures, outer joins and IEF, cursor commit and rollback behavior, default transaction isolation levels, scrollable cursors, concurrency options, conversion functions, and so on. SQLGetTypeInfo returns a result set that includes the native data types and the ODBC data types of a data source. SQLGetFunctions identifies whether a driver supports a specific ODBC function.
You'll find a program listing (INTRGATE.CPP) on the DBMS CompuServe Forum (GO DBMS) that illustrates interrogating the data source at run time to determine its features. INTRGATE creates and displays one or two lists, depending on the capabilities of the back end. The first list shows driver and server information for all ODBC data sources. If the back end supports stored procedures, it displays a count and list of stored procedures for that data source.
Programmers that are willing to trade features and ease of programming for better performance, make direct calls to the ODBC functions. Visual Basic and Access users learn the ins and outs of programming for the Access engine, commonly called JET. These products support a static result set (a snapshot) and a dynamic result set (a dynaset). Performance tends to be better using snapshots for smaller result sets and dynasets for larger result sets. Developers that write applications that use a lot of simple, single-table queries prefer the direct API technique because the payback for JET's overhead comes into play with complex queries and remote joins that take advantage of the Access engine's optimization features. Table 2 (Part C) shows the results of using this variety of techniques to run identical queries with the same data source.
If one looks at the timings while using different applications, or the response times from using various programming techniques, it becomes obvious that understanding the lay of the land may affect the success of ODBC projects. My rationale for presenting these comparisons is to simply demonstrate that there are many ODBC programming solutions, and you must evaluate those techniques in the context of your specific application. An application doing primarily single selects that require rapid response times is probably better served by avoiding data-aware controls. On the other hand, if the application requires rapid development of a data-entry program, then it is difficult to beat the ease of development with data controls. Writing to the API might be a preferred solution for applications doing simple queries, but the Access engine might be your choice for complex queries unless you are prepared to handle details such as caching and optimizing multitable queries across heterogeneous DBMSs. The message to readers is that ODBC is not unlike other software solutions that require analysis to match the tool to the task at hand.
Microsoft must also address the requirement for thunking software to do conversions between 16- and 32-bit addresses. A universal thunking manager can convert in either direction and support a mix of 16- and 32-bit ODBC components. One of the survey participants (Casey Kiernan) used ODBC to build a repository for Microsoft MIS, so one might expect that future repository products will support ODBC integration. The architecture may change as OLE objects and Cairo (Microsoft's object-oriented operating system) become more prominent, but any changes are likely to remain consistent with the Windows Open Services Architecture (WOSA) model. If one looks at printing and APIs such as ODBC, TAPI (telephony), and MAPI (messaging), it becomes clear that Microsoft's goal is to simplify programming for Windows and Windows NT application developers.
When asked to define the present and future role of ODBC and WOSA, Jonathan Lazarus, Microsoft's vice president of Systems Strategy, states that, "WOSA provides core services on the Windows platform that would otherwise need to be developed by individual ISV's and corporate developers (two of Microsoft's most important partners). By providing single interfaces to data (ODBC), mail (MAPI), interapplication communication (OLE), and other services on all Windows platforms, we provide a flexible architecture to customers who have a diverse set of systems they need to tie together. ODBC as a component of WOSA is part of our long-term strategy for Windows."
TABLE 1. ODBC functions | |||
| Level | Nav | Purpose | |
|---|---|---|---|
| Environment/ Connection | |||
| SQLAllocConnect | 0 | N | Allocate connection handle |
| SQLAllocEnv | 0 | N | Allocate environment handle |
| SQLConnect | 0 | Connect to data source | |
| SQLFreeConnect | 0 | N | Free connection handle |
| SQLFreeEnv | 0 | N | Free environment handle |
| SQLDisconnect | 0 | N | Disconnect from data source |
| SQLDriverConnect | 1 | N | Extended connect options, driver prompt |
| SQLBrowseConnect | 2 | Iterate through available connections | |
| SQLDataSources | 2 | N | Enumerate data sources |
| Statement | |||
| SQLAllocStmt | 0 | N | Allocate statement handle |
| SQLFreeStmt | 0 | N | Free statement handle |
| SQLPrepare | 0 | Prepare SQL request for execution | |
| SQLSetParam | 0 | Specify parameter marker storage, length, and type | |
| SQLExecute | 0 | Execute prepared statement | |
| SQLExecDirect | 0 | Execute a preparable statement | |
| SQLSetCursorName | 0 | Associate cursor name with statement handle | |
| SQLGetCursorName | 0 | Return cursor name for a statement | |
| SQLOpenTable* | N | Open table for navigation | |
| SQLParamData | 1 | N | Provide parameter data at execution time |
| SQLPutData | 1 | N | Send parameter's data at execution time |
| SQLNativeSql | 2 | Return SQL string after driver translation | |
| SQLNumParams | 2 | Return the count of parameters in a statement | |
| SQLDescribeParam | 2 | Describe parameter marker of prepared statement | |
| Results | |||
| SQLNumResultCols | 0 | N | Return the number of columns in a result set |
| SQLColAttributes | 0 | N | Return column descriptors for result set |
| SQLDescribeCol | 0 | Describe column characteristics | |
| SQLRowCount | 0 | Number of rows in UPDATE, DELETE, INSERT | |
| SQLBindCol | 0 | N | Identify columns to be fetched |
| SQLBindKey* | N | Specify storage, type, length, and scale for a key | |
| SQLFetch | 0 | Acquire data for bound columns | |
| SQLColumns | 1 | N | Return columns in tables |
| SQLGetData | 1 | N | Get data |
| SQLExtendedFetch | 2 | N | Extend fetch with block and scrollable cursors |
| SQLSetPos | 2 | N | Set cursor position in rowset |
| SQLMoreResults | 2 | Check for more data available from query | |
| Features and Information | |||
| SQLGetFuctions | 1 | N | Enumerate functions supported by driver |
| SQLGetInfo | 1 | N | Describe data-souce capabilities |
| SQLDrivers | 2 | N | Enumerate driver attributes |
| Metadata | |||
| SQLGetTypeInfo | 1 | N | Enumerate available data types |
| SQLTables | 1 | N | Return tables in catalog |
| SQLSpecialColumns | 1 | Info about unique row ID and updated columns | |
| SQLStatistics | 1 | N | Retrieve stats about table and indexes |
| SQLPrimaryKeys | 2 | Identify primary-key column names for a table | |
| SQLForeignKeys | 2 | Foreign-key columns for table or tables | |
| SQLTablePrivileges | 2 | List tables and associated privileges | |
| SQLColumnPrivileges | 2 | List columns and associated privileges | |
| SQLProcedures | 2 | Return stored-procedure list | |
| SQLProcedureColumns | 2 | Return columns of procedure | |
| Options | |||
| SQLSetConnectOption | 1 | N | Set connection options |
| SQLGetConnectOption | 1 | N | Get connection options |
| SQLSetStmtOption | 1 | N | Set statement options |
| SQLGetStmtOption | 1 | N | Get statement options |
| SQLParamOptions | 2 | Specify multiple values for parameters | |
| SQLSetScrollOptions | 2 | Set options that control cursor behavior | |
| Transaction and Query Control | |||
| SQLTransact | 0 | Commit or rollback transaction | |
| SQLCancel | 0 | Cancel current asynchronous function | |
| Error Processing | |||
| SQLError | 0 | N | Retrun error and state information |
| Source: Windows Multi-DBMS Programming (Wiley & Sons, Inc.). *Not supported in version 1.0 drivers. | |||
| A. Driver Improvement | |
|---|---|
| Upgraded drivers show improved performance.
1. Open table, populate grid
VIS = VB's VISDATA application (Access Engine) | 
|
| B. Performance with SQL passthrough (VISDATA) | |
|---|---|
| Performance of VB dynasets (no data control) and snapshots (grid) with and without SQL passthrough to the dBASE driver and SQL Server: SQL passthrough has an advantage. | |
|
Single table SELECT w/ ORDER BY Q+E 1.1 dBASE (6000 rows) without SQL passthrough with SQL passthrough
Single table SELECT w/ ORDER BY |
|
| C. Visual Basic Techniques: data control, dynasets, snapshots, and direct ODBC API | |
|---|---|
| Four programming techniques using the same single-table query. Performance varies with the technique. | |
| |
| D. Access engine caching | |
|---|---|
| The effect of the Access engine's caching is seen in the execution time of a series of snapshots: | |
| Seconds per snapshot |
|
| E. Typical application query times while accessing SQL Server and dBASE files | |
|---|---|
|
SELECT w/ dBASE 6000 rows, ORDER BY ODBC Pack 1.1
SELECT w/ SQL Server | 
|
|
ACC = Access 1.1 w/o SQL passthrough DEM = VBDEMO of ODBC SDK (direct API calls) INTG = Integra VDM OVW = ObjectView 2.1 VIS = VB's VISDATA application (Access engine) snapshots w/o SQL passthrough VSP = VB's VISDATA application Snapshots w/SQL passthrough XL = Excel 5.0 (MS Query) | |