Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

25.2.5. C API Prepared Statement Data types

Prepared statements mainly use the MYSQL_STMT and MYSQL_BIND data structures. A third structure, MYSQL_TIME, is used to transfer temporal data.

  • MYSQL_STMT

    This structure represents a prepared statement. A statement is created by calling mysql_stmt_init(), which returns a statement handle (that is, a pointer to a MYSQL_STMT). The handle is used for all subsequent statement-related functions until you close it with mysql_stmt_close().

    The MYSQL_STMT structure has no members that are for application use. Also, you should not try to make a copy of a MYSQL_STMT structure. There is no guarantee that such a copy will be usable.

    Multiple statement handles can be associated with a single connection. The limit on the number of handles 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, it is used with mysql_stmt_bind_param() to bind parameter data values to buffers for use by mysql_stmt_execute(). For output, it is used with mysql_stmt_bind_result() to bind result set buffers for use in fetching rows with mysql_stmt_fetch().

    The MYSQL_BIND structure contains the following members for use by application programs. Each is used both for input and for output, although sometimes for different purposes depending on the direction of data transfer.

    • enum enum_field_types buffer_type

      The type of the buffer. The allowable buffer_type values are listed later in this section. For input, buffer_type indicates what type of value you are binding to a statement parameter. For output, it indicates what type of value you expect to receive in a result buffer.

    • void *buffer

      For input, this is a pointer to the buffer in which a statement parameter's data value is stored. For output, it is a pointer to the buffer in which to return a result set column value. For numeric data types, buffer should point to a variable of the proper C type. (If you are associating the variable with a column that has the UNSIGNED attribute, the variable should be an unsigned C type. Indicate whether the variable is signed or unsigned by using the is_unsigned member, described later in this list.) For date and time data types, buffer should point to a MYSQL_TIME structure. For character and binary string data types, buffer should point to a character buffer.

    • 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(), or the maximum number of 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, length points to an unsigned long variable that indicates the length of the parameter value stored in *buffer; this is used by mysql_stmt_execute(). For output value binding, mysql_stmt_fetch() places the length of the column value that is returned into the variable that length points to.

      length is ignored for numeric and temporal data types because the length of the data value is determined by the buffer_type value.

    • 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. For output, this value is set to true after you fetch a row if the result set column value returned from the statement is NULL.

      is_null is a pointer to a boolean rather than a boolean scalar so that it can be used in the following way:

      • If your data values are always NULL, use MYSQL_TYPE_NULL to bind the column.

      • If your data values are always NOT NULL, set is_null = (my_bool*) 0.

      • In all other cases, you should set is_null to the address of a my_bool variable and change that variable's value appropriately between executions to indicate whether data values are NULL or NOT NULL.

    • my_bool is_unsigned

      This member is used for integer types. (These correspond to the MYSQL_TYPE_TINY, MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG, and MYSQL_TYPE_LONGLONG type codes.) is_unsigned should be set to true for unsigned types and false for signed types.

    • my_bool error

      For output, this member is used output to report data truncation errors. Truncation reporting must be enabled by calling mysql_options() with the MYSQL_REPORT_DATA_TRUNCATION option. When 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.

    To use a MYSQL_BIND structure, you should zero its contents to initialize it, and then set the members just described 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));
    
  • MYSQL_TIME

    This structure is used to send and receive DATE, TIME, DATETIME, and TIMESTAMP data directly to and from the server. This is done by setting the buffer_type member of a MYSQL_BIND structure to one of the temporal types, and setting the buffer member to point to a MYSQL_TIME structure.

    The MYSQL_TIME structure contains the following members:

    • 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 to indicate whether the time is negative.

    • unsigned long second_part

      The fractional part of the second. This member currently is unused.

    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 25.2.10, “C API Handling of Date and Time Values”.

The following table shows the allowable values that may be specified in the buffer_type member of MYSQL_BIND structures. The table also shows those SQL types that correspond most closely to each buffer_type value, and, for numeric and temporal types, the corresponding C type.

buffer_type Value SQL Type C Type
MYSQL_TYPE_BIT BIT
MYSQL_TYPE_TINY TINYINT char
MYSQL_TYPE_SHORT SMALLINT short int
MYSQL_TYPE_LONG INT int
MYSQL_TYPE_LONGLONG BIGINT long long int
MYSQL_TYPE_FLOAT FLOAT float
MYSQL_TYPE_DOUBLE DOUBLE double
MYSQL_TYPE_TIME TIME MYSQL_TIME
MYSQL_TYPE_DATE DATE MYSQL_TIME
MYSQL_TYPE_DATETIME DATETIME MYSQL_TIME
MYSQL_TYPE_TIMESTAMP TIMESTAMP MYSQL_TIME
MYSQL_TYPE_STRING CHAR/BINARY  
MYSQL_TYPE_VAR_STRING VARCHAR/VARBINARY  
MYSQL_TYPE_TINY_BLOB TINYBLOB/TINYTEXT  
MYSQL_TYPE_BLOB BLOB/TEXT  
MYSQL_TYPE_MEDIUM_BLOB MEDIUMBLOB/MEDIUMTEXT  
MYSQL_TYPE_LONG_BLOB LONGBLOB/LONGTEXT  

Implicit type conversion may be performed in both directions.


 
 
  Published under the terms of the GNU General Public License Design by Interspire