• 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

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

Back to the top

Steps to Reproduce Behavior

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

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, &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);
   } 
   					

3. Compile the code.

Back to the top