Thursday, February 28, 2008

ODBC Secrets

What is ODBC?

ODBC stands for Open Database Connect and is a Microsoft specification. ODBC provides a standard way of defining data sources and their data access methods. ODBC is designed around SQL and relational databases, but there is nothing preventing translation of the incoming SQL to another language.

The ODBC specification defines low level API calls that any application can make use of for database queries. By writing calls to the API, a reporting writer or other tool can portably access heterogeneous data sources with one set of source code.

Architectures

There are two basic architectures employed by the driver makers: single vs. multiple tier. Intersolv’s DataDirect ODBC, OpenLink Lite, and the Progress ODCB Driver are single tier drivers, while the rest are all multiple tier.
Single Tier
Single tier architectures use the driver itself to process the SQL query, implying PC side resolution. The driver connects to the database, sends SQL to the database, does any additional record selection or joining, and then passes the result to the application. Driver connections for Progress requires a client product such as Client Networking, 4GL Client, or ProVision to connect to the database, or use its own network protocol for a remote database. The Progress client is responsible for getting the records to the driver where it does the rest of the work. Starting in Version 8, executables separate from the full Client Networking product are shipped for establishing this connection. This smaller client is referred to as the Open Interface Driver and is combined with the Open Interface Broker for multi-user situations.
Multiple Tier
Queries are offloaded by the driver to another application in multiple tier architectures. This secondary layer is generally a networking program talks to a server side component. The server receives SQL requests from multiple network connections, resolves the request through interaction with the database, and returns the data to the PC’s secondary layer. The secondary program must still pass the final results to the driver. While it is not required, almost all multiple tier implementations make direction connections from the server to the database. The server side execution generally provides better performance since only selected records get passed to the client PC. Under Progress Client Networking, records are sometimes passed to the PC for selection, increasing network traffic. The specific circumstances where this happens are version specific, but joins for example, are determined by the client PC under all current versions of Progress.
JDBC and ODBC to JDBC Bridges

If you’re programming in Java, there is a standard similar to ODBC for Java. JDBC drivers are also available for Progress. They are generally of two different types, true JDBC drivers and ODBC to JDBC Bridges.

True JDBC involves your Java program making a connection to a database over a URL. ODBC bridges work the same way from the program side, but connect locally through the PC’s ODBC driver. The database can still be remote, just the ODBC must be set up locally.

No comments: