What ODBC Provides

According to the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide, ODBC provides the following:

• A library of functions that enable you to connect to a database, execute SQL code, and retrieve results
• A standard SQL syntax
• A standard set of error codes
• A standard way to connect and log on to a database
• A standard representation of data types

The first, second, and fourth provisions in the list are fairly straightforward, but the third and fifth are not. Although ODBC provides a standard way of getting to error codes, I would argue that the actual error codes are not standard. I have seen an error message that simply told me the database driver's error database could not be found and then only provided me with an error code. (See Figure 8-1.) A driver from another vendor routinely returns an error message that says, informatively, "<<error>> Failed to connect to server".

Figure 8-1 Uninformative error dialog generated by an ODBC driver.

Although representing data in a standard way has many benefits, it has a few drawbacks as well. The problem is that the goal is too ambitious. Before getting to examples, let's take a look at a warning in the data types appendix from the current ODBC documentation included with MSDN July 1999:

Important

---

The tables throughout this appendix are only a guideline and show commonly used names , ranges, and limits of SQL data types. A given data source may support only some of the listed data types and the characteristics of the supported data types may differ from those listed.

From this previous information, it is unclear what data types are available and what the ranges allowed might be. How can this be problematic ? Consider a program that copies date and time information from a Microsoft Access database to a Microsoft SQL Server database. The Microsoft SQL Server SMALLDATETIME field type is not identical to the Microsoft Access DATETIME field type; the conversion from the Access type to the SQL Server type will drop seconds and milliseconds . If the date and time field is being used to generate a unique key, this data loss will likely cause problems.

Despite the previous warnings, ODBC generally works. I will provide sample code that will run on either SQL Server or Access without any change to the object code. The SQL code passed to an ODBC driver can be generated at compile time or at run time, allowing the program to work with dynamically changing data sources. ODBC will often be the only feasible option. If you want to create a data-driven Web page in Internet Information Server and you do not want to resort to a custom data interface ISAPI program, ODBC is essential.