RE: Strange behaviour with SQLBulkOperations

Hi Thomas,

SQL Server 2005 Express Edition does support BulkOperation. A comparison in
functionalities between the Express Edition and other SQL Server editions can
be found at:
http://msdn2.microsoft.com/en-us/library/ms165636.aspx

Going back to your encountered problem, please try to set the "Statement
Attributes" before "SQLExecDirect". So, the flow of your program looks like
the following:

// Allocate Statement Handle

//SET STATEMENT ATTRIBUTES
// Set the cursor type.
// Lock out other users.
// Set the number of rows to process
// Set the size of the structure for each row.

// Execute SQL statement to open cursor

// Bind each column
// 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");


--------------------------------
Ming.
MDAC Team, Microsoft.




"Thomas Noone" wrote:

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



.

Relevant Pages

  • Strange behaviour with SQLBulkOperations
    ... I'm trying to implement bulk inserts via ODBC. ... I then tried it with Oracle Express, ... // Set the cursor type. ... Shouldn't SQL Server and Oracle support bulk operations? ...
    (microsoft.public.data.odbc)
  • Re: Error adding command properties for stored procedure call
    ... I see you are using the SQL Native Client for SQL Server ... A server cursor is not allowed on a remote stored procedure or stored ... The stored procedure called is a test one that simply SELECTs the name field ... int InitializeAndConnect; ...
    (microsoft.public.data.oledb)
  • Re: C5 woes. How stable is VFP really?
    ... After that I close the SQL server cursor and work with my manual cursor instead. ... a VFP9 app on a really old machine ... app was under more "pressure", VFP showed ...
    (microsoft.public.fox.programmer.exchange)
  • Re: SQL HELP PLEASE!! Cursor only returns part of the data
    ... First of all, it's not the cursor as such that is bad, it is the looping. ... and re-writing it to handle set-based data will cost you more ... set-based solution do not perform well, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Using Cursors
    ... it seems I've stirred up a hornet's nest with my cursor question. ... finding the discussion valuable in my circumstances (moving from Oracle to ... SQL Server). ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)