Chapter 6. The MySQL C API

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 queries, send them to the server, and process the results

• Status- 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 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 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, one thing they all have in common is that they must establish a connection to the server.) Development of the framework proceeds in the following stages:

• Begin with some bare-bones connection and disconnection code (client1).

• Add error checking (client2).

• Add the ability to get connection parameters at runtime, such as the hostname, username, and password (client3).

The resulting client3 program is reasonably generic, so you can use it as the basis for any number of other 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 hard-coded SQL statements and then develop code that can be used to process arbitrary statements. After that, we'll add some query-processing code to client3 to develop another program (client4) that's similar to the mysql client and can be used to issue queries interactively.

The chapter then shows how to take advantage of two capabilities that are new in MySQL 4:

• 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

Finally, we'll 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 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, so 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 the MySQL distribution itself includes several client programs that happen to be written in C (mysql, mysqladmin, and mysqldump, for example). Because the distribution is readily available in source form, 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 directory.