Microsoft KB Archive/272951

= FIX: Microsoft Access/Jet ODBC Driver Returns Size of 2 Gigabytes for Computed Text Columns =

Article ID: 272951

Article Last Modified on 4/7/2006

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Open Database Connectivity Driver for Access 4.0

-



This article was previously published under Q272951



SYMPTOMS
If you use the Microsoft Access (Jet) ODBC driver (Odbcjt32.dll version 4.0.5303.1), which is shipped with Microsoft Windows 2000 Service Pack (SP) 1, when you run statements that contain computed columns of a text data type, a call to the SQLDescribeCol ODBC API on the computed columns returns a column size of 2147483598 (2 GB).

Applications that try to use the column size returned from the call to the SQLDescribeCol ODBC API, to determine how much memory to allocate for the column, fail with &quot;Out of memory&quot; error messages.

Odbcjt32.dll version 4.0.5303.1 is also installed by Microsoft Data Access Components (MDAC) version 2.5, Service Pack 1.



CAUSE
Jet returns a column size of zero for computed columns, which the Jet ODBC driver interprets as an indication of a Long data type. Because of this, the Jet driver sets the column size to the maximum size allowed for Long data types, which is 2 GB. However, if the data type is a text data type (character or varchar) the type is still reported as SQL_VARCHAR, which should have a maximum size of 255.

An example taken from an ODBC trace follows that demonstrates the returned 2 GB column size as the seventh (7th) argument:

mdactest       65c-698 EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS) HSTMT              01EC18D8 UWORD                       2 UCHAR *            0x0012FAE4 [       8] &quot;Expr1001&quot; SWORD                     129 SWORD *            0x0012FA94 (8) SWORD *            0x0012FA86 (12) SQLULEN *          0x0012FA98 (2147483598) SWORD *            0x0012FA9C (0) SWORD *            0x0012FA9E (1)



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.5. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

293312 INFO: How to Obtain the Latest MDAC 2.5 Service Pack

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

  Date         Version        Size              File name ---  08/29/00     4.0.5629.0     270,608 bytes     Odbcjt32.dll NOTE: This hotfix is not included with either Jet SP5 or MDAC 2.6. Although the fix is expected to be included with MDAC 2.5 SP2, systems experiencing this problem must apply the hotfix to resolve this problem until MDAC 2.5 SP2 is released. Additionally, systems that are upgraded directly from MDAC 2.5 SP1 to MDAC 2.6 should also have this hotfix applied.



WORKAROUND
In some cases, it may be possible to wrap the calculated field in a datatype conversion function such as CInt or CLng, which converts the results to a non-text datatype.



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 first corrected in Microsoft Data Access Components 2.5 Service Pack 2.



MORE INFORMATION
This behavior can occur with any function that returns calculated values, which includes common functions like IIF, MAX, MIN, FIRST, RIGHT, MID, and so forth.

NOTE: This hotfix also contains a fix for the issue outlined in the following Microsoft Knowledge Base article:

271177 Unable To Repair Password Protected Database Via ODBC Control Panel

Steps to Reproduce Behavior
  Copy the code that follows into a Microsoft Visual C++ console application, and then compile the code. Please note that you may need to change the datasource name, user ID and password.
 * 1) include &quot;stdafx.h&quot;
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 

void ShowErrors(HDBC, HSTMT);

void main(void) {

SQLHANDLE henv; SQLHANDLE hdbc; SQLHANDLE hstmt; RETCODE rc = 0; SQLCHAR Statement[50] = &quot;Select IIF('A' = 'A', 'Y', 'N') from shippers&quot;;

SQLCHAR dsn[15] = &quot;northwind2000&quot;; SQLCHAR user[6] = &quot;admin&quot;; SQLCHAR pass[5] = &quot;&quot;; long sqlnts = -3; // variables for SQLDescribeCol char m_colname[20] = {&quot;\0&quot;}; short m_colsize = 0; short m_sqltype = 0; unsigned long m_prec = 0; short m_scale = 0; short m_nullable = 0;

rc = SQLAllocEnv(&henv); rc = SQLAllocConnect(henv, &hdbc); rc = SQLConnect(hdbc, dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS); if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) ShowErrors(hdbc, NULL); rc = SQLAllocStmt(hdbc, &hstmt);

rc = SQLExecDirect(hstmt, Statement, SQL_NTS); if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) ShowErrors(NULL, hstmt);

rc = SQLDescribeCol(hstmt, 1, (unsigned char *)m_colname, 20, &m_colsize, &m_sqltype, &m_prec, &m_scale, &m_nullable);

printf(&quot;Size returned from SQLDescribeCol is:\t%d&quot;, m_prec);

SQLFreeStmt(hstmt, SQL_CLOSE); SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv); }

void ShowErrors (HDBC hdbc, HSTMT hstmt) {

// Variables for SQLGetDiagRec SQLCHAR sqlState[20]; SQLCHAR errorMsg[1000]; SQLSMALLINT errorMsgLen; SQLINTEGER nativeError; char szTemp[4096]; // Retrieve the Raised error message if (hdbc!= NULL) SQLGetDiagRec(SQL_HANDLE_DBC,hdbc,1,sqlState,           &nativeError,errorMsg,1000,&errorMsgLen); else SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,sqlState,           &nativeError,errorMsg,1000,&errorMsgLen);

// Display the size of the returned error message, and the message itself sprintf(szTemp, &quot;Length=[%d] Text=%s&quot;, strlen((char*)errorMsg), errorMsg ); printf(&quot;ERROR! %s\n\n\n&quot;, szTemp);

printf(&quot;Press any key to exit...&quot;); getchar;

if (hstmt != NULL) {       SQLFreeStmt(hstmt, SQL_CLOSE); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); }   SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc);

exit(0);

}                    Run the code, and note that the size of the value returned for the precision is 2 GB.</li></ol>