PRB: E_FAIL Returned from Prepare() When SQL Statement Contains a Parameter in a Subquery

Using the OLE DB Provider for SQL Server and calling the ATL CCommand::Prepare() function, or ICommandPrepare::Prepare(), returns E_FAIL with the following error description: The SQL Server OLE DB Provider that ships with MDAC 2.7 returns the following improved error message:

The SQL command text for the OLE DB Command object contains a parameter in a subquery. For example, the following SQL statement causes the error:

The Microsoft OLE DB provider for SQL Server cannot derive parameter information from subqueries.

You must call CCommand::SetParameterInfo() or ICommandWithParameters::SetParameterInfo() before calling Prepare().

Please see the MORE INFORMATION section of this article for sample code that uses the ATL OLE DB consumer templates and demonstrates calling SetParameterInfo().

This behavior is by design.

Steps to Reproduce Behavior

1. Create two tables on a Microsoft SQL Server database: Table1 should have a character field and an integer field, Table2 should have an integer field only.
2. Create an accessor class that resembles the following:
   

   class CQuery1Accessor
   {
   public:
   	TCHAR m_field1[11];
   	LONG  m_field2;
   	LONG  m_field1param;
   
   BEGIN_COLUMN_MAP(CQuery1Accessor)
   	COLUMN_ENTRY(1, m_field1)
   	COLUMN_ENTRY(2, m_field2)
   
   END_COLUMN_MAP()
   
   BEGIN_PARAM_MAP(CQuery1Accessor)
   	COLUMN_ENTRY(1,m_field1param)
   END_PARAM_MAP()
   DEFINE_COMMAND(CQuery1Accessor, _T("SELECT * FROM Table1 WHERE field2 IN"
   			   "(SELECT field1 FROM Table2 where field1 > ?)"));
   
   	// You may want to call this function if you are inserting a record and want to
   	// initialize all the fields, if you are not going to explicitly set all of them.
   	void ClearRecord()
   	{
   		memset(this, 0, sizeof(*this));
   	}
   };
   
   						

3. Create a CCommand class that executes the command using the accessor class you just created. The CCommand class should resemble the following:
   

   class CQuery1 : public CCommand<CAccessor<CQuery1Accessor> >
   {
   public:
   	HRESULT Open()
   	{
   		HRESULT		hr;
   
   
   		hr = OpenDataSource();
   		if (FAILED(hr))
   			return hr;
   
   		return OpenRowset();
   	}
   	HRESULT OpenDataSource()
   	{
   		HRESULT		hr;
   		CDataSource db;
   		CDBPropSet	dbinit(DBPROPSET_DBINIT);
   
   		dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
   		dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("yourdatabase"));
   		dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("yourserver"));
   		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
   		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
   		dbinit.AddProperty(DBPROP_AUTH_PASSWORD, "");
   		dbinit.AddProperty(DBPROP_AUTH_USERID, "sa");
   
   		hr = db.Open(_T("SQLOLEDB"), &dbinit);
   		if (FAILED(hr))
   			return hr;
   
   		return m_session.Open(db);
   	}
   	HRESULT OpenRowset()
   	{
   		// Set properties for open
   		LPCTSTR szCommand = NULL;
   		HRESULT hr = S_OK;
   		hr = _CommandClass::GetDefaultCommand(&szCommand);
   		if (FAILED(hr))
   			return hr;
   		hr = CreateCommand(m_session);
   		if (SUCCEEDED(hr))
   		{
   			CComPtr<ICommandText> spCommandText;
   			hr = m_spCommand->QueryInterface(&spCommandText);
   			USES_CONVERSION;
   			if (SUCCEEDED(hr))
   			hr = spCommandText->SetCommandText(DBGUID_SQL, T2COLE(szCommand));
   		}
   /* --------------  Adding this code prevents the error:
   		ULONG ulOrds[]={1};
                   DBPARAMBINDINFO ParamInfo[1];
   
   		ParamInfo[0].pwszDataSourceType = (unsigned short *) L"DBTYPE_I4";
   		ParamInfo[0].bPrecision = 10;
   		ParamInfo[0].bScale = 0;
   		ParamInfo[0].dwFlags = DBPARAMFLAGS_ISINPUT;
   		ParamInfo[0].pwszName = NULL;
   
   		ParamInfo[0].ulParamSize = sizeof(LONG);
   
   		hr = SetParameterInfo(1, ulOrds, ParamInfo);
   ----------------*/ 
   		hr = Prepare(); //// hr =  E_FAIL without SetParameterInfo call
   		if (E_FAIL == hr)
   		{
   			AtlTraceErrorRecords();
   			return E_FAIL;
   		}
   
   		m_field1param = 0;
   		hr = CCommand<CAccessor<CQuery1Accessor> >::Open();
   		return hr;
   	}
   	CSession	m_session;
   };
   
   
   						

   In the code above, remove the comments around the code that calls SetParameterInfo() to prevent Prepare() from returning E_FAIL.
4. Finally, add some code in your application that opens the query:
   

   	CoInitialize(NULL);
   	CQuery1 rs;
   	rs.Open();
   	rs.MoveFirst();