OLE DB provider returns DATE value in SQLVARIANT column as BSTR

  atl, c++, oledb, sql

I have a special table, where I record some values in a SQL_VARIANT column.
This works nicely. Here simplified

CREATE TABLE [tbl] (
    [IdColumn] [int] NOT NULL,
    [NewValue] [sql_variant] NULL,
) 

To read this table I use a my current ATL OLE DB stuff and I can easily bind this column as VARIANT.
For DATE columns.

Here a sample query to my table

SELECT [IdColumn], [NewValue], SQL_VARIANT_PROPERTY([NewValue],'BaseType') FROM [tbl]

Output. The interesting column is 5571

IdColumn    NewValue        BaseType
2           A - NULL        nvarchar
5           Test            nvarchar
6           Test            nvarchar
2221        1858            int
2222        N001/04711      nvarchar
2224        10              float
2225        4803            int
5570        2021-04-03 12:00:00.000 datetime
5571        2021-03-02 00:00:00.000 date
5573        1               tinyint
5574        2               tinyint
5575        45              float
5576        15555           float
5577        2073            int
5578        99999           int
5579        4550            int
5580        9               int
5581        398             int

But the column is not bound as a VT_DATE or VT_R8 as I would expect. It is returned by my OLE DB provider as a VT_BSTR in the form yyy-mm-dd.

Here the simple code to read the table.

class CTestAccessor
{
public:
    long        m_lIdColumn;
    VARIANT     m_variant;

    BEGIN_COLUMN_MAP(CTestAccessor)
        COLUMN_ENTRY( 1, m_lIdColumn )
        COLUMN_ENTRY( 2, m_variant )
    END_COLUMN_MAP( )
};

void Test()
{
    // Determine the new date and time
    // Get the connector and execute
    CCommand<CAccessor<CTestAccessor> > cmd;
    HRESULT hr = cmd.Open(CONNECTOR.GetDataConnection(),_T("Select IdColumn, NewValue FROM tbl"));
    if (FAILED(hr))
        return;
        
    while ((hr = cmd.MoveNext())==S_OK)
    {
        TRACE(_T(__FUNCTION__ _T(" %d - %dn")),cmd.m_lIdColumn, cmd.m_variant.vt);
    }    
}

The output with the corresponding VT- types

Test 2 - 8
Test 5 - 8
Test 6 - 8
Test 2221 - 3
Test 2222 - 8
Test 2224 - 5
Test 2225 - 3
Test 5570 - 7
Test 5571 - 8
Test 5573 - 17
Test 5574 - 17
Test 5575 - 5
Test 5576 - 5
Test 5577 - 3
Test 5578 - 3
Test 5579 - 3
Test 5580 - 3
Test 5581 - 3

Easily can be seen that the column 5571 is of type DATE but returned as VT_BSTR(8). Whereas column 5570 defined as DATETIME is returned as VT_DATE(7)

The used provider is SQLNCLI11. Same happens using provider MSOLEDBSQL.1

  • I see no way to bind a SQL_VARIANT as other vales as VARIANT. Or is there a different way to bind this column?
  • Is there a known problem or known bug?
  • I don’t want to make the query complicated in using casts

Source: Windows Questions C++

LEAVE A COMMENT