Reading columns out of order returns incorrect values (SQL Server ODBC driver)

3
votes

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

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

Short Version

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

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

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

  • 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

Edit: Code examples provided for:

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

Background

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.

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

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.

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

This means that there are three columns:

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

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.

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;

Reading Column C first causes ColumnB to be Empty

In this MRCE, I am only reading the values of the two uniqueidentifier columns.

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" (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" (Variant Type VT_BSTR)
  • ColumnB: (empty) (Variant Type VT_EMPTY)

Minimum Code example (C#)

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);
        }
    }
}

with results:

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}

Minimum Code example (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.

And the console output

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

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);
}

And if you run:

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}

Minimum Code Example (html+javascript - Internet Explorer only)

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

Bonus Reading

1
sql-serverodbcado
What is the output of the Delphi code? (been many, many years since I've seen any!) - DavidG
@DavidG included output of Delphi code. Also included javascript version that you can run on your own command prompt! - Ian Boyd
So it's nothing to do with the GUID columns, you still see the issue if you have 2 VARCHAR columns. Secondly the problem goes away if you specify a cursor type of adOpenKeyset or adOpenDynamic. So I assume that the provider (Microsoft OLE DB Provider for ODBC) has a weird idea of what a forward-only cursor is supposed to be as it restricts you going backwards I guess. - DavidG

1 Answers

0
votes

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

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

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:

The SQL Server Native Client ODBC driver does not support using SQLGetData to retrieve data in random column order.

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.

You need to continue to use:

  • SQLOLEDB (supported)
  • SQLNCLI (deprecated)
  • ADO.net SqlClient (supported)

Bonus Reading