Documentation Home
MySQL 9.4 C API Developer Guide
Download this Manual
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb


MySQL 9.4 C API Developer Guide  / C API Prepared Statement Interface  /  Overview of the C API Prepared Statement Interface

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 withmysql_stmt_init(). To prepare the statement on the server, callmysql_stmt_prepare() and pass it a string containing the SQL statement.

  2. Set the values of any parameters usingmysql_stmt_bind_param() ormysql_stmt_bind_named_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 usingmysql_stmt_send_long_data().

  3. Callmysql_stmt_execute() to execute the statement.

  4. If the statement is aSELECT or any other statement that produces a result set, callmysql_stmt_result_metadata() if it is desired to obtain the result set metadata. This metadata is itself in the form of aMYSQL_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 callingmysql_stmt_bind_result().

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

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

  8. When statement execution has been completed, close the statement handler usingmysql_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 aSELECT statement's result set metadata by callingmysql_stmt_result_metadata(), you should also free the metadata usingmysql_free_result().

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

  • The server parses the statement and sends the okay status back to the client by assigning a statement ID. It also sends total number of parameters, a column count, and its metadata if it is a result set oriented statement. All syntax and semantics of the statement are checked by the server during this call.

  • The client uses this statement ID for the further operations, so that the server can identify the statement from among its pool of statements.

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

  • The client uses the statement handler and sends the parameter data to the server.

  • The server identifies the statement using the ID provided by the client, replaces the parameter markers with the newly supplied data, and executes the statement. If the statement produces a result set, the server sends the data back to the client. Otherwise, it sends an okay status and the number of rows changed, deleted, or inserted.

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

  • The client reads the data from the current row of the result set and places it into the application data buffers by doing the necessary conversions. If the application buffer type is same as that of the field type returned from the server, the conversions are straightforward.

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

Prepared Statement Logging

For prepared statements that are executed with themysql_stmt_prepare() andmysql_stmt_execute() C API functions, the server writesPrepare andExecute 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. Callmysql_stmt_prepare() to prepare the statement string"SELECT ?".

  2. Callmysql_stmt_bind_param() ormysql_stmt_bind_named_param() to bind the value3 to the parameter in the prepared statement.

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

EachPrepare andExecute 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. EachExecute lines shows a prepared statement after substitution of data values for? parameters.