BUG: SQLOLEDB Incorrectly Converts Char Data When AutoTranslate Is Turned On (331999)


The information in this article applies to:

  • Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
  • Microsoft OLE DB Provider for SQL Server 2000 2000.80.380.0
  • Microsoft OLE DB Provider for SQL Server 2000 2000.81.7713.0
This article was previously published under Q331999

SYMPTOMS

When you try to retrieve character data from a column with different collation than the client code page in Microsoft SQL OLE DB Provider (SQLOLEDB), you may receive question marks (??) instead of correct data when all the following conditions are true:
  • A connection is made against a Microsoft SQL Server 2000 database.
  • SSPROP_INIT_AUTOTRANSLATE is set to TRUE. SSPROP_INIT_AUTOTRANSLATE is part of the provider-specific DBPROPSET_SQLSERVERDBINIT property set.
  • A column is bound to an OLEDB DBTYPE_WCHAR data type in the bindings structure.

CAUSE

This is caused by a bug in SQLOLEDB provider. No conversion should occur in the scenario that this article describes. This is in accordance with SQL Server 2000 Books Online. The following is an excerpt from SQL Server 2000 Books Online: 
SSPROP_INIT_AUTOTRANSLATE Type: VT_BOOL
R/W: Read/write
Default: VARIANT_TRUE
Description: OEM/ANSI character conversion.  
These settings have no effect on the conversions that occur for these transfers:  char, varchar, or text server data sent to a Unicode DBTYPE_WSTR variable on the client.
However, in this scenario, SQLOLEDB does the following:
  1. Converts the data from column collation on the SQL Server to Unicode.
  2. Converts the resultant Unicode string to the client code page.
  3. Converts the translated ANSI string to Unicode data.
As a result, you see question marks returned instead of correct data. However, when SSPROP_INIT_AUTOTRANSLATE is set to FALSE, the results are as you expect.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create the following table in Microsoft SQL Server 2000, and then insert rows as follows: 
    CREATE TABLE PUBS..wchartest (
	field1 varchar(10) COLLATE Cyrillic_General_CI_AS NULL)
go
insert into wchartest values (0xE4E4E4E4E4)
insert into wchartest values (0xE4E4E4E4E4)
insert into wchartest values (0xE4E4E4E4E4)
  2. Run the following Microsoft Visual C++ 6.0 OLE DB sample application: 
    /********************************************************************
* OLE DB
********************************************************************/ 
#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS // Initialize OLE constants...
#define INITGUID        // ...once in each app

#include <windows.h>
#include <stdio.h>
#include "sqloledb.h"

//#include <sqloledb.h>
#include <oledb.h>      // OLE DB include files
#include <oledberr.h> 
#include <msdaguid.h>   // ODBC provider include files
#include <msdasql.h>
#include <iostream.h>

#define SSPROP_INIT_AUTOTRANSLATE		8

// Macros--number of row identifiers to retrieve
#define NUMROWS_CHUNK               35

// Prototypes
HRESULT myInitDSO(IDBInitialize** ppIDBI);
HRESULT mySetInitProps(IDBInitialize* pIDBInitialize);
HRESULT myCommand(IDBInitialize* pIDBI, IRowset** ppIRowset);
void    myGetData(IRowset* pIRowset);
void    DumpError(LPSTR lpStr);
HRESULT myGetColumnsInfo(IRowset* pIRowset, ULONG* pnCols,
                 DBCOLUMNINFO** ppColumnsInfo, OLECHAR** 
ppColumnStrings);
void    myCreateDBBindings(ULONG nCols, DBCOLUMNINFO* pColumnsInfo,
                 DBBINDING** ppDBBindings, char** ppRowValues);

 // Global task memory allocator
IMalloc*        g_pIMalloc = NULL;

/********************************************************************
* General OLE DB application main()
********************************************************************/ 
int main()
    {
    IDBInitialize*  pIDBInitialize = NULL;
    IRowset*        pIRowset = NULL;

    // Init OLE and set up the DLLs.
    CoInitialize(NULL);

    // Get the task memory allocator.
    if (FAILED(CoGetMalloc(MEMCTX_TASK, &g_pIMalloc)))
        goto EXIT;
    // Connect to the data source.
    if (FAILED(myInitDSO(&pIDBInitialize)))
        goto EXIT;

    // Get a session, set and execute a command.
    if (FAILED(myCommand(pIDBInitialize, &pIRowset)))
        goto EXIT;

    // Retrieve data from rowset.
    myGetData(pIRowset);

EXIT:
    // Clean up and disconnect.
    if (pIRowset != NULL)
        pIRowset->Release();

    if (pIDBInitialize != NULL)
        {
        if (FAILED(pIDBInitialize->Uninitialize()))
            {
            // Uninitialize is not required, but it will fail if an 
            // interface has not been released;  use it for
            // debugging.
            DumpError("Someone forgot to release something!");
            }
        pIDBInitialize->Release();
        }

    if (g_pIMalloc != NULL)
        g_pIMalloc->Release();

    CoUninitialize();
    
    return (0);
    }

 /********************************************************************
* Initialize the data source.
********************************************************************/ 
HRESULT myInitDSO
    (
    IDBInitialize** ppIDBInitialize  // [out]
    )
    {
    // Create an instance of the MSDASQL (ODBC) provider.
    //CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
    //    IID_IDBInitialize, (void**)ppIDBInitialize);

	// Create an instance of the MS SQL OLE DB provider
	GUID CLSID_SQLOLEDB	= {0xc7ff16cL,0x38e3,0x11d0,{0x97,0xab,0x0,0xc0,0x4f,0xc2,0xad,0x98}};

    // Create an instance of the SQLOLEDB ( SQL Server native OLE-DB provider. )
    CoCreateInstance(CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER,
        IID_IDBInitialize, (void**)ppIDBInitialize);


    if (*ppIDBInitialize == NULL)
        {
        return (E_FAIL);
        }

    if (FAILED(mySetInitProps(*ppIDBInitialize)))
        {
        return (E_FAIL);
        }

	HRESULT hr=(*ppIDBInitialize)->Initialize();
    //if (FAILED((*ppIDBInitialize)->Initialize()))
	if (FAILED(hr))
        {
        DumpError("IDBInitialze->Initialize failed.");
        return (E_FAIL);
        }

    return (NOERROR);
    }

/********************************************************************
* Set initialization properties on a data source.
********************************************************************/ 
HRESULT mySetInitProps
    (
    IDBInitialize*  pIDBInitialize  // [in]
    )
    {
  //  const ULONG     nProps = 4;
	const ULONG     nProps = 2;
    IDBProperties*  pIDBProperties;
    DBPROP          InitProperties[nProps],sqlProps[1];
    DBPROPSET       rgInitPropSet[2];
    HRESULT         hr;


			/*
		 * If provider is SQLOLEDB (Microsoft SQLServer), then set the 
		 * "AutoTranslate" property to VARIANT_FALSE.
		 */ 
		sqlProps[0].dwPropertyID = 0x8;
		sqlProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
		sqlProps[0].colid = DB_NULLID;
		sqlProps[0].vValue.vt = VT_BOOL;
		V_BOOL(&sqlProps[0].vValue) = VARIANT_TRUE;							
	
		/*
		 * Create the structure containing the properties.
		 */     
		rgInitPropSet[0].rgProperties    = sqlProps;
		rgInitPropSet[0].cProperties     = 1;
		rgInitPropSet[0].guidPropertySet = DBPROPSET_SQLSERVERDBINIT;


   // Initialize common property options.
    for (ULONG i = 0; i < nProps; i++ )
        {
        VariantInit(&InitProperties[i].vValue);
        InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[i].colid = DB_NULLID;
        }

	InitProperties[0].dwPropertyID = DBPROP_INIT_PROVIDERSTRING;	
    InitProperties[0].vValue.vt = VT_BSTR;
	InitProperties[0].dwOptions=DBPROPOPTIONS_REQUIRED;
	InitProperties[0].colid=DB_NULLID;
	InitProperties[0].dwStatus=DBPROPSTATUS_OK;
	InitProperties[0].vValue.bstrVal = SysAllocString(OLESTR("SERVER=kavi1;DATABASE=pubs;uid=sa;pwd="));
	
    rgInitPropSet[1].guidPropertySet = DBPROPSET_DBINIT;
    rgInitPropSet[1].cProperties = nProps;
    rgInitPropSet[1].rgProperties = InitProperties;

    // Set initialization properties.
    pIDBInitialize->QueryInterface(IID_IDBProperties, (void**) 
        &pIDBProperties);
    hr = pIDBProperties->SetProperties(2, rgInitPropSet);

    SysFreeString(InitProperties[0].vValue.bstrVal);
    //SysFreeString(InitProperties[2].vValue.bstrVal);
    //SysFreeString(InitProperties[3].vValue.bstrVal);

    pIDBProperties->Release();

    if (FAILED(hr))
        {
        DumpError("Set properties failed.");
        }

    return (hr);
    }

 /********************************************************************
* Execute a command, retrieve a rowset interface pointer.
********************************************************************/ 
HRESULT myCommand
    (
    IDBInitialize*  pIDBInitialize, // [in]
    IRowset**       ppIRowset       // [out]
    ) 
    {
    IDBCreateSession*   pIDBCreateSession;
    IDBCreateCommand*   pIDBCreateCommand;
    IRowset*            pIRowset;
    ICommandText*       pICommandText;
    LPCTSTR wSQLString = OLESTR("select * from wchartest");
    LONG                cRowsAffected;
    HRESULT             hr;

    // Get the DB session object.
    if (FAILED(pIDBInitialize->QueryInterface(IID_IDBCreateSession,
            (void**) &pIDBCreateSession)))
        {
        DumpError("Session initialization failed.");
        return (E_FAIL);
        }

    // Create the session, getting an interface for command creation.
    hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
        (IUnknown**) &pIDBCreateCommand);
    pIDBCreateSession->Release();
    if (FAILED(hr))
        {
        DumpError("Create session failed.");
        return (hr);
        }

    // Create the command object.
    hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
        (IUnknown**) &pICommandText);
    if (FAILED(hr))
        {
        DumpError("Create command failed.");
        return (hr);
        }
    pIDBCreateCommand->Release();

    // The command must have the actual text and also an indicator
    // of its language and dialect.
    pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);

     // Execute the command.
    hr = pICommandText->Execute(NULL, IID_IRowset, NULL, 
         &cRowsAffected, (IUnknown**) &pIRowset);
    if (FAILED(hr))
        {
        DumpError("Command execution failed.");
        }
    pICommandText->Release();

    *ppIRowset = pIRowset;
    return (hr);
    }

/********************************************************************
* Get the characteristics of the rowset (the ColumnsInfo interface).
********************************************************************/ 
HRESULT myGetColumnsInfo
    (
    IRowset*        pIRowset,        // [in]
    ULONG*          pnCols,          // [out]
    DBCOLUMNINFO**  ppColumnsInfo,   // [out]
    OLECHAR**       ppColumnStrings  // [out]
    )
    {
    IColumnsInfo*   pIColumnsInfo;
    HRESULT         hr;

    if (FAILED(pIRowset->QueryInterface(IID_IColumnsInfo, (void**) 
&pIColumnsInfo)))
        {
        DumpError("Query rowset interface for IColumnsInfo failed");
        return (E_FAIL);
        }

    hr = pIColumnsInfo->GetColumnInfo(pnCols, ppColumnsInfo, 
ppColumnStrings);
    if (FAILED(hr))
        {
        DumpError("GetColumnInfo failed.");
        *pnCols = 0;
        }

    pIColumnsInfo->Release();
    return (hr);
    }

 /********************************************************************
* Create binding structures from column information. Binding
* structures will be used to create an accessor that permits row value 
* retrieval.
********************************************************************/ 
void myCreateDBBindings
    (
    ULONG nCols,                 // [in]
    DBCOLUMNINFO* pColumnsInfo,  // [in]
    DBBINDING** ppDBBindings,    // [out]
    char** ppRowValues           // [out]
    )
    {
    ULONG       nCol;
    ULONG       cbRow = 0;
    DBBINDING*  pDBBindings;
    char*       pRowValues;

    pDBBindings = new DBBINDING[nCols];

    for (nCol = 0; nCol < nCols; nCol++)
        {
        pDBBindings[nCol].iOrdinal = nCol+1;
        pDBBindings[nCol].obValue = cbRow;
        pDBBindings[nCol].obLength = 0;
        pDBBindings[nCol].obStatus = 0;
        pDBBindings[nCol].pTypeInfo = NULL;
        pDBBindings[nCol].pObject = NULL;
        pDBBindings[nCol].pBindExt = NULL;
        pDBBindings[nCol].dwPart = DBPART_VALUE;
        pDBBindings[nCol].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        pDBBindings[nCol].eParamIO = DBPARAMIO_NOTPARAM;
        pDBBindings[nCol].cbMaxLen = pColumnsInfo[nCol].ulColumnSize;
        pDBBindings[nCol].dwFlags = 0;
		pDBBindings[nCol].wType=DBTYPE_WSTR;

        pDBBindings[nCol].bPrecision = pColumnsInfo[nCol].bPrecision;
        pDBBindings[nCol].bScale = pColumnsInfo[nCol].bScale;

        cbRow += pDBBindings[nCol].cbMaxLen;
        }

    pRowValues = new char[cbRow];

    *ppDBBindings = pDBBindings;
    *ppRowValues = pRowValues;

    return;
    }

 /********************************************************************
* Retrieve data from a rowset.
********************************************************************/ 
void myGetData
    (
    IRowset*    pIRowset    // [in]
    ) 
    {
    ULONG           nCols;
    DBCOLUMNINFO*   pColumnsInfo = NULL;
    OLECHAR*        pColumnStrings = NULL;
    //ULONG           nCol;
    ULONG           cRowsObtained;         // Number of rows obtained
    ULONG           iRow;                  // Row count
    HROW            rghRows[NUMROWS_CHUNK];// Row handles
    HROW*           pRows = &rghRows[0];   // Pointer to the row 
                                           // handles
    IAccessor*      pIAccessor;            // Pointer to the accessor
    HACCESSOR       hAccessor;             // Accessor handle
    DBBINDSTATUS*   pDBBindStatus = NULL;
    DBBINDING*      pDBBindings = NULL;
    char*           pRowValues;

    // Get the description of the rowset for use in binding structure
    // creation.
    if (FAILED(myGetColumnsInfo(pIRowset, &nCols, &pColumnsInfo,
        &pColumnStrings)))
        {
        return;
        }

    // Create the binding structures.
    myCreateDBBindings(nCols, pColumnsInfo, &pDBBindings, 
        &pRowValues);
    pDBBindStatus = new DBBINDSTATUS[nCols];

    // Create the accessor.
    pIRowset->QueryInterface(IID_IAccessor, (void**) &pIAccessor);
    pIAccessor->CreateAccessor(
        DBACCESSOR_ROWDATA, // Accessor will be used to retrieve row 
                            // data.
        nCols,              // Number of columns being bound
        pDBBindings,        // Structure containing bind info
        0,                  // Not used for row accessors 
        &hAccessor,         // Returned accessor handle
        pDBBindStatus       // Information about binding validity
        );

     // Process all the rows, NUMROWS_CHUNK rows at a time.
    while (TRUE)
        {
        pIRowset->GetNextRows(
            0,                  // Reserved
            0,                  // cRowsToSkip
            NUMROWS_CHUNK,      // cRowsDesired
            &cRowsObtained,     // cRowsObtained
            &pRows );           // Filled in w/ row handles.


        // All completed; there are no more rows left to get.
        if (cRowsObtained == 0)
            break;

        // Loop over rows obtained, getting data for each.
        for (iRow=0; iRow < cRowsObtained; iRow++)
            {
            pIRowset->GetData(rghRows[iRow], hAccessor, pRowValues);
            for (nCol = 0; nCol < nCols; nCol++)
              {
                MessageBoxA(NULL, pRowValues, pRowValues, MB_OK | MB_TOPMOST);
                wprintf(L"%S\n",pRowValues);
                wprintf(OLESTR("%s:"), pColumnsInfo[nCol].pwszName);
                wprintf(L"\t%S\n",&pRowValues[pDBBindings[nCol].obValue]);
				
              }
            wprintf(L"\n");
            }

        // Release row handles.
        pIRowset->ReleaseRows(cRowsObtained, rghRows, NULL, NULL, 
           NULL);
        }  // End while

    // Release the accessor.
    pIAccessor->ReleaseAccessor(hAccessor, NULL);
    pIAccessor->Release();

    delete [] pDBBindings;
    delete [] pDBBindStatus;

    g_pIMalloc->Free( pColumnsInfo );
    g_pIMalloc->Free( pColumnStrings );

    return;
    }

 /********************************************************************
* Dump an error to the console.
********************************************************************/ 
void DumpError(LPSTR lpStr)
    {
    printf(lpStr);
    printf("\n");
    }
    Note Before you run this sample, change the connection string as appropriate for your environment.
The result is as follows: 
3F 00 3F 00 3F 00 3F 00 00
The result "3F 00 3F 00 3F 00 3F 00 00" is equivalent to "????"
Modification Type:MajorLast Reviewed:10/2/2003
Keywords:kbbug KB331999 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.