23.8.10 C API Prepared Statement Function Overview

The functions available for prepared statement processing are summarized here and described in greater detail in a later section. See Section 23.8.11, “C API Prepared Statement Function Descriptions”.

FunctionDescription
mysql_stmt_affected_rows()Returns the number of rows changed, deleted, or inserted by prepared UPDATE, DELETE, or INSERT statement
mysql_stmt_attr_get()Gets value of an attribute for a prepared statement
mysql_stmt_attr_set()Sets an attribute for a prepared statement
mysql_stmt_bind_param()Associates application data buffers with the parameter markers in a prepared SQL statement
mysql_stmt_bind_result()Associates application data buffers with columns in a result set
mysql_stmt_close()Frees memory used by a prepared statement
mysql_stmt_data_seek()Seeks to an arbitrary row number in a statement result set
mysql_stmt_errno()Returns the error number for the last statement execution
mysql_stmt_error()Returns the error message for the last statement execution
mysql_stmt_execute()Executes a prepared statement
mysql_stmt_fetch()Fetches the next row of data from a result set and returns data for all bound columns
mysql_stmt_fetch_column()Fetch data for one column of the current row of a result set
mysql_stmt_field_count()Returns the number of result columns for the most recent statement
mysql_stmt_free_result()Free the resources allocated to a statement handle
mysql_stmt_init()Allocates memory for a MYSQL_STMT structure and initializes it
mysql_stmt_insert_id()Returns the ID generated for an AUTO_INCREMENT column by a prepared statement
mysql_stmt_next_result()Returns/initiates the next result in a multiple-result execution
mysql_stmt_num_rows()Returns the row count from a buffered statement result set
mysql_stmt_param_count()Returns the number of parameters in a prepared statement
mysql_stmt_param_metadata()(Return parameter metadata in the form of a result set) Currently, this function does nothing
mysql_stmt_prepare()Prepares an SQL statement string for execution
mysql_stmt_reset()Resets the statement buffers in the server
mysql_stmt_result_metadata()Returns prepared statement metadata in the form of a result set
mysql_stmt_row_seek()Seeks to a row offset in a statement result set, using value returned from mysql_stmt_row_tell()
mysql_stmt_row_tell()Returns the statement row cursor position
mysql_stmt_send_long_data()Sends long data in chunks to server
mysql_stmt_sqlstate()Returns the SQLSTATE error code for the last statement execution
mysql_stmt_store_result()Retrieves a complete result set to the client

Call mysql_stmt_init() to create a statement handle, then mysql_stmt_prepare() to prepare the statement string, mysql_stmt_bind_param() to supply the parameter data, and mysql_stmt_execute() to execute the statement. You can repeat the mysql_stmt_execute() by changing parameter values in the respective buffers supplied through mysql_stmt_bind_param().

You can send text or binary data in chunks to server using mysql_stmt_send_long_data(). See Section 23.8.11.26, “mysql_stmt_send_long_data().

If the statement is a SELECT or any other statement that produces a result set, mysql_stmt_prepare() also returns the result set metadata information in the form of a MYSQL_RES result set through mysql_stmt_result_metadata().

You can supply the result buffers using mysql_stmt_bind_result(), so that the mysql_stmt_fetch() automatically returns data to these buffers. This is row-by-row fetching.

When statement execution has been completed, close the statement handle using mysql_stmt_close() so that all resources associated with it can be freed.

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().

Execution Steps

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

  1. Create a prepared statement handle 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. If the statement will produce a result set, call mysql_stmt_result_metadata() to obtain the result set metadata. This metadata is itself in the form of result set, albeit a separate one from the one that contains the rows returned by the query. The metadata result set indicates how many columns are in the result and contains information about each column.

  3. 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.

  4. Call mysql_stmt_execute() to execute the statement.

  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, by changing the parameter values and re-executing the statement.

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.