INSERTs and arrays w/ ODBC 
Author Message
 INSERTs and arrays w/ ODBC

Hello,

We need to do bulk inserts to tables in a DB2/400 schema via ODBC
using the native ODBC API with C++. Our server is an AS/400 V5R1M0.
Client is Windows 2000 SP2 with Client Access ODBC driver.

The application I am writing tested fine for a few dozen rows using
SQLPrepare/SQLExecute, 1 row at a time. Unfortunately, we need to do
several thousand a day in production, so for performance reasons, we
desperately need to implement an array insert.

Questions:
(1) Is this the best way to do it using ODBC and SQL?
(2) What are the limits as to array size (couldn't find any docs about
this on the IBM website)? IOW I'm looking for an optimal value for
DEF_BLOCKSIZE in the code below.
(3) What CLI alternatives are there which can be implemented on a
Windows 2000 client?

I can't seem to get the syntax right. The Microsoft ODBC docs are
clear that in the call to SQLBindParameter(), you pass the
"ParameterValuePtr" as "the address of an array", which is also how
I've seen it done in some examples (e.g. at the www.mimer.com
developer site, click on the "Optimize ODBC" link once you're there).
However, in the example given on the IBM website (unfortunately
written in Visual Basic), they pass something like "arrayVal[0]" which
puts the address of the first *element* of the array on the stack, NOT
the address of an array (i.e. the address of a variable containing a
pointer to the first element of the array).

So I tried it both ways. Regardless of how I call it, I get one of two
error messages: If I call it using the address of the name of the
array without the index, I get a 22003 "Numeric value xxxx out of
range" error. If I use the other syntax (address of first element), I
get something like "The row number 10 was incorrect".

Here is the code (sorry if it's a bit long:

~~~ code starts ~~~
SQL statement is:
   INSERT INTO ISLVOLLZ.tmpisedi
        (scan_nr, imp_id, file_id, anzretrg)
   VALUES  ( ?, {ts '2003-01-29 17:40:36.242'},  ?,  ?);
   --
table definition is:

    CREATE TABLE islvollz.tmpisedi (
    scan_nr     DECIMAL(15,0)   NOT NULL,
    imp_id      TIMESTAMP       NOT NULL,
    file_id     INTEGER         NOT NULL,
    anzretrg    INTEGER         NOT NULL);
    -- CONSTRAINTs omitted for brevity

C++ code is:
//----------------------------
// declare arrays (actually these are allocated
// on the heap with "new", but using array
// syntax here for brevity's sake):
//----------------------------
    const SQLUINTEGER DEF_BLOCKSIZE = 10; // easier to debug than 8000
    __int64    arScanNr[TotalRowCount];
    SQLTCHAR   arScanNr_Access[TotalRowCount];
    int        arFileId[TotalRowCount];
    int        arDupl[TotalRowCount];      
    SQLUSMALLINT arStatus[DEF_BLOCKSIZE];
    SQLUINTEGER  remaining_rows = TotalRowCount;
//----------------------------
// bind parameters:
//----------------------------
    SQLBindParameter(qry_handle, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT,
        SQL_DECIMAL, 15, 0, &arScanNr, 0, NULL);
    SQLBindParameter(qry_handle, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
        SQL_INTEGER, 0, 0, &arFileId, 0, NULL);
    SQLBindParameter(qry_handle, 3, SQL_PARAM_INPUT, SQL_C_SLONG,
        SQL_INTEGER, 0, 0, &arDupl, 0, NULL);
//----------------------------
// fill arrays here (omitted ...
// this much works correctly, however).
//----------------------------
// set statement attributes:
//----------------------------
    SQLSetStmtAttr(qry_handle, SQL_ATTR_PARAM_STATUS_PTR, arStatus,
        SQL_IS_POINTER);
    SQLSetStmtAttr(qry_handle, SQL_ATTR_PARAMS_PROCESSED_PTR,
        &rows_processed, SQL_IS_POINTER);

//----------------------------
// insert blocks of new rows:
//----------------------------
    while ( remaining_rows > 0 && (e.retval != SQL_ERROR))
    {
        if (remaining_rows > DEF_BLOCKSIZE)
            num_rows = DEF_BLOCKSIZE;
        else
            num_rows = remaining_rows;

        retval = SQLSetStmtAttr(qry_handle, SQL_ATTR_PARAMSET_SIZE,
            (SQLPOINTER)num_rows, SQL_IS_UINTEGER);

        if (retval != SQL_ERROR)
            retval = SQLExecute(qry_handle);

        if (retval == SQL_ERROR)
            remaining_rows = 0;
        else
            remaining_rows -= DEF_BLOCKSIZE;
    }
//----------------------------
// cleanup ... (omitted)
//----------------------------
~~~ code ends ~~~

Thanks for any help (and sorry for the cross-post).

Bob Hairgrove



Mon, 18 Jul 2005 02:55:13 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. INSERTs and arrays w/ ODBC

2. ODBC Array Insert

3. order of data in array for Pro*C array insert/update

4. Array Fetch - Array Insert

5. Array DML inserts and for each row inserts

6. How to use array insert to insert floats

7. Find inserted record location after ODBC Insert

8. ODBC - insert/updating two databases with one insert/update

9. ODBC - insert/updating two databases with one insert/update

10. SQLServer2000 INSERT numerical array - best practices?

11. Insert multiple rows as an array....

12. High Performance Array Insert to a Database Table


 
Powered by phpBB® Forum Software