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

View products that this article applies to.

This article was previously published under Q308264

Symptoms

SQLDescribeCol returns an incorrect column size for a complex union query that contains parameters and a WHERE clause.

↑ Back to the top

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.

↑ Back to the top

Resolution

To work around this problem, do either of the following:

Compile the query into a stored procedure that uses parameters.
Reverse the order of the SELECT statements in the union query so that the constant field is in the last SELECT statement.

↑ Back to the top

Status

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

↑ Back to the top

More information

The following is an example of a query that fails:

SELECT 'AB' FROM a where col1=? UNION SELECT col1 FROM b WHERE 1= ?

If you call SQLDescribeCol to obtain the column size for the result of this query, the result is 2. This is incorrect, because the resultant column size for this query is equal to the size of the col1 column.

This problem is similar to the problem that is described in the following Microsoft Knowledge Base article:

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

Note that a fix is available from Microsoft for the problem that is described in Q308211. However, the problem that is described in this article involves more complex queries and is not solved by the fix. The sample query is only one example of a complex query that fails. Many factors may make a query complex and therefore subject to this behavior.

Steps to Reproduce Behavior

Create the following tables on SQL Server:

CREATE TABLE [dbo].[a] (
	[a] [char] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[b] (
	[col1] [char] (20) NULL 
) ON [PRIMARY]
GO

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, &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*) "<insert dsn name here>", SQL_NTS,
              (SQLCHAR*) "<insert user id here>", SQL_NTS, (SQLCHAR*) "<insert password here>", 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 where col1=? UNION SELECT col1 FROM b WHERE 1= ?", 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);
}