MySQL Phrasebook

The C API is the underlying interface used by all the other APIs, and almost every function call in another language's API maps directly to a corresponding C library function.

To use the C API you must link your program with the libmysqlclient.so library that is installed automatically if you compile MySQL from source. For those binary distributions that are split into separate components, you need to install the Libraries and Header Files package for your platform, named MySQL-devel-*.

A C program using the MySQL API must include the mysql.h file that resides in your system's default include directory. You would use the following:

#include <mysql/mysql.h>

Linking to libmysqlclient.so depends on your compiler, but the following example shows a basic example of how you would compile a mytest.c using the MySQL API using gcc:

shell> gcc o mytest mytest.c -lmysqlclient

If you encounter error messages when compiling, you can use the mysql_config utility to find out what compiler options are required. The --cflags, --include, and --libs switches cause mysql_config to display the compiler flags and defines and to include path and library options, respectively.

[View full width]

Connecting to MySQL

Two function calls are required to connect to a MySQL database. First, you must initialize a MYSQL type object and then use this object as an argument to the mysql_real_connect() function. That object is then used as a resource argument to subsequent API calls to indicate which database connection to use.

The port argument is the TCP/IP port used to connect to the database. For localhost connections, this value should be zero. The unix_socket argument is NULL unless you want to specify a different socket or named pipe to use for the connection.

The client_flag argument should remain NULL unless you need to enable certain features of the MySQL client library, which are not covered in this chapter.

The return value from mysql_real_connect() is a MYSQL object if the connection is successful. The value is the same as the object passed in the first argument, so you do not need to assign this new object to a variable. The function returns NULL if the connection fails for any reason.

Executing a Query

The query argument passed to mysql_query() should be a null-terminated string containing a single SQL query. The terminating semicolon is not required. This query is executed against the database connection defined in the MYSQL object passed in the first argument.

If your query contains binary data, you must use mysql_real_query() and specify the length of the query string. Binary data may contain the \0 character, which mysql_query() treats as the end of the string.

The return value is NULL on success, or one of the values shown in Table 8.1 if there is an error.

Fetching Data from a Result Set

Before you can fetch the data returned by a query in your application, you have to assign the result of that query to a MYSQL_RES object. The mysql_use_result() function assigns the result to this type of object using the most recently executed query on the specified database connection.

After assigning the result, you can continue to execute further queries without destroying the reference to this query result. mysql_use_result() returns NULL on success or the error codes from Table 8.1 on failure.

The mysql_fetch_row() function fetches data from the query one row at a time and returns each row into a MYSQL_ROW structure. Values are then accessed as row[0] up to row[n-1], where n is the number of columns in the data set.

To loop through the entire data set, use mysql_num_fields(result) and mysql_num_rows(result) to find the number of columns and rows returned, respectively. You can also use a while loop to fetch each row in turn, as mysql_fetch_row() returns NULL when there are no more rows to be fetched. The example program in Listing 8.1 later in this chapter shows this in action.

Note

You can use mysql_query() to execute any SQL statementit does not have to be a SELECT query. It is nonsense, however, to try retrieving data rows from an INSERT, UPDATE, or DELETE operation, and doing so causes an error.

Displaying Error Messages

Whenever MySQL encounters an error, you can find the internal error number and the corresponding error message using the mysql_errno() and mysql_error() functions.

Their arguments are the MYSQL connection object, and the error information returned relates to the last query executed on this connection. If the most recent query was successful, mysql_errno() returns 0 and mysql_error() returns NULL.

You can use these functions to find the cause of an error both when connecting to a database and when executing a query.

Closing a Connection

When you are done with a MySQL connection, you should close it using mysql_close(). The resources allocated by mysql_init() are de-allocated.

A Sample C Program

Listing 8.1 is a sample program that establishes a database connection, executes a query against the sample database, and outputs the result in a tabular format.

Listing 8.1. Executing a Query Using the C API

The output from running the compiled program looks like the following:

1 good Jacob Two-Two Meets the Hooded Fang 2 mint In the Night Kitchen 3 poor How to Be a Grouch 4 good Green Eggs and Ham 5 (null) Inferno