乱序读取列会返回错误的值(SQL Server ODBC驱动程序)

问题描述

• 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.

短版

如果我按 SELECT 顺序读取 uniqueidentifier 值，则会返回正确的值:

Short Version

If i read uniqueidentifier values in SELECT order, i am returned the correct values:

• ColumnB :(读取有效值)
• ColumnC (读取有效值)

• ColumnB: (read valid value)
• ColumnC (read valid value)

如果我在选择顺序之外读取了uniqueidentifier列值，则较早的列将不返回任何内容(有时会返回垃圾):

If i read uniqueidentifier column values outside of select order, the earlier columns return nothing (and sometimes junk):

• ColumnC (读取有效值)
• ColumnB (返回空)

• ColumnC (read valid value)
• ColumnB (returns empty)

我已经对此进行了测试:

I've tested this on:

• Microsoft SQL Azure(RTM)-12.0.2000.8
• Microsoft SQL Server 2012(SP3)
• Microsoft SQL Server 2008 R2(SP2)
• Microsoft SQL Server 2005-9.00.5000.00(Intel X86)
• Windows 10
• Windows 7
• Windows Vista

编辑:提供的代码示例:

• C#(命令行应用程序)
• Delphi (命令行应用程序)
• Javascript (命令行cscript)
• HTML + Javascript (仅Internet Explorer)

• C# (command-line application)
• Delphi (command-line application)
• Javascript (command line cscript)
• Html+Javascript (Internet Explorer only)

使用关于OleDb驱动程序已弃用的公告，我想使用SQL Server的ODBC驱动程序进行测试.当我更改连接以使用其中一种SQL Server ODBC驱动程序(例如"{SQL Server}")并执行相同的SQL语句时.

With the announcement of the deprecation of OleDb drivers, I wanted to test using the ODBC drivers for SQL Server. When I change the connection to use one of the SQL Server ODBC drivers (e.g. "{SQL Server}") and execute the same SQL statement.

更新-不建议使用:六年后，Microsoft已宣布取消使用SQL Server OLE DB驱动程序. (存档)

Update - Undeprecated: Six years later, Microsoft has announced the un-deprecation the SQL Server OLE DB driver. (archive)

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

With this in mind, we have decided to undeprecate OLE DB and release a new version by March 2018.

---

我正在对三个固定列进行查询:

---

I'm issuing a query for three fixed columns:

SELECT
   CAST('Hello' AS varchar(max)) AS ColumnA,
   CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB,
   CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC

这意味着有三列:

| ColumnA            | ColumnB                              | ColumnC                              |
| varchar(max)       | uniqueidentifier                     | uniqueidentifier                     |
|--------------------|--------------------------------------|--------------------------------------|
| 'Hello'            | C6705EDE-CE58-4AB9-81BE-679AC1E75DE6 | 2466C151-88EC-40C0-B091-25B6BD74070C |

我正在使用ADO(本地COM)和 SQL Server ODBC驱动程序来连接到SQL Server:

I am using ADO (native COM) and the SQL Server ODBC driver to connect to SQL Server:

Provider=MSDASQL;Driver={SQL Server};Server={vader};Database=master;Trusted_Connection=Yes;

首先读取C列会导致ColumnB为空

在此MRCE中，我仅读取两个uniqueidentifier列的值.

recordset.Fields['ColumnB'].Value;
recordset.Fields['ColumnC'].Value;

并且如果我依次读取两列 ，则显示的值是正确的:

and if i read the two columns in that order, the values come out correct:

• ColumnB :"C6705EDE-CE58-4AB9-81BE-679AC1E75DE6"(变体类型VT_BSTR)
• ColumnC :"2466C151-88EC-40C0-B091-25B6BD74070C"(变体类型VT_BSTR)

• ColumnB: "C6705EDE-CE58-4AB9-81BE-679AC1E75DE6" (Variant Type VT_BSTR)
• ColumnC: "2466C151-88EC-40C0-B091-25B6BD74070C" (Variant Type VT_BSTR)

但是，如果我以其他顺序读取列值:

But if i read the column values in the other order:

• ColumnC :"2466C151-88EC-40C0-B091-25B6BD74070C"(变体类型VT_BSTR)
• ColumnB : (empty) (变量类型VT_EMPTY)

• ColumnC: "2466C151-88EC-40C0-B091-25B6BD74070C" (Variant Type VT_BSTR)
• ColumnB: (empty) (Variant Type VT_EMPTY)

using System;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            TestIt();
        }

        private static void TestIt()
        {
            String serverName = "vader";
            String CRLF = "\r\n";

            String connectionString = "Provider=MSDASQL;Driver={SQL Server};Server={" + serverName + "};Database=master;Trusted_Connection=Yes;";
            WriteLn("ConnectionString: " + connectionString);
            WriteLn("");

            Int32 adOpenForwardOnly = 0;
            Int32 adLockReadOnly = 1;
            Int32 adCmdText = 1;

            dynamic rs = CreateOleObject("ADODB.Recordset");

            String sql = "SELECT " + CRLF +
                " CAST('Hello' AS varchar(max)) AS ColumnA, " + CRLF +
                " CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB," + CRLF +
                " CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC";

            WriteLn("Command text:");
            WriteLn(sql);
            WriteLn("");

            WriteLn("Executing query");
            rs.open(sql, connectionString, adOpenForwardOnly, adLockReadOnly, adCmdText);
            WriteLn("Query complete");

            if (rs.EOF) return; //just to shut people up

            var columnC = rs("ColumnC").Value;
            var columnB = rs("ColumnB").Value;

            WriteLn("ColumnB: " + columnB);
            WriteLn("ColumnC: " + columnC);
        }

        private static dynamic CreateOleObject(string progID)
        {
            Type comType = Type.GetTypeFromProgID(progID);
            var instance = Activator.CreateInstance(comType);

            return instance;
        }

        private static void WriteLn(string v)
        {
            Console.WriteLine(v);
        }
    }
}

有结果:

ConnectionString: Provider=MSDASQL;Driver={SQL Server};Server={vader};Database=master;Trusted_Connection=Yes;

Command text:
SELECT
 CAST('Hello' AS varchar(max)) AS ColumnA,
 CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB,
 CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC

Executing query
Query complete
ColumnB:
ColumnC: {2466C151-88EC-40C0-B091-25B6BD74070C}

最小代码示例(Delphi)

program Project3;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ADOInt,
  ComObj,
    ActiveX;

function DataTypeEnumToStr(t: DataTypeEnum): string;
begin
    case t of
    adEmpty: Result := 'adEmpty';
    adSmallInt: Result := 'adSmallInt';
    adInteger: Result := 'adInteger';
    adTinyInt: Result := 'adTinyInt';
    adBigInt: Result := 'adBigInt';
    adUnsignedTinyInt: Result := 'adUnsignedTinyInt';
    adUnsignedSmallInt: Result := 'adUnsignedSmallInt';
    adUnsignedInt: Result := 'adUnsignedInt';
    adUnsignedBigInt: Result := 'adUnsignedBigInt';
    adSingle: Result := 'adSingle';
    adDouble: Result := 'adDouble';
    adCurrency: Result := 'adCurrency';
    adDecimal: Result := 'adDecimal';
    adNumeric: Result := 'adNumeric';
    adBoolean: Result := 'adBoolean';
    adError: Result := 'adError';
    adUserDefined: Result := 'adUserDefined';
    adVariant: Result := 'adVariant';
    adIDispatch: Result := 'adIDispatch';
    adIUnknown: Result := 'adIUnknown';
    adGUID: Result := 'adGUID';
    adDate: Result := 'adDate';
    adDBDate: Result := 'adDBDate';
    adDBTime: Result := 'adDBTime';
    adDBTimeStamp: Result := 'adDBTimeStamp';
    adBSTR: Result := 'adBSTR';
    adChar: Result := 'adChar';
    adVarChar: Result := 'adVarChar';
    adLongVarChar: Result := 'adLongVarChar';
    adWChar: Result := 'adWChar';
    adVarWChar: Result := 'adVarWChar';
    adLongVarWChar: Result := 'adLongVarWChar';
    adBinary: Result := 'adBinary';
    adVarBinary: Result := 'adVarBinary';
    adLongVarBinary: Result := 'adLongVarBinary';
    adChapter: Result := 'adChapter';
    adFileTime: Result := 'adFileTime';
    adDBFileTime: Result := 'adDBFileTime';
    adPropVariant: Result := 'adPropVariant';
    adVarNumeric: Result := 'adVarNumeric';
    adArray: Result := 'adArray';
    else
        Result := IntToStr(t);
    end;
end;

procedure TestLoadingGUID;
var
    connectionString: string;
    sql: string;
    rs: _Recordset;
    s: string;
    guid: TGUID;
    i: Integer;
    fld: Field;

    function DumpField(const FieldName: string): string;
    var
        sValue: string;
        vt: TVarType;
        value: OleVariant;
    begin
        WriteLn('Reading '+FieldName+' column');
        value := rs.Fields[FieldName].Value;

        sValue := value;
        vt := TVarData(value).VType;
        WriteLn('   VType: '+IntToStr(vt));
        WriteLn('   Value: "'+sValue+'" (as string)');
        WriteLn('');
    end;

begin
{
    Tested:
        Windows 10
        Windows 7

        Microsoft SQL Server 2012 (SP3)
        Microsoft SQL Server 2008 R2 (SP2)
        Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
}

    Write('Enter name of server to connect to (leave blank for VADER): ');
    ReadLn(s);

    if s = '' then
        s := 'vader';

    connectionString := 'Provider=MSDASQL;Driver={SQL Server};Server={'+s+'};Database=master;Trusted_Connection=Yes;';
    WriteLn('ConnectionString: '+connectionString);
    WriteLn;


//  sql := 'SELECT CAST(NULL AS varchar(max)) AS ColumnA, newid() AS ColumnB, newid() as ColumnC';
    sql := 'SELECT '+#13#10+
            '   CAST(''Hello'' AS varchar(max)) AS ColumnA, '+#13#10+
            '   CAST(''C6705EDE-CE58-4AB9-81BE-679AC1E75DE6'' AS uniqueidentifier) AS ColumnB,'+#13#10+
            '   CAST(''2466C151-88EC-40C0-B091-25B6BD74070C'' AS uniqueidentifier) AS ColumnC';


    rs := CoRecordset.Create;
    rs.Open(sql, connectionString, adOpenForwardOnly, adLockReadOnly, adCmdText);
    WriteLn('');

    WriteLn('Command text: ');
    WriteLn(sql);
    WriteLn;

    if rs.EOF then Exit; //just to shut people up

    WriteLn('Recordset Fields');
    for i := 0 to rs.Fields.Count-1 do
    begin
        fld := rs.Fields[i];
        if fld.DefinedSize = MaxInt then
            WriteLn(Format('   %d.  %s: %s(%s)', [i, fld.Name, DataTypeEnumToStr(fld.Type_), 'max']))
        else
            WriteLn(Format('   %d.  %s: %s(%d)', [i, fld.Name, DataTypeEnumToStr(fld.Type_), fld.DefinedSize]));
    end;
    WriteLn('');
    WriteLn('');

    WriteLn('Fields["ColumnA"]: "'+rs.Fields['ColumnA'].Value+'"  (VType: '+IntToStr(TVarData(rs.Fields['ColumnA'].Value).VType)+')');
    WriteLn('Fields["ColumnC"]: "'+rs.Fields['ColumnC'].Value+'"  (VType: '+IntToStr(TVarData(rs.Fields['ColumnC'].Value).VType)+')');
    WriteLn('Fields["ColumnB"]: "'+rs.Fields['ColumnB'].Value+'"  (VType: '+IntToStr(TVarData(rs.Fields['ColumnB'].Value).VType)+')');
    WriteLn('');

    WriteLn('Fields[0]: "'+rs.Fields[0].Value+'"  (VType: '+IntToStr(TVarData(rs.Fields[0].Value).VType)+')');
    WriteLn('Fields[2]: "'+rs.Fields[2].Value+'"  (VType: '+IntToStr(TVarData(rs.Fields[2].Value).VType)+')');
    WriteLn('Fields[1]: "'+rs.Fields[1].Value+'"  (VType: '+IntToStr(TVarData(rs.Fields[1].Value).VType)+')');
    WriteLn('');



    DumpField('ColumnA');
    DumpField('ColumnB');
    s := DumpField('ColumnC');

    if s = '' then
    begin
        WriteLn(Format('WARNING: ColumnB expected to not-empty, but was "%s"',  [s]));
        Exit;
    end;
end;


begin
  try
        CoInitialize(nil);
        TestLoadingGUID;
  except
     on E: Exception do
        Writeln(E.ClassName, ': ', E.Message);
  end;

    WriteLn('Press enter to close');
    Readln;
end.

和控制台输出

Enter name of server to connect to (leave blank for VADER):
ConnectionString: Provider=MSDASQL;Driver={SQL Server};Server={vader};Database=master;Trusted_Connection=Yes;


Command text:
SELECT
        CAST('Hello' AS varchar(max)) AS ColumnA,
        CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB,
        CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC

Recordset Fields
   0.  ColumnA: adLongVarChar(max)
   1.  ColumnB: adGUID(16)
   2.  ColumnC: adGUID(16)


Fields["ColumnA"]: "Hello"  (VType: 1)
Fields["ColumnC"]: "{2466C151-88EC-40C0-B091-25B6BD74070C}"  (VType: 8)
Fields["ColumnB"]: ""  (VType: 0)

Fields[0]: ""  (VType: 0)
Fields[2]: "{2466C151-88EC-40C0-B091-25B6BD74070C}"  (VType: 8)
Fields[1]: ""  (VType: 0)

Reading ColumnA column
   VType: 0
   Value: "" (as string)

Reading ColumnB column
   VType: 0
   Value: "" (as string)

Reading ColumnC column
   VType: 8
   Value: "{2466C151-88EC-40C0-B091-25B6BD74070C}" (as string)

WARNING: ColumnB expected to not-empty, but was ""
Press enter to close

最小代码示例(JavaScript)

为扩大受众范围，以下是javascript中与上述相同的代码:

Minimum Code Example (Javascript)

To widen the audience, here's the same above code in javascript:

OdbcFails.js

main();

function main() {
  serverName = "vader";
  CRLF = "\r\n";

  var connectionString = "Provider=MSDASQL;Driver={SQL Server};Server={"+serverName+"};Database=master;Trusted_Connection=Yes;";
    WriteLn("ConnectionString: "+connectionString);
    WriteLn("");

  adOpenForwardOnly = 0;
  adLockReadOnly = 1;
  adCmdText = 1;
  var rs = new ActiveXObject("ADODB.Recordset");

  var sql = "SELECT "+CRLF+
            " CAST('Hello' AS varchar(max)) AS ColumnA, "+CRLF+
            " CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB,"+CRLF+
            " CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC";

    WriteLn("Command text:");
    WriteLn(sql);
    WriteLn("");

  WriteLn("Executing query");
  rs.open(sql, connectionString, adOpenForwardOnly, adLockReadOnly, adCmdText);
  WriteLn("Query complete");

    if (rs.EOF) return; //just to shut people up

  var columnC = rs("ColumnC").Value;
  var columnB = rs("ColumnB").Value;

   WriteLn("ColumnB: "+columnB);
   WriteLn("ColumnC: "+columnC);

}

function WriteLn(str) {
  WScript.Echo(str);
}

如果您运行:

C:\Users\ian>cscript OdbcFails.js

C:\Users\ian>cscript OdbcFails.js

Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

ConnectionString: Provider=MSDASQL;Driver={SQL Server};Server={vader};Database=master;Trusted_Connection=Yes;

Command text:
SELECT
 CAST('Hello' AS varchar(max)) AS ColumnA,
 CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB,
 CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC

Executing query
Query complete
ColumnB: undefined
ColumnC: {2466C151-88EC-40C0-B091-25B6BD74070C}

最小代码示例(html + javascript-仅Internet Explorer)

<!doctype html>
<html>

<head>
    <script>
        function WriteLn(str) {
            console.log(str);
        }

        function main() {
            serverName = "vader";
            CRLF = "\r\n";

            var connectionString = "Provider=MSDASQL;Driver={SQL Server};Server={" + serverName + "};Database=master;Trusted_Connection=Yes;";
            WriteLn("ConnectionString: " + connectionString);
            WriteLn("");

            adOpenForwardOnly = 0;
            adLockReadOnly = 1;
            adCmdText = 1;
            var rs = new ActiveXObject("ADODB.Recordset");

            var sql = "SELECT " + CRLF +
                " CAST('Hello' AS varchar(max)) AS ColumnA, " + CRLF +
                " CAST('C6705EDE-CE58-4AB9-81BE-679AC1E75DE6' AS uniqueidentifier) AS ColumnB," + CRLF +
                " CAST('2466C151-88EC-40C0-B091-25B6BD74070C' AS uniqueidentifier) AS ColumnC";

            WriteLn("Command text:");
            WriteLn(sql);
            WriteLn("");

            WriteLn("Executing query");
            rs.open(sql, connectionString, adOpenForwardOnly, adLockReadOnly, adCmdText);
            WriteLn("Query complete");

            if (rs.EOF) return; //just to shut people up

            var columnC = rs("ColumnC").Value;
            var columnB = rs("ColumnB").Value;

            WriteLn("ColumnB: " + columnB);
            WriteLn("ColumnC: " + columnC);

        }

        main();

    </script>

    <body>
    </body>
    <script>

奖金阅读

• MSDN博客: Microsoft正在与ODBC进行本地关系数据访问 ( =" archive )
• ADO.Net博客: Microsoft SQL Server OLEDB提供程序弃用公告 ( =" 存档 )
• MSDN:将SQL Server应用程序从OLE DB转换为ODBC (存档)
• HAL2020: OLE DB和SQL Server:历史记录，结束游戏和某些Microsoft污垢" (存档)
  • 无效的描述符索引读取varchar(max) (存档)

  Bonus Reading

  • MSDN Blogs: Microsoft is Aligning with ODBC for Native Relational Data Access (archive)
  • ADO.Net Blog: Microsoft SQL Server OLEDB Provider Deprecation Announcement (archive)
  • MSDN: Converting SQL Server Applications from OLE DB to ODBC (archive)
  • HAL2020: OLE DB and SQL Server: History, End-Game, and some Microsoft "dirt" (archive)
    • Invalid descriptor index reading varchar(max) (archive)

    推荐答案

    答案是，这种行为不会在ODBC驱动程序中得到解决.

    The answer is that this behviour won't be fixed in the ODBC driver.

    在1980年代后期，强制客户端仅按顺序从行缓冲区中读取列具有性能上的好处.您会问驱动程序是否允许您通过 SqlGetInfo 函数:

    In the late 1980s there was a performance benefit to forcing the client to only read columns out of the row buffer in order. You would ask the driver if you were allowed to read column values in any order through the the SqlGetInfo function:

    SqlGetInfo(..., SQL_GD_ANY_ORDER, ...) //returns true or false
    

    可以为任何未绑定的列(包括最后一个绑定列之前的列)调用
    • SQL_GD_ANY_COLUMN = SQLGetData .请注意，除非也返回SQL_GD_ANY_ORDER，否则列必须按列号升序调用.
    可以以任何顺序为未绑定的列调用
    • SQL_GD_ANY_ORDER = SQLGetData .请注意，除非最后一个SQL_GD_ANY_COLUMN也返回，否则只能对最后一个绑定列之后的列调用 SQLGetData .
    • SQL_GD_ANY_COLUMN = SQLGetData can be called for any unbound column, including those before the last bound column. Note that the columns must be called in order of ascending column number unless SQL_GD_ANY_ORDER is also returned.
    • SQL_GD_ANY_ORDER = SQLGetData can be called for unbound columns in any order. Note that SQLGetData can be called only for columns after the last bound column unless SQL_GD_ANY_COLUMN is also returned.

    即使这些天计算机具有超过4MB的RAM，现代的SQL Server ODBC驱动程序从Windows 3.0时代开始继续接受此限制:

    Even though computers have more than 4MB of RAM these days, the modern SQL Server ODBC driver continues to opt-in to this limitation from the Windows 3.0 era:

    他们很好，可以支持，例如已经使用了17年的OLEDB驱动程序以及ADO.NET SqlClient驱动程序.但是他们没有；因此ODBC驱动程序是不适合实际使用的令人讨厌的可憎对象.

    They very well could support such a thing, as 17 year old OLEDB drivers, as well as the ADO.NET SqlClient drivers do. But they don't; so the ODBC driver is brain-dead abomination unsuitable for real-world use.

    您需要继续使用:

    • SQLOLEDB(受支持的 )
    • SQLNCLI(不建议使用)
    • ADO.net SqlClient(受支持)

    奖励阅读

    • 用于SQL Server和SQLGetData的ODBC驱动程序11局限性

    客户端驱动程序支持政策

    这篇关于乱序读取列会返回错误的值(SQL Server ODBC驱动程序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！