SQLBulkOperations

[Michael Eckstein]

Hi all,
I use MS SQL Server Version 8.00.194 developer version, and ODBC for SQL
Server version 3.70.08.20.
I use the OBDC function SQLBulkOperations for SELECT and INSERT. The
operation goes fine for
SELECT, but after calling SQLBulkOperations for INSERT the following error
results:

S1C00: [Microsoft][ODBC Cursor Library]Driver not capable

Does anybody know if the SQLBulkOperations for INSERT (Operation=SQL_ADD) is
supported by MS SQL Server
and ODBC specified above and if so, then what could be the cause of the
above error.

Thank you for your replies in advance.
Best regards, Michael

[Ulrich Sprick]

Hi Michael,

here's an example of inserting array data. the operation may be restricted
to a certain cursor type, e.g. a keyset driven cursor.

hth, ulli

***** example ******

To insert data with SQLBulkOperations(), an application performs the
following sequence of steps:

1. Executes a query that returns a result set
2. Sets the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of
rows that it wants to insert.
3. Calls SQLBindCol() to bind the data that it wants to insert. The data is
bound to an array with a size equal to the value of SQL_ATTR_ROW_ARRAY_SIZE.
Note: The size of the array pointed to by the SQL_ATTR_ROW_STATUS_PTR
statement attribute should either be equal to SQL_ATTR_ROW_ARRAY_SIZE or
SQL_ATTR_ROW_STATUS_PTR should be a null pointer.
4. Calls SQLBulkOperations(StatementHandle, SQL_ADD) to perform the
insertion.
If the application has set the SQL_ATTR_ROW_STATUS_PTR statement attribute,
then it can inspect this array to see the result of the operation.

/* ... */
SQLCHAR * stmt =
"INSERT INTO CUSTOMER ( Cust_Num, First_Name, Last_Name ) "
"VALUES (?, ?, ?)" ;

SQLINTEGER Cust_Num[] = {
10, 20, 30, 40, 50, 60, 70, 80, 90, 100,
110, 120, 130, 140, 150, 160, 170, 180, 190, 200,
210, 220, 230, 240, 250,
} ;

SQLCHAR First_Name[][31] = {
"EVA", "EILEEN", "THEODORE", "VINCENZO", "SEAN",
"DOLORES", "HEATHER", "BRUCE", "ELIZABETH", "MASATOSHI",
"MARILYN", "JAMES", "DAVID", "WILLIAM", "JENNIFER",
"JAMES", "SALVATORE", "DANIEL", "SYBIL", "MARIA",
"ETHEL", "JOHN", "PHILIP", "MAUDE", "BILL",
} ;

SQLCHAR Last_Name[][31] = {
"SPENSER", "LUCCHESI", "O'CONNELL", "QUINTANA", "NICHOLLS",
"ADAMSON", "PIANKA", "YOSHIMURA", "SCOUTTEN", "WALKER",
"BROWN", "JONES", "LUTZ", "JEFFERSON", "MARINO",
"SMITH", "JOHNSON", "PEREZ", "SCHNEIDER", "PARKER",
"SMITH", "SETRIGHT", "MEHTA", "LEE", "GOUNOT",
} ;

/* ... */
/* Prepare the statement */
rc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_PARAMSET_SIZE,
( SQLPOINTER ) row_array_size,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_SLONG,
SQL_INTEGER,
0,
0,
Cust_Num,
0,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

rc = SQLBindParameter( hstmt,
2,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
31,
0,
First_Name,
31,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

rc = SQLBindParameter( hstmt,
3,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
31,
0,
Last_Name,
31,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

rc = SQLBulkOperations( hstmt, SQL_ADD );

CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "Inserted %ld Rows\n", row_array_size ) ;

"Michael Eckstein" <ib...@bajt.cz> schrieb im Newsbeitrag
news:9tj6gh$2nf$1...@news.vol.cz...

[Michael Eckstein]

Hi Ulli,
thank you for your reply. The posted code is pretty straightforward but has
some error. I tried it but unfortunately without success - SQLBulkOperations
returned error

S1010: [Microsoft][ODBC Driver Manager] Function sequence error

After some correction (SQLExecuteDirect instead of SQLPrepare) arised the
old error:

S1C00: [Microsoft][ODBC Cursor Library]Driver not capable

Please, could you send me a verified piece of code, if you have?
Thank you very much in advance.

Best regards, Michael

"Ulrich Sprick" <ulrich...@gmx.de> wrote in message
news:3c03c2c7$1...@news.teuto.net...

[Michael Eckstein]

Hi,
I tried the function SQLBulkOperations for SQL_ADD in ORACLE.
There the program went OK. The same program running in MS SLQ Server
ended with the below cited error.
It seems that this function is not supported in MS SQL Server, at least
in MS SQL Server Version 8.00.194 developer version, and ODBC
for SQL Server version 3.70.08.20. Never mind, I'll use Oracle without
problems.
Best regards,
Michael

"Michael Eckstein" <ib...@bajt.cz> wrote in message
news:9uj33c$1at6$1...@news.vol.cz...