SQL Server ODBC Driver Does Not Cause Errors

Question

This question is part of a series of errors in the Microsoft ODBC Driver:

• The ODBC driver cannot raise errors; but instead suppresses them .
• Reading columns out of order returns incorrect results
• Cannot execute stored procedure that is SYNONYM

Microsoft said it would not fix these errors in the ODBC driver.

Background

If I have a sample table:

CREATE TABLE Wallet (
    WalletID int NOT NULL,
    Name varchar(50) NOT NULL
)

I am trying to release sql that inserts into a table without specifying the value of the NOT NULL column of WalletID:

INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')
INSERT INTO Wallet (Name) VALUES ('Ardent Defender') --Constraint violation

SQL Server gives an error message:

( 1 ())
Msg 515, 16, 2, 2
NULL "WalletID", "Scratch.dbo.Wallet"; . INSERT .
.

.

ADO/OLEDB OLE DB SQL Server (SQLOLEDB):

Provider = SQLOLEDB; = hyperion; = Contoso; = Trub4dor;

INSERT, ADO/OLEDB/COM , :

NULL "WalletID", ""; . INSERT

.

ODBC

OLE DB ( MS , ODBC ) , SQL Server ODBC:

Provider = MSDASQL; Driver = {SQL Server}; Server = {hyperion}; UID = {Contoso}; PWD = {Trub4dor};

---

- . Microsoft OLE DB SQL Server OLE DB SQL Server: msoledbsql. ()

Microsoft Microsoft OLE DB SQL Server, SQL Server (SNAC). , Windows, Azure SQL Database JDBC ODBC . , , , SQL Server - OLE DB, .

With this in mind, we decided to remove OLE DB and publish a new version in the first quarter of the calendar year 2018 March 2018.

---

I release my batch:

INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')
INSERT INTO Wallet (Name) VALUES ('Ardent Defender')

I was surprised to learn the same SQL statement:

• which triggers the error INSERT FAILSin SQL Server itself:

  

• resulting in a client-side error when using the SQL Server OLE DB provider

When using the ODBC driver

will be no problem . The statement is executed without any error.

I was confused for about an hour when my SQL statements were executed without errors, but the rows did not appear in the database.

Silent failure

Obviously, silent failure is not good.

• And obviously, I just can't use the SQL Server ODBC driver ,
• Microsoft OLE DB SQL Server.

?

ADO-OLEDB-ODBC . ODBC? MSDASQL?

Psuedocode

Delphi ADO. - # .

String commandText = 
      "INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')"+CRLF+
      "INSERT INTO Wallet (Name) VALUES ('Ardent Defender')";

ADOConnection conn = new ADOConnection();
conn.ConnectionString = szConnectionString;
conn.Open();

HRESULT hr = conn.Execute(commandText, ref recordsAffected, [eoExecuteNoRecords]);

HRESULT - , FAILED.

Answer 1

SQL Server DONE_IN_PROC , SET NOCOUNT ON. ( ) ADO, , NextRecordset.

- SET NOCOUNT ON .

Answer 2

, ODBC.

, , ODBC SQL Server 2014:

• SQL Server 11.0
• ODBC 11 SQL Server
• SQL Server

. , , :

; : 515; : 23000; msg: [Microsoft] [ODBC SQL Server Driver] [SQLServer] NULL "WalletID", "Test.dbo.Walle t"; . INSERT . ; : 3621; : 01000; msg: [Microsoft] [ SQL Server ODBC] [SQL Server] .

, , , .

. , . , . , , SQL_ERROR .

:

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

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[])
{
    SQLHSTMT stmt = SQL_NULL_HSTMT;

    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 Native Client 11.0};Server=.\\TEST;Database=Test;Trusted_Connection=yes;";
//  SQLWCHAR     strConnect[256] = L"Driver={ODBC Driver 11 for SQL Server};Server=.\\TEST;Database=Test;Trusted_Connection=yes;";
    SQLWCHAR     strConnect[256] = L"Driver={SQL Server};Server=.\\TEST;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);
    }

    // Enable manual commit
    nResult = SQLSetConnectAttr(handleDBC, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleDBC, SQL_HANDLE_DBC);
    }

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

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

    nResult = SQLExecDirect(handleStatement, L"CREATE TABLE Wallet (WalletID int NOT NULL,  Name varchar(50) NOT NULL)", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleStatement, SQL_HANDLE_STMT);
    }

    //nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (2, 'Fan') INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')", SQL_NTS);
    nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (2, 'Fan') INSERT INTO Wallet (Name) VALUES ('Fan')", SQL_NTS);
    //nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (Name) VALUES ('Fan')", SQL_NTS);
    //nResult = SQLExecDirect(handleStatement, L"INSERT INTO Wallet (WalletID, Name) VALUES (1, 'Fan')", SQL_NTS);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleStatement, SQL_HANDLE_STMT);
    }

    // Try to end transaction. Will fail, but no error information can be fetched(?)
    nResult = SQLEndTran(SQL_HANDLE_DBC, handleDBC, SQL_COMMIT);
    if (!SQL_SUCCEEDED(nResult))
    {
        printErr(handleDBC, SQL_HANDLE_DBC);
    }

    return 0;
}

Answer 3

WalletID int auto-increment.