DOCUMENT:Q236786 21-DEC-2001 [odbc] TITLE :PRB: "Numeric Value Out of Range" Error Calling GetFieldValue PRODUCT :Open Database Connectivity (ODBC) PROD/VER::2.1,2.5,2.6,2.7,6.0,Build 2.573.2927,Build 2.573.3513,Build 2.573.3711,Build 2.573.420 OPER/SYS: KEYWORDS:kbfile kbDatabase kbDriver kbMFC kbODBC kbOracle kbVC kbGrpDSVCDB kbGrpDSMDAC kbDSuppor ====================================================================== ------------------------------------------------------------------------------- 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, Build 2.573.6526, Build 2.573.7326, Build 2.573.7626, Build 2.573.7713.2 - Microsoft Data Access Components versions 2.1, 2.5, 2.6, 2.7 - Microsoft Visual C++, 32-bit Editions, version 6.0 ------------------------------------------------------------------------------- SYMPTOMS ======== If CRecordset::GetFieldValue() is used on a numeric column to retrieve a value from an SQL UNION derived recordset and the Microsoft Oracle ODBC driver is used, then the following error message occurs: Error in column 1: Numeric value out of range State:22003,Native:0,Origin:[Microsoft][ODBC driver for Oracle] Following is an example of the code that can cause the error: CString strValue; CDatabase db; db.OpenEx("DSN=MSORACLEDSN;PWD=demo;UID=demo", CDatabase::noOdbcDialog); CRecordset rs(&db); rs.Open(CRecordset::snapshot, "SELECT field1, field2 FROM table1" " UNION SELECT field1, field2 FROM table2"); rs.GetFieldValue(short(0), strValue); CAUSE ===== The MFC (Microsoft Foundation Classes) CRecordset class gathers information about a resultset from the SQLDescribeCol() ODBC function. The SQLDescribeCol() function gives the column size for the column, which the CRecordset class then uses in the GetFieldValue() function to determine the size of the buffer. In the case where the SQL statement uses a UNION with a numeric column, SQLDescribeCol() returns 0 (zero) for the column size, which in turn gets sent to the SQLGetData() ODBC call that GetFieldValue() performs. As a result, the data is truncated because the buffer is not large enough to hold the complete number, and therefore, the error occurs. NOTE: The GetFieldValue() call always add space for the sign and decimal point. This call allows smaller numbers to work although numbers that are three digits or more cause the error. RESOLUTION ========== To resolve this problem, specify the maximum precision that you need for the field. Following are two ways to do this. 1. Create a global function called GetNumericFieldValue() and instead of calling GetFieldValue(), call this new function. Here is what the function looks like: void GetNumericFieldValue(CRecordset &rs, int nIndex, CString &strValue) { // Special GetFieldValue function for Microsoft Oracle driver to handle UNIONs. nIndex++; #ifndef _UNICODE CString& strData = strValue; #else CString strProxy; CString& strData = strProxy; #endif // Specify max length for buffer - add 3 for null, sign, and decimal. const int nLen = 38+3; void* pvData = strData.GetBufferSetLength(nLen); // Now can actually get the data. long nActualSize = rs.GetData(rs.m_pDatabase, rs.m_hstmt, nIndex, SQL_C_CHAR, pvData, nLen, rs.m_rgODBCFieldInfos[nIndex - 1].m_nSQLType); // Handle NULL data separately. if (nActualSize == SQL_NULL_DATA) { // Clear value. strValue.Empty(); } else { // May need to clean up and call SQLGetData again if necessary. rs.GetLongCharDataAndCleanup(rs.m_pDatabase, rs.m_hstmt, nIndex, nActualSize, &pvData, nLen, strData, rs.m_rgODBCFieldInfos[nIndex - 1].m_nSQLType); #ifdef _UNICODE // Now must convert string to Unicode. strValue = (LPCSTR)strData.GetBuffer(0); #endif // _UNIOCDE } } 2. A more elegant approach is to create a new class derived from CRecordset class that contains the fix. You can then place the following code at the top of your .cpp file (you don't have to change any other code where CNumericFixRecordset is a class that has a modified GetFieldValue() function that specifies the maximum precision of the column). #define CRecordset CNumericFixRecordset Download the self-extracting file Numericfix.exe to get the .h and .cpp files for the CNumericFixRecordset class. STATUS ====== This behavior is by design. MORE INFORMATION ================ The following file is available for download from the Microsoft Download Center: Numericfix.exe For more information about how to download files from the Microsoft Download Center, please see the following Microsoft Web site: http://www.microsoft.com/downloads/search.asp and click How to use the Microsoft Download Center. Additional query words: Numericfix ====================================================================== Keywords : kbfile kbDatabase kbDriver kbMFC kbODBC kbOracle kbVC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbmdac270 Technology : kbVCsearch kbAudDeveloper kbODBCSearch kbMDACSearch kbMDAC210 kbMDAC250 kbMDAC260 kbODBCOracle25732927 kbODBCOracle25733513 kbODBCOracle25733711 kbODBCOracle25734202 kbODBCOracle25734403 kbODBCOracle25736526 kbVC600 kbVC32bitSearch kbMDAC270 kbodbcOracle25737626 kbodbcoracle25735303 kbODBCOracle25736019 kbODBCOracle25737326 kbODBCOracle257377132 kbODBCOracle250Search Version : :2.1,2.5,2.6,2.7,6.0,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,Build 2.573.6526,Build 2.573.7326,Build 2.573.7626,Build 2.573.7713.2 Issue type : kbprb ============================================================================= 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 2001.