乱序读取列会返回错误的值(SQL Server ODBC驱动程序) [英] Reading columns out of order returns incorrect values (SQL Server ODBC driver)

查看：84
本文介绍了乱序读取列会返回错误的值(SQL Server ODBC驱动程序)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

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

使用

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已存档)

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

以前，Microsoft

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'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 |

注意:显然，当我发现错误时，我正在从真实表中选择真实数据.在创建MRCE的过程中，发现上述与数据库无关的查询也触发了失败.

Note: Obviously when i discovered the bug i was selecting real data from a real table. In my quest to create a MRCE found the above database-agnostic query also triggers the failure.

我正在使用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>

奖金阅读

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆