Can I use ODBC statement after error (is it valid)?

Question

Can I use an ODBC statement handle again (i.e. is it valid) after a command using it, for example SQLExecute, fails? (does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO)

And is this possibly DBMS/driver-specific?

I couldn't find anything about this on the ODBC Programmer's reference page.

Answer 1

I do not find any authoritative answers in the doc neither. But I would say: Yes - you can, except the error code returned is SQL_INVALID_HANDLE:

Reasoning:

1. None of the docs of the functions that require a statement-handle as argument mention anything about a handle being invalidated in case of error. All that matters is the return code. So if its not explicitly forbidden, it should work.

2. In the case you get an SQL_ERROR returned, you can use the same statement handle to fetch more information about that error. So the statement handle still has a valid context.

   3: We are using the same statement again and again, even in case of SQL_ERROR returned. And we did not have any problems so far. Well, but mostly we do not get any Errors..

Update, after comment about "Statement has been terminated": Yes, you can re-use the same statement-handle. The error is just indicating that the currently running statement has been terminated by the server. See the following sample, which produces such an error and then uses the same statement again to do a successful insert:

#include <windows.h>
#include <tchar.h>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <sqlucode.h>

void printErr(SQLHANDLE handle, SQLSMALLINT handleType)
{
    SQLSMALLINT recNr = 1;
    SQLRETURN ret = SQL_SUCCESS;
    while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
        SQLWCHAR sqlState[5 + 1];
        errMsg[0] = 0;
        SQLINTEGER nativeError;
        SQLSMALLINT cb = 0;
        ret = SQLGetDiagRec(handleType, handle, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
        }
        ++recNr;
    }
}


int _tmain(int argc, _TCHAR* argv[])
{
    // connect to db
    SQLRETURN   nResult = 0;
    SQLHANDLE   handleEnv = 0;

    nResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE*)&handleEnv);
    nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, SQL_IS_INTEGER);

    SQLHANDLE   handleDBC = 0;
    nResult = SQLAllocHandle(SQL_HANDLE_DBC, handleEnv, (SQLHANDLE*)&handleDBC);

    SQLWCHAR     strConnect[256] = L"Driver={SQL Server};Server=.\\INSTANCE;Database=Test;Trusted_Connection=yes;";
    SQLWCHAR     strConnectOut[1024] = { 0 };
    SQLSMALLINT nNumOut = 0;
    nResult = SQLDriverConnect(handleDBC, NULL, (SQLWCHAR*)strConnect, SQL_NTS, (SQLWCHAR*)strConnectOut, sizeof(strConnectOut), &nNumOut, SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(nResult))
        printErr(handleDBC, SQL_HANDLE_DBC);

    SQLHSTMT    handleStatement = SQL_NULL_HSTMT;
    nResult = SQLAllocHandle(SQL_HANDLE_STMT, handleDBC, (SQLHANDLE*)&handleStatement);
    if (!SQL_SUCCEEDED(nResult))
        printErr(handleDBC, SQL_HANDLE_DBC);

    // try to drop table Wallet, ignore if it exists
    nResult = SQLExecDirect(handleStatement, L"DROP TABLE Wallet", SQL_NTS);

    // create table Wallet
    nResult = SQLExecDirect(handleStatement, L"CREATE TABLE Wallet (WalletID int NOT NULL,  Name nvarchar(5) NOT NULL)", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
            printErr(handleStatement, SQL_HANDLE_STMT);

    // Create a query that fails with data truncation and statement got terminated error:
    nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (1, 'SomethingTooLong')", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
        printErr(handleStatement, SQL_HANDLE_STMT);

    // and now run a query on the same statement and check in the db: Has been inserted just fine
    nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (2, 'Fan')", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
        printErr(handleStatement, SQL_HANDLE_STMT);
    // actually we should now free all handles properly...
    return 0;
}

The output of this program is:

ERROR; native: 8152; state: 22001; msg: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. ERROR; native: 3621; state: 01000; msg: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

But the last insert query that works without error, using the statement, has been executed successfully: Check in your database and see that the row has been inserted (and that the SQLExecDirect did not return any error).

See here for SQL_INVALID_HANDLE: https://msdn.microsoft.com/en-us/library/ms716219(v=vs.85).aspx