6.2.2 C API Prepared Statement Type Conversions (original) (raw)

6.2.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 theMYSQL_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 a buffer_type value of MYSQL_TYPE_LONGLONG and use a C variable of type long 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 achar[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 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 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, callmysql_stmt_result_metadata() after executing the statement withmysql_stmt_execute(). The metadata provides access to the type codes for the result set as described in Section 6.4.23, “mysql_stmt_result_metadata()”, andSection 5.2, “C API Basic 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 5.2, “C API Basic Data Structures”). If so, the character set is binary, which indicates binary rather than nonbinary data. This enables you to distinguish BINARY fromCHAR,VARBINARY fromVARCHAR, and theBLOB types from theTEXT types.

If you cause the max_length member of theMYSQL_FIELD column metadata structures to be set (by callingmysql_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, aTINYINT 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 asigned 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, seeCaching of Prepared Statements and Stored Programs.