23.8.9 C API Prepared Statement Data Structures

To obtain a statement handle, 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().

This structure is a handle for a prepared statement. A handle
is created by calling
mysql_stmt_init(), which
returns a pointer to a MYSQL_STMT. The
handle is used for all subsequent operations with the
statement until you close it with
mysql_stmt_close(), at which
point the handle becomes invalid.

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 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 statement input (data values
sent to the server) and output (result values returned from
the server):

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 23.8.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.

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().

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.

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.

MYSQL_TIME

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 (unused before MySQL
5.6.4)