C API Prepared Statements
-------------------------
to
execute a statement more than once. The statement is first parsed to
prepare it for execution. Then it is executed one or more times at a
later time, using the statement handle returned by the prepare function.
it is executed. Prepared execution also can provide a reduction of
network traffic because for each execution of the prepared statement,
it is necessary only to send the data for the parameters.
Another advantage of prepared statements is that it uses a binary
protocol that makes data transfer between client and server more
efficient. Prepared statements also can support input and output
binding for multiple query execution.
C API Prepared Statement Datatypes
----------------------------------
*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.
Prepared statements mainly use the `MYSQL_STMT' and `MYSQL_BIND' data
structures. A third structure, `MYSQL_TIME', is used to transfer
temporal data.
The handle is used
for all subsequent statement-related functions.
The `MYSQL_STMT' structure has no members that are for application
use.
Multiple statement handles can be associated with a single
connection. The limit on the number of handles depends on the
available system resources.
`MYSQL_BIND'
This structure is used both for query input (data values sent to
the server) and output (result values returned from the server).
For input, it is used with `mysql_bind_param()' to bind parameter
data values to buffers for use by `mysql_execute()'. For output,
it is used with `mysql_bind_result()' to bind result set buffers
for use in fetching rows with `mysql_fetch()'.
The `MYSQL_BIND' structure contains the following members for use
by application programs. Each is used both for input and for
output, though sometimes for different purposes depending on the
direction of data transfer.
`enum enum_field_types buffer_type'
The type of the buffer. The allowable `buffer_type' values
are listed later in this section. For input, `buffer_type'
indicates what type of value you are binding to a query
parameter. For output, it indicates what type of value you
expect to receive in a result buffer.
`void *buffer'
For input, this is a pointer to the buffer in which a query
parameter's data value is stored. For output, it is a pointer
to the buffer in which to return a result set column value.
For numeric column types, `buffer' should point to a variable
of the proper C type. (If you are associating the variable
with a column that has the `UNSIGNED' attribute, the variable
should be an `unsigned' C type.) For date and time column
types, `buffer' should point to a `MYSQL_TIME' structure.
For character and binary string column types, `buffer' should
point to a character buffer.
`unsigned long buffer_length'
The actual size of `*buffer' in bytes. This indicates the
maximum amount of data that can be stored in the buffer. For
character and binary C data, the `buffer_length' value
specifies the length of `*buffer' when used with
`mysql_bind_param()', or the maximum number of data bytes
that can be fetched into the buffer when used with
`mysql_bind_result()'.
`unsigned long *length'
A pointer to an `unsigned long' variable that indicates the
actual number of bytes of data stored in `*buffer'. `length'
is used for character or binary C data. For input parameter
data binding, `length' points to an `unsigned long' variable
that indicates the length of the parameter value stored in
`*buffer'; this is used by `mysql_execute()'. If `length' is
a null pointer, the protocol assumes that all character and
binary data are null-terminated. For output value binding,
`mysql_fetch()' places the length of the column value that is
returned into the variable that `length' points to.
`length' is ignored for numeric and temporal datatypes
because the length of the data value is determined by the
`buffer_type' value.
that you are passing a `NULL'
value as a query parameter. For output, this value will be
set to true after you fetch a row if the result value
returned from the query is `NULL'.
of a `MYSQL_BIND'
structure to one of the temporal types, and setting the `buffer'
member to point to a `MYSQL_TIME' structure.
The `MYSQL_TIME' structure contains the following members:
`unsigned int year'
The year.
`unsigned int month'
The month of the year.
`unsigned int day'
The day of the month.
`unsigned int hour'
The hour of the day.
`unsigned int minute'
The minute of the hour.
`unsigned int second'
The second of the minute.
`my_bool neg'
A boolean flag to indicate whether the time is negative.
`unsigned long second_part'
The fractional part of the second. This member currently is
unused.
values.
The `hour', `minute', and `second' elements are used for `TIME',
`DATETIME', and `TIMESTAMP' values. *Note C API date handling::.
`buffer_type' value, and, for numeric and temporal types, the
corresponding C type.
`INT' `long int'
`MYSQL_TYPE_LONGLONG' `BIGINT' `long long int'
`MYSQL_TYPE_FLOAT' `FLOAT' `float'
`MYSQL_TYPE_DOUBLE' `DOUBLE' `double'
`MYSQL_TYPE_TIME' `TIME' `MYSQL_TIME'
`MYSQL_TYPE_DATE' `DATE' `MYSQL_TIME'
`MYSQL_TYPE_DATETIME' `DATETIME' `MYSQL_TIME'
`MYSQL_TYPE_TIMESTAMP' `TIMESTAMP' `MYSQL_TIME'
`MYSQL_TYPE_STRING' `CHAR'
`MYSQL_TYPE_VAR_STRING' `VARCHAR'
`MYSQL_TYPE_TINY_BLOB' `TINYBLOB/TINYTEXT'
`MYSQL_TYPE_BLOB' `BLOB/TEXT'
`MYSQL_TYPE_MEDIUM_BLOB' `MEDIUMBLOB/MEDIUMTEXT'
`MYSQL_TYPE_LONG_BLOB' `LONGBLOB/LONGTEXT'
Implicit type conversion may be performed in both directions.
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.
*Note C API Prepared statement functions::.
*Function* *Description*
*mysql_prepare()* Prepares an SQL string for execution.
*mysql_param_count()* Returns the number of parameters in a prepared
SQL statement.
*mysql_get_metadata()* Returns prepared statement metadata in the form
of a result set.
*mysql_bind_param()* Associates application data buffers with the
parameter markers in a prepared SQL statement.
*mysql_execute()* Executes the prepared statement.
*mysql_stmt_affected_rows()* Returns the number of rows changes, deleted, or
inserted by the last `UPDATE', `DELETE', or
`INSERT' query.
*mysql_bind_result()* Associates application data buffers with
columns in the result set.
*mysql_stmt_store_result()* Retrieves the complete result set to the client.
*mysql_stmt_data_seek()* Seeks to an arbitrary row number in a statement
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_num_rows()* Returns total rows from the statement buffered
result set.
*mysql_fetch()* Fetches the next row of data from the result
set and returns data for all bound columns.
*mysql_stmt_close()* Frees memory used by prepared statement.
*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_sqlstate()* Returns the SQLSTATE error code for the last
statement execution.
*mysql_send_long_data()* Sends long data in chunks to server.
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_get_metadata()'.
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_get_metadata()', 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_get_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_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.
[Назад] [Содержание] [Вперед]
| Главная |