Chapter 6. Writing MySQL Programs Using C

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 that establish and terminate a session with a server.

• Routines that construct SQL statements, send them to the server, and process the results.

• Status-checking and error-reporting functions for determining the exact reason for an error when an API call fails.

• Routines that help you process options given in option files or on the command line.

This chapter shows how to use the C client library to write your own programs, using conventions that are reasonably consistent with those used by the client programs included in the MySQL distribution. I assume that you know something about programming in C, but I've tried not to assume that you're an expert.

The first part of this chapter develops a series of short programs. The series culminates in a simple program that serves as 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, one thing all have in common is that they must establish a connection to the server.

The resulting skeleton program is reasonably generic, so it is usable as the basis for any number of other client programs. After developing it, we'll pause to consider how to execute various kinds of SQL statements. Initially, we'll discuss how to handle specific hardcoded statements, and then develop code that can be used to process arbitrary statements. After that, we'll add some statement-processing code to the skeleton to develop another program that's similar to the mysql client and that can be used to issue statements interactively.

The chapter then shows how to take advantage of several other capabilities offered by the client library:

• How to write client programs that communicate with the server over secure connections using the Secure Sockets Layer (SSL) protocol.

• How to write applications that use libmysqld, the embedded server library.

• How to send multiple statements to the server at once and then process the result sets that come back.

• How to use server-side prepared statements.

SSL and libmysqld were introduced in MySQL 4.0. Multiple-statement execution and server-side prepared statements were introduced in MySQL 4.1.

This chapter discusses only those functions and data types from the client library that we need for the example programs. For a comprehensive listing of all functions and types, see Appendix G, "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 so that you can try them directly without typing them in yourself. They are part of the sampdb distribution; you can find them under the capi directory of the distribution. See Appendix A, "Obtaining and Installing Software," for downloading instructions.

Where to Find Example Programs 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 a MySQL source distribution includes several client programs that happen to be written in C (mysql, mysqladmin, and mysqldump, for example). Because the distribution is readily available, it 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 its client and tests directories.