Microsoft KB Archive/320744

= PRB: Incorrect NUMERIC and DECIMAL conversions with non-English locales =

Article ID: 320744

Article Last Modified on 5/25/2004

-

APPLIES TO


 * Microsoft OLE DB 2.5
 * Microsoft OLE DB 2.6
 * Microsoft OLE DB 2.7
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q320744



Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry



SYMPTOMS
When you select NUMERIC or DECIMAL data through OLE DB and convert the data to DBTYPE_R8 (a double or float host data type for 8-byte floating-point data), the resulting values may not be correct if the locale is non-English.

This problem is most likely to occur under the following circumstances:
 * The Regional Options for the account of a process is set to a locale that uses a comma as the decimal separator (or decimal symbol) and a period as the thousand separator (or digit grouping symbol). -and-


 * You are logged on to a computer remotely (for example, through Terminal Services or Microsoft NetMeeting).

The following table includes examples of incorrect conversions:

Value Being Selected   Incorrect Conversion    Correct Conversion

12345678.09    1.23457e+009        1.23457e+007 12345678.000000002     1.23457e+016        1.23457e+007 1.23456        123456          1.23456 1.2         12          1.2

This behavior might occur on Microsoft Windows 2000, Microsoft Windows XP, and Microsoft Windows Server 2003. The resolution outlined in this article applies equally to all operating systems.



CAUSE
OLE DB uses an internal Data Conversion Library to convert NUMERIC and DECIMAL data to DBTYPE_R8. The Data Conversion Library relies on the Oleaut32.dll file to convert NUMERIC and DECIMAL data to DBTYPE_R8. Oleaut32.dll first calls the OLEAUT32!_VarStrFromNum method to convert the value to a string and then calls the OLEAUT32!VarR8FromStr method to convert the resulting string to a variant.

OLEAUT32!_VarStrFromNum does not take locale into account and assumes that the decimal and thousand separators are their English equivalents (&quot;.&quot; and &quot;,&quot; respectively).

However, these characters are the opposite of the English usage in some locales where the decimal separator is a comma and the thousand separator is a period. When you use these locales, _VarStrFromNum does not recognize the comma as a decimal separator, and the resulting precision and scale information is not accurate.



RESOLUTION
Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk. To work around this problem, use one of the workarounds that follow. Both of these workarounds require that you add the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLEAUT VarConversionLocaleSetting

You must set the DWORD value of this key to 1. When you set this value to 1, Oleaut32.dll must select the conversion format that is based on the current identity of the thread that requests these values. If the user's profile does not exist or is not loaded in the registry, Oleaut32.dll uses the System Default settings from the HKEY_USERS\.default\Control Panel\International registry hive.

Note Oleaut32.dll also exposes a function that can be called by the application directly, as an alternative to using the registry setting. This function is SetVarConversionLocaleSetting, and it accepts a single DWORD parameter that should match the 0, 1, or 2 values that are specified. Calling this function does not change the values in the registry. Instead, it sets global values in Oleaut32.dll in the calling process.

Workaround 1
Configure the service to run under a specific account, and set the Regional Options for this account to use an English locale. When you combine this with the VarConversionLocaleSetting registry setting, the conversion is always executed under an English locale.

Workaround 2
If you permit the account to use non-English locale, modify the decimal separator (or decimal symbol) and thousand separator (or digit grouping symbol) to use the English equivalents. To do this, follow these steps:
 * 1) In Control Panel, double-click Regional Options.
 * 2) Click the Numbers tab in the Regional Options dialog box.
 * 3) In the Decimal symbol list, select the period (.).
 * 4) In the Digit grouping symbol list, select the comma.
 * 5) Click the Currency tab in the Regional Options dialog box.
 * 6) Repeat steps 3 and 4, and then click OK.



STATUS
This problem was first corrected in Windows 2000 Service Pack 2.

To resolve this problem, obtain the latest service pack for Windows 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

260910 How to obtain the latest Windows 2000 service pack



MORE INFORMATION
The VarConversionLocaleSetting registry key includes three possible values. To make sure that Oleaut32.dll uses the correct Regional Options, you must set the value of VarConversionLocaleStting to 1. For more information about this registry key, see the &quot;References&quot; section.

For the registry key to affect computers that are running Microsoft Windows 2000 and Microsoft Windows 2000 Service Pack 1, you must apply the hotfix that is mentioned in Q271587. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

271587 ASP pages use Time/Date format based on user that is logged on

Steps to reproduce the behavior
Note Follow these steps on a computer that you do not physically have to log on to. If it is possible, use Terminal Services, Remote Desktop, or NetMeeting. This problem typically does not occur if you follow these steps when you log on to a computer directly (interactive mode).  Copy the code sample that follows these steps into a Microsoft Visual C++ console application, and then compile the application as TestOleaut.exe. Create a user account on the test computer, and then log on as this new user. In Control Panel, double-click Regional Options, and then click German (Germany) in the Your locale (location) list. Click Apply, and then click OK.</li> In Control Panel, double-click Scheduled Tasks, and then double-click Add Schedule Task.</li> Configure a task that runs the TestOleaut.exe application, but disable the task so that the task is not run at any scheduled time.</li> Configure this task to run under the account that you created in step 2.</li> In Windows Explorer, double-click TestOleaut.exe to run the executable file directly. This creates a file named Outfile.txt on the root of drive C.</li> Open Outfile.txt. Notice that the values are returned correctly:

<pre class="fixed_text">Value returned: 1.234568e+007, length: 8, status: 0

</li> In Scheduled Tasks, right-click New Task, and then click Run. This rewrites the data in the Outfile.txt file, which converts through the account that is set to German for Regional Options.</li> Open Outfile.txt again. Notice that the output displays values that are not converted correctly:

<pre class="fixed_text">Value returned: 1.234568e+009, length: 8, status: 0

</li></ol>

Sample Code
 * 1) include &quot;stdafx.h&quot;


 * 1) include <windows.h>
 * 2) include <stdio.h>
 * 3) include <stddef.h>
 * 4) include <oledb.h>

int main(int argc, char* argv[]) {

ICommandText * pICommandText; IDBCreateSession * pIDBCreateSession; IDBCreateCommand * pIDBCreateCommand; IDBInitialize * pIDBInitialize; IDBProperties * pIDBProperties; IRowset            *pIRowset; IAccessor              *pIAccessor;

const ULONG nProps = 4; DBPROP InitProperties[nProps]; DBPROPSET rgInitPropSet; ULONG          cRowsObtained; ULONG          coutputBindings = 1; DBBINDING      outputBindings[1]; DBBINDSTATUS   outputStatus[1]; HACCESSOR      hOutputAccessor; HROW           rghRow; // Row Handle HROW           *pRow = &rghRow; CLSID      clsid; HRESULT    hr;

struct _outputBuffer{ DWORD status; double value; int length; } outputBuffer; outputBuffer.status = 0; outputBuffer.value = 0.0;

if( FAILED(hr = CLSIDFromProgID(L&quot;SQLOLEDB&quot;, &clsid) ) ) return 0;

hr = CoInitialize(NULL);

hr = CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER,   IID_IDBInitialize, (void**)&pIDBInitialize);

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

//Level of prompting that is performed to complete the connection process. InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT; InitProperties[0].vValue.vt = VT_I2; InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;

//datasource name InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE; InitProperties[1].vValue.vt = VT_BSTR; InitProperties[1].vValue.bstrVal = SysAllocString(OLESTR(&quot;YourServer&quot;));

//userid InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID; InitProperties[2].vValue.vt = VT_BSTR; InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR(&quot;sa&quot;));

//password InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD; InitProperties[3].vValue.vt = VT_BSTR; InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR(&quot;&quot;));

//Assign the four property structures to property set. rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT; rgInitPropSet.cProperties = nProps; rgInitPropSet.rgProperties = InitProperties;

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

//Get the DB Session object. hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,               (void**)&pIDBCreateSession);

//Create the session, getting an interface for command creation. hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,               (IUnknown**)&pIDBCreateCommand); pIDBCreateSession->Release;

//Create the Command object. hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,               (IUnknown**)&pICommandText); pIDBCreateCommand->Release;

//Set the command text. pICommandText->SetCommandText( DBGUID_DBSQL, L&quot;select convert(decimal(10,2),12345678.09)&quot;);

//Execute the command. hr = pICommandText->Execute(NULL, IID_IRowset,NULL,NULL,(IUnknown **)&pIRowset);

outputBindings[0].iOrdinal = 1;        //Set ordinal. outputBindings[0].pTypeInfo = NULL;        //Reserved outputBindings[0].pObject = NULL;          //for an OLE object outputBindings[0].pBindExt = NULL;         //Reserved outputBindings[0].dwPart = DBPART_VALUE|DBPART_STATUS|DBPART_LENGTH; outputBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED; //Client-owned memory outputBindings[0].eParamIO = DBPARAMIO_NOTPARAM;   //Not a parameter outputBindings[0].dwFlags = 0;         //Reserved outputBindings[0].obStatus = offsetof(_outputBuffer,status);   // Offset in consumers buffer outputBindings[0].obValue = offsetof(_outputBuffer,value); // Offset in consumers buffer outputBindings[0].obLength = offsetof(_outputBuffer,length);   // Offset in consumers buffer outputBindings[0].cbMaxLen = sizeof(outputBuffer);     // Size in bytes of the value // part in the consumers buffer outputBindings[0].wType = DBTYPE_R8;               // Data type indicator

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

hr = pIRowset->GetNextRows(0, 0, 1, &cRowsObtained, &pRow );

//Retrieve the data, and print it to a text file. hr = pIRowset->GetData( rghRow, hOutputAccessor, (void *)&outputBuffer);

FILE *fp; fp = fopen( &quot;c:\\outfile.txt&quot;, &quot;wt&quot; ); fprintf( fp, &quot;Value returned: %e, length: %i, status: %i\n&quot;,outputBuffer.value, outputBuffer.length, outputBuffer.status); fflush(fp); fclose(fp);

//Release row handles. pIRowset->ReleaseRows( cRowsObtained,&rghRow, NULL, NULL, NULL );

pIRowset->Release;

//Release the output accessor. pIAccessor->ReleaseAccessor(hOutputAccessor,NULL); pIAccessor->Release; pICommandText->Release;

return 0; }

<div class="references_section">