Binding int64 (SQL_BIGINT) as a query parameter results in a run-time error in Oracle 10g ODBC

Question

I have an insert in a table using ODBC 3.0 on Oracle 10g that does not work, and I have no idea why. The database is in Windows Server 2003. The client is in Windows XP.

Table:

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

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 is successful, but then SQLExecute fails. There is no diagnostic message.

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

Answer 1

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

Like you, we also find that SQLExecute() fails at runtime. And calling SQLGetDiagRec() does not return anything, but a simple message like "Oracle 10g does not support SQL_C_SBIGINT" . Grr ....

In any case, Appendix G.1 does not say how you should associate the data to be sent to the table with a column defined as NUMBER(20) . Therefore, we should all guess and use any (undocumented) technique. It would be nice if some kind of hint or suggestion for a “better” method was given in Appendix G.1.

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