MySQL 5.7 C API Developer Guide

6.1 Overview of the C API Prepared Statement Interface

To prepare and execute a statement, an application follows these steps:

  1. Create a prepared statement handler with mysql_stmt_init(). To prepare the statement on the server, call mysql_stmt_prepare() and pass it a string containing the SQL statement.

  2. Set the values of any parameters using mysql_stmt_bind_param(). All parameters must be set. Otherwise, statement execution returns an error or produces unexpected results.

    If there are large text or binary data values to be sent, you can send them in chunks to the server using mysql_stmt_send_long_data().

  3. Call mysql_stmt_execute() to execute the statement.

  4. If the statement is a SELECT or any other statement that produces a result set, call mysql_stmt_result_metadata() if it is desired to obtain the result set metadata. This metadata is itself in the form of a MYSQL_RES result set, albeit a separate one from the one that contains the rows returned by the query. The metadata result set indicates the number of columns in the result and contains information about each one.

  5. If the statement produces a result set, bind the data buffers to use for retrieving the row values by calling mysql_stmt_bind_result().

  6. Fetch the data into the buffers row by row by calling mysql_stmt_fetch() repeatedly until no more rows are found.

  7. Repeat steps 3 through 6 as necessary. You can repeat the mysql_stmt_execute() to re-execute the statement by changing parameter values in the respective buffers supplied through mysql_stmt_bind_param().

  8. When statement execution has been completed, close the statement handler using mysql_stmt_close() so that all resources associated with it can be freed. At that point the handler becomes invalid and should no longer be used.

  9. If you obtained a SELECT statement's result set metadata by calling mysql_stmt_result_metadata(), you should also free the metadata using mysql_free_result().

When mysql_stmt_prepare() is called, the MySQL client/server protocol performs these actions:

When mysql_stmt_execute() is called, the MySQL client/server protocol performs these actions:

When mysql_stmt_fetch() is called, the MySQL client/server protocol performs these actions:

If an error occurs, you can get the statement error number, error message, and SQLSTATE code using mysql_stmt_errno(), mysql_stmt_error(), and mysql_stmt_sqlstate(), respectively.

Prepared Statement Logging

For prepared statements that are executed with the mysql_stmt_prepare() and mysql_stmt_execute() C API functions, the server writes Prepare and Execute lines to the general query log so that you can tell when statements are prepared and executed.

Suppose that you prepare and execute a statement as follows:

  1. Call mysql_stmt_prepare() to prepare the statement string "SELECT ?".

  2. Call mysql_stmt_bind_param() to bind the value 3 to the parameter in the prepared statement.

  3. Call mysql_stmt_execute() to execute the prepared statement.

As a result of the preceding calls, the server writes the following lines to the general query log:

Prepare  [1] SELECT ?
Execute  [1] SELECT 3

Each Prepare and Execute line in the log is tagged with a [N] statement identifier so that you can keep track of which prepared statement is being logged. N is a positive integer. If there are multiple prepared statements active simultaneously for the client, N may be greater than 1. Each Execute lines shows a prepared statement after substitution of data values for ? parameters.