Re: Strange behaviour with SQLBulkOperations

Thanks very much.
The problem was with the example code.

Thomas
"Pak-Ming Cheung [MSFT]" <Pak-Ming Cheung [MSFT]@discussions.microsoft.com>
schrieb im Newsbeitrag
news:BFB29508-6A25-4D8C-8149-0FF34F272123@xxxxxxxxxxxxxxxx
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

  • Re: Access to Excel 97 gurus help needed.
    ... > You don't need to qualify each field with the table (stored query) name when ... Could have been edited from SQL generated by a wizard e.g. MS Query ... adOpenDynamic cursor type is specified for the Jet optimizer i.e. to ... isn't the ADO default cursor type forward-only anyhow? ...
    (microsoft.public.excel.programming)
  • Re: MV and SQL
    ... >> finer details than a combination of database constraints and SQL ... If you take away the multivalue feature, ... > handled informally by programmers. ... binding" but they are in some way. ...
    (comp.databases.theory)
  • MV and SQL
    ... > finer details than a combination of database constraints and SQL ... If you take away the multivalue feature, ... handled informally by programmers. ... binding", and late binding is, arguably, a "good thing". ...
    (comp.databases.theory)
  • RE: BTS Deployment - Binding Exception
    ... Look for the connection string used by your SQL adapter in your binding ... is pointing to SQL server which is up and running. ...
    (microsoft.public.biztalk.general)
  • Re: ADO to ADO.NET 2.0
    ... It walks through the issues faced by developers with the same ... SQL Express is far more mature (it's based on the SQL Server binaries that ... ADO.NET datasets is better than binding to joined tables for updates. ...
    (microsoft.public.dotnet.framework.adonet)