MySQL 5.6 C API Developer Guide
Prepared statements use several data structures:
To obtain a statement handler, pass a MYSQL
connection handler to
mysql_stmt_init()
, which
returns a pointer to a MYSQL_STMT
data
structure. This structure is used for further operations with
the statement. To specify the statement to prepare, pass the
MYSQL_STMT
pointer and the statement string
to mysql_stmt_prepare()
.
To provide input parameters for a prepared statement, set up
MYSQL_BIND
structures and pass them to
mysql_stmt_bind_param()
. To
receive output column values, set up
MYSQL_BIND
structures and pass them to
mysql_stmt_bind_result()
.
The MYSQL_TIME
structure is used to
transfer temporal data in both directions.
The following discussion describes the prepared statement data types in detail. For examples that show how to use them, see Section 11.10, “mysql_stmt_execute()”, and Section 11.11, “mysql_stmt_fetch()”.
This structure is a handler for a prepared statement. A
handler is created by calling
mysql_stmt_init()
, which
returns a pointer to a MYSQL_STMT
. The
handler is used for all subsequent operations with the
statement until you close it with
mysql_stmt_close()
, at which
point the handler becomes invalid and should no longer be
used.
The MYSQL_STMT
structure has no members
intended for application use. Applications should not try to
copy a MYSQL_STMT
structure. There is no
guarantee that such a copy will be usable.
Multiple statement handlers can be associated with a single connection. The limit on the number of handlers depends on the available system resources.
This structure is used both for statement input (data values sent to the server) and output (result values returned from the server):
For input, use MYSQL_BIND
structures
with
mysql_stmt_bind_param()
to
bind parameter data values to buffers for use by
mysql_stmt_execute()
.
For output, use MYSQL_BIND
structures
with
mysql_stmt_bind_result()
to bind buffers to result set columns, for use in fetching
rows with
mysql_stmt_fetch()
.
To use a MYSQL_BIND
structure, zero its
contents to initialize it, then set its members appropriately.
For example, to declare and initialize an array of three
MYSQL_BIND
structures, use this code:
MYSQL_BIND bind[3]; memset(bind, 0, sizeof(bind));
The MYSQL_BIND
structure contains the
following members for use by application programs. For several
of the members, the manner of use depends on whether the
structure is used for input or output.
enum enum_field_types buffer_type
The type of the buffer. This member indicates the data
type of the C language variable bound to a statement
parameter or result set column. For input,
buffer_type
indicates the type of the
variable containing the value to be sent to the server.
For output, it indicates the type of the variable into
which a value received from the server should be stored.
For permissible buffer_type
values, see
Section 9.1, “C API Prepared Statement Type Codes”.
void *buffer
A pointer to the buffer to be used for data transfer. This is the address of a C language variable.
For input, buffer
is a pointer to the
variable in which you store the data value for a statement
parameter. When you call
mysql_stmt_execute()
,
MySQL use the value stored in the variable in place of the
corresponding parameter marker in the statement (specified
with ?
in the statement string).
For output, buffer
is a pointer to the
variable in which to return a result set column value.
When you call
mysql_stmt_fetch()
, MySQL
stores a column value from the current row of the result
set in this variable. You can access the value when the
call returns.
To minimize the need for MySQL to perform type conversions between C language values on the client side and SQL values on the server side, use C variables that have types similar to those of the corresponding SQL values:
For numeric data types, buffer
should point to a variable of the proper numeric C
type. For integer variables (which can be
char
for single-byte values or an
integer type for larger values), you should also
indicate whether the variable has the
unsigned
attribute by setting the
is_unsigned
member, described
later.
For character (nonbinary) and binary string data
types, buffer
should point to a
character buffer.
For date and time data types,
buffer
should point to a
MYSQL_TIME
structure.
For guidelines about mapping between C types and SQL types and notes about type conversions, see Section 9.1, “C API Prepared Statement Type Codes”, and Section 9.2, “C API Prepared Statement Type Conversions”.
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_stmt_bind_param()
to
specify input values, or the maximum number of output data
bytes that can be fetched into the buffer when used with
mysql_stmt_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, set
*length
to indicate the actual length
of the parameter value stored in
*buffer
. This is used by
mysql_stmt_execute()
.
For output value binding, MySQL sets
*length
when you call
mysql_stmt_fetch()
. The
mysql_stmt_fetch()
return
value determines how to interpret the length:
If the return value is 0, *length
indicates the actual length of the parameter value.
If the return value is
MYSQL_DATA_TRUNCATED
,
*length
indicates the nontruncated
length of the parameter value. In this case, the
minimum of *length
and
buffer_length
indicates the actual
length of the value.
length
is ignored for numeric and
temporal data types because the
buffer_type
value determines the length
of the data value.
If you must determine the length of a returned value before fetching it, see Section 11.11, “mysql_stmt_fetch()”, for some strategies.
my_bool *is_null
This member points to a my_bool
variable that is true if a value is
NULL
, false if it is not
NULL
. For input, set
*is_null
to true to indicate that you
are passing a NULL
value as a statement
parameter.
is_null
is a
pointer to a boolean scalar, not a
boolean scalar, to provide flexibility in how you specify
NULL
values:
If your data values are always
NULL
, use
MYSQL_TYPE_NULL
as the
buffer_type
value when you bind the
column. The other MYSQL_BIND
members, including is_null
, do not
matter.
If your data values are always NOT
NULL
, set is_null = (my_bool*)
0
, and set the other members appropriately
for the variable you are binding.
In all other cases, set the other members
appropriately and set is_null
to
the address of a my_bool
variable.
Set that variable's value to true or false
appropriately between executions to indicate whether
the corresponding data value is
NULL
or NOT
NULL
, respectively.
For output, when you fetch a row, MySQL sets the value
pointed to by is_null
to true or false
according to whether the result set column value returned
from the statement is or is not NULL
.
my_bool is_unsigned
This member applies for C variables with data types that
can be unsigned
(char
, short int
,
int
, long long int
).
Set is_unsigned
to true if the variable
pointed to by buffer
is
unsigned
and false otherwise. For
example, if you bind a signed char
variable to buffer
, specify a type code
of MYSQL_TYPE_TINY
and set
is_unsigned
to false. If you bind an
unsigned char
instead, the type code is
the same but is_unsigned
should be
true. (For char
, it is not defined
whether it is signed or unsigned, so it is best to be
explicit about signedness by using signed
char
or unsigned char
.)
is_unsigned
applies only to the C
language variable on the client side. It indicates nothing
about the signedness of the corresponding SQL value on the
server side. For example, if you use an
int
variable to supply a value for a
BIGINT UNSIGNED
column,
is_unsigned
should be false because
int
is a signed type. If you use an
unsigned int
variable to supply a value
for a BIGINT
column,
is_unsigned
should be true because
unsigned int
is an unsigned type. MySQL
performs the proper conversion between signed and unsigned
values in both directions, although a warning occurs if
truncation results.
my_bool *error
For output, set this member to point to a
my_bool
variable to have truncation
information for the parameter stored there after a row
fetching operation. When truncation reporting is enabled,
mysql_stmt_fetch()
returns
MYSQL_DATA_TRUNCATED
and
*error
is true in the
MYSQL_BIND
structures for parameters in
which truncation occurred. Truncation indicates loss of
sign or significant digits, or that a string was too long
to fit in a column. Truncation reporting is enabled by
default, but can be controlled by calling
mysql_options()
with the
MYSQL_REPORT_DATA_TRUNCATION
option.
This structure is used to send and receive
DATE
,
TIME
,
DATETIME
, and
TIMESTAMP
data directly to and
from the server. Set the buffer
member to
point to a MYSQL_TIME
structure, and set
the buffer_type
member of a
MYSQL_BIND
structure to one of the temporal
types (MYSQL_TYPE_TIME
,
MYSQL_TYPE_DATE
,
MYSQL_TYPE_DATETIME
,
MYSQL_TYPE_TIMESTAMP
).
The MYSQL_TIME
structure contains the
members listed in the following table.
Member | Description |
---|---|
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 indicating whether the time is negative |
unsigned long second_part |
The fractional part of the second in microseconds |
Only those parts of a MYSQL_TIME
structure
that apply to a given type of temporal value are used. The
year
, month
, and
day
elements are used for
DATE
,
DATETIME
, and
TIMESTAMP
values. The
hour
, minute
, and
second
elements are used for
TIME
,
DATETIME
, and
TIMESTAMP
values. See
Chapter 17, C API Prepared Statement Handling of Date and Time Values.