Incorrect return value from SQLMoreResults

[bkline]

Background

Here is what the spec says about the return value from SQLMoreResults for multi-statement batches in which one or more of the statements have failed.

If one of the statements in a batch fails, SQLMoreResults will return either SQL_ERROR or SQL_SUCCESS_WITH_INFO. If the batch was aborted when the statement failed or the failed statement was the last statement in the batch, SQLMoreResults will return SQL_ERROR. If the batch was not aborted when the statement failed and the failed statement was not the last statement in the batch, SQLMoreResults will return SQL_SUCCESS_WITH_INFO. SQL_SUCCESS_WITH_INFO indicates that at least one result set or count was generated and that the batch was not aborted.¹

Expected

For a multi-statement batch, the driver returns SQL_SUCCESS_WITH_INFO from SQLMoreResults when all of the following conditions are met:

• the statement to which we just moved has failed
• the batch has not been aborted (i.e., XACT_ABORT is OFF (the default), the handles are still valid and uncorrupted, etc.)
• the statement is not the last statement in the batch

The statement handle is still valid and available for processing the non-failing statements in the batch.

Observed

Under those conditions, the Microsoft ODBC driver returns SQL_ERROR instead of SQL_SUCCESS_WITH_INFO even though the statement handle is still longer valid and available for processing the remaining statements in the batch.

Repro

The following program can be used to demonstrate the observed behavior.

/*
 * repro.c
 *
 * Show the return code from SQLMoreResults with a multi-statement batch.
 *
 * Usage:
 *   ./repro CONNECTION-STRING
 */

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

/* Print all diagnostic records on a handle. */
static void print_diag(const char *fn, SQLSMALLINT handle_type, SQLHANDLE handle)
{
    SQLSMALLINT i = 1;
    SQLCHAR sqlstate[6], msg[1024];
    SQLINTEGER native;
    SQLSMALLINT msg_len;
    SQLRETURN ret;

    while ((ret = SQLGetDiagRec(handle_type, handle, i++,
                                sqlstate, &native, msg, sizeof(msg), &msg_len))
           == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
    {
        fprintf(stderr, "  %s DIAG: SQLSTATE=%s native=%ld msg=%s\n",
                fn, (char *)sqlstate, (long)native, (char *)msg);
    }
}

/* Check return code; print diagnostics on unexpected failure. */
static void check(const char *fn, SQLRETURN ret,
                  SQLSMALLINT handle_type, SQLHANDLE handle,
                  SQLRETURN expected)
{
    if (ret == expected)
        return;
    fprintf(stderr, "UNEXPECTED: %s returned %d (expected %d)\n",
            fn, (int)ret, (int)expected);
    print_diag(fn, handle_type, handle);
}

int main(int argc, char *argv[])
{
    if (argc < 2) {
        fprintf(stderr, "Usage: %s <connection-string>\n", argv[0]);
        return 1;
    }

    SQLRETURN    ret;
    SQLHENV      henv  = SQL_NULL_HENV;
    SQLHDBC      hdbc  = SQL_NULL_HDBC;
    SQLHSTMT     hstmt = SQL_NULL_HSTMT;
    int          rc    = EXIT_SUCCESS;

    /* --- allocate environment ------------------------------------------ */
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    check("SQLAllocHandle(ENV)", ret, SQL_HANDLE_ENV, henv, SQL_SUCCESS);

    ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                        (SQLPOINTER)SQL_OV_ODBC3, 0);
    check("SQLSetEnvAttr", ret, SQL_HANDLE_ENV, henv, SQL_SUCCESS);

    /* --- connect -------------------------------------------------------- */
    ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    check("SQLAllocHandle(DBC)", ret, SQL_HANDLE_DBC, hdbc, SQL_SUCCESS);

    ret = SQLDriverConnect(hdbc, NULL,
                           (SQLCHAR *)argv[1], SQL_NTS,
                           NULL, 0, NULL,
                           SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLDriverConnect failed\n");
        print_diag("SQLDriverConnect", SQL_HANDLE_DBC, hdbc);
        rc = EXIT_FAILURE;
        goto cleanup;
    }
    printf("SQLDriverConnect succeeded ...\n");

    /* --- execute statement --------------------------------------------- */
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    check("SQLAllocHandle(STMT)", ret, SQL_HANDLE_STMT, hstmt, SQL_SUCCESS);
    SQLCHAR *batch =
        (SQLCHAR *)
        "SET XACT_ABORT OFF; "
        "SELECT 1 AS n; "
        "SELECT 1/0 AS boom; "
        "SELECT 2 AS n;";
    ret = SQLExecDirect(hstmt, batch, SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLExecDirect failed unexpectedly.\n");
        print_diag("SQLExecDirect", SQL_HANDLE_STMT, hstmt);
        goto cleanup;
    }
    printf("SQLExecDirect succeeded ...\n");

    /* Consume result set 0 (SELECT 1). */
    {
        SQLINTEGER n;
        SQLLEN     ind;
        SQLBindCol(hstmt, 1, SQL_C_LONG, &n, sizeof(n), &ind);
        SQLFetch(hstmt);
        if (n == 1)
            printf("first SQLFetch succeeded ...\n");
        else {
            printf("first SQLFetch: expected 1, got %d\n", n);
            rc = EXIT_FAILURE;
        }
        SQLFreeStmt(hstmt, SQL_UNBIND);
    }

    // Move to the next (failing) statement's results set.
    ret = SQLMoreResults(hstmt);
    switch (ret) {
    case SQL_SUCCESS_WITH_INFO:
        printf("first SQLMoreResults succeeded (returning SQL_SUCCESS_WITH_INFO as expected) ...\n");
        break;
    case SQL_ERROR:
        printf("first SQLMoreResults: expected SQL_SUCCESS_WITH_INFO, got SQL_ERROR ...\n");
        rc = EXIT_FAILURE;
        break;
    case SQL_SUCCESS:
        printf("first SQLMoreResults: expected SQL_SUCCESS_WITH_INFO, got SQL_SUCCESS ...\n");
        rc = EXIT_FAILURE;
        break;
    default:
        printf("first SQLMoreResults: expected SQL_SUCCESS_WITH_INFO, got %d ...\n", ret);
        rc = EXIT_FAILURE;
        break;
    }
    print_diag("first SQLMoreResults", SQL_HANDLE_STMT, hstmt);

    // Find out if the statement handle is still valid. Some drivers (Microsoft)
    // mistakenly return SQL_ERROR for the first call to SQLMoreResults() when
    // they really mean SQL_SUCCESS_WITH_INFO, because they leave the handle open
    // for business. Others (FreeTDS) return SQL_SUCCESS for the previous call,
    // but the statement handle is no longer alive.
    ret = SQLMoreResults(hstmt);
    switch (ret) {
    case SQL_SUCCESS:
        printf("second SQLMoreResults succeeded (returning SQL_SUCCESS as expected) ...\n");
        break;
    case SQL_SUCCESS_WITH_INFO:
        printf("second SQLMoreResults: expected SQL_SUCCESS, got SQL_SUCCESS_WITH_INFO\n");
        rc = EXIT_FAILURE;
        break;
    case SQL_ERROR:
        printf("second SQLMoreResults: expected SQL_SUCCESS, got SQL_ERROR\n");
        rc = EXIT_FAILURE;
        break;
    default:
        printf("second SQLMoreResults: expected SQL_SUCCESS, got %d\n", ret);
        rc = EXIT_FAILURE;
        break;
    }
    print_diag("SQLMoreResults", SQL_HANDLE_STMT, hstmt);

    /* Consume result set 2 (SELECT 2). */
    {
        SQLINTEGER n;
        SQLLEN     ind;
        SQLBindCol(hstmt, 1, SQL_C_LONG, &n, sizeof(n), &ind);
        if (!SQL_SUCCEEDED(SQLFetch(hstmt)) || n != 2) {
            printf("second SQLFetch: statement handle is no longer valid ...\n");
            rc = EXIT_FAILURE;
        }
        else
            printf("second SQLFetch succeeded ...\n");
        SQLFreeStmt(hstmt, SQL_UNBIND);
    }

cleanup:
    if (hstmt != SQL_NULL_HSTMT) SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    if (hdbc  != SQL_NULL_HDBC)  { SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); }
    if (henv  != SQL_NULL_HENV)  SQLFreeHandle(SQL_HANDLE_ENV, henv);
    return rc;
}

Output

SQLDriverConnect succeeded ...
SQLExecDirect succeeded ...
first SQLFetch succeeded ...
first SQLMoreResults: expected SQL_SUCCESS_WITH_INFO, got SQL_ERROR ...
  first SQLMoreResults DIAG: SQLSTATE=22012 native=8134 msg=[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Divide by zero error encountered.
second SQLMoreResults succeeded (returning SQL_SUCCESS as expected) ...
second SQLFetch succeeded ...

Environment

Component	Version
OS	macOS Tahoe 26.4 (M1)
DBMS	Microsoft SQL Server 2022 (RTM-CU24) (KB5080999) - 16.0.4245.2 (X64)
Driver Manager	unixODBC 2.3.14
ODBC Driver	ODBC Driver 18 for SQL Server 18.5.1.1

Footnotes

1. The design decision to have SQL_ERROR represent different semantics depending on the statement position seems flawed to me, but that's a separate issue. ↩

[jahnvi480]

Hi @bkline, thanks for the detailed report and the C repro!

We've confirmed the behavior you described, the Microsoft ODBC Driver (both v17 and v18) does return SQL_ERROR instead of SQL_SUCCESS_WITH_INFO for mid-batch failures when XACT_ABORT is OFF and the failed statement is not the last in the batch. The statement handle remains valid despite the incorrect return code.

While the ODBC driver-level fix is tracked separately, this was also causing a problem in the PHP driver: our core_sqlsrv_next_result() was calling SQLCancel() on the statement handle when it saw SQL_ERROR, which destroyed all remaining result sets in the batch. So even though the handle was still valid, users of sqlsrv_next_result() and PDOStatement::nextRowset() could never reach subsequent results.

We've opened PR #1600 to fix this on the PHP driver side. The fix reports the error through the normal error channels (sqlsrv_errors() / PDOStatement::errorInfo()) without aborting the batch, so users can continue calling next_result() / nextRowset() to reach the remaining result sets.

[bkline]

Thanks very much for the speedy followup, @jahnvi480, very impressive, as always! 🙌

While the ODBC driver-level fix is tracked separately, ...

Thanks for passing this on to that team. 🙏