FIX: Microsoft Access/Jet ODBC Driver returns size of 2 gigabytes for computed text columns

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 "Out of memory" error messages.

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

The problem may also occur in other Access ODBC drivers.

For more information about this problem in Windows Vista, click the following article number to view the article in the Microsoft Knowledge Base:

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:

To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.5. For more information, click the following article number to view the article in the Microsoft Knowledge Base: The English version of this fix should have the following file attributes or later:
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.

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.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft Data Access Components 2.5 Service Pack 2.

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:

Steps to reproduce behavior

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

   #include "stdafx.h"
   #include <windows.h>
   #include <stdio.h>
   #include <sql.h>
   #include <sqlext.h>
   
   
   void ShowErrors(HDBC, HSTMT);
   
   
   void main(void)
   {
   
   	SQLHANDLE henv;
   	SQLHANDLE hdbc;
   	SQLHANDLE hstmt;
   	
   	RETCODE rc = 0;
   	
   	SQLCHAR Statement[50] = "Select IIF('A' = 'A', 'Y', 'N') from shippers";	
   
   	SQLCHAR dsn[15] = "northwind2000";
   	SQLCHAR user[6] = "admin";
   	SQLCHAR pass[5] = "";
   	long sqlnts = -3;
   	
   //	variables for SQLDescribeCol
   	char  m_colname[20] = {"\0"};
   	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("Size returned from SQLDescribeCol is:\t%d", 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, "Length=[%d] Text=%s", strlen((char*)errorMsg), errorMsg );
   	printf("ERROR!  %s\n\n\n", szTemp);
   
   	printf("Press any key to exit...");
   	getchar();
   
   	if (hstmt != NULL)
   	{
   		SQLFreeStmt(hstmt, SQL_CLOSE);
   		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   	}
   	SQLDisconnect(hdbc);
   	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   
   	exit(0);
   
   
   }
   					

2. Run the code, and note that the size of the value returned for the precision is 2 GB.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base: