APIs Available for MySQL

To facilitate application development, MySQL provides a client library written in the C programming language that enables 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 and carry out communications 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, so 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 (though these implement the client/server protocol directly rather than using the C library to handle communication.) Check the development portal at MySQL Web site for an up-to-date list because new language APIs become available from time to time:

 http://www.mysql.com/portal/development/html/ 

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, so we'll concentrate on three of the most popular:

• The C client library API. This is the primary programming interface to MySQL. For example, it's used 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, of course.) The most common uses of DBI with MySQL are for writing standalone clients to be invoked from the command line and for scripts intended 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 in the page. "PHP" originally meant Personal Home Page, but PHP has grown far beyond its original humble beginnings. The PHP Web site now uses the name to stand for "PHP: Hypertext Preprocessor," which is self-referential in the same manner as GNU ("GNU's Not UNIX"). 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. This 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 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.

Predecessors of DBI and PHP

The Perl predecessor to DBI is the Mysqlperl module, which is no longer supported and should not be used for new MySQL development. For one thing, Mysqlperl is MySQL-dependent, whereas DBI is not. If you write Perl applications for MySQL and then decide you want to use them with another database engine, it's easier to port DBI scripts than Mysqlperl scripts because they are less dependent on a particular database engine. (If you do happen to obtain a Perl script for accessing MySQL that is written for Mysqlperl, you can still use DBI because DBI can be built to include Mysqlperl emulation support.)

The predecessor to PHP 3 and PHP 4 is PHP/FI 2.0 (FI stands for "form interpreter"). Like Mysqlperl, PHP/FI is obsolete and I won't discuss it further. At this point, PHP 3 is also declining in use in favor of PHP 4, which offers much-improved features and performance.

The C API

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 the MySQL bindings for other languages, with the exception of the Java APIs. 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 Origin of the MySQL C API

If you have experience writing programs for the mSQL RDBMS, you'll notice that the MySQL C API is similar to the corresponding C API for mSQL. When the MySQL developers began implementing their SQL engine, a number of useful free utilities were available for mSQL. To make it possible to port those mSQL utilities to MySQL with minimum difficulty, the MySQL API was designed deliberately to be similar to the mSQL API. (MySQL even comes with a msql2mysql script that does simple textual substitution of mSQL API function names to the corresponding MySQL names. This operation is relatively trivial, but it actually takes care of much of the work involved in converting a mSQL program for use with MySQL.)

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 by 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. This module formerly went by the name Msql-Mysql-modules because it was originally written for mSQL and then extended for MySQL later. The current name of DBD::mysql reflects the fact that MySQL has pretty well eclipsed mSQL in popularity.

Figure 5.1. DBI architecture.

The DBI architecture enables 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 from which database the data originated.

The result is an interface that, from the application writer's point of view, hides differences between database engines but works with a wide variety of engines as many for which there are drivers. DBI provides a consistent client interface that increases portability by allowing you to access each database 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 as follows:

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

To use PostgreSQL or mSQL instead, connect as follows:

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

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 databases.

• DBD modules often provide engine-specific types of information to allow script writers to use particular features of particular database systems. As an example of this, 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 or not columns are numeric, and so forth. These properties don't necessarily have any analog in other databases. 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, "The Perl DBI API," I make little effort to avoid MySQL-specific constructs provided by the MySQL DBD, and all of them are listed in Appendix G, "Perl DBI API Reference." That's because you should know what those constructs are so that you can decide for yourself whether or not to use them.

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."

The PHP API

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 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 that 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 simply doesn't report any count):

 <html>  <head> <title>U.S. Historical League</title> </head> <body bgcolor="white"> <p>Welcome to the U.S. Historical League Web Site.</p> <?php # USHL home page $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret")     or exit (); mysql_select_db ("sampdb")     or exit (); $result_id = mysql_query ("SELECT COUNT(*) FROM member")     or exit (); if ($row = mysql_fetch_row ($result_id))     print ("<p>The League currently has " . $row[0] . " members.</p>"); mysql_free_result ($result_id); ?> </body> </html> 

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 implementing 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. (If you prefer a more DBI-like approach, consider using PEAR, the PHP Extension and Add-on Repository. PEAR is an adjunct to PHP that includes a PEAR::DB module that provides a more abstract interface to database engines using a two-level architecture similar to that used by DBI. Visit pear.php.net for details.)