Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q308211

↑ Back to the top


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

↑ 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 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

↑ Back to the top


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.

↑ Back to the top


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

↑ Back to the top


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.

↑ Back to the top


Keywords: kbhotfixserver, kbqfe, kbbug, kbfix, kbmdac260sp2fix, KB308211

↑ Back to the top

Article Info
Article ID : 308211
Revision : 6
Created on : 2/23/2007
Published on : 2/23/2007
Exists online : False
Views : 546