2.0. IntroductionThis 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.
MySQL client APIs provide the following capabilities, each of which is covered in a section of this chapter:
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:
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. AssumptionsTo use the material in this chapter most effectively, you should make sure that the following assumptions are satisfied:
MySQL Client API ArchitectureOne 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:
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:
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. |