The Ultimate Guide to High Availability with MariaDB

MYSQL_BIND

The MYSQL_BIND structure is used to bind parameters (which will be sent to the 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 the types and definitions section.
  • 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: Pointer to a 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 10.2 or above. It allows clients 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 (the default) the application binds up to 3 arrays to a column: a data array, a length array and optionally an indicator array.

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

  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 the array_size parameter.

column_wise_binding

An example for column wise binding can be found here.

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 An example for row wise binding can be found here.

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.