ODBC is designed to work with numerous database management systems. ODBC provides a consistent application programming interface (API) that works with different databases through the services of a database-specific driver.
A consistent API means that the functions a program calls to make a connection, execute a command, and retrieve results are identical whether the program is talking to Oracle or SQL Server.
ODBC also defines a standardized call-level interface and uses standard escape sequences to specify SQL functions that perform common tasks but have different syntax in different databases. The ODBC drivers can automatically convert this ODBC syntax to either Oracle native or Microsoft SQL Server native SQL syntax without requiring the revision of any program code. In some situations, the best approach is to write one program and allow ODBC to perform the conversion process at run time.
ODBC is not a magical solution for achieving complete database independence, full functionality, and high performance from all databases. Different databases and third-party vendors offer varying levels of ODBC support. Some drivers just implement core API functions mapped on top of other interface libraries. Other drivers, such as the Microsoft SQL Server driver, offer full Level 2 support in a native, high-performance driver.
If a program uses only the core ODBC API, it will likely forego features and performance capabilities with some databases. Furthermore, not all native SQL extensions can be represented in ODBC escape sequences (such as Oracle DECODE and SQL Server CASE expressions).
Additionally, it is common practice to write SQL statements to take advantage of the database’s optimizer. The techniques and methods that enhance performance within Oracle are not necessarily optimal within Microsoft SQL Server 7.0. The ODBC interface can not translate techniques from one RDBMS to another.
ODBC does not prevent an application from using database-specific features and tuning for performance, but the application needs some database-specific sections of code. ODBC makes it easy to keep the program structure and the majority of the program code consistent across multiple databases.