Oracle ODBC insert fails without any error diagnostic

Question

I am executing a simple INSERT statement with one parameter (SQL_BIGINT) bound using the SQLBindParameter function. The execution fails, but there is no error diagnostic. The command looks like this:

INSERT INTO oracle_test_table (id) VALUES (?)

If I use the SQLGetDiagField to get the SQL_DIAG_NUMBER (the count of error diagnostics) - it's 0. And trying to retrieve the diagnostics using SQLGetDiagRec doesn't return anything either.

Minimal example:

#include <cstdint>
#include <cstdio>
#include <string>

#include <sql.h>
#include <sqlext.h>

/** Oracle ODBC insert fail without error diagnostic test.
 *  ======================================================
 *
 *  compile with: g++ oracle_test.cpp -o oracle_test -lodbc -std=c++11
 */


// Extract error diagnostic.
void extractDiag(SQLSMALLINT handleType, SQLHANDLE& handle, std::string& s) {
    SQLINTEGER i = 1;
    SQLINTEGER native;
    SQLCHAR state[7];
    SQLCHAR text[512];
    SQLSMALLINT len;
    SQLRETURN ret;

    while(true) {
        ret = SQLGetDiagRecA(handleType, handle, i++, state, &native, text, sizeof(text), &len);
        if (!SQL_SUCCEEDED(ret))
            break;
        s += "[";
        s += reinterpret_cast<char*>(state);
        s += "] (native ";
        s += std::to_string(native);
        s += "): ";
        s += reinterpret_cast<char*>(text);
        s += "; ";
    }
}

// Allocate a statement handle.
int allocateStatement(SQLHDBC& dbConn, SQLHSTMT& stmt) {
    SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT, dbConn, &stmt);
    if (!SQL_SUCCEEDED(ret)) {
        std::string s("statement allocation failed: \n");
        extractDiag(SQL_HANDLE_DBC, dbConn, s);
        fprintf(stderr, "%s\n", s.c_str());
        return 1;
    }
    return 0;
}

int main() {
    SQLRETURN ret;
    SQLHENV env;
    SQLHDBC dbConn;
    SQLHSTMT stmt;

    // Allocate an environment handle.
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

    // Use ODBC version 3.
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3, 0);

    // Allocate a connection handle.
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbConn);

    // Set connection attributes.
    SQLSetConnectAttr(dbConn, SQL_ATTR_QUIET_MODE, 0, SQL_IS_POINTER);
    SQLSetConnectAttr(dbConn, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)60, SQL_IS_UINTEGER);
    SQLSetConnectAttr(dbConn, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)60, SQL_IS_UINTEGER);

    // Connection string.
    char connStr[] = "UID=user;PWD=pass;DRIVER={Oracle 12c ODBC driver};Dbq=192.168.15.1:1521/dbora;"; // Oracle
    //char connStr[] = "UID=user;PWD=pass;DRIVER={PostgreSQL Unicode};Server=192.168.15.1;Database=dbpg;"; // PostgreSQL
    //char connStr[] = "UID=user;PWD=pass;DRIVER={MySQL ODBC 5.3 Unicode Driver};Server=192.168.15.1;Database=dbmy;"; // MySQL

    // Connect.
    ret = SQLDriverConnectA(dbConn, 0, reinterpret_cast<SQLCHAR*>(connStr), sizeof(connStr)-1, 0, 0, 0, SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(ret)) {
        std::string s("connection failed: \n");
        extractDiag(SQL_HANDLE_DBC, dbConn, s);
        fprintf(stderr, "%s\n", s.c_str());
        return 1;
    }

    char dropTable[] = "DROP TABLE oracle_test_table";
    char createTable[] = "CREATE TABLE oracle_test_table (id int)";
    char insert[] = "INSERT INTO oracle_test_table (id) VALUES (?)";

    // Drop table in case it exists.
    if (allocateStatement(dbConn, stmt))
        return 1;
    ret = SQLExecDirectA(stmt, reinterpret_cast<SQLCHAR*>(dropTable), sizeof(dropTable)-1);
    // could fail if there's no table... ignored
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    // Create new table.
    if (allocateStatement(dbConn, stmt))
        return 1;
    ret = SQLExecDirectA(stmt, reinterpret_cast<SQLCHAR*>(createTable), sizeof(createTable)-1);
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    // Allocate handle for the INSERT statement.
    if (allocateStatement(dbConn, stmt))
        return 1;

    // Bind BIGINT parameter.
    int64_t ival = 2;
    ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 19, 0, &ival, sizeof(int64_t), 0);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "int binding failed\n");
        return 1;
    }

    // Execute the INSERT statement.
    ret = SQLExecDirectA(stmt, reinterpret_cast<SQLCHAR*>(insert), sizeof(insert)-1);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "insert failed\n");

        SQLULEN diagCount = 0;
        ret = SQLGetDiagField(SQL_HANDLE_STMT, stmt, 0, SQL_DIAG_NUMBER, &diagCount, SQL_IS_UINTEGER, 0);
        if (!SQL_SUCCEEDED(ret))
            fprintf(stderr, "failed retrieving error diagnostic count\n");
        else
            fprintf(stderr, "error diagnostic count: %lu\n", diagCount);

        std::string s("error diagnostics:\n");
        extractDiag(SQL_HANDLE_STMT, stmt, s);
        fprintf(stderr, "%s\n", s.c_str());

        return 1;
    }

    return 0; 
}

Other commands including CREATE TABLE, DROP TABLE or INSERT with values directly in the command (instead of binding using SQLBindParameter) execute without any problems. And the error diagnostic retrieving otherwise works totally fine. For example when trying to drop a table that doesn't exist, it correctly returns:

[42S02] (native 942): [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

But here, nothing gets returned.

Has anyone of you encountered this problem that executing a command fails but there's no error diagnostic available?

I am using "Oracle 12c ODBC driver" (Instant Client 12.1.0.2.0) on Xubuntu 16.04 64bit. ODBC version 3 is used. The DB is "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit" running on CentOS 7.

The same code is working fine with PostgreSQL 9.2.15 (driver "PostgreSQL Unicode" 9.3.300),

and with MySQL 5.5.50 (driver "MySQL ODBC 5.3 Unicode Driver" 5.3.6).

Answer 1

I finally found why this was happening in this other question. Apparently Oracle ODBC driver doesn't support binding of BIGINT parameters. The bad thing is that it just doesn't give you any error info about why it failed.

So, in case you want to use 64bit integer parameters (SQL_C_UBIGINT or SQL_C_SBIGINT), you have to bind them as strings instead, as in this example:

int64_t val = 123456789;
char* cstr = convert_to_string(val);
size_t len = strlen(cstr);
SQLLEN ind = len;
SQLRETURN ret = SQLBindParameter(hstmt, column, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, len, 0, cstr, len, &ind);