|
|
APIs Available for MySQLTo 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: http://dev.mysql.com/downloads/ 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:
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 APIThe 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 APIThe 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):
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:
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 PHP APILike 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):
<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
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 ();
?>
</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 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 http://pear.php.net.) 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.  |
|
|