Easysoft Blog

Fri, 11 Nov 2016

ODBC 4.0

ODBC is a data access API that allows an application to be written without having to specify or know the database system that it will use when run, in other words, ODBC insulates the application from the specifics of accessing a database.

The release history of ODBC is:

1.01993The first version of ODBC. Only a few ODBC 1.0 applications and drivers still exist (on Windows) and none we know of on Linux.
2.01994Small reorganisation of the API (e.g. new SQLBindParameter replacing SQLSetParam) core, level 1 and 2 conformance changes, new data types.

There are still a number of ODBC 2.0 applications and drivers around. On Linux, most ODBC drivers are ODBC 3 and the few that are still ODBC 2.0 are generally moving to 3.

There was also an ODBC 2.5.

3.01995ODBC 3.0 introduced a large number of new APIs and ODBC descriptor handles. Most ODBC drivers on Linux are now ODBC 3.x and many applications are also 3.x.
3.5x1997 Introduction of Unicode.
3.8x2009Driver aware connection pooling, which allows an ODBC driver to better estimate the cost of reusing a connection from the pool based on a user's connection settings.

Asynchronous connection operation, which enable applications to populate multiple connections in the pool at startup time so that subsequent connection requests can be more efficiently served.

Driver-specific C data types, which are useful for supporting new DBMS data types that existing C types do not correctly represent. Before version 3.8, ODBC drivers had to use a generic type such as SQL_C_BINARY to work with DBMS-specific types, which the application would then need to reconstruct.

Streamed output parameters, which enable an application to call SQLGetData with a small buffer multiple times to retrieve a large parameter value, reducing the application's memory footprint. (We provide a SQL Server specific example for streamed output parameters in our C samples section.)

Microsoft recently announced it was working on ODBC 4.0, the first significant update to the ODBC specification since 1997. ODBC 4.0 aims to address the requirements of modern data stores. For example, data with a hierarchical structure or a data source that uses web authentication to control access to it.

The major new features that ODBC 4.0 includes are:

Private driversODBC drivers that are only visible to a particular application. Private drivers are not exposed in the system wide ODBC data source administrator application.
Language extensions through additional SQL Escape ClausesODBC escape clauses provide a DBMS-neutral way for applications to uses constructs that are not part of SQL-92. For example, outer joins, function execution, datetime literals and so on. ODBC 4.o provides new escape sequences to cover additional SQL constructs such as limit clauses as well as other extensions to SQL such as selecting inserted, updated or deleted values.
Semi-structured data: Tables whose schema may not be defined or may change on a row-by-row basisA schema can be inferred from a schema-less source such as a JSON, XML, or CSV document, to expose a standard relational view of the data.
Hierarchical data: Data with nested structure (structured fields, lists)Applications requesting ODBC 4.x may see a more faithful representation of hierarchically structured data containing row, collection, and untyped data values.
Web authenticationThe ODBC API SQLDriverConnect has been changed to be compatible with the requirements of a web authentication mechanism such as OAuth. For example, it will be possible to use SQLDriverConnect to initially supply an access token with a limited lifetime and then extend the duration of a session with a refresh token.