Microsoft KB Archive/244554

= FIX: Oracle ODBC Driver Appends Trailing Zero to Numeric Columns =

Article ID: 244554

Article Last Modified on 10/12/2005

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q244554



SYMPTOMS
When using the Microsoft ODBC Driver for Oracle, NUMERIC fields that have been defined with a null or odd scale but are bound as a character ODBC datatype (such as SQL_C_CHAR) are returned with a trailing zero appended to the decimal portion of the number, so that the scale contains an even number of digits.

This can potentially cause problems in applications such as Microsoft Access, which does not compare "1.123" and 1.1230" equally.



CAUSE
Internal to Oracle, NUMERIC fields that have been defined with an odd scale are actually stored in Oracle with a trailing zero as the least significant digit. Changes in recent builds of the ODBC driver for Oracle permit these trailing zeros to be returned to the calling application.



RESOLUTION
This problem has been resolved with the Microsoft Oracle ODBC driver that ships with MDAC 2.6.

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next Microsoft Data Access Components service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix should have the following file attributes or later:

  Date      Time    Version      Size    File name     Platform -  8/17/99           2.573.4509  141KB    msorcl32.dll



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

This problem was corrected in MDAC 2.6.



MORE INFORMATION
The hotfix version of the Microsoft Oracle ODBC driver (msorcl32.dll) has been modified to include a new connection attribute that will strip trailing zeros from NUMERIC fields being returned as SQL_C_CHAR. This connection attribute may also be added as an additional parameter to an Oracle connection string.

To configure this new attribute once the hotfix has been installed, use the ODBC Administrator to locate an existing datasource that uses the Microsoft ODBC for Oracle driver, and double-click on the DSN to bring up the configuration screen.

In the Oracle ODBC driver configuration screen, use the Options button to expand the dialog box so that extended configuration attributes are displayed. At the bottom of the dialog box will be a checkbox titled "Truncate insignificant trailing zero when using null or odd scale."

Checking this box will cause a new parameter called "StripTrailingZero" to be added to the datasource in the registry. This is a String value, and will be set to "1" for TRUE, and "0" for FALSE.

"StripTrailingZero" can also be added directly to a connection string, for example:

DSN=OracleServer;UID=scott;PWD=tiger;StripTrailingZero=1; -or-

Driver=Microsoft ODBC for Oracle;Server=OracleServer;UID=scott;PWD=tiger;StripTrailingZero=1;

Manual Installation

 * 1) Close or stop any applications or services that are using Msorcl32.dll. This may include Internet Information Server (IIS), Microsoft Transaction Server (MTS), and any ODBC, ADO, or OLE DB applications.
 * 2) Download the hotfix version of Msorcl32.dll into a temporary directory.
 * 3) Locate and rename the current version of Msorcl32.dll, which is in the \Winnt\System32 folder for Windows NT computers, and in the \Windows\System folder for Windows 9x computers.
 * 4) Copy the hotfix version of Msorcl32.dll into the same location, and then restart your services and applications.

Steps to Reproduce Behavior

 * 1) Use the ODBC Administrator to configure an Oracle datasource so that the "Truncate insignificant trailing zero when using null or odd scale" checkbox is cleared (not set).
 * 2) Copy the following code into a console application (modifying your Oracle server, username and password as necessary), compile and run it.
 * 3) Notice that several values have extra zeros appended to them. In particular, all of the odd scale fields have extra zeros, even when this puts the number of decimal digits beyond the defined size. Additionally, the no-scale fields will have zeros appended to create an even number of decimal digits.
 * 4) Use the ODBC Administrator to modify the Oracle datasource so that the "Truncate insignificant trailing zero when using null or odd scale" checkbox is checked, and run the code again. Notice that all values appear as they should.


 * 1) include "stdafx.h"
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 

void CreateTable(HSTMT);

main {

RETCODE rc; HENV henv; HDBC hdbc; HSTMT hstmt;

// Variables for SQLConnect char * dsn = "OracleServer"; char * uid = "scott"; char * pwd = "tiger";

// Variables for SQLBindCol char m_charOddScale[20] = {"\0"}; char m_charNoScale[20] = {"\0"}; long sqlnts = SQL_NTS;

// miscellaneous variables char strOutput[40] = {"\0"}; char * SQLStr = "select OddScale, NoScale from ScaleTable";

// Allocate ODBC handles and connect to Oracle rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv); rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,0); rc = SQLAllocHandle(SQL_HANDLE_DBC,henv, &hdbc);

rc = SQLConnect(hdbc, (unsigned char *)dsn,       SQL_NTS, (unsigned char *)uid,        SQL_NTS, (unsigned char *)pwd, SQL_NTS);

rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

// Table creation function CreateTable(hstmt);

// Execute the statement and bind the column as SQL_C_CHAR rc = SQLExecDirect(hstmt, (unsigned char *)SQLStr, SQL_NTS);

rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, m_charOddScale, 20, &sqlnts); rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, m_charNoScale, 20, &sqlnts);

printf("\nOddScale (3) and NoScale as SQL_C_CHAR\n\n");

// Fetch records and print the results while (SQLFetch(hstmt) != SQL_NO_DATA_FOUND) {

memset(strOutput,' ',sizeof(strOutput)); strncpy(strOutput, m_charOddScale, strlen(m_charOddScale)); strncpy(&strOutput[15],m_charNoScale, strlen(m_charNoScale)+1);

printf("\t%s\n",strOutput);

}

// Cleanup SQLFreeStmt(hstmt, SQL_CLOSE); SQLFreeStmt(hstmt, SQL_DROP); SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv); return(TRUE); };

//--- CreateTable

void CreateTable(HSTMT hstmt) {

RETCODE rc = 0; char SqlStatements[6][70] = {"Drop table ScaleTable", "Create table ScaleTable (OddScale number(10,3), NoScale number)", "Insert into ScaleTable values (1.1, 1.1)", "Insert into ScaleTable values (1.12, 1.12)", "Insert into ScaleTable values (1.123, 1.123)", "Insert into ScaleTable values (1.005, 1.005)"};

rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[0], SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE); rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[1], SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE); rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[2], SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE); rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[3], SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE); rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[4], SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE); rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[5], SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE);

}

Additional query words: oracle msorcl32.dll odbc null odd scale numeric sql_c_char truncate extra trailing zero

Keywords: kbbug kbfix kboracle kbqfe kbdatabase kbmdac260fix kbdriver kbmdacnosweep kbhotfixserver KB244554

-

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

© Microsoft Corporation. All rights reserved.