The Open Database Connectivity interface is a widely accepted application programming
interface (API) for database access. It is based on the Call-Level Interface (CLI)
specifications from X/Open and ISO/IEC for database APIs and uses Structured Query
Language (SQL) as its database access language.
ODBC is designed for maximum interoperability, the ability of a single application to
access different database management systems with the same source code. Database
applications call functions in the ODBC interface, which are implemented in
database-specific modules called drivers. The use of drivers isolates applications from
database-specific behavior. Because drivers are loaded at run time, a user only has to add
a new driver to access a new database management system; it is not necessary to recompile
the application.
It is important to understand that ODBC is designed to expose database capabilities, not supplement them. Thus, application writers should not expect that using ODBC will suddenly transform a simple database into a fully featured relational database engine. Nor are driver writers expected to implement functionality not found in the underlying database. An exception to this is that developers who write drivers that directly access file data (such as data in an Xbase file) are required to write a database engine that supports at least minimal SQL functionality.
Applications that use ODBC are responsible for any cross-database functionality. ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.
Although Trinc-Prolog uses ODBC, the ODBC functions are completely hidden from the prolog developer by transforming the function-oriented interface of ODBC to an object-oriented interface that contains three classes. It is however still important to understand the concepts of ODBC and how to administer databases with ODBC. Understanding ODBC also helps when developing/debugging an application that uses the database classes because it is possible to have Trinc-Prolog display the ODBC error codes that are generated.
Allowing users to access data on an ad-hoc basis requires giving them a language in which to express their requests. A single request to a database is defined as a query; such a language is called a query language. Many query languages were developed for this purpose but one of these became the most popular: Structured Query Language, invented at IBM in the 1970s. It is more commonly known by its acronym, SQL, and is pronounced both as “ess-cue-ell” and “sequel”. SQL became an ANSI standard in 1986 and an ISO standard in 1987; it is used in many database management systems.
SQL-92 can also be used to create and manipulate databases so it is important to know how to read and write SQL statements. ODBC defines a standard SQL grammar. This grammar is based on the X/Open SQL CAE specification. Differences between the two grammars are minor. There are also some extensions to the grammar to expose commonly available language features not covered by the X/Open grammar.
Applications can submit statements using ODBC or database specific grammar. If a statement uses ODBC grammar that is different from database specific grammar, the driver attempts to convert it before sending it to the database. However, such conversions are rare because most database management systems already use standard SQL grammar. By using database specific statements there is the danger of the application becoming database specific and loosing its interoperability.