If you've visited the PHP mailing lists or done any serious database development you have probably come across ODBC (Open DataBase Connectivity), and may know that it's a way of accessing databases. But what is it beyond that? Why would you use it? What can it possibly be good for? Do we really need to look at YAFA (Yet Another Acronym) and explore the inevitable complexity hidden behind a few innocuous letters? Well…yes. Instead of starting off by redefining the acronym, let's consider what ODBC does for us.
ODBC is an operating system and database independent communication API that allows a client application to communicate via standards-based function calls to a back-end database without relying on database specific (read proprietary) communication protocols.
Why is this important? Why not just develop against Oracle, or SQL Server, or Informix, and be done with it? There are several reasons. Database and platform "agnosticism" in an application enables portability and freedom. The importance of this freedom rapidly becomes apparent when considering limits of a single database and platform, be it regard to scalability, speed, licensing, or any other variables between different production systems. ODBC also allows development of applications on desktop development environments with ultimate deployment on any platform.
Also, not every database is good for every task. MySQL in its current form is great as a read-only database providing content for web sites, but you might prefer to choose Oracle for a data warehouse or Virtuoso as an integration database.
This chapter attempts to closely pack an overview of ODBC in with installation instructions, tips, and tricks that will save you time (or get you out of trouble), and give you examples of how to use ODBC in real world situations. This chapter will not cover basic relational database concepts; for an overview of those, see Chapter 17.
In this chapter, we will cover the following topics:
ODBC history and architecture basics
Installation on Windows and *nix systems
The PHP ODBC API
ODBC with MS SQL Server
ODBC with MS Access
Alternate approaches to database abstraction
An example application