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

• Microsoft OLE DB Provider for SQL Server, versions 7.0, 7.01, included with:
  • Microsoft Data Access Components versions 2.0, 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2

SYMPTOMS

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:

Syntax error or access violation.

CAUSE

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:

SELECT * FROM Table1 WHERE field2 IN <BR/>
(SELECT field1 FROM Table2 where field1 > ?) 

RESOLUTION

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

STATUS

This behavior is by design.

MORE INFORMATION

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

Additional query words:

Keywords : kbDAO200 kbDatabase kbOLEDB kbSQLServ kbConsumer kbGrpVCDB kbDSupport
Version : WINDOWS:2.0,2.1,2.1 (GA),2.1 SP1,2.1 SP2,7.0,7.01
Platform : WINDOWS
Issue type : kbprb