To achieve better performance we need to support bulk operations: Instead of executing a prepared statement n times it shold be possible to bind an array of size N and execute the statement once.
Since the length of an array element might change, an array element could be a NULL value or the column default should be used we need to introduce indicator variables. An Indicator can have the following values:
Indicator variable arrays will be stored in the MYSQL_BIND structure.
if send_types_to_server && num_params > 0
if (indicator variable set)
The function mysql_stmt_attr_set should support the following attributes:
STMT_ATTR_ARRAY_SIZE (specifies the size of bound array)
STMT_ATTR_BIND_TYPE (specifies the type of binding: column or row wise binding)
The API will now also support row wise binding, where the application defines the structure containing elements for each column. The application declares the size of the structure to the driver with the SQL_ATTR_BIND_TYPE statement attribute and binds the address of each member Thus, the connector can calculate the address of the data for a particular row and column as
Address = Bound Address + (Row Number * Structure Size)
In case the structure contains non fixed length members like char * (which are implicitly allocated) the address of this member has to be specified. BIND_IS_PTR flag in bind.flags needs to be set to indicate this special case.
We need to check if the server supports bulk operations, so we need an additional define
Open tables only in the beginning of the batch (the array) processing. (Limitation: all data should be in one packet i.e. less then max allowed packet).
No need full cleanup for Items between executions for different bindings of the batch (array) (Especially for Item_field: tables are opened and can't change).
Especially popular statement types could be optimized like multi-value INSERT, i.e. short loop over all parameters after all check and preparation/optimization to execute (SELECT-like could use subquery execution mechanism).
Bulk operations cannot be used in combination with mysql_stmt_send_long_data. See also Long Data and SQLSetPos and SQLBulkOperations