ODBC Returning success but actually failing (SQL Native)

I'm talking to SQL 2005 using the SQL Native client in ODBC:

Here's what I'm doing:

1) SQLPrepare () -- Prepair an insert stmt
2) SQLBindParameter() -- Binding the parameter
3) Set the Parameter to a value
4) SQLExecute() -- Execute the statment
5) Set the Parameter to a different value
6) SQLExecute() -- Execute the statment

Both calls to SQLExecute() return SUCCESS_WITH_INFO.

The problem is that only the 1st SQLExecute() ever makes it to the database. The second SQLExecute() never gets executed at the database AND it returns a success condition.

When I check what the info is, it's "[Microsoft][SQL Native Client]Fractional truncation". Not an issue since I'm setting a datetime value in the database and I don't care about the milliseconds.

Here's the problems with this:

1) SQLExecute() return success yet it does not execute the statement. How can I tell if the statement fails if SQLExecute() always return success?

2) Why does a previous success with info cause all future executes to not happen?

So, is there a way to tell odbc to ignore the info and allow the next execute to run?

Thanks for any help,

Scott

Could you send some sample code that demonstrates the problem please?

How do you know the 2nd execute is not making it to the server? How you looked at a profiler trace or are you just examining the database?

|||

I'm use SQL Server profiler to determine the second SQLExecute is never making it to the database.

I've done some more testing on this and here's what I have found:

1) The problem exists with the "SQL Native Client" ODBC driver. The "SQL Server" does not have this problem. Switching to the "SQL Server" driver fixes the problem.

2) The problem seems to exist only when using the SQL

Here's how to reproduce the problem:

Step 1: In SQL 2005, create table with a datetime field. Here's the code I used:
CREATE TABLE [dbo].[TestTbl] ( [TestDate] [datetime] NULL )

Step 2: Create an ODBC connection to this table using the SQL Native Client driver. In my example, I called it "TestNative"

Step 3: Here's the VC++ code that uses ODBC to connect to the database and insert data into the TestTbl table:

-- BEGIN CODE BLOCK
CDatabase db;
db.OpenEx ("DSN=TestNative;APP=Test App;WSID=SCOTT1;DATABASE=Test2;Trusted_Connection=Yes", CDatabase::noOdbcDialog);

HSTMT hstmt;
::SQLAllocHandle (SQL_HANDLE_STMT, db.m_hdbc, &hstmt);

LPCTSTR strSQL = ;
::SQLPrepare (hstmt, (SQLCHAR *) "INSERT INTO TestTbl (TestDate) VALUES (?)", SQL_NTS);

SQL_TIMESTAMP_STRUCT sqlTimestamp;
SQLLEN Ind = sizeof (sqlTimestamp);
::SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 0, 4, &sqlTimestamp, Ind, &Ind);

sqlTimestamp.year = 2006;
sqlTimestamp.month = 6;
sqlTimestamp.day = 1;
sqlTimestamp.hour = 1;
sqlTimestamp.minute = 1;
sqlTimestamp.second = 1;
sqlTimestamp.fraction = 0;
::SQLExecute (hstmt);

sqlTimestamp.day = 2;
::SQLExecute (hstmt);

::SQLFreeHandle (SQL_HANDLE_STMT, &hstmt);
-- END CODE BLOCK

Using the ODBC "SQL Server" driver, this code inserts 2 records into TestTbl.
Using the ODBC "SQL Native Client" drivre, only 1 record gets inserted.

Running SQL Server profiler to monitor what happens in the database, when the first SQLExecute() executes, a "exec sp_prepexec ..." statement is run on server. This happens with both ODBC drivers.
The problem is that with the second SQLExecute() nothing happens in the database if the "SQL Native Client" driver is used. If the "SQL Server" driver is used, "exec sp_execute 1,''2006-06-02 01:01:00:000''" is run at the server.

|||

I couldn't get the sample code to run as supplied because of an error in SQLBindParameter. You need ColumnSize and DecimalDigits set to 19, 0 or 23, 3. (See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbccolumn_size.asp for details)

Once I'd fixed the problem with SQLBindParameter I got two rows inserted with both SQL Server and SQL Native Client drivers.

I ran my repro as an ODBC v3 app.

If I ran the app as an ODBC v2 app with your settings for SQLBindParameter then I managed to reproduce your problem, but if I ran with 19,0 or 23,3 I got two rows inserted.

So, if you run as an ODBC v3 app (via SQLSetEnvAttr call before connecting) you'll get better diagnostics from SQLBindParameter. Even if not, if you provide correct values for precision and scale on SQLBindParameter the problem will go away.

We'll investigate why the two drivers behave differently.