Using the MySQL Client Library to Write Your Own Programs
- General Procedure for Building Client Programs
- Client 1—Connecting to the Server
- Client 2—Adding Error Checking
- Client 3—Making the Connection Code Modular
- Client 4—Getting Connection Parameters at Runtime
- Processing Queries
- Client 5—An Interactive Query Program
- Miscellaneous Topics
This chapter is from the book
This chapter is from the book
This chapter is from the book
MySQL provides a client library written in the C programming language that you can use to write client programs that access MySQL databases. This library defines an application programming interface that includes the following facilities:
-
Connection management routines to establish and terminate a session with a server.
-
Routines to construct queries, send them to the server, and process the results.
-
Status- and error-reporting functions for determining the exact reason for an error when other C API calls fail.
This chapter shows how to use the client library to write your own programs. Some of the goals we'll keep in mind are consistency with existing client programs in the MySQL distribution, as well as modularity and reusability of the code. I assume you know something about programming in C, but I've tried not to assume you're an expert.
The chapter develops a series of client programs in a rough progression from very simple to more complex. The first part of this progression develops the framework for a client skeleton that does nothing but connect to and disconnect from the server. The reason for this is that although MySQL client programs are written for different purposes, they all have one thing in common: They establish a connection to the server.
We'll build the skeleton in steps:
-
Write some bare-bones connection and disconnection code (client1).
-
Add error checking (client2).
-
Make the connection code modular and reusable (client3).
-
Add the ability to get connection parameters (host, user, password) at runtime (client4).
This framework is reasonably generic, and you can use it as the basis for any number of client programs. After developing it, we'll pause to consider how to handle various kinds of queries. Initially, we'll discuss how to handle specific hardcoded SQL statements, then develop code that can be used to process arbitrary statements. After that, we'll add our query-processing code to our client framework to develop another program (client5) that's similar to the mysql client.
We'll also consider (and solve) some common problems, such as, "How can I get information about the structure of my tables" and "How can I insert images in my database?"
This chapter discusses functions and data types from the client library only as they are needed. For a comprehensive listing of all functions and types, see Appendix F, "C API Reference." You can use that appendix as a reference for further background on any part of the client library you're trying to use.
The example programs are available online for downloading so you can try them directly without typing them in yourself. See Appendix A, "Obtaining and Installing Software," for instructions.
Where to Find Examples
A common question on the MySQL mailing list is "Where can I find some examples of clients written in C?" The answer, of course, is "right here in this book!" But something many people seem not to consider is that the MySQL distribution contains several client programs (mysql, mysqladmin, and mysqldump, for example), most of which are written in C. Because the distribution is readily available in source form, MySQL itself provides you with quite a bit of example client code. Therefore, if you haven't already done so, grab a source distribution sometime and take a look at the programs in the client directory. The MySQL client programs are in the public domain and you may freely borrow code from them for your own programs.
Between the examples provided in this chapter and the client programs included in the MySQL distribution, you may be able to find something similar to what you want to do when writing your own programs. If you do, you may be able to reuse code by copying an existing program and modifying it. You should read this chapter to gain an understanding of how the client library works. Remember, however, that you don't always need to write everything yourself from the ground up. (You'll notice that code reusability is one of the goals in our discussion of writing programs in this chapter.) If you can avoid a lot of work by building on what someone else has already done, so much the better.
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 almost any client program you write.
Basic System Requirements
When you write a MySQL client program in C, you'll need a C compiler, obviously. The examples shown here use gcc. You'll also need the following in addition to your own source files:
The MySQL header files
The MySQL client library
The MySQL header files and client library constitute client programming support. They may be installed on your system already. Otherwise, you need to obtain them. If MySQL was installed from a source or binary distribution, client programming support should have been installed as part of that process. If MySQL was installed from RPM files, this support won't be present unless you installed the developer RPM. If you need to install the MySQL header files and library, see Appendix A.
Compiling and Linking the Client
To compile and link a client program, you must specify where the MySQL header files and client library are located because they usually are not installed in locations that the compiler and linker search by default. For the following example, suppose the header file and client library locations are /usr/local/include/mysql and /usr/local/lib/mysql.
To tell the compiler how to find the MySQL header files, pass it a -I/usr/local/include/mysql argument when you compile a source file into an object file. For example, you might use a command like this:
% gcc -c -I/usr/local/include/mysql myclient.c
To tell the linker where to find the client library and what its name is, pass -L/usr/local/lib/mysql and -lmysqlclient arguments when you link the object file to produce an executable binary, as follows:
% gcc -o myclient myclient.o -L/usr/local/lib/mysql -lmysqlclient
If your client consists of multiple files, name all the object files on the link command. If the link step results in an error having to do with not being able to find the floor() function, link in the math library by adding -lm to the end of the command:
% gcc -o myclient myclient.o -L/usr/local/lib/mysql -lmysqlclient -lm
You might need to add other libraries as well. For example, you'll probably need -lsocket -lnsl on Solaris.
If you don't use make to build programs, I suggest you learn how so that you won't have to type a lot of program-building commands manually. Suppose you have a client program, myclient, comprising two source files, main.c and aux.c, and a header file, myclient.h. A simple Makefile to build this program might look like this:
CC = gcc INCLUDES = -I/usr/local/include/mysql LIBS = -L/usr/local/lib/mysql -lmysqlclient all: myclient main.o: main.c myclient.h $(CC) -c $(INCLUDES) main.c aux.o: aux.c myclient.h $(CC) -c $(INCLUDES) aux.c myclient: main.o aux.o $(CC) -o myclient main.o aux.o $(LIBS) clean: rm -f myclient main.o aux.o
If your system is one for which you need to link in the math library, change the value of LIBS to add -lm to the end:
LIBS = -L/usr/local/lib/mysql -lmysqlclient -lm
If you need other libraries, such as -lsocket and -lnsl, add those to LIBS, too.
Using the Makefile, you can rebuild your program whenever you modify any of the source files simply by typing "make". That's easier and less error prone than typing a long gcc command.