Microsoft KB Archive/308264

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

Article ID: 308264

Article Last Modified on 2/23/2007

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.5 Service Pack 2
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft Data Access Components 2.6 Service Pack 2
 * 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 Microsoft SQL Server 3.7

-



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.



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



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



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):
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include 
 * 7) 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,&quot;Invalid handle!\n&quot;); return; }

while (SQLGetDiagRec(hType, hHandle, ++iRec,(SQLCHAR *)szState, &iError, (SQLCHAR *)szMessage,(SQLSMALLINT)(sizeof(szMessage) /           sizeof(TCHAR)),(SQLSMALLINT *)NULL) == SQL_SUCCESS) {   fprintf(stderr,TEXT(&quot;[%5.5s] %s (%d)\n&quot;),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*) &quot; &quot;, SQL_NTS,             (SQLCHAR*) &quot; &quot;, SQL_NTS, (SQLCHAR*) &quot; &quot;, 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*) &quot;SELECT 'AB' FROM a where col1=? UNION SELECT col1 FROM b WHERE 1= ?&quot;, 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 &quot;2&quot; as the column size. It should print &quot;20&quot;. printf(&quot;Column size reported is: %d\n&quot;, colSize); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); SQLFreeHandle(SQL_HANDLE_ENV, henv); }                    </li> Compile the code.</li></ol>

Additional query words: SQLDescribeCol odbc

Keywords: kbbug kbnofix KB308264

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.