Team LiB
Previous Section Next Section

APIs Available for MySQL

To facilitate application development, MySQL provides a client library written in the C programming language that allows you to access MySQL databases from within any C program. The client library implements an application programming interface (API) that defines how client programs establish connections to and communicate with the server.

However, you are not limited to using C to write MySQL programs. You have several choices for writing applications that talk to the MySQL server. Many other language processors are either written in C themselves or have the capability of using C libraries. The MySQL client library provides the means whereby MySQL bindings for these languages can be built on top of the C API. Examples of these are the client APIs for Perl, PHP, Python, Ruby, C++, Tcl, and others. There are also interfaces for Java (although these implement the client-server protocol directly rather than using the C library to handle communication). Check the development downloads area at the MySQL AB Web site for an up-to-date list, because new language APIs become available from time to time:

Each language binding defines its own interface that specifies the rules for accessing MySQL. There is insufficient space here to discuss each of the APIs available for MySQL. Instead, we'll concentrate on three of the most popular APIs:

  • The C client library API. This is the primary programming interface to MySQL. It's used, for example, to implement the standard clients in the MySQL distribution, such as mysql, mysqladmin, and mysqldump.

  • The DBI (Database Interface) API for Perl. DBI is implemented as a Perl module that interfaces with other modules at the DBD (Database Driver) level, each of which provides access to a specific type of database engine. The particular DBD module on which we'll concentrate is the one that provides MySQL support. The most common uses of DBI with MySQL are for writing standalone clients to be invoked from the command line and for scripts to be invoked by a Web server to provide Web access to MySQL.

  • The PHP API. PHP is a server-side scripting language that provides a convenient way of embedding programs in Web pages. Such a page is processed by PHP on the server host before being sent to the client, which allows the script to generate dynamic content, such as including the result of a MySQL query into the page. "PHP" originally meant Personal Home Page, but PHP has grown far beyond its original humble beginnings. Like DBI, PHP includes support for accessing several database engines in addition to MySQL.

Each of these three APIs is described in detail in its own chapter. The present chapter provides a comparative overview of the APIs to describe their general characteristics and to give you an idea why you might choose one over another for particular applications.

There's no reason to consider yourself locked into a single API, of course. Get to know each API and arm yourself with the knowledge that enables you to choose between them wisely. If you have a large project with several components, you might use multiple APIs and write some parts in one language and other parts in another language, depending on which one is most appropriate for each piece of the job. You may also find it instructive to implement an application in several ways if you have time. This gives you direct experience with different APIs as they apply to your own applications.

If you need to get the software necessary for using any of the APIs, see Appendix A, "Obtaining and Installing Software."

Should you be interested in additional MySQL programming information beyond what is presented in the following chapters, other books are available. The two with which I am most familiar (because I wrote them) are MySQL and Perl for the Web (New Riders, 2001) and MySQL Cookbook (O'Reilly, 2002). The first provides extensive coverage of the use of MySQL and DBI in Web environments. The second discusses Perl and PHP, and also shows how to write MySQL programs using Python's DB-API interface and the Java JDBC interface. If you're interested specifically in Java, see MySQL and Java Developer's Guide (Matthews, Cole, and Gradecki; Wiley, 2003). One of the authors (Mark Matthews) is the creator of MySQL Connector/J, the official Java interface for MySQL.


The C API is used within the context of compiled C programs. It's a client library that provides the lowest level interface available for talking to the MySQL server, giving you the capabilities you need for establishing a connection to and conversing with the server.

The C clients provided in the MySQL distribution are based on this API. The C client library also serves as the basis for most of the MySQL bindings for other languages. For example, the MySQL-specific driver for the Perl DBI module and the PHP processor are both made MySQL-aware by linking in the code for the MySQL C client library.

The Perl DBI API

The DBI API is used within the context of applications written for the Perl scripting language. This API is the most highly architected of the three APIs we're considering, because it tries to work with as many databases as possible, while at the same time hiding as many database-specific details as possible from the script writer. DBI does this using Perl modules that work together in a two-level architecture (see Figure 5.1):

  • The DBI (database interface) level provides the general purpose interface for client scripts. This level provides an abstraction that does not refer to specific database engines.

  • The DBD (database driver) level provides support for various database engines by means of drivers that are engine specific. The DBD-level module that implements DBI support for MySQL is named DBD::mysql.

Figure 5.1. DBI architecture.

The DBI architecture allows you to write applications in relatively generic fashion. When you write a DBI script, you use a standard set of database-access calls. The DBI layer invokes the proper driver at the DBD level to handle your requests, and the driver handles the specific issues involved in communicating with the particular database server you want to use. The DBD level passes data returned from the server back up to the DBI layer, which presents the data to your application. The form of the data is consistent no matter which database the data originated from.

From the application writer's point of view, the result is an interface that hides differences between database engines, yet works with a wide variety of enginesas many as there are drivers for. DBI provides a consistent client interface that increases portability because you can access each database engine in a uniform fashion.

The one aspect of script writing that is necessarily engine-specific occurs when you connect to a database server, because you must indicate which driver to use to establish the connection. For example, to use a MySQL database, you connect like this:

$dbh = DBI->connect ("DBI:mysql:...");

To use PostgreSQL or Oracle instead, connect like this:

$dbh = DBI->connect ("DBI:Pg:...");
$dbh = DBI->connect ("DBI:Oracle:...");

After you've made the connection, you don't need to make any specific reference to the driver. DBI and the driver itself work out the database-specific details.

That's the theory, anyway. However, you should be aware of two factors that work against DBI script portability:

  • SQL implementations differ between RDBMS engines, and it's perfectly possible to write SQL statements for one engine that another will not understand. If your SQL is reasonably generic, your scripts will be correspondingly portable between engines. But if your SQL is engine dependent, your scripts will be, too. For example, if you use the MySQL-specific SHOW TABLES statement, your script won't work with other database servers.

  • DBD modules often provide engine-specific types of information to allow script writers to use particular features of particular database systems. For example, the MySQL DBD provides a way to access properties of the columns in a query result such as the maximum length of values in each column, whether columns are numeric, and so forth. Other database servers don't necessarily make analogous types of information available. DBD-specific features are antithetical to portability; by using them, you make it difficult to use a script written for MySQL with other database systems.

Despite the potential of these two factors for making your scripts database specific, the DBI mechanism for providing database access in an abstract fashion is a reasonable means of achieving portability. It's up to you to decide how much you want to take advantage of non-portable features. As you will discover in Chapter 7, "Writing MySQL Programs Using Perl DBI," I make little effort to avoid MySQL-specific constructs provided by the MySQL DBD. That's because you should know what those constructs are so that you can decide for yourself whether to use them. For further information, see Appendix H, "Perl DBI API Reference," which lists all the MySQL-specific constructs.

The Meaning of DBI and DBD

Although the DBI level is database independent and the DBD level is database dependent, that isn't what "DBI" and "DBD" stand for. They mean "database interface" and "database driver."


Like Perl, PHP is a scripting language. Unlike Perl, PHP is designed less as a general-purpose language than as a language for writing Web applications. The PHP API is used primarily as a means of embedding executable scripts into Web pages. This makes it easy for Web developers to write pages with dynamically generated content. When a client browser sends a request for a PHP page to a Web server, PHP executes any script it finds in the page and replaces it with the script's output. The result is sent to the browser. This allows the page that actually appears in the browser to change according to the circumstances under which the page is requested. For example, when the following short PHP script is embedded in a Web page, it displays the IP address of the client host that requested the page:

<?php echo $_SERVER["REMOTE_ADDR"]; ?>

As a less trivial and more interesting application, you can use a script to provide up-to-the-minute information to visitors based on the contents of your database. The following example shows a simple script such as might be used at the Historical League Web site. The script issues a query to determine the current League membership count and reports it to the person visiting the site (if an error occurs, the script reports no count):

<title>U.S. Historical League</title>
<body bgcolor="white">
<p>Welcome to the U.S. Historical League Web Site.</p>
# USHL home page

require_once "DB.php";

$conn =& DB::connect ("mysql://sampadm:secret@localhost/sampdb");
if (DB::isError ($conn))
    exit ();
$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    exit ();
if ($row =& $result->fetchRow ())
    print ("<p>The League currently has " . $row[0] . " members.</p>");
$result->free ();
$conn->disconnect ();

PHP scripts typically look like HTML pages with executable code embedded inside <?php and ?> tags. A page can contain any number of code fragments. This provides an extremely flexible approach to script development. For example, you can write a PHP script as a normal HTML page initially to set up the general page framework, and then add code later to generate the dynamic parts of the page.

PHP makes no effort to unify the interface to different database engines the way DBI does. Instead, the interface to each engine looks much like the interface for the corresponding C library that implements the low-level API for that engine. For example, the names of the PHP functions that you use to access MySQL from within PHP scripts are very similar to the names of the functions in the MySQL C client library.

A more DBI-like approach is available for PHP by using PEAR, the PHP Extension and Add-on Repository. PEAR is an adjunct to PHP. It includes a DB module that provides a more abstract interface to database engines using a two-level architecture similar to that used by DBI. (For details, visit The preceding PHP script and the scripts in Chapter 8, "Writing MySQL Programs Using PHP," employ the DB module for database access rather than the MySQL-specific calls provided in PHP itself.

    Team LiB
    Previous Section Next Section