Introduction to MySQL Programming

Why Write Your Own MySQL Programs? A MySQL distribution includes a set of utility programs. For example, mysqldump exports the structural definitions and contents of tables, mysqlimport loads data files into tables, mysqladmin performs administrative operations, and mysql enables you to interact with the server to execute arbitrary queries. Each of the standard MySQL utilities is designed to be a small, focused program with a specific, limited function. This is true even for mysql, which is more flexible than the other utilities in the sense that you can use it to execute any number of different queries. It's designed with the single purpose of allowing you to issue SQL queries directly to the server and view the results. This limited nature of the MySQL clients is not a flaw—it's by design. The programs are general-purpose utilities that are not intended to anticipate all possible requirements you might have. The MySQL developers do not subscribe to the philosophy of writing huge, bloated programs that try to do everything you might possibly want to do (and thus end up including lots of code for many things you don't care about at all). The standard client programs handle many of the most common tasks that MySQL users need to perform, but applications sometimes have requirements that are not addressed by the capabilities of those clients. In this part of the book, we'll discuss what you need to know to write your own MySQL-based programs for accessing your databases. To make this possible, MySQL includes a client-programming library that provides you with the flexibility to satisfy whatever specialized requirements your applications may have. By giving you access to the MySQL server, the client library opens up possibilities limited only by your own imagination. To understand specifically what you gain by writing your own programs, consider what you can accomplish that way in comparison to using the capabilities of the mysql client and its no-frills interface to the MySQL server: You can customize input handling. With mysql, you enter raw SQL statements. With your own programs, you can provide input methods for the user that are more intuitive and easier to use. The program can eliminate the need for the user to know SQL—or even to be aware of the role of the database in the task being performed. Input collection can be something as rudimentary as a command-line style interface that prompts the user and reads a value or something as sophisticated as a screen-based entry form implemented using a screen management package, such as curses or S-Lang, an X window using Tcl/Tk, or a form in a Web page. For most people, it's a lot easier to specify search parameters by filling in a form rather than by issuing a SELECT statement. For example, a real estate agent looking for houses in a certain price range, style, or location just wants to enter search parameters into a form and get back the qualifying offerings with a minimum of fuss. For entering new records or updating existing records, similar considerations apply; a keyboard operator in a data entry department should need to know only the values to be entered into records, not the SQL syntax for INSERT, REPLACE, or UPDATE. An additional reason to interpose an input-collection layer between the end user and the MySQL server is that you can validate input provided by the user. For example, you can check dates to make sure they conform to the format that MySQL expects, or you can require certain fields to be filled in. Some applications might not even involve a user, such as when input for MySQL is generated by another program. You might configure your Web server to write log entries to MySQL rather than to a file. A system monitoring program can be set up to run periodically and record status information to a database. You can customize your output. mysql output is essentially unformatted; you have a choice of tab-delimited or tabular style. If you want nicer-looking output, you must format it yourself. This might range from something as simple as printing "Missing" rather than NULL to more complex report-generation requirements. Consider the following report: State City Sales ------------------------------ AZ Mesa $94,384.24 Phoenix $17,328.28 ----------------------- subtotal $117,712.52 ------------------------------ CA Los Angeles $118,198.18 Oakland $38,838.36 ----------------------- Subtotal $157,036.54 ============================== TOTAL $274,749.06 This report includes several specialized elements: Customized headers Suppression of repeating values in the State column so that the values are printed only when they change Subtotal and total calculations Formatting of numbers, such as 94384.24, to print as dollar amounts, such as $94,384.24 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. Choosing an API This section provides general guidelines to help you choose an API for various types of applications. It compares the capabilities of the C, DBI, and PHP APIs to give you some idea of their relative strengths and weaknesses and to indicate when you might choose one over another. I should probably point out first that I am not advocating any one of these languages over the others, although I do have my preferences. You will have your own preferences, too, as did the technical reviewers for this book. In fact, one reviewer felt that I should emphasize the importance of C for MySQL programming to a much greater extent, whereas another thought I should come down much harder on C programming and discourage its use! Weigh the factors discussed in this section and come to your own conclusions. A number of considerations can enter in to your assessment of which API to choose for a particular task: Intended execution environment. The context in which you expect the application to be used. Performance. How efficiently applications perform when written in the API language. Ease of development. How convenient the API and its language make application writing. Portability. Whether or not the application will be used for database systems other than MySQL. General Procedure for Building Client Programs This section describes the steps involved in compiling and linking a program that uses the MySQL client library. The commands to build clients vary somewhat from system to system, and you may need to modify the commands shown here a bit. However, the description is general and you should be able to apply it to most client programs you write. Basic System Requirements When you write a MySQL client program in C, you'll obviously need a C compiler. The examples shown here use gcc, which is probably the most common compiler used on UNIX. You'll also need the following in addition to the program's own source files: The MySQL header files The MySQL client library Client 1—Connecting to the Server Our first MySQL client program is about as simple as can be—it connects to a server, disconnects, and exits. That's not very useful in itself, but you have to know how to do it because you must be connected to a server before you can do anything with a MySQL database. Connecting to a MySQL server is such a common operation that the code you develop to establish the connection is code you'll use in every client program you write. Additionally, this task gives us something simple to start with. The client can be fleshed out later to do something more useful. The code for our first client program, client1, consists of a single source file, client1.c: /* client1.c - connect to and disconnect from MySQL server */ #include #include static char *opt_host_name = NULL; /* server host (default=localhost) */ static char *opt_user_name = NULL; /* username (default=login name) */ static char *opt_password = NULL; /* password (default=none) */ static unsigned int opt_port_num = 0; /* port number (use built-in value) */ static char *opt_socket_name = NULL; /* socket name (use built-in value) */ static char *opt_db_name = NULL; /* database name (default=none) */ static unsigned int opt_flags = 0; /* connection flags (none) */ static MYSQL *conn; /* pointer to connection handler */ int main (int argc, char *argv[]) { /* initialize connection handler */ conn = mysql_init (NULL); /* connect to server */ mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password, opt_db_name, opt_port_num, opt_socket_name, opt_flags); /* disconnect from server */ mysql_close (conn); exit (0); } Processing Queries The purpose of connecting to the server is to conduct a conversation with it while the connection is open. This section shows how to communicate with the server to process queries. Each query you run involves the following steps: Construct the query. The way you do this depends on the contents of the query—in particular, whether it contains binary data. Issue the query by sending it to the server. The server will execute the query and generate a result. Process the query result. This depends on what type of query you issued. For example, a SELECT statement returns rows of data for you to process. An INSERT statement does not.