Login/Register
How to parameterize 12/30/1899 to SQL Server native client when DataTypeCompatility is on?
Asked Answered
Solved sql-serverdelphioledbado
C

1

7

Short Version

Trying to pass the datetime value 12/30/1899 to SQL Server, fails with Invalid date format - but only for the native client drivers, and only in DataTypeCompatiblity mode.

Long Version

When trying to use parameterized queries in ADO, against SQL Server:

SELECT ?

I parameterize the datetime value as an adDBTimeStamp:

//Language agnostic, vaguely C#-like pseudo-code
void TestIt()
{
   DateTime dt = new DateTime("3/15/2020");
   VARIANT v = DateTimeToVariant(dt);

   Command cmd = new Command();
   cmd.CommandText = "SELECT ? AS SomeDate";
   cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
   
   Connection cn = GetConnection();
   cmd.Set_ActiveConnection(cn);
   cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
}

And that works fine when the date is 3/15/2020.

You create a VARIANT, with a VType of 7 (VT_DATE), and a value that is an 8-byte floating point value:

VARIANT
   Int32  vt = 7; //VT_DATE
   Double date = 0;

But it fails on 12/30/1899

If I do the same test code with one particular datetime, it fails:

void TestIt()
{
   DateTime dt = new DateTime("12/30/1899");
   VARIANT v = DateTimeToVariant(dt);

   Command cmd = new Command();
   cmd.CommandText = "SELECT ? AS SomeDate";
   cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
   
   Connection cn = GetConnection();
   cmd.Set_ActiveConnection(cn);
   cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
}

The ADO OLEDB provider throws an exception (i.e. before it even reaches SQL Server):

Invalid date format

But it doesn't happen with all SQL Server OLEDB providers

When debugging this issue, I realized it doesn't happen with all of the SQL Server OLEDB providers. Microsoft generally has 4 OLE DB Providers for SQL Server:

  • SQLOLEDB: Microsoft OLE DB Provider for SQL Server (has shipped with Windows since Windows 2000)
  • SQLNCLI: SQL Server Native Client (shipped with SQL Server 2005)
  • SQLNCLI10: SQL Server Native Client 11.0 (shipped with SQL Server 2008)
  • SQLNCLI11: SQL Server Native Client 12.0 (shipped with SQL Server 2012)
  • MSOLEDBSQL: Microsoft OLE DB Driver for SQL Server (shipped with SQL Server 2016)

When trying it with some different providers, it does work fine for some:

  • SQLOLEDB: Works
  • SQLNCLI11 (without DataTypeCompatibility): Works
  • SQLNCLI11 (with DataTypeCompatiility on): Fails

DataTypeCompatibility?

Yes. ActiveX Data Objects (ADO), a friendly COM wrapper around the unfriendly COM OLEDB API, doesn't understand the new date, time, xml, datetime2, datetimeoffset data types. New OLEDB data type constants were created to represents these new types. So any existing OLEDB applications wouldn't understand the new constants.

To that end, a new keyword is supported by the "native" OLE DB drivers:

  • DataTypeCompatibility=80

which you can add to your connection string:

"Provider=SQLNCLI11; Data Source=screwdriver; User ID=hatguy; Password=hunter2;DataTypeCompatibility=80;"

This instructs the OLEDB driver to only return OLEDB data types that were in existance when OLEDB was first invented:

SQL Server data type SQLOLEDB SQLNCLI SQLNCLI
(w/DataTypeCompatibility=80)
Xml adLongVarWChar 141 (DBTYPE_XML) adLongVarChar
datetime adDBTimeStamp adDBTimeStamp adDBTimeStamp
datetime2 adVarWChar adDBTimeStamp adVarWChar
datetimeoffset adVarWChar 146 (DBTYPE_DBTIMESTAMPOFFSET) adVarWChar
date adVarWChar adDBDate adVarWChar
time adVarWChar 145 (DBTYPE_DBTIME2) adVarWChar
UDT 132 (DBTYPE_UDT) adVarBinary (documented,untested)
varchar(max) adLongVarChar adLongVarChar adLongVarChar
nvarchar(max) adLongVarWChar adLongVarWChar adLongVarWChar
varbinary(max) adLongVarBinary adLongVarBinary adLongVarBinary
timestamp adBinary adBinary adBinary

And there's the failure

When:

  • trying to parameterize a datetime value
  • with a value of 12/30/1899
  • when using a "native client" driver
  • and DataTypeCompatilibty is on
  • the driver itself chokes on the value
  • when its value is, in fact perfectly fine.

There's nothing inherently wrong with trying to use a date of '12/30/1899`:

  • SELECT CAST('18991230' AS datetime) works fine
  • it works fine in the original OLE DB driver
  • it works fine in the "native" OLE DB drivers
  • it just fails in the native driver with DataTypeCompatibility on

Obviously this is a bug in Microsoft OLE DB drivers. But it's an absolute truth that Microsoft will never, ever, ever, EVER, fix the bug.

So how to work-around it?

I can detect this special datetime, and I can try to work around this bug in our data access layers.

  • But I need a value I can place into a VARIANT structure,
  • that represents 12/30/1899 12:00:00 AM
  • that works under SQOLEDB
  • and under SQLNCLIxx drivers
  • and under MSOLEDBSQL driver
  • in DataTypeCompatibilityMode
  • (and what the hell, even with the mode off - although it's invalid to use ADO without it on)

T-SQL generated by the driver

When the OLE DB driver does bother to actually do what i say, we can profile the RPC generated:

SQOLEDB

exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime','1899-12-30 00:00:00'

SQLNCLI11

exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime2(0)','1899-12-30 00:00:00'

CMRE (Delphi)

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ComObj,
  ActiveX,
  ADOdb,
  ADOint,
  Variants;

function GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;
var
    connectionString: string;
begin
{
    SQLOLEDB - Default provider with Windows
    SQLNCLI11 - SQL Server 2008 native client
}
    connectionString := 'Provider='+Provider+'; Data Source=screwdriver;User ID=hydrogen;Password=hunter2;';
    if DataTypeCompatibility then
        connectionString := connectionString+'DataTypeCompatibility=80';

    Result := CoConnection.Create;
    Result.Open(connectionString, '', '', adConnectUnspecified);
end;

procedure Test(ProviderName: string; DataTypeCompatibility: Boolean);
var
    dt: TDateTime;
    v: OleVariant;
    cmd: _Command;
    cn: _Connection;
    recordsAffected: OleVariant;
    s: string;
begin
    dt := EncodeDate(1899, 12, 30);// 12/30/1899 12:00:00 AM (also known in Delphi as zero)
    v := dt; //the variant is of type VT_DATE (7)

    cmd := CoCommand.Create;
    cmd.CommandText := 'SELECT ? AS SomeDate';
    cmd.Parameters.Append(cmd.CreateParameter('', adDBTimeStamp, adParamInput, 0, v));

    try
        cn := GetConnection(ProviderName, DataTypeCompatibility);
    except
        on E: Exception do
            begin
                WriteLn('Provider '+ProviderName+' not installed: '+E.message);
                Exit;
            end;
    end;

    if SameText(ProviderName, 'SQLOLEDB') then
        s := ''
    else if DataTypeCompatibility then
        s := ' (with DataTypeCompatibility)'
    else
        s := ' (without DataTypeCompatibility)';

    cmd.Set_ActiveConnection(cn);
    try
        cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);
        WriteLn('Provider '+ProviderName+s+': success.');
    except
        on E:Exception do
            begin
                WriteLn('Provider '+ProviderName+s+' failed: '+E.Message);
            end;
    end;

end;

procedure Main;
begin
    CoInitialize(nil);

    Test('SQLOLEDB', False);        //SQL Server client that ships with Windows since 2000

    Test('SQLNCLI', False);     //SQL Server 2005 native client
    Test('SQLNCLI', True);      //SQL Server 2005 native client, w/ DataTypeCompatibilty

    Test('SQLNCLI10', False);   //SQL Server 2008 native client
    Test('SQLNCLI10', True);    //SQL Server 2008 native client, w/ DataTypeCompatibilty

    Test('SQLNCLI11', False);   //SQL Server 2012 native client
    Test('SQLNCLI11', True);    //SQL Server 2012 native client, w/ DataTypeCompatibilty

    Test('MSOLEDBSQL', False);  //SQL Server 2016 native client
    Test('MSOLEDBSQL', True);   //SQL Server 2016 native client, w/ DataTypeCompatibilty
end;


begin
  try
    Main;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
    WriteLn('Press enter to close');
    ReadLn;
end.

And while this is not a Delphi-specific question; I am using Delphi. So it's tagged as Delphi. If you complain I'm going to choke your tongue out.

Note: This is not ADO.net, it is ADO. It is not managed .NET Framework Class Library, it is the native Win32 COM OLE DB API.

Cuellar answered 15/3, 2020 at 16:39 Comment(4)
I am aware of the special significange of December 30, 1899 in OLE automation in general, and VARIANT record in particular. On the other hand, it's simply a C VARIANT structure with VType of VT_DATE, and a floating-point value of zero. – Cuellar
Why do you need to store that date in the database? – Canonicals
Set NumericScale property of your parameter to anything in 1-7 range to force mapping of your parameter to datetime2 instead of smalldatetime. Your CMRE should give the same results for any date outside 1900-01-01 - 2079-06-06 (SQLOLEDB will also fail if the date is outside datetime range). – Ingravescent
@Oliver There were things that happened on that date. For situations where i want the field to be NULL, i would write null instead. – Cuellar
C
0

BrakNicku had the answer.

Set NumericScale property of your parameter to anything in 1-7 range.

Changing the code from:

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);

to

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;

works.

It even works with SQLOLEDB driver against SQL Server 2000.

Precision and Scale of different data types

Returning rowsets from SQL Server containing different data types, i can ask OLEDB what the Precision, and NumericScale of various T-SQL data types are:

SQL Server type   ADO type               Precision  NumericScale  DefinedSize
----------------  ---------------------  ---------  ------------  -----------
int               adInteger (3)          10         255           4
real              adSingle (4)           7          255           4
money             adCurrency (6)         19         255           8
bit               adBoolean (11)         255        255           2
tinyint           adUnsignedTinyInt (17) 3          255           1
bigint            adBigInt (20)          19         255           8
uniqueidentifier  adGUID (72)            255        255           16
char(35)          adChar (129)           255        255           35
nchar(35)         adWChar (130)          255        255           35
decimal(15,5)     adNumeric (131)        15         5             19
datetime          adDBTimeStamp (135)    23         3             16
varchar(35)       adVarChar (200)        255        255           35
text              adLongVarChar (201)    255        255           2147483647
varchar(max)      adLongVarChar (201)    255        255           2147483647
nvarchar(35)      adVarWChar (202)       255        255           35
nvarchar(max)     adLongVarWChar (203)   255        255           1073741823
xml               adLongVarWChar (203)   255        255           1073741823
image             adLongVarBinary (205)  255        255           2147483647
varbinary(max)    adLongVarBinary (205)  255        255           2147483647

Since SQL Server returns a datetime field with a NumericScale of 3; there may be a virtue in changing:

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;

to

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 3;

Bonus Reading

Don't ever, ever, EVER try to parameterize a datetime as adDBTimestamp. There are data-loss bugs in Microsoft's SQL Server OLEDB drivers (all of them):

  • SQLOLEDB (1999) - Fails
  • SQLNCLI (2005) - Fails
  • SQLNCLI10 (2008) - Fails
  • SQLNCLI11 (2010) - Fails
  • MSOLEDBSQL (2012) - Fails

The correct answer is to parameterize all datetime values as a string (e.g. adVarChar) using the "ODBC 24-hour format":

  • yyyy-mm-dd hh:mm:ss.zzz
  • 2021-03-21 18:16:22.619
Cuellar answered 22/3, 2020 at 2:8 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.