Microsoft KB Archive/236786

= PRB: "Numeric Value Out of Range" Error when Calling GetFieldValue and Using SQL UNION =

Article ID: 236786

Article Last Modified on 7/18/2007

-

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 Open Database Connectivity 2.5
 * 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
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Visual C++ 6.0 Service Pack 5

-



This article was previously published under Q236786



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.   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++;

CString& strData = strValue; CString strProxy; CString& strData = strProxy; // Specify max length for buffer - add 3 for null, sign, and decimal. const int nLen = 38+3; void* pvData = strData.GetBufferSetLength(nLen);
 * 1) ifndef _UNICODE
 * 1) else
 * 1) endif

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

// Now must convert string to Unicode. strValue = (LPCSTR)strData.GetBuffer(0); } }                         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). Download the self-extracting file Numericfix.exe to get the .h and .cpp files for the CNumericFixRecordset class. 
 * 1) ifdef _UNICODE
 * 1) endif // _UNIOCDE
 * 1) define CRecordset CNumericFixRecordset



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.aspx

and click How to use the Microsoft Download Center.

Additional query words: Numericfix

Keywords: kbdownload kbdatabase kbdriver kbfile kboracle kbprb KB236786

-

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

© Microsoft Corporation. All rights reserved.