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



The information in this article applies to:

  • Microsoft OLE DB Provider for SQL Server 7.01, when used with:
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
    • Microsoft Data Access Components 2.7
  • Microsoft OLE DB Provider for SQL Server 7.0, when used with:
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
    • Microsoft Data Access Components 2.7

This article was previously published under Q235053

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.
The SQL Server OLE DB Provider that ships with MDAC 2.7 returns the following improved error message:
Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.

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 > ?)
				

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

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

Modification Type:MinorLast Reviewed:11/3/2003
Keywords:kbConsumer kbDatabase kbDTL kbprb KB235053