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

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();
   						

Back to the top