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

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

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] "Expr1001"
		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.

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.

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


}

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

REFERENCES