Binding int64 (SQL_BIGINT) as query parameter causes error during execution in Oracle 10g ODBC

Question

I've got an insert into a table using ODBC 3.0 on Oracle 10g that is failing and I have no idea why. The database is on Windows Server 2003. The client is on Windows XP.

The table:

CREATE TABLE test ( testcol NUMBER(20,0) NULL );

The ODBC calls:

SQLAllocHandle(SQL_HANDLE_STMT) = SQL_SUCCESS
SQLPrepare(INSERT INTO test (testcol) VALUES (?);) = SQL_SUCCESS

SQLINTEGER nStrLen = 0;
__int64 nInt64 = 99;
SQLBindParameter(hStatement, 1, SQL_PARAM_INPUT, 
    SQL_C_SBIGINT, SQL_BIGINT, 20, 0, &nInt64, 0, &nStrLen) = SQL_SUCCESS

SQLExecute() = SQL_ERROR
SQLGetDiagRec(1) = SQL_NO_DATA

SQLBindParameter succeeds but then SQLExecute fails. There is no diagnostic message.

I have had to resort to writing the int64 to a string and binding it as a string. Is this the only way to bind a int64?

Answer 1

The Oracle 10g Admin Guide in Appendix G.1 says that the Oracle 10g ODBC driver does not support either SQL_C_SBIGINT or SQL_C_UBIGINT.

Like you, we also find that at run time the SQLExecute() fails. And a call to SQLGetDiagRec() returns nothing, rather than a simple message like "Oracle 10g does not support SQL_C_SBIGINT", . Grr....

Anyway, the Appendix G.1 does not say how you should bind data to send into a table with a column defined like NUMBER(20). So we all have to guess, and use whatever (undocumented) technique works. It would be nice if the Appendix G.1 gave some kind of hint or suggestion as to the "best" way.

If converting the number to a string and then binding works for you, stick with that.