SQL Server ODBC驱动程序未引发错误 [英] SQL Server ODBC Driver not raising errors

问题描述

此问题是Microsoft ODBC中一系列错误的一部分 驱动程序:

This question is part in a series of bugs in the Microsoft ODBC driver:

• ODBC驱动程序无法引发错误；而是压制它们
• 乱序读取列会返回错误的结果
• 无法执行作为SYNONYM的存储过程

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

Microsoft已表示不会在ODBC中修复这些错误 驱动程序.

Microsoft has said they will not be fixing these bugs in their ODBC driver.

背景

如果我有一个样本表:

Background

If i have a sample table:

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

我尝试发出插入表中的sql，而未为 NOT NULL WalletID列指定值:

i attempt to issue sql that inserts into the table without specifying a value to the NOT NULL WalletID column:

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

SQL Server给出错误:

SQL Server gives an error:

(受影响的1行)
消息515，第16级，状态2，第2行
无法将值NULL插入表"Scratch.dbo.Wallet"的"WalletID"列中；列不允许为空. INSERT失败.
该声明已终止.

(1 row(s) affected)
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'WalletID', table 'Scratch.dbo.Wallet'; column does not allow nulls. INSERT fails.
The statement has been terminated.

那是有道理的.

使用ADO/OLEDB和SQL Server OLE DB提供程序(SQLOLEDB)连接时:

When connecting using ADO/OLEDB, and the SQL Server OLE DB Provider (SQLOLEDB):

Provider = SQLOLEDB;数据源= hyperion;用户ID = Contoso;密码= Trub4dor;

Provider=SQLOLEDB;Data Source=hyperion;User ID=Contoso;Password=Trub4dor;

然后我执行 INSERT ，ADO/OLEDB/COM基础结构将返回一个故障，并作为一种例外返回到高级语言:

And i execute the the INSERT, the ADO/OLEDB/COM infrastructure returns a failure, which comes back to the high level language as an exception:

无法将值NULL插入表"Wallet"的"WalletID"列中；列不允许为空. INSERT失败

Cannot insert the value NULL into column 'WalletID', table 'Wallet'; column does not allow nulls. INSERT fails

这一切都说得通.

使用该本地客户端 OLE DB提供程序(和

With the derprication of that native client OLE DB providers (and the MS recommendation that you do not use the native client ODBC drivers), i thought i would try my hand at using the SQL Server ODBC driver:

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

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

---

更新-不建议使用:六年后，Microsoft 存档)

---

Update - Undeprecated: Six years later, Microsoft has announced the un-deprecation of OLE DB support for SQL Server, and the creations of a third OLE DB driver for SQL Server: msoledbsql. (archive)

以前，Microsoft 宣布弃用SQL Server的Microsoft OLE DB提供程序，它是SQL Server本机客户端(SNAC)的一部分.当时，我们做出这个决定的目的是，在我们使用Azure SQL数据库进入云时代时，尝试为开发人员提供有关Windows本机软件开发的更多信息，并尝试为开发人员利用JDBC和ODBC的相似之处.但是，在随后的审查中，确定过时是一个错误，因为SQL Server中的大量方案仍然依赖OLE DB，而更改这些方案将破坏某些现有的客户方案.

Previously, Microsoft announced deprecation of the Microsoft OLE DB Provider for SQL Server, part of the SQL Server Native Client (SNAC). At the time, this decision was made to try to provide more simplicity for the developer story around Windows native software development as we moved into the cloud era with Azure SQL Database, and to try to leverage the similarities of JDBC and ODBC for developers. However, during subsequent reviews it was determined that deprecation was a mistake because substantial scenarios within SQL Server still depend on OLE DB and changing those would break some existing customer scenarios.

考虑到这一点，我们决定取消过时的OLE DB 并在 2018日历年第一季度 2018年3月之前发布新版本.

With this in mind, we have decided to undeprecate OLE DB and release a new version by the first quarter of calendar year 2018 March 2018.

---

我签发我的批次:

---

I issue my batch:

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

我惊讶地发现了相同的SQL语句:

I was surprised to learn that same SQL statement:

• 会在SQL Server本身中触发INSERT FAILS错误:

，在使用SQL Server OLE DB提供程序时会导致客户端错误

that results in a client side error when using the SQL Server OLE DB Provider

将静默失败.该语句将执行而不会引发任何错误.

will silently fail when using the ODBC driver. The statement executes without any error being raised.

当我的SQL语句无错误运行时，我感到困惑了大约一个小时，但是这些行不会出现在数据库中.

I was confused for about an hour when my SQL statements ran without error, but the rows would not appear in the database.

显然，无声的失败是没有用的.

Obviously a silent failure is no good.

• 显然，我不能使用 SQL Server ODBC驱动程序，
• 并继续使用 用于SQL Server的Microsoft OLE DB提供程序提供程序.

• And obviously i can just not use the SQL Server ODBC driver,
• and continue to use the Microsoft OLE DB Provider for SQL Server provider.

但是发生了什么事?

如何告诉ADO-OLEDB-ODBC驱动程序报告错误.它是ODBC连接字符串设置吗?是MSDASQL连接字符串设置吗?

How do i tell the ADO-OLEDB-ODBC driver to report errors. Is it an ODBC connection string setting? Is it an MSDASQL connection string setting?

我实际上是将Delphi与ADO一起使用.但是我会将其转码为伪C#样式代码，以便于概念上的理解.

I'm actually using Delphi with ADO. But i'll transcode it into pseudo C# style code for easier conceptual understanding.

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 ，则会抛出本地语言异常.

In reality the checking of the HRESULT is handled by the language infrastructure and compiler magic - throwing a native language exception if it FAILED.

推荐答案

除非指定

SQL Server returns DONE_IN_PROC messages to the client after each statement unless you specify SET NOCOUNT ON. These messages (row counts) will affect ADO applications that are not coded to handle the multiple record sets returned by calling the NextRecordset method.

简单的解决方案是将SET NOCOUNT ON指定为批处理或存储过程中的第一条语句.

The easy solution is to specify SET NOCOUNT ON as the first statement in the batch or stored procedure.

这篇关于SQL Server ODBC驱动程序未引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！