Chapter 12. Using PostgreSQL from an ODBC Client Application

ODBC ( open database connectivity) is an API (application programming interface) that provides an application with a consistent database interface. To understand the architecture of ODBC, it helps to understand the problem that ODBC was designed to solve.

Let's say that you are an independent software vendor and you have just finished developing an accounting package that you intend to sell to as many users as possible. Your accounting application was designed to store its data in Sybase. Your original application uses the Sybase OpenClient interface to interact with the database. One day, a potential customer tells you that he is very interested in buying your application, but his corporate standard mandates that all data must be stored in PostgreSQL. If you want to sell your product to this customer, you have two options.

First, you could add a second interface to your application and somehow arrange things so that your application would use whichever database is available. That would leave you with a Sybase-specific interface and a PostgreSQL-specific interface. The downside to this approach is that you now have twice as much code to maintain (not to mention having to learn both interfaces). If you encounter another customer who requires Oracle support, you'll have to learn and maintain three interfaces.

Your other choice is to use a database-independent interface from the start. That's ODBC. ODBC gives your application a single API that can interact with PostgreSQL, Oracle, Sybase, SQL Server, MySQL, and many other databases.

The ODBC interface is based on the X/Open CLI (call-level interface) standard. The X/Open CLI standard is compatible with the ISO/IEC SQL/CLI standard. This means that an application that is written to use the ODBC standard API will also be compatible with the X/Open CLI standard and the ISO/IEC SQL/CLI standard. There are two important consequences to all this: An ODBC application can interact with many databases, and the standard is not likely to change at the whim of a single database vendor.

ODBC won't solve all your database portability problems. It provides an industry- standard API for establishing database connections, sending commands to a server, and retrieving the results. ODBC does not provide a standard language. If your application sends commands that are specific to PostgreSQL, that application won't automatically work with an Oracle backend. For example, in PostgreSQL, END WORK is a synonym for the more common COMMIT . If you are trying to build a portable application, you should use COMMIT rather than END WORK . In practice, most applications can use a common subset of SQL to achieve database portability. ODBC provides API portability, and SQL provides language portability. With this combination, your application can be very portable.

In this chapter, I'll focus on using ODBC from an application written in C or C++. ODBC would be a very useful API if it only provided a consistent database interface to C programs. However, ODBC offers another important feature ”you can use ODBC to access databases from languages such as Visual Basic, Microsoft Access, FoxPro, Delphi, and others. You can also use ODBC to connect a web server to an ODBC-compliant database. I'll talk more about the PostgreSQL/Web server connection in Chapter 15, "The PHP API."