FIX: SQLDescribeCol Returns Wrong Column Size in a Union Query with Parameters (308211)


The information in this article applies to:

  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.5 SP2
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.6 SP1
  • Microsoft Data Access Components 2.7
  • Microsoft ODBC Driver for SQL Server 2000 2000.80.194
  • Microsoft ODBC Driver for SQL Server 2000 2000.80.380
  • Microsoft ODBC Driver for SQL Server 2000 2000.81.7713.0
  • Microsoft ODBC Driver for SQL Server 3.7
This article was previously published under Q308211

SYMPTOMS

SQLDescribeCol returns the wrong column length if all of the following conditions are met:
  • The query consists of a union of two or more SELECT statements.
  • One of the WHERE clauses contains parameters.
  • The first of the two unioned queries returns a constant for a column (for example, SELECT 'ABC') and the other returns a column (for example, SELECT col1).

CAUSE

The driver truncates the query for the meta data on the UNION keyword. As a result, only meta data for the first query is requested from Microsoft SQL Server. The second query is ignored. If you change the order of the queries, SQLDescribeCol returns correct data.

RESOLUTION

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

300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack

WORKAROUND

To work around this problem, follow these steps:
  1. Compile the query into a stored procedure that contains parameters.
  2. Reverse the order of the SELECT statements in the union query so that the constant field is in the latter SELECT statement.
  3. Call SQLBindParameter before you call SQLDescribeCol.

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.6 Service Pack 2.

NOTE: This fix does not resolve more complex queries, such as those that are described in the following article in the Microsoft Knowledge Base:

308264 BUG: SQLDescribeCol Returns Wrong Column Size in a Complex Union Query with Parameters

MORE INFORMATION

The following is an example of a query that fails: 
SELECT 'AB' FROM a UNION SELECT col1 FROM b WHERE 1=?
When you call SQLDescibeCol for this query, the query returns 2 (which is the size of AB). The query should return the size of col1 and not the size of AB.

Steps to Reproduce Behavior

  1. Create the following tables on Microsoft SQL Server:
    CREATE TABLE [dbo].[a] (
	[a] [char] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[b] (
	[col1] [char] (20) NULL 
) ON [PRIMARY]
GO
  2. Paste the following code in a Microsoft Visual C++ console application (note that you must change the data source name, user ID, and password to correspond to your situation): 
    #include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <conio.h>
#include <Odbcss.h>
#include <stdio.h>

void HandleError(SQLHANDLE	hHandle, SQLSMALLINT hType, RETCODE RetCode)
{
    SQLSMALLINT	iRec = 0;
    SQLINTEGER	iError;
    TCHAR	szMessage[1000];
    TCHAR	szState[SQL_SQLSTATE_SIZE + 1];

    if (RetCode == SQL_INVALID_HANDLE)
    {
	fprintf(stderr,"Invalid handle!\n");
	return;
    }

    while (SQLGetDiagRec(hType, hHandle, ++iRec,(SQLCHAR *)szState, <BR/>
           &iError,(SQLCHAR *)szMessage, (SQLSMALLINT)(sizeof(szMessage)/ 
           sizeof(TCHAR)),(SQLSMALLINT *)NULL) == SQL_SUCCESS)
    {
         fprintf(stderr,TEXT("[%5.5s] %s (%d)\n"),szState,szMessage,iError);
    }

}

void main(int argc, char* argv[])
{
    SQLHENV henv;
    SQLHDBC hdbc;
    SQLHSTMT hstmt;
    SQLRETURN nstatus;
    
    //For clarity, do not check the return codes in some cases.
    nstatus = SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);
    nstatus = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
    nstatus = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);

    nstatus = SQLConnect(hdbc, (SQLCHAR*) "<dsn name>", SQL_NTS, 
                         (SQLCHAR*) "<user id>", SQL_NTS, (SQLCHAR*) "<password>", SQL_NTS);
    if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO)
    {
	HandleError(hdbc,SQL_HANDLE_DBC,nstatus);
	return;
    }

    nstatus = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
    nstatus = SQLPrepare(hstmt,(SQLCHAR*) "SELECT 'AB' FROM a UNION SELECT col1 FROM b WHERE col1= ?", SQL_NTS);

    if (nstatus != SQL_SUCCESS)
    {
	HandleError(hstmt,SQL_HANDLE_STMT,nstatus);
	return;
    }
	
    SQLCHAR szCol[255];
    SQLUINTEGER colSize;
    SQLSMALLINT slen, dataType, decDigits, Nullable;
    nstatus = SQLDescribeCol(hstmt, 1, szCol, 255, &slen, &dataType, &colSize, &decDigits, &Nullable);
	
    if (nstatus != SQL_SUCCESS)
    {
	HandleError(hstmt,SQL_HANDLE_STMT,nstatus);
	return;
    }

    //The following line prints "2" as the column size. It should print "20".
    printf("Column size reported is: %d\n", colSize);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
  3. Compile the code.
Modification Type:MinorLast Reviewed:9/26/2005
Keywords:kbHotfixServer kbQFE kbbug kbfix kbmdac260sp2fix KB308211
©2004 Microsoft Corporation. All rights reserved.