WL#348: MyODBC 3.51 - Batch Parameter Processing(Arrays of Parameters)

Status: On-Hold   —   Priority: High

Note: This functionality is present in 5.1 now. So possibly this task is a 
matter of back-porting it to 3.51 driver. Currently we focus on 5.1 development 
and think about deprecating 3.51.

Lawrins input on this:

WL#348(parameters arrays) has been implemented in the 5.1. But it is not easy to 
downmerge merge that feature as it uses descriptors structures implemented in 
the 5.1 only. I'm  not even sure such merge is affordable. As the intention is 
to make it a last 3.51 release we need to decide on that.

Support of Batch or Array Parameter processing in MyODBC 3.51 Driver:
Current versions only supports one level of parameters, and we need extend this 
to allow 'n' parameters to be supplied using a single call.

The logic here is similar to that of bulk inserts, where we use row param 
buffers for the batch processing, and we need to implement the same for input 
parameter processing.

To use arrays of parameters, the application calls SQLSetStmtAttr with an 
Attribute argument of SQL_ATTR_PARAMSET_SIZE to specify the number of sets of 
parameters. It calls SQLSetStmtAttr with an Attribute argument of 
SQL_ATTR_PARAMS_PROCESSED_PTR to specify the address of a variable in which the 
driver can return the number of sets of parameters processed, including error 
sets. It calls SQLSetStmtAttr with an Attribute argument of 
SQL_ATTR_PARAM_STATUS_PTR to point to an array in which to return status 
information for each row of parameter values. The driver stores these addresses 
in the structure it maintains for the statement.

Note:  In ODBC 2.x, SQLParamOptions was called to specify multiple values for a 
parameter. In ODBC 3.x, the call to SQLParamOptions has been replaced by calls 
to SQLSetStmtAttr to set the SQL_ATTR_PARAMSET_SIZE and 

Example : 
Suppose user wants to insert 10 rows of data using the single insert statement 
with varying data in a single execution.

Here is the simple steps:

SQLINTEGER id[10]={1,2,3,4,5,6,7,8,9,10};
SQLCHAR    name10][20]={'mysql1','mysql2',..'mysql10'};

- Prepare the insert statement using SQLPrepare
  "Insert into my_batch_param_insert(id,name) values(?,?)";

- Set the Param array size to 10 using SQLSetStmtAttr

- Bind the parameters 
  SQLBindParameter(1, &id);
  SQLBindParameter(2, name);

- Set the data in the predefined arrays.

- Now insert data using SQLExecute.

  This call inserts all 10 rows of data in a single statement. This is 
applicable to any SQL statement which accepts parameters.

As that of bulk inserts, that is already supported in 3.51 Driver.

Regards, Venu