9.1.6 C API Prepared Statement Function Overview

Note: The API for prepared statements is still subject to revision. This information is provided for early adopters, but please be aware that the API may change.

The functions available for prepared statement processing are summarised here and described in greater detail in a later section. See section 9.1.7 C API Prepared Statement Function Descriptions.

Call mysql_prepare() to prepare and initialise the statement handle, mysql_bind_param() to supply the parameter data, and mysql_execute() to execute the query. You can repeat the mysql_execute() by changing parameter values in the respective buffers supplied through mysql_bind_param().

If the query is a SELECT statement or any other query that produces a result set, mysql_prepare() will also return the result set metadata information in the form of a MYSQL_RES result set through mysql_prepare_result().

You can supply the result buffers using mysql_bind_result(), so that the mysql_fetch() will automatically return data to these buffers. This is row-by-row fetching.

You can also send the text or binary data in chunks to server using mysql_send_long_data(), by specifying the option is_long_data=1 or length=MYSQL_LONG_DATA or -2 in the MYSQL_BIND structure supplied with mysql_bind_param().

When statement execution has been completed, the statement handle must be closed 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_prepare_result(), you should also free it using mysql_free_result().

Execution Steps:

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

1. Call mysql_prepare() and pass it a string containing the SQL statement. For a successful prepare operation, mysql_prepare() returns a valid statement handle to the application.
2. If the query produces a result set, call mysql_prepare_result() 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_bind_param(). All parameters must be set. Otherwise, query execution will return an error or produce unexpected results.
4. Call mysql_execute() to execute the statement.
5. If the query produces a result set, bind the data buffers to use for retrieving the row values by calling mysql_bind_result().
6. Fetch the data into the buffers row by row by calling mysql_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_prepare() is called, the MySQL client/server protocol performs these actions:

• The server parses the query and sends the OK status back to the client by assigning a statement ID. It also sends total number of parameters, a column count, and its meta information if it is a result set oriented query. All syntax and semantics of the query 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. The client also allocates a statement handle with this ID and returns the handle to the application.

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

• The client uses the statement handle 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 query. If the query produces a result set, the server sends the data back to the client. Otherwise, it sends an OK status and total number of rows changed, deleted, or inserted.

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

• The client reads the data from the packet row by row 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.

You can get the statement error code, error message, and SQLSTATE value using mysql_stmt_errno(), mysql_stmt_error(), and mysql_stmt_sqlstate(), respectively.

User Comments

Add your own comment.