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 and carry out communications with the server.

However, you are not limited to using C to write MySQL programs. 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. This gives you many choices for writing applications that talk to the MySQL server. Client APIs exist for Perl, PHP, Java, Python, C++, Tcl, and others. Check the MySQL Reference Manual or the MySQL Web site for an up-to-date list because new language APIs are added from time to time.

Each language binding defines its own interface, specifying 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.

  • The DBI (Database Interface) API for the Perl general purpose scripting language. 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 scripting language that provides a convenient way of embedding programs in Web pages. Such a page is processed by PHP 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 Homage 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").

Consider Building on the Work of Others

When the standard MySQL clients are insufficient for your needs, you need not always write your own programs. Other people already have been busy writing programs, many of which are freely available. See Appendix I, "Useful Third-Party Tools," for some examples. You just might find a few that can save you some work.


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 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 that you'd like to try, see Appendix A, "Obtaining and Installing Software," for instructions.

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.

Predecessors of DBI and PHP

The Perl predecessor to DBI is the Mysqlperl module, Mysql.pm. This module 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 obtain a Perl script for accessing MySQL and discover that it's written for Mysqlperl rather than for DBI, you can still use DBI. DBI can be built to include Mysqlperl emulation support, so it's not necessary to install both packages.

The predecessor to PHP 3 is PHP/FI 2.0 (FI stands for "form interpreter"). Like Mysqlperl, PHP/FI is obsolete and I won't discuss it further.


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, yet actually takes care of much of the work involved in converting a mSQL program for use with MySQL.)


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 code are both made MySQL-aware by linking in the code for the MySQL C client library. (This process is illustrated by the DBI and PHP installation instructions in Appendix A.)

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 is implemented via Perl modules that use a two-level architecture (see Figure 5.1):

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

  • The DBD (database driver) level. Support for various database engines is provided at this level by drivers that are engine specific.

Figure 5.1. DBI architecture.


MySQL support for DBI is provided by the Msql-Mysql-modules distribution. This module operates at the DBD level. As you can tell from the distribution name and also from Figure 5.1, it's possible for a driver to provide support for more than one RDBMS. Msql-Mysql-modules was written originally for mSQL, then extended for MySQL later. This reflects the similarity of the C APIs for mSQL and for MySQL. Since the MySQL C API was designed to be similar to the mSQL C API, it made sense to extend the mSQL DBD (which uses the mSQL C API) to work with MySQL.

The DBI architecture allows you to write applications in relatively generic fashion. When you write a DBI script, you use a standard set of 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 what database the data originated from.

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

The one necessarily database-specific aspect of script writing occurs when you open a database. You indicate which driver to use when you establish the connection. For example, to use a MySQL database, you connect like this:

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

To use Postgres or mSQL instead, you connect like this:

$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. Let DBI and the driver work out database-specific details.

That's the theory, anyway. However, at least two factors work against DBI script portability:

  • SQL implementations differ betweeen RDBMS engines, and it's perfectly possible to write SQL for one engine that another will not understand. If your SQL is reasonably generic, your scripts will be correspondingly portable between engines. 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. For example, the MySQL DBD provides a way to access properties of the columns in a query result such as the maximum length of 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, and by using them, you make it difficult to use a script written for MySQL with other database systems.[1]

    [1] Nevertheless, you will discover that in Chapter 7, "The Perl DBI API," 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 or not to use them.

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

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

<HTML>
<HEAD>
<TITLE>US Historical League</TITLE>
</HEAD>
<BODY>

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


<P>Welcome to the US Historical League Website.
<?php
$link = @mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
    or exit ();
mysql_select_db ("samp_db")
    or exit ();
$result = mysql_query ("SELECT COUNT(*) FROM member")
    or exit ();
if ($row = mysql_fetch_array ($result))
    echo "<P>The League currently has " . $row[0] . " members";
mysql_free_result ($result);
?>
</BODY></HTML>

PHP scripts typically look like HTML pages with scripts embedded inside the '<?php' and '?>' tags. A page can contain several scripts. This provides an extremely flexible approach to script development. For example, if you like, you can write a normal HTML page to build the general page framework, then add script content later.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset