Using Server-Side Prepared Statements

In the earlier parts of this chapter, the code for SQL statement processing is based on the original method provided by the MySQL client library that sends and retrieves all information in string form. This section discusses how to use the binary protocol that is available as of MySQL 4.1. The binary protocol supports server-side prepared statements and allows transmission of data values in native format.

Not all statements can be prepared. The prepared-statement API applies to these statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most variations of SHOW.

The binary protocol underwent quite a bit of revision during the earlier releases of MySQL 4.1. In particular, many functions were renamed from their pre-4.1.2 names. For best results, you should try to use a recent version of MySQL.

To use the binary protocol, you must create a statement handler. With this handler, send a statement to the server to be "prepared," or preprocessed. The server analyzes the statement, remembers it, and sends back information about it that the client library stores in the statement handler.

A statement to be prepared can be parameterized by including '?' characters to indicate where data values appear that you will supply later when you execute the statement. For example, you might prepare a statement like this:

INSERT INTO score (event_id,student_id,score) VALUES(?,?,?)

This statement includes three '?' characters that act as parameter markers or placeholders. Later, you can supply data values to be bound to the placeholders. These complete the statement when you execute it. By parameterizing a statement, you make it reusable: The same statement can be executed multiple times, each time with a new set of data values. What this means is that you send the text of the statement only once. Each time you execute the statement, you need send only the data values. For repeated statement execution, this provides a performance boost:

The server needs to analyze the statement only once, not each time it is executed.
Network overhead is reduced, because you send only the data values for each execution, not an entire statement.
Data values are sent without conversion to string form, which reduces execution overhead. For example, the three columns named in the preceding INSERT statement all are INT columns. Were you to use mysql_query() or mysql_real_query() to execute a similar INSERT statement, it would be necessary to convert the data values to strings for inclusion in the text of the statement. With the prepared statement interface, you send the data values separately in binary format.
No conversion is needed for retrieving results, either. In result sets returned by prepared statements, non-string values are returned in binary format without conversion to string form.

The binary protocol does have some disadvantages, compared to the original non-binary protocol:

It is more difficult to use because more setup is necessary for transmitting and receiving data values.
The binary protocol does not support all statements. For example, USE statements don't work.
For interactive programs, you may as well use the original protocol. In that case, each statement received from the user is executed only once. There is little benefit to using prepared statements, which provide the greatest efficiency gain for statements that you execute repeatedly.

The general procedure for using a prepared statement involves several steps:

1.	Allocate a statement handler by calling `mysql_stmt_init()`. This function returns a pointer to the handler, which you use for the following steps.
2.	Call `mysql_stmt_prepare()` to send a statement to the server to be prepared and associated with the statement handler. The server determines certain characteristics of the statement, such as what kind of statement it is, how many parameter markers it contains, and whether it will produce a result set when executed.
3.	If the statement contains any placeholders, you must provide data for each of them before you can execute it. To do this, set up a `MYSQL_BIND` structure for each parameter. Each structure indicates one parameter's data type, its value, whether it is `NULL`, and so on. Then bind these structures to the statement by calling `mysql_stmt_bind_param()`.
4.	Invoke `mysql_stmt_execute()` to execute the statement.
5.	If the statement modifies data rather than producing a result set (for example, if it is an `INSERT` or `UPDATE`), call `mysql_stmt_affected_rows()` to determine the number of rows affected by the statement.
6.	If the statement produces a result set, call `mysql_stmt_result_metadata()` if you want to obtain metadata about the result set. To fetch the rows, you use `MYSQL_BIND` structures again, but this time they serve as receptacles for data returned from the server rather than a source of data to send to the server. You must set up one `MYSQL_BIND` structure for each column in the result set. They contain information about the values you expect to receive from the server in each row. Bind the structures to the statement handler by calling `mysql_stmt_bind_result()`, and then invoke `mysql_stmt_fetch()` repeatedly to get each row. After each fetch, you can access the column values for the current row. An optional action you can take before calling `mysql_stmt_fetch()` is to call `mysql_stmt_store_result()`. If you do this, the result set rows are fetched all at once from the server and buffered in memory on the client side. Also, the number of rows in the result set can be determined by calling `mysql_stmt_num_rows()`, which otherwise returns zero. After fetching the result set, call `mysql_stmt_free_result()` to release memory associated with it.
7.	If you want to re-execute the statement, return to step 4.
8.	If you want to prepare a different statement using the handler, return to step 2.
9.	When you're done with the statement handler, dispose of it by calling `mysql_stmt_close()`.

A client application can prepare multiple statements, and then execute each in the order appropriate to the application. If the client connection closes while the server still has prepared statements associated with the connection, the server disposes of them automatically.

The following discussion describes how to write a simple program that inserts some records into a table and then retrieves them. The part of the program that processes INSERT statement illustrates how to use placeholders in a statement and transmit data values to the server to be bound to the prepared statement when it is executed. The part that processes a SELECT statement shows how to retrieve a result set produced by executing a prepared statement. You can find the source for this program in the prepared.c and process_prepared_statement.c files in the capi directory of the sampdb distribution. I won't show the code for setting up the connection because it is similar to that for earlier programs.

The main part of the program that sets up to use prepared statements looks like this:

void
process_prepared_statements (MYSQL *conn)
{
MYSQL_STMT  *stmt;
char        *use_stmt = "USE sampdb";
char        *drop_stmt = "DROP TABLE IF EXISTS t";
char        *create_stmt =
    "CREATE TABLE t (i INT, f FLOAT, c CHAR(24), dt DATETIME)";

    /* select database and create test table */

    if (mysql_query (conn, use_stmt) != 0
        || mysql_query (conn, drop_stmt) != 0
        || mysql_query (conn, create_stmt) != 0)
    {
        print_error (conn, "Could not set up test table");
        return;
    }

    stmt = mysql_stmt_init (conn);  /* allocate statement handler */
    if (stmt == NULL)
    {
        print_error (conn, "Could not initialize statement handler");
        return;
    }

    /* insert and retrieve some records */
    insert_records (stmt);
    select_records (stmt);

    mysql_stmt_close (stmt);        /* deallocate statement handler */
}

First, we select a database and create a test table. The table contains four columns of varying data types: an INT, a FLOAT, a CHAR, and a DATETIME. These different data types need to be handled in slightly different ways, as will become evident.

After the table has been created, we invoke mysql_stmt_init() to allocate a prepared statement handler, insert and retrieve some records, and deallocate the handler. All the real work takes place in the insert_records() and select_records() functions, which we will get to shortly. For error handling, the program also uses a function, print_stmt_error(), that is similar to the print_error() function used in earlier programs but invokes the error functions that are specific to prepared statements:

static void
print_stmt_error (MYSQL_STMT *stmt, char *message)
{
    fprintf (stderr, "%s\n", message);
    if (stmt != NULL)
    {
        fprintf (stderr, "Error %u (%s): %s\n",
                mysql_stmt_errno (stmt),
                mysql_stmt_sqlstate(stmt),
                mysql_stmt_error (stmt));
    }
}

The insert_records() function takes care of adding new records to the test table. It looks like this:

static void
insert_records (MYSQL_STMT *stmt)
{
char            *stmt_str = "INSERT INTO t (i,f,c,dt) VALUES(?,?,?,?)";
MYSQL_BIND      param[4];
int             my_int;
float           my_float;
char            my_str[26]; /* ctime() returns 26-character string */
MYSQL_TIME      my_datetime;
unsigned long   my_str_length;
time_t          clock;
struct tm       *cur_time;
int             i;

    printf ("Inserting records...\n");

    if (mysql_stmt_prepare (stmt, stmt_str, strlen (stmt_str)) != 0)
    {
        print_stmt_error (stmt, "Could not prepare INSERT statement");
        return;
    }

    /*
     * perform all parameter initialization that is constant
     * and does not change for each row
     */

    memset ((void *) param, 0, sizeof (param)); /* zero the structures */

    /* set up INT parameter */

    param[0].buffer_type = MYSQL_TYPE_LONG;
    param[0].buffer = (void *) &my_int;
    param[0].is_unsigned = 0;
    param[0].is_null = 0;
    /* buffer_length, length need not be set */

    /* set up FLOAT parameter */

    param[1].buffer_type = MYSQL_TYPE_FLOAT;
    param[1].buffer = (void *) &my_float;
    param[1].is_null = 0;
    /* is_unsigned, buffer_length, length need not be set */

    /* set up CHAR parameter */

    param[2].buffer_type = MYSQL_TYPE_STRING;
    param[2].buffer = (void *) my_str;
    param[2].buffer_length = sizeof (my_str);
    param[2].is_null = 0;
    /* is_unsigned need not be set, length is set later */

    /* set up DATETIME parameter */

    param[3].buffer_type = MYSQL_TYPE_DATETIME;
    param[3].buffer = (void *) &my_datetime;
    param[3].is_null = 0;
    /* is_unsigned, buffer_length, length need not be set */

    if (mysql_stmt_bind_param (stmt, param) != 0)
    {
        print_stmt_error (stmt, "Could not bind parameters for INSERT");
        return;
    }

    for (i = 1; i <= 5; i++)
    {
        printf ("Inserting record %d...\n", i);

        (void) time (&clock);   /* get current time */

        /* set the variables that are associated with each parameter */

        /* param[0]: set my_int value */
        my_int = i;

        /* param[1]: set my_float value */
        my_float = (float) i;

        /* param[2]: set my_str to current ctime() string value */
        /* and set length to point to var that indicates my_str length */
        (void) strcpy (my_str, ctime (&clock));
        my_str[24] = '\0';  /* chop off trailing newline */
        my_str_length = strlen (my_str);
        param[2].length = &my_str_length;

        /* param[3]: set my_datetime to current date and time components */
        cur_time = localtime (&clock);
        my_datetime.year = cur_time->tm_year + 1900;
        my_datetime.month = cur_time->tm_mon + 1;
        my_datetime.day = cur_time->tm_mday;
        my_datetime.hour = cur_time->tm_hour;
        my_datetime.minute = cur_time->tm_min;
        my_datetime.second = cur_time->tm_sec;
        my_datetime.second_part = 0;
        my_datetime.neg = 0;

        if (mysql_stmt_execute (stmt) != 0)
        {
            print_stmt_error (stmt, "Could not execute statement");
            return;
        }

        sleep (1);  /* pause briefly (to let the time change) */
    }
}

The overall purpose of insert_records() is to insert five records into the test table, each of which will contain these values:

An INT value from 1 to 5.
A FLOAT value from 1.0 to 5.0.
A CHAR value. To generate these values, we'll call the ctime() system function to get the value of "now" as a string. ctime() returns values that have this format:
```
Sun Sep 19 16:47:23 CDT 2004
```
A DATETIME value. This also will be the value of "now," but stored in a MYSQL_TIME structure. The binary protocol uses MYSQL_TIME structures to transmit DATETIME, TIMESTAMP, DATE, and TIME values.

The first thing we do in insert_records() is prepare an INSERT statement by passing it to mysql_stmt_prepare(). The statement looks like this:

INSERT INTO t (i,f,c,dt) VALUES(?,?,?,?)

The statement contains four placeholders, so it's necessary to supply four data values each time the statement is executed. Placeholders typically represent data values in VALUES() lists or in WHERE clauses. But there are places in which they cannot be used:

As identifiers such as table or column names. This statement is illegal:
```
SELECT * FROM ?
```
You can use placeholders on one side of an operator, but not on both sides. This statement is legal:
```
SELECT * FROM student WHERE student_id = ?
```
However, this statement is illegal:
```
SELECT * FROM student WHERE ? = ?
```
This restriction is necessary so that the server can determine the data type of parameters.

The next step is to set up an array of MYSQL_BIND structures, one for each placeholder. As demonstrated in insert_records(), setting these up involves two stages:

1.	Initialize all parts of the structures that will be the same for each row inserted.
2.	Perform a row-insertion loop that, for each row, initializes the parts of the structures that vary for each row.

You could actually perform all initialization within the loop, but that would be less efficient.

The first initialization stage begins by zeroing the contents of the param array containing the MYSQL_BIND structures. The program uses memset(), but you could use bzero() if your system doesn't have memset(). These two statements are equivalent:

memset ((void *) param, 0, sizeof (param));
bzero ((void *) param, sizeof (param));

Clearing the param array implicitly sets all structure members to zero. Code that follows sets some members to zero to make it explicit what's going on, but that is not strictly necessary. In practice, you need not assign zero to any structure members after clearing the structures.

The next step is to assign the proper information to each parameter in the MYSQL_BIND array. For each parameter, the structure members that need to be set depend on the type of value you're transmitting:

The buffer_type member always must be set; it indicates the data type of the value. Appendix G contains a table that lists each of the allowable type codes and shows the SQL and C types that correspond to each code.
The buffer member should be set to the address of the variable that contains the data value. insert_records() declares four variables to hold row values: my_int, my_float, my_str, and my_datetime. Each param[i].buffer value is set to point to the appropriate variable. When it comes time to insert a row, we'll set these four variables to the table column values and they will be used to create the new row.
The is_unsigned member applies only to integer data types. It should be set to true (non-zero) or false (zero) to indicate whether the parameter corresponds to an UNSIGNED integer type. Our table contains a signed INT column, so we set is_unsigned to zero. Were the column an INT UNSIGNED, we would set is_unsigned to 1, and also would declare my_int as unsigned int rather than as int.
The is_null member indicates whether you're transmitting a NULL value. In the general case, you set this member to the address of a my_bool variable. Then, before inserting any given row, you set the variable true or false to specify whether or not the value to be inserted is NULL. If no NULL values are to be sent (as is the case here), you can set is_null to zero and no my_bool variable is needed.
For character string values or binary data (BLOB values), two more MYSQL_BIND members come into play. These indicate the size of the buffer in which the value is stored and the actual size of the current value being transmitted. In many cases these might be the same, but they will be different if you're using a fixed-size buffer and sending values that vary in length from row to row. buffer_length indicates the size of the buffer. length is a pointer; it should be set to the address of an unsigned long variable that contains the actual length of the value to be sent.

For numeric and temporal data types, buffer_length and length need not be set. The size of each of these types is fixed and can be determined from the buffer_type value. For example, MYSQL_TYPE_LONG and MYSQL_TYPE_FLOAT indicate four-byte and eight-byte values.

After the initial setup of the MYSQL_BIND array has been done, we bind the array to the prepared statement by passing the array to mysql_stmt_bind_param(). Then it's time to assign values to the variables that the MYSQL_BIND structures point to and execute the statement. This takes place in a loop that executes five times. Each iteration of the loop assigns values to the statement parameters:

For the integer and floating-point parameters, it's necessary only to assign values to the associated int and float variables.
For the string parameter, we assign the current time in string format to the associated char buffer. This value is obtained by calling ctime(), and then chopping off the newline character.
The datetime parameter also is assigned the current time, but this is done by assigning the component parts of the time to the individual members of the associated MYSQL_TIME structure.

With the parameter values set, we execute the statement by invoking mysql_stmt_execute(). This function transmits the current values to the server, which incorporates them into the prepared statement and executes it.

When insert_records() returns, the test table has been populated and select_records() can be called to retrieve them. select_records() looks like this:

static void
select_records (MYSQL_STMT *stmt)
{
char            *stmt_str = "SELECT i, f, c, dt FROM t";
MYSQL_BIND      param[4];
int             my_int;
float           my_float;
char            my_str[24];
unsigned long   my_str_length;
MYSQL_TIME      my_datetime;
my_bool         is_null[4];

    printf ("Retrieving records...\n");

    if (mysql_stmt_prepare (stmt, stmt_str, strlen (stmt_str)) != 0)
    {
        print_stmt_error (stmt, "Could not prepare SELECT statement");
        return;
    }

    if (mysql_stmt_field_count (stmt) != 4)
    {
        print_stmt_error (stmt, "Unexpected column count from SELECT");
        return;
    }

    /*
     * initialize the result column structures
     */

    memset ((void *) param, 0, sizeof (param)); /* zero the structures */

    /* set up INT parameter */

    param[0].buffer_type = MYSQL_TYPE_LONG;
    param[0].buffer = (void *) &my_int;
    param[0].is_unsigned = 0;
    param[0].is_null = &is_null[0];
    /* buffer_length, length need not be set */

    /* set up FLOAT parameter */

    param[1].buffer_type = MYSQL_TYPE_FLOAT;
    param[1].buffer = (void *) &my_float;
    param[1].is_null = &is_null[1];
    /* is_unsigned, buffer_length, length need not be set */

    /* set up CHAR parameter */

    param[2].buffer_type = MYSQL_TYPE_STRING;
    param[2].buffer = (void *) my_str;
    param[2].buffer_length = sizeof (my_str);
    param[2].length = &my_str_length;
    param[2].is_null = &is_null[2];
    /* is_unsigned need not be set */

    /* set up DATETIME parameter */

    param[3].buffer_type = MYSQL_TYPE_DATETIME;
    param[3].buffer = (void *) &my_datetime;
    param[3].is_null = &is_null[3];
    /* is_unsigned, buffer_length, length need not be set */

    if (mysql_stmt_bind_result (stmt, param) != 0)
    {
        print_stmt_error (stmt, "Could not bind parameters for SELECT");
        return;
    }

    if (mysql_stmt_execute (stmt) != 0)
    {
        print_stmt_error (stmt, "Could not execute SELECT");
        return;
    }

    /*
     * fetch result set into client memory; this is optional, but it
     * allows mysql_stmt_num_rows() to be called to determine the
     * number of rows in the result set.
     */

    if (mysql_stmt_store_result (stmt) != 0)
    {
        print_stmt_error (stmt, "Could not buffer result set");
        return;
    }
    else
    {
        /* mysql_stmt_store_result() makes row count available */
        printf ("Number of rows retrieved: %lu\n",
                        (unsigned long) mysql_stmt_num_rows (stmt));
    }

    while (mysql_stmt_fetch (stmt) == 0)    /* fetch each row */
    {
        /* display row values */
        printf ("%d  ", my_int);
        printf ("%.2f  ", my_float);
        printf ("%*.*s  ", my_str_length, my_str_length, my_str);
        printf ("%04d-%02d-%02d %02d:%02d:%02d\n",
                    my_datetime.year,
                    my_datetime.month,
                    my_datetime.day,
                    my_datetime.hour,
                    my_datetime.minute,
                    my_datetime.second);
    }

    mysql_stmt_free_result (stmt);          /* deallocate result set */
}

select_records() prepares a SELECT statement, executes it, and retrieves the result. In this case, the statement contains no placeholders:

SELECT i, f, c, dt FROM t

That means we don't need to set up any MYSQL_BIND structures before executing the statement. But we're not off the hook. The bulk of the work in select_records(), just as in insert_records(), is setting up an array of MYSQL_BIND structures. The difference is that they're used to receive data values from the server after executing the statement rather than to set up data values to be sent to the server before executing the statement.

Nevertheless, the procedure for setting up the MYSQL_BIND array is somewhat similar to the corresponding code in insert_records():

Zero the array.
Set the buffer_type member of each parameter to the appropriate type code.
Point the buffer member of each parameter to the variable where the corresponding column value should be stored when rows are fetched.
Set the is_unsigned member for the integer parameter to zero.
For the string parameter, set the buffer_length value to the maximum number of bytes that should be fetched, and set length to the address of an unsigned long variable. At fetch time, this variable will be set to the actual number of bytes fetched.
For every parameter, set the is_null member to the address of a my_bool variable. At fetch time, these variables will be set to indicate whether the fetched values are NULL. (Our program ignores these variables after fetching rows because we know that the test table contains no NULL values. In the general case, you should check them.)

After setting up the parameters, we bind the array to the statement by calling mysql_stmt_bind_result(), and then execute the statement.

At this point, you can immediately begin fetching rows by calling mysql_stmt_fetch(). Our program demonstrates an optional step that you can do first: It calls mysql_stmt_store_result(), which fetches the entire result set and buffers it in client memory. The advantage of doing this is that you can call mysql_stmt_num_rows() to find out how many rows are in the result set. The disadvantage is that it uses more memory on the client side.

The row-fetching loop involves calling mysql_stmt_fetch() until it returns a non-zero value. After each fetch, the variables associated with the parameter structures contain the column values for the current row.

Once all the rows have been fetched, a call to mysql_stmt_free_result() releases any memory associated with the result set.

At this point, select_rows() returns to the caller, which invokes mysql_stmt_close() to dispose of the prepared statement handler.

The preceding discussion provides a broad overview of the prepared statement interface and some of its key functions. The client library includes several other related functions; for more information, consult Appendix G.