Microsoft KB Archive/308211

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

Article ID: 308211

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.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 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
  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   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 <Odbcss.h>
 * 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, <BR/> &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 UNION SELECT col1 FROM b WHERE col1= ?&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: kbhotfixserver kbqfe kbbug kbfix kbmdac260sp2fix KB308211

-

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

© Microsoft Corporation. All rights reserved.