Strange behaviour with SQLBulkOperations
- From: "Thomas Noone" <cash@xxxxxxxxxxxx>
- Date: Sun, 24 Dec 2006 13:26:39 +0100
Hi,
I'm trying to implement bulk inserts via ODBC.
I started out with an example from "Visual C++ unleashed" for MS SQL Server,
which ultimately always ended with an "Invalid cursorstate".
I then tried it with Oracle Express, which actually succeeded.
MS Access failed.
This is the basic code I am using, after successfully setting up and
connecting via odbc:
void CSqltestDoc::OnViewBulkinsert() {
SQLRETURN sr;
SQLHSTMT hstmt;
SQLCHAR SQL[] = "SELECT EmpName, Salary, Dept FROM Employee";
struct rowTag {
SQLCHAR name[51];
double salary;
SQLCHAR dept[11];
SQLINTEGER nameLength;
SQLINTEGER salaryLength;
SQLINTEGER deptLength;
} row [7];
// Allocate Statement Handle
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in allocating statement in
OnViewBulkinsert");
// Execute SQL statement to open cursor
sr = SQLExecDirect(hstmt, SQL, SQL_NTS);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error executing statement in
OnViewBulkinsert");
//SET STATEMENT ATTRIBUTES
// Set the cursor type.
sr = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
DBError(hstmt);
// Lock out other users.
sr = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
(void*) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in setting locking in
OnViewBulkinsert");
// Set the number of rows to process
sr = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(void *) 7, SQL_IS_INTEGER);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in array size in
OnViewBulkinsert");
// Set the size of the structure for each row.
sr = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE,
(void *) sizeof(row[0]), SQL_IS_INTEGER);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in setting bind type in
OnViewBulkinsert");
// Bind each column
sr = SQLBindCol(hstmt, 1, SQL_C_CHAR, row[0].name, sizeof(row[0].name),
&row[0].nameLength);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in Binding 1 in
OnViewBulkinsert");
sr = SQLBindCol(hstmt, 2, SQL_C_DOUBLE, &row[0].salary,
sizeof(row[0].salary), &row[0].salaryLength);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in Binding 2 in
OnViewBulkinsert");
sr = SQLBindCol(hstmt, 3, SQL_C_CHAR, row[0].dept, sizeof(row[0].dept),
&row[0].deptLength);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in Binding 3 in
OnViewBulkinsert");
strcpy((char *) row[0].name, "Gilligan");
strcpy((char *) row[1].name, "Skipper");
strcpy((char *) row[2].name, "Mr. Howe");
strcpy((char *) row[3].name, "Mrs. Howe");
strcpy((char *) row[4].name, "Ginger");
strcpy((char *) row[5].name, "Professor");
strcpy((char *) row[6].name, "Mary Ann");
for(int loop=0; loop < 7; loop++) {
row[loop].salary = 50000;
strcpy((char *) row[loop].dept, "IS");
row[loop].nameLength = SQL_NTS;
row[loop].salaryLength =
sizeof(row[0].salary);
row[loop].deptLength = SQL_NTS;
}
// Add new rows to the database
sr = SQLBulkOperations(hstmt, SQL_ADD);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
displayODBCError(sr, "Error in bulk insert");
}
Would anybody have any idea, why my code is showing this strange behaviour?
Shouldn't SQL Server and Oracle support bulk operations?
I would very much appreciate any suggestions on how to proceed or what else
to look into, on successfully using SQLBulkOperations with SQL Server
(Express) and Oracle.
Currently I'm stumped.
Thanks,
Thomas
.
- Follow-Ups:
- RE: Strange behaviour with SQLBulkOperations
- From: Pak-Ming Cheung [MSFT]
- RE: Strange behaviour with SQLBulkOperations
- Prev by Date: 80004005 "unspecified error" jet db driver
- Next by Date: Cannot Update Excel File
- Previous by thread: 80004005 "unspecified error" jet db driver
- Next by thread: RE: Strange behaviour with SQLBulkOperations
- Index(es):
Relevant Pages
|
|
