Microsoft KB Archive/249803

= BUG: CRecordset::GetTextLen returns an incorrect length for UNICODE data when you use MFC ODBC with the SQL Server ODBC driver =

Article ID: 249803

Article Last Modified on 11/21/2006

-

APPLIES TO

 Microsoft Foundation Class Library 4.2, when used with:  Microsoft Visual C++ 4.2 Enterprise Edition

 Microsoft Visual C++ 4.2 Professional Edition

 Microsoft Visual C++ 5.0 Enterprise Edition

 Microsoft Visual C++ 5.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 6.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Standard Edition</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q249803

<div class="symptoms_section">

SYMPTOMS
With the SQL Server Open Database Connectivity (ODBC) driver (version 3.70.06.90 or later), UNICODE data is treated as UNICODE and the behavior of SQLDescribeCol is changed from the previous drivers. If you use the MFC ODBC CRecordset class with the latest drivers, the length returned from CRecordset::GetTextLen is incorrect, and therefore the data is truncated when CRecordset::GetFieldValue is called.

<div class="cause_section">

CAUSE
With UNICODE fields in SQL Server version 7.0, for example, and a field with "nchar" data type that calls SQLDescribeCol on this column, the previous SQL Server ODBC driver (version 3.60.03.19 or earlier) returns SQL_CHAR with the column size doubled. However, if SQLDescribeCol is called with the new SQL Server ODBC driver (version 3.70.06.90 or later), SQL_WCHAR is returned with the column size as defined in the database.

In MFC ODBC, when CRecordset::GetFieldValue is called, it will call CRecordset::GetTextLen, which doesn't provide you with the option to check against the SQL_WCHAR datatype. It just adds one more byte to the result from SQLDescribeCol. For UNICODE data, this length is incorrect and the fetched data is truncated.

<div class="resolution_section">

RESOLUTION
There are two ways you can work around this problem:  Use the odbccmpt utility.

The odbccmpt utility is shipped with SQL Server version 7.0 (you can find it in the Mssql7\Binn folder). The odbccmpt utility sets certain behaviors of the SQL Server ODBC version 3.7 driver to be compatible with the earlier SQL Server ODBC drivers.

One way to use this utility is to run it from the DOS prompt; for example:

dos prompt>odbccmpt yourAppName

No code modification is required under this option.</li> Use the overwrite CRecordset::GetFieldValue method.

Under certain circumstances, if the odbccmpt utility cannot be used, another workaround is to overwrite CRecordset::GetFieldValue. The following steps outline the method to overwrite GetFieldValue to handle the SQL_WCHAR datatype:</li></ul>

<ol>  Add the following three methods to your CRecordset-derived class declaration: void GetFieldValueMyEx(LPCTSTR lpszName, CString& strValue); void GetFieldValueMyEx(short nIndex, CString& strValue); int PASCAL GetTextLenMyEx(short nSQLType, UDWORD nPrecision);

</li>  From the Mfc/Src/Dbcore.cpp file, copy and paste the implementation of these three methods, corresponding to the methods in your .cpp file: void GetFieldValue(LPCTSTR lpszName, CString& strValue); void GetFieldValue(short nIndex, CString& strValue); int PASCAL GetTextLen(short nSQLType, UDWORD nPrecision); </li>  The only modification you need to make is inside GetTextLen, which you need to add the check against SQL_WCHAR, for example: if(nSQLType == SQL_WCHAR) {   nLen = 2*nPrecision + 1; }                   </li>  Implement GetTextLenMyEx as follows: int PASCAL CMyRecordset::GetTextLenMyEx(short nSQLType, UDWORD nPrecision) {   int nLen;

if (nSQLType == SQL_LONGVARCHAR || nSQLType == SQL_LONGVARBINARY) {       // Use a dummy length of 1 (will just get NULL terminator). nLen = 1; }   else {       // Better know the length. ASSERT(nPrecision >= 0); if(nSQLType == SQL_WCHAR) {           nLen = 2*nPrecision + 1; }       else {                      nLen = nPrecision + 1; }       // If converting Numeric or Decimal to text, you need // room for decimal point and sign in string. if (nSQLType == SQL_NUMERIC || nSQLType == SQL_DECIMAL) nLen += 2; }

return nLen; }                   </li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

<div class="moreinformation_section">

Steps to reproduce the behavior
<ol>  Use the following script to create a table named "TestingTable" in the SQL Server 7.0 Pubs sample database and insert one row into the table: CREATE TABLE [dbo].[TestingTable] (   [id] [int] IDENTITY (1, 1) NOT NULL,    [net_address] [nchar] (12) NULL ) ON [PRIMARY]

INSERT INTO TestingTable(net_address) VALUES ('a12345678901') </li> Set up a data source name (DSN) with the name "pubs" that points to the Pubs database.</li>  Create a Win32 console application that supports MFC, add the following code into the "else" block of _tmain, and then add the "afxdb.h" header file for MFC ODBC classes: Db.Open("pubs"); CRecordset myquery(&Db); try {   myquery.Open(CRecordset::forwardOnly, _T("select net_address from TestingTable"),    CRecordset::readOnly |CRecordset::executeDirect); CString sID; if (!myquery.IsBOF) {       myquery.GetFieldValue("net_address", sID); }   AfxMessageBox(sID); myquery.Close; Db.Close; } catch(CDBException * e) { e->ReportError; e->Delete; return -1; }                   </li> <li>Compile and run the project.</li></ol>

From the message box, you will see the data returned is "a1234567890", which is truncated.

<div class="references_section">