DOCUMENT:Q244554 08-MAY-2002 [odbc] TITLE :FIX: Oracle ODBC Driver Appends Trailing Zero to Numeric Columns PRODUCT :Open Database Connectivity (ODBC) PROD/VER::2.1,2.5,Build 2.573.2927,Build 2.573.3513,Build 2.573.3711,Build 2.573.4202,Build 2.57 OPER/SYS: KEYWORDS:kbDatabase kbDriver kbODBC kbOracle kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC210SP2bug ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft ODBC for Oracle version 2.5, versions Build 2.573.2927, Build 2.573.3513, Build 2.573.3711, Build 2.573.4202, Build 2.573.4403, Build 2.573.5303, Build 2.573.6019 - Microsoft Data Access Components versions 2.1, 2.5 ------------------------------------------------------------------------------- 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 fix is now available from Microsoft, but it is only intended to correct the problem described in this article and should be applied only to systems experiencing this specific problem. This fix may receive additional testing at a later time, to further ensure product quality. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Data Access Components service pack that contains this fix. 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, please go to the following address on the World Wide Web: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS NOTE: In special cases, charges that are normally incurred for support calls may be canceled, if a Microsoft Support Professional determines that a specific update will resolve your problem. Normal 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 this to be 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 --------------------------- - 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). - Copy the following code into a console application (modifying your Oracle server, username and password as necessary), compile and run it. - 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. - 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. #include "stdafx.h" #include #include #include #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 : kbDatabase kbDriver kbODBC kbOracle kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC210SP2bug kbMDAC260fix kbMDACNoSweep Technology : kbAudDeveloper kbODBCSearch kbMDACSearch kbMDAC210 kbMDAC250 kbODBCOracle25732927 kbODBCOracle25733513 kbODBCOracle25733711 kbODBCOracle25734202 kbODBCOracle25734403 kbodbcoracle25735303 kbODBCOracle25736019 kbODBCOracle250Search Version : :2.1,2.5,Build 2.573.2927,Build 2.573.3513,Build 2.573.3711,Build 2.573.4202,Build 2.573.4403,Build 2.573.5303,Build 2.573.6019 Hardware : ALPHA x86 Issue type : kbbug Solution Type : kbfix ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.