Section 2.0. Introduction | MySQL Cookbook

2.0. Introduction

This chapter discusses how to write programs that use MySQL. It covers basic application programming interface (API) operations that are fundamental to the programming recipes developed in later chapters. These operations include connecting to the MySQL server, issuing statements, and retrieving the results.

MySQL-based client programs can be written using several languages. The languages covered in this book are Perl , Ruby, PHP, Python, and Java, for which we'll use the interfaces in the following table. Appendix A indicates where to get the software for each interface.

Language	Interface
Perl	Perl DBI
Ruby	Ruby DBI
PHP	PEAR DB
Python	DB-API
Java	JDBC

MySQL client APIs provide the following capabilities, each of which is covered in a section of this chapter:

Connecting to the MySQL server, selecting a database, and disconnecting from the server: Every program that uses MySQL must first establish a connection to the server, and most programs also select a default database to use. In addition, well-behaved MySQL programs close the connection to the server when they're done with it.
Checking for errors: Many people write MySQL programs that perform no error checking at all. Such programs are difficult to debug when things go wrong. Any database operation can fail and you should know how to find out when that occurs and why. That knowledge enables you to take appropriate action such as terminating the program or informing the user of the problem.
Executing SQL statements and retrieving results: The whole point of connecting to a database server is to execute SQL statements. Each API provides at least one way to do this, as well as several methods for processing statements results.
Handling special characters and NULL values in statements: One way to write a statement that refers to specific data values is to embed the values directly in the statement string. However, some characters such as quotes and backslashes have special meaning, and you must take certain precautions when constructing statements containing them. The same is true for NULL values. If you do not handle these properly, your programs may generate SQL statements that are erroneous or that yield unexpected results. If you incorporate data from external sources into queries, you might become open to SQL injection attacks. Most APIs provide a way of writing statements that enables you to refer to data values symbolically. When you execute the statement, you supply the data values separately and the API places them into the statement string after properly encoding any special characters or NULL values.
Identifying NULL values in result sets: NULL values are special not only when you construct statements, but also in results returned from statements. Each API provides a convention for recognizing and dealing with them.

No matter which programming language you use, it's necessary to know how to perform each of the fundamental database API operations, so each operation is shown in all five languages. Seeing how each API handles a given operation should help you see the correspondences between APIs more easily and better understand the recipes shown in the following chapters, even if they're written in a language you don't use very much. (Later chapters usually illustrate recipe implementations using only one or two languages.)

It may seem overwhelming to see each recipe in several languages if you're interested only in one particular API. If so, I advise you to approach the recipes as follows: read just the introductory part that provides the general background, and then go directly to the section for the language in which you're interested. Skip the other languages. Should you develop an interest in writing programs in other languages later, you can always come back and read the other sections then.

This chapter also discusses the following topics, which are not directly part of MySQL APIs but can help you use them more easily:

Writing library files: As you write program after program, you may find that there are certain operations you carry out repeatedly. Library files provide a way to encapsulate the code for these operations so that you can perform them from multiple scripts without including all the code in each script. This reduces code duplication and makes your programs more portable. This section shows how to write a library file for each API that includes a routine for connecting to the serverone operation that every program that uses MySQL must perform. (Later chapters develop additional library routines for other operations.)
Additional techniques for obtaining connection parameters: An early section on establishing connections to the MySQL server relies on connection parameters hardwired into the code. However, there are several other ways to obtain parameters, ranging from storing them in a separate file to allowing the user to specify them at runtime.

To avoid manually typing in the example programs, you should obtain the recipes source distribution. (See Appendix A.) Then, when an example says something like "create a file named xyz that contains the following information ...," you can just use the corresponding file from the recipes distribution. The scripts for this chapter are located under the api directory, with the exception of the library files that can be found in the lib directory.

The primary table used for examples in this chapter is named profile. It first appears in Section 2.4, which you should know in case you skip around in the chapter and wonder where it came from. See also the section at the very end of the chapter about resetting the profile table to a known state for use in later chapters.

NOTE

The programs discussed here can be run from the command line. For instructions on invoking programs for each of the languages covered here, see Appendix B.

Assumptions

To use the material in this chapter most effectively, you should make sure that the following assumptions are satisfied:

MySQL programming support must be installed for any language processors that you plan to use. If you need to install any of the APIs, see Appendix A.
You should already have set up a MySQL user account for accessing the server and a database to use for trying statements. As described in Section 1.1, the examples in this book use a MySQL account that has a username and password of cbuser and cbpass, and we'll connect to a MySQL server running on the local host to access a database named cookbook. If you need to create the account or the database, see the instructions in that recipe.
The discussion here assumes a certain basic understanding of the API languages. If a recipe uses language constructs with which you're not familiar, consult a good general text that covers that language. Appendix D lists some resources that may be helpful.
Proper execution of some of the programs might require that you set certain environment variables. See Appendix B, for general information about setting environment variables, and Section 2.3 for details about environment variables that apply specifically to searching for library files.

MySQL Client API Architecture

One thing that all MySQL client programs have in common, no matter which language you use, is that they connect to the server using some kind of application programming interface that implements a communications protocol. This is true regardless of the program's purpose, whether it's a command-line utility, a job that runs automatically on a predetermined schedule, or a script that's used from a web server to make database content available over the Web. MySQL APIs provide a standard way for you, the application developer, to express database operations. Each API translates your instructions into something the MySQL server can understand.

The server itself speaks a low-level protocol that I call the raw protocol. This is the level at which direct communication takes place over the network between the server and its clients. A client establishes a connection to the port on which the server is listening and communicates with it by speaking the client-server protocol in its most basic terms. (Basically, the client fills in data structures and shoves them over the network.) It's not productive to attempt to communicate directly with the server at this level, nor to write programs that do so. The raw protocol is a binary communication stream that is efficient, but not particularly easy to use, a fact that usually deters developers from attempting to write programs that talk to the server this way. More convenient access to the MySQL server is available through a programming interface that is written at a level above that of the raw protocol. The interface handles the details of the raw protocol on behalf of your programs. It provides calls for operations such as connecting to the server, sending statements, retrieving the results of statements, and obtaining statement status information.

Most MySQL APIs do not implement the raw protocol directly. Instead, they are linked to and rely on the MySQL client library that is included with MySQL distributions. The client library is written in C and thus provides the basis of an interface for communicating with the server from within C programs. The majority of the standard clients in the MySQL distribution are written in C and use this API. You can use it in your own programs, too, and should consider doing so if you want the most efficient programs possible. However, most third-party application development is not done in C. Instead, the C API is most often used indirectly as an embedded library within other languages. This is how MySQL communication is implemented for Perl, Ruby, PHP, Python, and several other languages. The MySQL API for these higher-level languages is written as a "wrapper" around the C routines, which are linked to the language processor.

The benefit of this approach is that it allows a language processor to talk to the MySQL server on your behalf using the C routines while providing an interface in which you specify database operations more conveniently. For example, scripting languages such as Perl or Ruby typically make it easy to manipulate text without having to allocate string buffers or dispose of them when you're done with them the way you do in C. Higher-level languages let you concentrate more on what you're trying to do and less on the details that you must think about when you're writing directly in C.

This book doesn't cover the C API in any detail because we never use it directly; the programs developed in this book use higher-level interfaces that are built on top of the C API. However, if you'd like to write MySQL client programs in C, the following sources of information may be helpful:

The MySQL Reference Manual contains a chapter that describes all the C API functions. You should also have a look at the source for the standard MySQL clients provided with the MySQL source distribution that are written in C. Source distributions and the manual both are available at the MySQL web site, http://dev.mysql.com/, and you can obtain the manual in printed form from MySQL Press.
The book MySQL by Paul DuBois (Sams) contains reference material for the C API, and also includes a chapter that provides detailed tutorial instructions for writing MySQL programs in C. The chapter is available online at http://www.kitebird.com/mysql-book/. The source code for the sample programs discussed in the chapter is available from the same site for you to study and use. Those programs were deliberately written for instructional purposes, so you may find them easier to understand than the standard clients in the MySQL source distribution.

Java interfaces for MySQL do not use the C client library. They implement the raw protocol directly but map protocol operations onto the JDBC interface. You write your Java programs using standard JDBC calls, and JDBC passes your requests for database operations to the lower-level MySQL interface, which converts them to operations that communicate with the MySQL server using the raw protocol.

The MySQL programming interfaces used in this book share a common design principle: they all use a two-level architecture. The top level of this architecture provides database-independent methods that implement database access in a portable way that's the same no matter which database management system you're using, be it MySQL, PostgreSQL, Oracle, or whatever. The lower level consists of a set of drivers, each of which implements the details for a particular database system. The two-level architecture enables application programs to use an abstract interface that is not tied to the details involved with accessing any particular database server. This enhances portability of your programs because you just select a different lower-level driver to use a different type of database. That's the theory, at least. In practice, perfect portability can be somewhat elusive:

The interface methods provided by the top level of the architecture are consistent regardless of the driver you use, but it's still possible to issue SQL statements that contain constructs supported only by a particular server. For MySQL, a good example is the SHOW statement that provides information about database and table structure. If you use SHOW with a non-MySQL server, an error is the likely result.
Lower-level drivers often extend the abstract interface to make it more convenient to get at database-specific features. For example, the MySQL driver for Perl DBI makes the most recent AUTO_INCREMENT value available as an attribute of the database handle so that you can access it as $dbh->{mysql_insertid}. These features often make it easier to write a program initially, but at the same time make it less portable and require some rewriting should you port the program to use with another database system.

Despite these factors that compromise portability to some extent, the general portability characteristics of the two-level architecture provide significant benefits for MySQL developers.

Another thing that the APIs used in this book have in common is that they are object-oriented. Whether you write in Perl, Ruby, PHP, Python, or Java, the operation that connects to the MySQL server returns a value that enables you to process statements in an object-oriented manner. For example, when you connect to the database server, you get a database connection object that you use to further interact with the server. The interfaces also provide other objects, such as objects for statements, result sets, or metadata.

Now let's see how to use these programming interfaces to perform the most fundamental MySQL operations: connecting to and disconnecting from the server.