6.2 C API Prepared Statement Data Structures (original) (raw)

MYSQL_STMT

This structure is a handler for a prepared statement. A handler is created by callingmysql_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 withmysql_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_bind_param() to define attributes for a query. (In the following discussion, treat any mention of statement parameters for prepared statements as also applying to query attributes.)
• For output, use MYSQL_BIND structures withmysql_stmt_bind_result() to bind buffers to result set columns, for use in fetching rows withmysql_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 threeMYSQL_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, seeSection 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 callmysql_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 callmysql_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 bechar for single-byte values or an integer type for larger values), you should also indicate whether the variable has theunsigned attribute by setting theis_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 aMYSQL_TIME structure.
    For guidelines about mapping between C types and SQL types and notes about type conversions, seeSection 6.2.1, “C API Prepared Statement Type Codes”, andSection 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, thebuffer_length value specifies the length of *buffer when used withmysql_stmt_bind_param() ormysql_stmt_bind_named_param() to specify input values, or the maximum number of output data bytes that can be fetched into the buffer when used withmysql_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 bymysql_stmt_execute().
  For output value binding, MySQL sets*length when you callmysql_stmt_fetch(). Themysql_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 isMYSQL_DATA_TRUNCATED,*length indicates the nontruncated length of the parameter value. In this case, the minimum of *length andbuffer_length indicates the actual length of the value.
    length is ignored for numeric and temporal data types because thebuffer_type value determines the length of the data value.
    If you must determine the length of a returned value before fetching it, seeSection 6.4.12, “mysql_stmt_fetch()”, for some strategies.
• bool *is_null
  This member points to a 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 specifyNULL values:
  • If your data values are alwaysNULL, useMYSQL_TYPE_NULL as thebuffer_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 = (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 bool variable. Set that variable's value to true or false appropriately between executions to indicate whether the corresponding data value is NULL orNOT 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.
• 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 isunsigned and false otherwise. For example, if you bind a signed char variable to buffer, specify a type code of MYSQL_TYPE_TINY and setis_unsigned to false. If you bind anunsigned 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 anint variable to supply a value for aBIGINT UNSIGNED column,is_unsigned should be false becauseint is a signed type. If you use anunsigned int variable to supply a value for a BIGINT column,is_unsigned should be true becauseunsigned 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.
• bool *error
  For output, set this member to point to abool variable to have truncation information for the parameter stored there after a row fetching operation. When truncation reporting is enabled,mysql_stmt_fetch() returnsMYSQL_DATA_TRUNCATED and*error is true in theMYSQL_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 callingmysql_options() with theMYSQL_REPORT_DATA_TRUNCATION option.

MYSQL_TIME

This structure is used to send and receiveDATE,TIME,DATETIME, andTIMESTAMP 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 aMYSQL_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.

Only those parts of a MYSQL_TIME structure that apply to a given type of temporal value are used. Theyear, month, andday elements are used forDATE,DATETIME, andTIMESTAMP values. Thehour, minute, andsecond elements are used forTIME,DATETIME, andTIMESTAMP values. SeeSection 3.6.4, “Prepared Statement Handling of Date and Time Values”.