Help and Support

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

View products that this article applies to.
Article ID:235053
Last Review:November 3, 2003
Revision:2.1
This article was previously published under Q235053
On This Page

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.

Back to the top

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.

Back to the top

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

Back to the top

STATUS

This behavior is by design.

Back to the top

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

Back to the top

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

Back to the top

Keywords: 
kbconsumer kbdatabase kbprb kbdtl KB235053

Back to the top

Article Translations

 

Other Support Options

  • Contact Microsoft
    Phone Numbers, Support Options and Pricing, Online Help, and more.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.