Rechercher dans le manuel MySQL
28.7.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 anint
variable to pass an integer value to the server that is to be stored into aFLOAT
column, MySQL converts the value to floating-point format before storing it.If you fetch an SQL
MEDIUMINT
column value, but specify abuffer_type
value ofMYSQL_TYPE_LONGLONG
and use a C variable of typelong long int
as the destination buffer, MySQL converts theMEDIUMINT
value (which requires less than 8 bytes) for storage into thelong 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 abuffer_type
value ofMYSQL_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 ischar[]
. For example,12.345
is returned to the client as'12.345'
. If you specifyMYSQL_TYPE_NEWDECIMAL
and bind a string buffer to theMYSQL_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-formatDECIMAL
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 ischar[]
. 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.
Before binding variables to the MYSQL_BIND
structures that are to be used for fetching column values, you
can check the type codes for each column of the result set. This
might be desirable if you want to determine which variable types
would be best to use to avoid type conversions. To get the type
codes, call
mysql_stmt_result_metadata()
after executing the prepared statement with
mysql_stmt_execute()
. The
metadata provides access to the type codes for the result set as
described in Section 28.7.11.23, “mysql_stmt_result_metadata()”, and
Section 28.7.5, “C API Data Structures”.
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 28.7.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.
Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. For more information, see Section 8.10.3, “Caching of Prepared Statements and Stored Programs”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-c-api-prepared-statement-type-conversions.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.