20.6.9.2 C API Prepared Statement Type Conversions

Prepared statements transmit data between the client and server
using C language variables on the client side that correspond to
SQL values on the server side. If there is a mismatch between
the C variable type on the client side and the corresponding SQL
value type on the server side, MySQL performs implicit type
conversions in both directions.

MySQL knows the type code for the SQL value on the server side.
The buffer_type value in the
MYSQL_BIND structure indicates the type code
of the C variable that holds the value on the client side. The
two codes together tell MySQL what conversion must be performed,
if any. Here are some examples:

If you use MYSQL_TYPE_LONG with an
int variable to pass an integer value to
the server that is to be stored into a
FLOAT column, MySQL converts
the value to floating-point format before storing it.

If you fetch an SQL MEDIUMINT
column value, but specify a buffer_type
value of MYSQL_TYPE_LONGLONG and use a C
variable of type long long int as the
destination buffer, MySQL converts the
MEDIUMINT value (which
requires less than 8 bytes) for storage into the
long long int (an 8-byte variable).

If you fetch a numeric column with a value of 255 into a
char[4] character array and specify a
buffer_type value of
MYSQL_TYPE_STRING, the resulting value in
the array is a 4-byte string '255\0'.

MySQL returns DECIMAL values
as the string representation of the original server-side
value, which is why the corresponding C type is
char[]. For example,
12.345 is returned to the client as
'12.345'. If you specify
MYSQL_TYPE_NEWDECIMAL and bind a string
buffer to the MYSQL_BIND structure,
mysql_stmt_fetch() stores
the value in the buffer as a string without conversion. If
instead you specify a numeric variable and type code,
mysql_stmt_fetch() converts
the string-format DECIMAL
value to numeric form.

For the MYSQL_TYPE_BIT type code,
BIT values are returned into
a string buffer, which is why the corresponding C type is
char[]. The value represents a bit string
that requires interpretation on the client side. To return
the value as a type that is easier to deal with, you can
cause the value to be cast to integer using either of the
following types of expressions:

SELECT bit_col + 0 FROM t
SELECT CAST(bit_col AS UNSIGNED) FROM t

To retrieve the value, bind an integer variable large enough
to hold the value and specify the appropriate corresponding
integer type code.

To determine whether output string values in a result set
returned from the server contain binary or nonbinary data, check
whether the charsetnr value of the result set
metadata is 63 (see Section 20.6.5, “C API Data Structures”). If
so, the character set is binary, which
indicates binary rather than nonbinary data. This enables you to
distinguish BINARY from
CHAR,
VARBINARY from
VARCHAR, and the
BLOB types from the
TEXT types.

If you cause the max_length member of the
MYSQL_FIELD column metadata structures to be
set (by calling
mysql_stmt_attr_set()), be aware
that the max_length values for the result set
indicate the lengths of the longest string representation of the
result values, not the lengths of the binary representation.
That is, max_length does not necessarily
correspond to the size of the buffers needed to fetch the values
with the binary protocol used for prepared statements. Choose
the size of the buffers according to the types of the variables
into which you fetch the values. For example, a
TINYINT column containing the value -128
might have a max_length value of 4. But the
binary representation of any TINYINT value
requires only 1 byte for storage, so you can supply a
signed char variable in which to store the
value and set is_unsigned to indicate that
values are signed.