MYSQL_BIND

You are viewing an old version of this article. View the current version here.

MYSQL_BIND structure is used to bind parameters (which will be sent to server) and result sets (output sent from server to client). The MYSQL_BIND structure is bound with mysql_stmt_bind_param() or mysql_stmt_bind_result() to a prepared statement.

Members of MYSQL_BIND structure

  • enum enum_field_types field_type: Type of the buffer for in- or output. For a complete list of types see section types and definitions.
  • void buffer: Address of a variable, array or structure used for data transfer.
  • unsigned long buffer_length: Size of buffer in bytes.
  • unsigned long * length: Pointer to a length variable for output or array of length elements for input (array binding).
  • my_bool * error: Pointer to an error variable for output.
  • my_bool * is_null: Ponter to an null indicator for output.
  • char * u.indicator: Array of indicator variables for input (array binding)
  • my_bool is_unsigned: set when numeric data type is unsigned

Array binding

Array binding for bulk insert/updates was introduced with Connector/C 3.0 and requires MariaDB Server 10.2 or above. It allows client to control the number of rows that will be physically transferred between the server and the client in one logical bind or fetch. This can greatly improve the performance of many applications by trading buffer space for time (network traffic) and is a better and more secure alternative to LOAD DATA LOCAL INFILE, especially when the data will be generated within application.

Indicator variables

Indicator variables are used to represent special semantics like NULL or DEFAULT values.

Indicator variableDescription
STMT_INDICATOR_NTSString is null terminated
STMT_INDICATOR_NONENo semantics
STMT_INDICATOR_NULLNULL value
STMT_INDICATOR_DEFAULTuse columns default value
STMT_INDICATOR_IGNORESkip update of column

Column wise binding

When using column wise binding (default) the application binds up to 3 arrays to a column: A data array, a length array and optional an indicator array.

The number of rows has to be set by calling mysql_stmt_attr_set() with option STMT_ATTR_ARRAY_SIZE:

  unsigned int array_size= 100;
  mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, array_size);

Each array contains as many elements as specified in array_size parameter.

column_wise_binding

Row wise binding

When using row wise binding the application binds up to 3 elements of a structure to a column: A data element, a length element and an optional indicator element.

The application declares the size of the structure with the STMT_ATTR_ROW_SIZE attribute and binds the address of each member in the first element of the array:

unsigned int row_size= sizeof(struct my_data);
mysql_stmt_attr_set(stmt, STMT_ATTR_ROW_SIZE, &row_size);

Connector/C can now calculate the address of the data for a particular row and column as address= bind_address + row_nr * row_size where rows are numbered from 0 to size of rowset - 1.

If row_size is zero, column wise binding will be used instead. row_wise_binding

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.