ODBC Branches Out ODBC Branches OutRecent innovations in ODBC include multiplatform drivers, ODBC servers, replication, Web access, and more.
Open database connectivity (ODBC) is a multidatabase application programming interface (API) that has evolved into a multiplatform API that exhibits performance comparable to native SQL APIs. Although not as widely understood as older technologies such a s SQL, ODBC has been ported to multiple operating systems and spurred product innovations by a number of companies. ODBC is available for all Windows operating systems, a variety of Unix systems, the PowerPC operating system, Macintosh, and OS/2. ODBC 3. 0, the next release forecast for early 1996, includes major new features that will align ODBC with the ANSI/ISO SQL-92 call-level interface (CLI). (Discussing ODBC requires numerous acronyms. See Table 1 for a list of abbreviations used in this article.)
ODBC is ubiquitous. Its acceptance by database developers has prompted a medley of companies to produce drivers. Companies such as Intersolv Inc., Visigenic Software Inc., OpenLink Software Inc., and Simba Technologies Inc. provide drivers for multiple D BMSs and operating systems. Many companies such as Microsoft Corp., Powersoft Corp., Centura Software (formerly Gupta Corp.), ProtoView Development Co., Integra Technology International, and Intersolv ship ODBC drivers with their developer products. Even companies with record managers and traditional ISAM products have developed ODBC drivers. Btrieve Technologies Inc., FairCom U.S. Corp., and Sequiter Software Inc. recently added drivers for their products. Despite the existence of drivers for most majo r DBMS products, there is still a market for driver developer kits. Syware Inc. and Simba Technologies Inc. sell SDKs to developers writing ODBC drivers for ISAM, proprietary, and application-defined databases.
Competition in the ODBC driver market continues emphasizing performance as a tool to gain market share and attract developers using proprietary SQL APIs. The emergence of high performance drivers and an upcoming release with significant new features are two of several developments on the ODBC front. Recent innovations include a product that integrates ODBC and DCE, ODBC servers (server-side ODBC), and software for accessing ODBC databases from the World Wide Web. In addition to these developments, Micro soft is working to integrate ODBC into its OLE architecture for enterprise-wide applications. Readers that are unfamiliar with ODBC or the concept of database drivers should refer to "Understanding ODBC and Multidatabase APIs" (DBMS, March 1994, page 44) . Readers that want ODBC 3.0 information should refer to "Understanding ODBC 3.0 Standards and OLE DB" (in this issue).
Intersolv, OpenLink, and Visigenic are the primary suppliers of drivers for different databases and operating systems, but they are not the only source for ODBC drivers. Some companies (such as Quadbase Systems Inc.) provide drivers for their own DBMS pr oducts. Other companies (such as IBM) provide a driver for a specific niche market such as the AS/400. And some companies (such as Information Builders Inc.) support ODBC as an adjunct to their own middleware. Despite the multitude of commercially availa ble drivers (see the table, "OBDC Drivers,"), there are still situations for which developers need to write an ODBC driver to support proprietary or specialized data formats. When a custom driver is required, a developer can ch oose driver developer tool kits from Simba Technologies, Syware, and Automation Technology Inc.
For developers building enterprise applications, Oracle and Sybase offer gateway products that use ODBC to connect to heterogeneous databases. Oracle Open Gateways connect to approximately 30 different DBMSs while presenting the programmer with transpare nt connections using Oracle Call Interface. Open Gateways use ODBC for some data sources such as Informix, Sybase, and Ingres. Oracle also provides a Transparent Gateway Toolkit with examples that illustrate how a programmer can use the gateway to work w ith ODBC drivers. Sybase recently consolidated its gateway products to produce a new gateway called OpenConnect that, like Sybase's Replication Server, operates on ODBC data sources. Sybase supports multiple client-side APIs so programmers writing for Op enConnect can use ODBC or Sybase's Open Client CT-Library and DB-Library interfaces.
Another group of first-generation ODBC adopters included Microsoft Access users who did not know how to use SQL passthrough queries to boost the Jet engine's client/server performance. On the basis of experience with early applications and desktop driver s, some industry observers concluded that ODBC drivers were poor performers for either desktop or client/server applications. Assertions about ODBC performance are questionable when they fail to differentiate between application performance, object layer performance, component performance, and API performance. The analogy that I use to describe the flawed application-centric approach to evaluating ODBC performance is that someone observing my mother driving a Ferrari Testarossa is likely to conclude tha t the Ferrari has a performance problem.
Printed comments about poor ODBC performance have not been accompanied by references to published studies. Published test results present a different picture of ODBC and native API performance. Previous DBMS articles have discussed internal Microsoft tes ts (see my article, "Understanding ODBC and Multidatabase APIs," DBMS, March 1994, page 44), and independent tests by Larry Kostmayer ("VB to SQL Server Connections," DBMS, October 1994, page 94) compared SQL Server's DB-Library to the Microsoft SQL Serv er ODBC Driver. Both benchmarks debunked the myth of major performance problems using ODBC. In my March 1994 DBMS article, I discussed how my tests using C++, Visual Basic, and other client tools pointed to the choice of language and programming techniqu e, not ODBC, as the root cause of performance problems.
Despite these results and similar results posted to online services, it seemed that additional tests with several SQL servers would provide useful insights. To provide a broader spectrum of data for evaluating ODBC driver performance, I undertook, with t he assistance of Intersolv, a project to test APIs for Oracle, Sybase System 10, Informix, and Ingres. The tests compared Intersolv ODBC drivers with Oracle Call Interface (OCI), Sybase Open Client Library (CT-Library), and Informix Embedded SQL (ESQL). Tests for CA-OpenIngres were incomplete at the time of this writing.
To produce a valid comparison and eliminate factors affecting performance, each performance test used the same testbed comparing native and ODBC drivers. The benchmarks repeated each test using the same components, with the obvious exception of the SQL A PI. While using either ODBC or a native API, each test used the same SQL statement, data tables, data population, client, network, and SQL server.
The benchmark application, APIBench, is a Windows program that permits the user to specify one or more tests to run and the API to use when running tests. (For more information about the APIBench software, see "Performance Testing, ODBC, and Native SQL A PIs," Dr. Dobb's Sourcebook, January/February 1996, page 17.)
APIBench is a C application that makes direct calls to ODBC and native functions so the benchmark results won't be influenced by the behaviors of object layers and encapsulations such as the Access Engine, Borland Database Engine, or Oracle Glue. APIBenc h is strictly a tool for performance testing while using SQL so that it contains no logic to benchmark Xbase, PAL, or other native interfaces to desktop databases. The application maintains two files containing test results. For each test, the first file (bench.out) logs execution times, SQL statements, run counts, error messages, and other information. The second file (results.out) includes an array of execution times for each of 10 different tests.
APIBench permits a user to select as many as nine different tests for each run. For each run the user can execute a populate test, two update tests, five selection queries, and one of two deletion tests. The SELECT queries test aggregation, parameter sub stitution, joins, selections by individual row, and typical SQL operations against two indexed tables (employees and departments). APIBench lets users specify the number of runs, and it uses an initialization file (benchmrk.ini) to specify how many rows to populate.
Development of the software and performance testing of the APIs required a mix of hardware and software. Although benchmark development and initial tests involved multiple protocol stacks, TCP/IP or NetWare IPX/SPX were used for audited tests. The server s were 486/66s with 32MB of RAM, a 386/40 with 32MB of RAM, and a Sun Sparcstation with 16MB RAM. Server operating systems were Windows NT 3.5, OS/2 Warp 3.0, Sun OS 4.1.3, and NetWare 3.12. Client machines were 386s, 486s, and, in one instance, a 60 MHz Pentium. Development of the client application used different operating systems, but the client OS for all audited tests was Windows 3.1.
Results of the tests are available from several sources. Microsoft's ftp server has the benchmark results in a Word document named "benchmark.doc" in ftp://ftp.microsoft.com/developr/msdn/odbc. You can also surf over to Intersolv's Web page (http://www.i ntersolv.com) or download apitest.zip from the ODBC library of the WINEXT forum on CompuServe. It is important to understand that the results of the APIBench tests were valid for comparing APIs accessing the same DBMS, but not for comparing relative perf ormance of DBMSs or servers. The test configuration remained constant while testing ODBC and the native API, but different configurations were used for different DBMSs.
Open Horizon recently announced Connection Application Broker (CAB), which supports three-tier development by registering business rules and providing application services to products such as Visual Basic and PowerBuilder. CAB leverages the ODBC paradigm for remote business logic. ODBC permits a developer to use centralized business rules in the form of stored procedures that take input parameters and return parameters or result sets. Programmers can dynamically query ODBC's SQLProcedures and SQLProcedu reColumns functions for information about procedures and use SQLExecDirect to execute them. CAB uses a similar model that permits a developer to dynamically query remote business rules except that it, in addition to SQL, also supports stored rules in oth er languages. CAB uses an application interface language that is similar to the interface definition language (IDL) used to define object interfaces for CORBA and OLE. Developers using tools such as C++, Visual Basic, PowerBuilder, and SQLWindows can pro gram multi-tier applications by using a central registry, an application broker implemented as an ODBC driver, and CAB's application interface language.
Open Horizon is partnering with Forté Software and Dynasty Technologies to automatically register Forté and Dynasty's business rules with CAB. Connection three-tier products currently work with IBM's CICS/6000 and Transarc Corp.'s Encina, but Open Horizon recently announced new partnerships that will integrate Application Broker with AT&T's Top End, Novell's Tuxedo, and IMC.
NobleNet Inc. is another software company that provides tools that permit ODBC developers to partition logic and develop multi-tiered applications. NobleNet's OneDriver ODBC SDK is a vehicle for developing a single, universal driver for client machines. EZ-RPC lets Windows and Macintosh developers easily generate remote procedures calls (RPCs). Developers using C/C++, PowerBuilder, Delphi, SQLWindows, and other developer products use NobleNet's IDL, IDL compiler, and RPC library to distribute the logic of their applications. EZ-RPC supports asynchronous, callback, broadcast, and batch RPCs for a variety of operating systems, including all flavors of Windows, over 25 Unix platforms, NetWare, OpenVMS, OS/2, and Macintosh.
To simplify ODBC administration and reduce the number of components at a client workstation, a variety of companies have augmented their product lines with software that moves much of the ODBC infrastructure to an ODBC server. By using this server archit ecture (see Figure 1), the client infrastructure is reduced to a single driver and network transport.
In 1993, OpenLink Software introduced a product that replaced proprietary communication layers with a network layer for serving ODBC clients. The architecture uses a session rule book and a generic ODBC driver for Windows, Unix, Macintosh, and OS/2 clien ts. OpenLink Workgroup Edition and Enterprise Edition connect to servers running Windows NT, OS/2, Unix, and VMS. OpenLink drivers provide connectivity to Progress, Unify, Ingres, Oracle, Informix, Sybase, and Microsoft SQL Server databases.
Visigenic will ship ODBC-Net, its implementation of server-side ODBC, in early 1996. Visigenic reports that the ODBC-Net client driver reduces the typical fat client to a 300K footprint. The ODBC-Net server uses a multi-threaded architecture that is capa ble of listening for and servicing requests from concurrent clients while using a thread-per-client request. The ODBC-Net server manager provides monitoring capabilities by maintaining performance information and statistics for each client connection.
Intersolv previewed a server, code-named Purveyor, but dropped the project after its October 1995 acquisition of the TechGnosis SequeLink technology to integrate with its Virtual Data Warehouse strategy. Intersolv's DataDirect SequeLink uses a server-sid e encapsulation of ODBC, with servers for Windows NT, HP-UX, Open VMS, OSF/1, MVS, OS/2, SCO, Solaris, and other operating systems. The SequeLink client software supports those environments plus the Macintosh, UnixWare, and all Windows operating systems. The client architecture includes a SequeLink API and an ODBC layer that uses a single, generic driver.
August Software Corp. has an ODBC server architecture that currently supports Macintosh clients. The client software, OverDriver, is a Macintosh gateway driver that operates over AppleTalk and TCP/IP transports to communicate with Windows NT servers. Aug ust's ODBC Router is a Windows NT service that permits Macintosh and Power Mac clients to use server-side ODBC drivers running under Windows NT. Currently in development is a cross-platform ODBC server solution that adds support for Windows clients, LINU X 1.2 clients, and asynchronous processing.
Ensodex Inc.'s Hot Sockets is another product in the ODBC server category. It supports LAN and Internet connectivity for ODBC applications by using a TCP/IP transport layer (Ensodex ODBC client software for Unix and Windows) and an ODBC server for machin es running Windows NT, Solaris, HP-UX, or AIX. Hot Sockets includes the TCP/IP transport layer, the Hot Sockets pass-through driver, and the Hot Sockets ODBC server.
Simba Technologies produces Simba Express, an architecture that includes SimbaManager, an ODBC server-side component, and SimbaClient, a Windows-based ODBC driver. SimbaExpress's communications layer can run on top of TCP/IP, SLIP, and IPX/SPX protocols. SimbaClient runs on Windows 3.x and Windows 95, while SimbaManager runs on AIX and Windows NT. SimbaManager for HP-UX, SCO, and Solaris should currently be available. SimbaManager offers drivers for Oracle, Sybase, Btrieve, C-ISAM, and Cobol, with other drivers in development.
VMark Software recently announced a new middleware product, HyperStar, that uses object messaging for all client and server requests. Traditional middleware works with client/server protocols that exchange data packets between client and server, whereas this object messaging middleware supports the passing or exchange of objects. Sybase is reportedly working on revisions to its tabular data stream (TDS) protocol to support passing objects. VMark's HyperStar provides Windows, Macintosh, and Unix clients with a single ODBC driver that accesses a variety of databases, including uniVerse, Micro Focus Cobol ISAM files, Informix, Sybase, Oracle, DB2, and Ingres. VMark HyperStar servers provide access to over 50 different database formats.
Several vendors provide tools to access ODBC data sources from Web browsers. I'll describe Allaire's Cold Fusion and Nomad's WebDBC. Aspect Software Engineering's dbWeb and Questar Microsystems' WebQuest also provide a Web-to-ODBC interface. A growing nu mber of other products integrate ODBC and the Web in various ways.
Cold Fusion uses DBML (database markup language) tags, which are similar in concept to HTML and SGML (standard generalized markup language) tags. Using DBML tags requires no coding in a language such as Perl or C++. You use HTML to specify all data forma tting and presentation, and the Cold Fusion CGI script (DBML.EXE) handles all of the database interaction. Cold Fusion supports parameter queries, stored procedures, and forms data entry with data validation rules. It is capable of formatting output usin g currency, date, time, and numeric styles. ODBC drivers used with Cold Fusion must be 32-bit drivers that support ODBC-core SQL and the level 1 ODBC API. HTML doesn't support required fields but Cold Fusion defines a _required suffix for a field's name attribute so that the CGI script rejects incomplete forms.
Nomad's WebDBC provides Web database access for Windows NT, Windows 95, Macintosh, and Unix users. WebDBC is a CGI-compliant application that takes Web requests, accesses databases via ODBC, and uses HTML 3.0 extensions to format results. WebDBC also sup ports input forms validation, execution of stored procedures, and parameter queries. It implements security on a per-database, per-table, or per-operation basis, and it performs queries by converting URLs into SQL statements. It returns results using HTX files that are HTML files with WebDBC markers.
Working Set's DataRamp is an ODBC server architecture that provides secure access to servers on the Internet. The DataRamp browser works as a helper for Netscape, Mosaic, and other Web browsers. You can use browser queries in Web documents and the browse r will use the DataRamp client to execute the query and transfer the results sets. The DataRamp browser lets you embed a query in HTML. To create a query that you store in a DataRamp query file (DRQ), you use the URL of the DataRamp server, the ODBC data source name, and the query's SQL statement. To reference the query in HTML, you use a string such as :
<A href="nwemp.drq">Employees</A>
You can configure the HTTP server to recognize DRQ files as MIME type "application/dataramp-query." Specifying a unique MIME type makes it easier for a browser to recognize the file and invoke the correct "helper" application, in this case DataRamp. Data Ramp includes client-side public key encryption of ODBC data streams, asynchronous statement execution, multi-threaded server drivers, and a monitor that provides connection, network, and client statistics. DataRamp clients can communicate using Winsock and TCP/IP for Windows operating systems. The DataRamp server installs and runs as a Windows NT service.
Additional Web data access products are being developed by organizations with multidatabase and ODBC expertise. HAHT Software, a new venture by the developers that founded Q+E Software (which Intersolv acquired), has been developing WebApp, an end-to-end application development environment that supports ODBC data access. HAHTSITE is an integrated toolset that includes WYSIWYG authoring, Web-enabled graphics, visual programming with a VB-compatible language, wizards, widgets, team programming features, d ebuggers, and multiplatform development and deployment capabilities.
In recent months, Microsoft has been actively campaigning to raise its visibility among Web developers. Part of the campaign includes developing a Web server that will compete with products such as Oracle's Web Server. At the present time it appears that access to ODBC databases is one of the distinctions between the Oracle and Microsoft Web servers. Microsoft is bundling an ODBC-compliant database connector with Microsoft Internet Information Server 1.0, while Oracle offers connections to other databas es as an option.
Oracle WebServer connects to databases using Web Agent (a procedural gateway), CGI 1.1, and SQL*Net. Web Agent can call Oracle PL/SQL stored procedures, and it supports HTML-2 and some HTML-3 tags. Web Server includes a limited-edition Oracle Workgroup S erver, but connecting to non-Oracle databases requires an upgrade to the Web Server Option of the Oracle Enterprise Server. Oracle PowerBrowser is a new browser, HTML authoring environment, local Blaze database, and personal HTTP server. Using PowerBrows er with other databases requires a similar upgrade to enterprise software such as Oracle Enterprise Server, and possibly a transparent gateway and ODBC adapter. (Oracle has positioned connectivity to non-Oracle databases as "enterprise software," over an d above their local database or workgroup server, and therefore it costs more.)
Syware has also leveraged its knowledge of ODBC to create DataSync, one of the few tools that can perform data synchronization and replication across heterogeneous data sources. DataSync provides the ability to replicate on a transaction-basis using rule s, expressions, icons, and even a programmer's API. You specify rules and expressions that define subsets and then create icons so that you can do point-and-click replication. When you define subsets, you include tables, keys, restrictions, insert option s, and propagation rules. DataSync supports a variety of insert operators including Don't Change, Replace, Query, Query Replace, Maximum, Minimum, Additive Merge, and Multiplicative Merge.
| ANSI | American National Standards Institute |
| API | Application Programming Interface |
| CLI | Call Level Interface |
| CGI | Common Gateway Interface |
| CORBA | Common Object Request Broker Architecture |
| DBML | Database Markup Language |
| DCE | Distributed Computing Environment |
| DLL | Dynamic Link Library |
| HTML | Hypertext Markup Language |
| HTTP | Hypertext Transfer Protocol |
| ISAM | Index Sequential Access Method |
| ISO | International Standards Organization |
| MIME | Multipurpose Internet Mail Extensions |
| OSF | Open Systems Foundation |
| RPC | Remote Procedure Call |
| SAG | SQL Access Group |
| SGML | Standard Generalized Markup Language |
| SNMP | Simple Network Management Protocol |
| URL | Uniform Resource Locator |
| WWW | World Wide Web |
