6.2 C API Prepared Statement Data Structures

6.2.1 C API Prepared Statement Type Codes

6.2.2 C API Prepared Statement Type Conversions

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 6.4.10, “mysql_stmt_execute()”, and Section 6.4.11, “mysql_stmt_fetch()”.

• MYSQL_STMT

  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.

• MYSQL_BIND

  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 6.2.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 6.2.1, “C API Prepared Statement Type Codes”, and Section 6.2.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 6.4.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.

• 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

  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 Section 3.6.3, “Prepared Statement Handling of Date and Time Values”.