[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-dev
Subject:    Array Binding (an ODBC example)
From:       martin () easysoft ! com
Date:       2001-08-09 9:16:13
[Download RAW message or body]

Just in case it helps the current discussion.

In ODBC binding arrays can be used on insert operations and selects.
Using bound arrays is MUCH faster (I can provide timings if anyone
needs them).

To insert multiple rows one might use code like this:

ctype p1array[n]
ctype p2array[n]
ctype p3array[n]
SQLINTEGER indicator1[n]
SQLINTEGER indicator2[n]
SQLINTEGER indicator3[n]
SQLUSMALLINT pstatus[n]
SQLUINTEGER params_processed

SQLPrepare(insert into table values(?,?,?)
SQLSetStmtAttr(...,SQL_ATTR_PARAM_BIND_TYPE, SQL_BIND_BY_COLUMN, ...)
SQLSetStmtAttr(...,SQL_ATTR_PARAMSET_SIZE, n, ...)
SQLSetStmtAttr(...,SQL_ATTR_PARAM_STATUS_PTR, pstatus,...)
SQLSetStmtAttr(...,SQL_ATTR_PARAMS_PROCESSED_PTR, &params_processed,...)
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_xyz, SQL_xyz,
                 column_size, decimal_digits,
                 &p1array, sizeof(p1[0]), &indicator1[0])
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_xyz, SQL_xyz,
                 column_size, decimal_digits,
                 &p2array, sizeof(p2[0]), &indicator2[0])
SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_xyz, SQL_xyz,
                 column_size, decimal_digits,
                 &p3array, sizeof(p3[0]), &indicator3[0])
SQLExecute(stmt);
for (i = 0; i < n; i++)
  if (pstatus[n] != SQL_PARAM_SUCCESS &&
      pstatus[n] != SQL_PARAM_SUCCCESS_WITH_INFO) possible error
print "%lu params processed\", params_processed

which would create n rows in the table.
row1: p1array[0], p2array[0], p3array[0]
row2: p1array[1], p2array[1], p3array[1]
.
.
rown: p1array[n-1], p2array[n-1], p3array[n-1]

The indicator arrays should contain values describing the parameters:

SQL_NTS - the parameter is a NULL terminated string.
SQL_NULL_DATA - the parameter is a NULL
SQL_DEFAULT_PARAM - procedure uses default value for this parameter
result of SQL_LEN_DATA_AT_EXEC(size) in which case SQLPutData must be used to
  send the parameter to the driver (things get more complex here)

There is alot more to this as you need to know how the driver handles 
array row counts and array selects. You can also set the
SQL_ATTR_PARAM_OPERATION_PTR to say how each row of parameters should be
handled e.g. ignore a row in the array.

It is presumably faster because:

[1] SQL is parsed only once (compared with n insert statements)
[2] the driver gets pointers to the arrays of parameters in one call and
    simply increments a ptr down each array to get the values.


The same applies to binding columns in result-sets except you use SQLBindCol
and set the SQL_ATTR_BIND_TYPE, SQL_ATTR_ROW_ARRAY_SIZE and
SQL_ATTR_ROW_STATUS_PTR statement attributes.

There are two types of bound parameters and columns as there is column-wise
binding and row-wise binding (this is set by the call to set
SQL_ATTR_PARAM_BIND_TYPE and SQL_ATTR_ROW_BIND_TYPE). When these are set to
SQL_BIND_BY_COLUMN (0) the driver reads the values as if they are defined:

ctype p1array[0]

as above.

When using row-wise binding usually a structure is created for the row and the
calls to SQLBindParameter/SQLBindCol pass the address of each element in the
structure. The SQL_ATTR_PARAM_BIND_TYPE/SQL_ATTR_BIND_TYPE is set tot he size
of the structure so the driver knows what offset to add to each parameter to
get to the same parameter in the next row.


e.g.
struct row {
ctype p1;
ctype p2;
ctype p3;
}

struct a_row[n];
SQLSetStmtAttr(...,SQL_ATTR_PARAM_BIND_TYPE, sizeof(a_row[0]),...)
SQLSetStmtAttr(...,SQL_ATTR_PARAMSET_SIZE, n)
SQLBindParameter(...,&a_row[0].p1,..., sizeof(a_row[0].p1),...)

column-wise binding (the first method above) is used more frequently.

Hope this helps.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

[prev in list] [next in list] [prev in thread] [next in thread]