Oracle ODBC insert fails without any error diagnostic
<p>I am executing a simple INSERT statement with one parameter (<code>SQL_BIGINT</code>) bound using the <code>SQLBindParameter</code> function. The execution fails, but there is no error diagnostic. The command looks like this:</p>

<pre><code>INSERT INTO oracle_test_table (id) VALUES (?)
</code></pre>

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

<p>Minimal example:</p>

<pre><code>#include &lt;cstdint&gt;
#include &lt;cstdio&gt;
#include &lt;string&gt;

#include &lt;sql.h&gt;
#include &lt;sqlext.h&gt;

/** 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&amp; handle, std::string&amp; s) {
    SQLINTEGER i = 1;
    SQLINTEGER native;
    SQLCHAR state[7];
    SQLCHAR text[512];
    SQLSMALLINT len;
    SQLRETURN ret;

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

// Allocate a statement handle.
int allocateStatement(SQLHDBC&amp; dbConn, SQLHSTMT&amp; stmt) {
    SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT, dbConn, &amp;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, &amp;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, &amp;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&lt;SQLCHAR*&gt;(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&lt;SQLCHAR*&gt;(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&lt;SQLCHAR*&gt;(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, &amp;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&lt;SQLCHAR*&gt;(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, &amp;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; 
}
</code></pre>

<p>Other commands including CREATE TABLE, DROP TABLE or INSERT with values directly in the command (instead of binding using <code>SQLBindParameter</code>) 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:</p>

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

<p>But here, nothing gets returned.</p>

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

<p>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.</p>

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

<p>and with MySQL 5.5.50 (driver "MySQL ODBC 5.3 Unicode Driver" 5.3.6).</p>