Taking advantage of ODBC translation capabilities

Question

Towards the bottom of the SQLNativeSql() function documentation it seems to be indicated that ODBC drivers perform translation.

It says:

The following are examples of what SQLNativeSql might return for the following input SQL string containing the scalar function CONVERT. Assume that the column empid is of type INTEGER in the data source:

SELECT { fn CONVERT (empid, SQL_SMALLINT) } FROM employee

A driver for Microsoft SQL Server might return the following translated SQL string:

SELECT convert (smallint, empid) FROM employee

A driver for ORACLE Server might return the following translated SQL string:

SELECT to_number (empid) FROM employee

A driver for Ingres might return the following translated SQL string:

SELECT int2 (empid) FROM employee

Is this really true? Can ODBC really translate SQL queries so that ideally, your application could run on any database system by sending queries through ODBC?

In practice does this actually work?

Where can you find the "ODBC SQL Syntax" listing?

Answer 1

See this link

Answer 2

The translation is dependent on the driver itself. In many cases, it will return the exact SQL statement that you give it. So, no ODBC does not provide a magic bullet. You still have to worry about the database you are running queries against. If using multiple database engines, the application has to tailor queries to each one (or use generic SQL that runs on all of them).