Microsoft KB Archive/331999

= BUG: SQLOLEDB Incorrectly Converts Char Data When AutoTranslate Is Turned On =

Article ID: 331999

Article Last Modified on 10/2/2003

-

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.



Steps to Reproduce the Behavior
  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)   Run the following Microsoft Visual C++ 6.0 OLE DB sample application: /********************************************************************
 * OLE DB
 * 1) define UNICODE
 * 2) define _UNICODE
 * 3) define DBINITCONSTANTS // Initialize OLE constants...
 * 4) define INITGUID       // ...once in each app
 * 1) define INITGUID       // ...once in each app


 * 1) include 
 * 2) include 
 * 3) include &quot;sqloledb.h&quot;

//#include 
 * 1) include      // OLE DB include files
 * 2) include 
 * 3) include   // ODBC provider include files
 * 4) include <msdasql.h>
 * 5) include <iostream.h>


 * 1) define SSPROP_INIT_AUTOTRANSLATE      8

// Macros--number of row identifiers to retrieve
 * 1) 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;

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

// 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(&quot;Someone forgot to release something!&quot;); }       pIDBInitialize->Release; }

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

CoUninitialize; return (0); }

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

// 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(&quot;IDBInitialze->Initialize failed.&quot;); return (E_FAIL); }

return (NOERROR); }

/******************************************************************** 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;
 * Set initialization properties on a data source.

/*        * If provider is SQLOLEDB (Microsoft SQLServer), then set the * &quot;AutoTranslate&quot; 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(&quot;SERVER=kavi1;DATABASE=pubs;uid=sa;pwd=&quot;)); 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(&quot;Set properties failed.&quot;); }

return (hr); }

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

// Get the DB session object. if (FAILED(pIDBInitialize->QueryInterface(IID_IDBCreateSession,           (void**) &pIDBCreateSession))) {       DumpError(&quot;Session initialization failed.&quot;); 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(&quot;Create session failed.&quot;); return (hr); }

// Create the command object. hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,       (IUnknown**) &pICommandText); if (FAILED(hr)) {       DumpError(&quot;Create command failed.&quot;); 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(&quot;Command execution failed.&quot;); }   pICommandText->Release;

*ppIRowset = pIRowset; return (hr); }

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

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

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

pIColumnsInfo->Release; return (hr); }

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

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

/******************************************************************** 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;
 * Retrieve data from a rowset.

// 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&quot;%S\n&quot;,pRowValues); wprintf(OLESTR(&quot;%s:&quot;), pColumnsInfo[nCol].pwszName); wprintf(L&quot;\t%S\n&quot;,&pRowValues[pDBBindings[nCol].obValue]); }           wprintf(L&quot;\n&quot;); }

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

/******************************************************************** void DumpError(LPSTR lpStr) {   printf(lpStr); printf(&quot;\n&quot;); } Note Before you run this sample, change the connection string as appropriate for your environment. </li></ol>
 * Dump an error to the console.

The result is as follows: <pre class="fixed_text">3F 00 3F 00 3F 00 3F 00 00 The result &quot;3F 00 3F 00 3F 00 3F 00 00&quot; is equivalent to &quot;????&quot;

Additional query words: unicode SSPROP_INIT_AUTOTRANSLATE sqloledb conversion collation ? question mark

Keywords: kbbug KB331999

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.