Ronggang,
Ok, I finally have a conclusion (a very very odd one!). I came trying to replicate my application behavior and reducing it to isolate the problem, than I found this weird issue... Sorry if it has a simple solution, but I do not know how to fix...
The point is:
If the call to SQLBindParameter() is not in same function, it does not work to bind NULL value (works to bind any other value). The reduced sample below can be used to simulate the problem. The table structure is the same from your sample.
#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <iostream>
#include <assert.h>
#ifdef _UNICODE
#undef _UNICODE
#endif
// display error message when an error occurs
void displayErrorMessage(SQLSMALLINT handleType, SQLHANDLE handle)
{
RETCODE rc;
const int NAME_LEN = 300;
SQLTCHAR state[NAME_LEN];
SQLTCHAR errorInfo[NAME_LEN];
SQLINTEGER nativeError;
SQLSMALLINT cbLength;
char szMsg[4096];
SQLINTEGER numRecords;
rc = SQLGetDiagRec(handleType, handle, 1, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
printf((const char *)errorInfo);
printf( "\n" );
SQLGetDiagField(handleType, handle, 0, SQL_DIAG_NUMBER, &numRecords, SQL_IS_INTEGER, NULL);
for (int i=1; i<=numRecords; i++)
{
rc = SQLGetDiagRec(handleType, handle, i, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
printf((const char *)errorInfo);
printf( "\n" );
printf((const char *)state);
printf( "\n" );
}
}
void BindOutsideCurrentFunction( HSTMT hstmt )
{
SQLRETURN res;
SQLLEN nInd = SQL_NULL_DATA;
res = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TIMESTAMP_LEN, 0, NULL, 0, &nInd );
assert( res == SQL_SUCCESS );
}
int main(int argc, char* argv[])
{
RETCODE rc;
HENV henv = NULL;
HDBC hdbc = NULL;
HSTMT hstmt = NULL;
// allocate environment handle
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// set the environment attribute
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// allocate connection handle
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
// using SQLDriverConnect to connect to the database
rc = SQLDriverConnect( hdbc, NULL, (SQLTCHAR *) ("DSN=xHB001;UID=sa;PWD=;"), SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_NOPROMPT );
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
displayErrorMessage(SQL_HANDLE_DBC, hdbc);
return -1;
}
SQLSetConnectOption( hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF );
// allocate statement handle
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
rc = SQLPrepare(hstmt, (SQLTCHAR *)"INSERT INTO [TEST_TABLE_RDD_ODBCRDD] ( [DATE_LIM] ) VALUES ( ? ); SELECT @@IDENTITY ;", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
/*
// Note: uncomment this block and comment call to BindOutsideCurrentFunction() below
// and SQLExecute will work fine. Note that BindOutsideCurrentFunction() does the SAME
// that this block does.
SQLRETURN res;
SQLLEN nInd = SQL_NULL_DATA;
res = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TIMESTAMP_LEN, 0, NULL, 0, &nInd );
assert( res == SQL_SUCCESS );
*/
BindOutsideCurrentFunction( hstmt );
rc = SQLExecute(hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
SQLFreeStmt(hstmt, SQL_CLOSE);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLEndTran( SQL_HANDLE_DBC, (SQLHANDLE) hdbc, SQL_COMMIT );
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
I'm using:
SQL Server 2000
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Installed Edition: Professional
Microsoft Visual C++ 2005 77626-009-0000007-41361
Security Update for Microsoft Visual Studio 2005 Professional Edition - ENU (KB925674)
BTW I tested with Postgres and MySQL backends with same results.
Any clue ?
Thank you,
Marcelo
