From Ingres Community Wiki

Expand the batch support functionality introduced in Ingres 10 to ODBC.

Enhancement Number

SIR 126217 - Adds batch support for execution of a single query with arrays of parameters.

Design Documents

The DDS for the DBMS part of batch execution is here

The DDS for client batch processing (primarily OpenAPI) is here

The DDS for ODBC batch processing is here is here

ODBC Bulk Loading

Several customers have requested true batch query capability in the ODBC. Customers expected that the Ingres ODBC driver could load template or test databases in a matter of seconds, but this took far longer if the ODBC was used. This happened because the ODBC only emulated batch statements. Instead, the ODBC executed the same statement multiple times.

Addition of bulk loading capability for the Ingres DBMS and OpenIngres API permits the ODBC driver to load bulk data at a much higher speed.

Types of ODBC Batch Statements

The ODBC specs provide two types of batch execution:

1. Execution of a single query with arrays of parameters.
2. Explicit batch execution. An explicit batch is two or more SQL statements separated by semicolons (;). The statements may be directly executed or prepared and executed.

Both explicit and single-query batch execution may use dynamic parameters.

Parameter Arrays

The SQLBindParameter() function may bind a single parameter or an array of parameters. Each element of parameter array may represent a row; this is called row-wise binding. A parameter element may represent a column; this is called row-wise binding. The ODBC application invokes SQLSetStmtAttr() to characterize the parameter array.

Once the array has been described and variables bound, the application invokes the query only once, from the application point of view. The driver sends the parameter array data to the DBMS server according to the specified array characteristics. The implementation of the batch query under the covers is specific to the driver.

NOTE: This has been implemented in Ingres 10.1 and also available as a patch in Ingres 10.0

Explicit Batch Processing

Explicit batch queries allow any number of insert, update or delete statements within the same query buffer. These statements do not have to be the same. Parameters of explicit batches are numbered 1 through N as though the multiple statements were logically only one statement. Here is an example query:

insert into myTable values (?, ?); delete from myTable where myColumn = (?)

Note that the first statement is separated from the second with a semicolon. This is a requirement of explicit batch processing in the ODBC.

Testing Requirements

Sample Test Programs

odbc_row_col_wise.c

Documentation