(mysql.info) c-api-prepared-statement-function-overview
Info Catalog
(mysql.info) c-api-prepared-statement-datatypes
(mysql.info) c
(mysql.info) c-api-prepared-statement-functions
22.2.6 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
c-api-prepared-statement-functions.
*Function* *Description*
*mysql_stmt_affected_rows()*Returns the number of rows changes, deleted, or
inserted by prepared `UPDATE', `DELETE', or
`INSERT' statement.
*mysql_stmt_attr_get()* Get 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 the result set.
*mysql_stmt_close()* Frees memory used by 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 the prepared statement.
*mysql_stmt_fetch()* Fetches the next row of data from the result set
and returns data for all bound columns.
*mysql_stmt_fetch_column()*Fetch data for one column of the current row of
the 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 the statement
handle.
*mysql_stmt_init()* Allocates memory for `MYSQL_STMT' structure and
initializes it.
*mysql_stmt_insert_id()*Returns the ID generated for an `AUTO_INCREMENT'
column by prepared statement.
*mysql_stmt_num_rows()* Returns total rows from the statement buffered
result set.
*mysql_stmt_param_count()*Returns the number of parameters in a prepared
SQL statement.
*mysql_stmt_param_metadata()*Return parameter metadata in the form of a
result set.
*mysql_stmt_prepare()* Prepares an SQL string for execution.
*mysql_stmt_reset()* Reset 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 the complete result set to the client.
Call `mysql_stmt_init()' to create a statement handle, then
`mysql_stmt_prepare' to prepare it, `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()'.
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.
You can also send the text or binary data in chunks to server using
`mysql_stmt_send_long_data()'. See mysql-stmt-send-long-data.
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_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 `msyql_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 produces 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:
* 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.
When `mysql_stmt_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 statement. If the statement produces a
result set, the server sends the data back to the client.
Otherwise, it sends an okay status and total number of rows
changed, deleted, or inserted.
When `mysql_stmt_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.
If an error occurs, you can get the statement error code, error
message, and SQLSTATE value 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.
Version notes: `Prepare' lines are displayed without `[N]' before MySQL
4.1.10. `Execute' lines are not displayed at all before MySQL 4.1.10.
Info Catalog
(mysql.info) c-api-prepared-statement-datatypes
(mysql.info) c
(mysql.info) c-api-prepared-statement-functions
automatically generated byinfo2html