Using Stored Procedures with ATL

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

There are many ways to interface a database. Often
programmers use the easiest method available to them, which can cause costly
inefficient code. One mistake I often see, is the use of run-time built SQL
strings that are passed to the database, which need to be parsed and
interpreted, prior to returning a set of records or performing some other
transaction. Every proprietary database whether it be ORACLE, DB2, or SQL
Server, has some method of storing precompiled SQL syntax, which can then be
provoked from an application. Needless to say, this can proved better perform as
well as providing a tightly defined interface between Server and client application. In
SQL Server, theses precompiled SQL statements are called stored
procedures. In the following example I will illustrate how to use OLEDB, with the help of
the ATL data consumer object, to retrieve records from a SQL Server, using a
stored procedure.

1. The Stored Procedure
Creating stored procedures in Visual Studio is easy and straightforward.
If creating stored procedures is a completely foreign concept, you might
want to do some research before reading this article. Try checking out

Programming Stored Procedures
in the MSDN library. The stored procedure
we will be using is below.

Create Procedure
spGetImage
@id int

As
SELECT
ImageId, ImageDescription, ImagePath
FROM
tblImages
WHERE
ImageId = @id


Often I see the will card * used rather than typing out the column names.
Specify column names will create a tightly defined interface with your
database, while using * will cause any changes in the table structure to break
the client. So don’t be lazy, type out your SELECT statement.

1. ATL Data consumer
Calling a stored procedure directly through OLEDB Interfaces can be a
timely and tedious task. Fortunately ATL as a hand full of template class
that make interfacing a database rather pain less (#include <atldbcli.h>). The two classes will be
using are CCommand and CAccessor (which derives from CAccessorBase).
If your stored procedure doesn’t require parameters you could use CTable
rather than CCommand.

1. Insert and ATL object and choose Data Consumer

The object wizrard will provide you wiht the following code:
First a custom class based of the structure spGetImage that is used by
the CAccessor Template which in turn is used by the CCommand template class.
Notice the Parameter map and define command macros that constitute much of the custom assessor class.

class CspGetImageAccessor

{

public:

	LONG m_RETURNVALUE;

	LONG m_id;

BEGIN_PARAM_MAP(CspGetImageAccessor)

	SET_PARAM_TYPE(DBPARAMIO_OUTPUT)

	COLUMN_ENTRY(1, m_RETURNVALUE)

	SET_PARAM_TYPE(DBPARAMIO_INPUT)

	COLUMN_ENTRY(2, m_id)

END_PARAM_MAP()
DEFINE_COMMAND(CspGetImageAccessor, _T(“{ ? = CALL dbo.spGetImage;1 (?) }”))
	void ClearRecord()

	{

		memset(this, 0, sizeof(*this));

	}

};
class CspGetImage : public CCommand &lt CAccessor &lt CspGetImageAccessor &gt &gt

{

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_INIT_PROMPT, (short)4);

		dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(“DRIVER=SQL Server;SERVER=****;UID=sa;PWD=pwd;DATABASE=OnlineSchool;Network=DBMSSOCN;Address=****”));

		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);

		hr = db.Open(_T(“MSDASQL”), &dbinit);

		if (FAILED(hr))

			return hr;
		return m_session.Open(db);

	}

	HRESULT OpenRowset()

	{

		return CCommand &lt CAccessor &lt CspGetImageAccessor &gt &gt ::Open(m_session);

	}

	CSession m_session;

};

Unfortunately the Object Wizard isn’t smart enough to also define the columns that will be return by our stored procedure. These will have to be added by hand.

…

	LONG m_RETURNVALUE;

	LONG m_id;



	LONG m_ImageId;

	TCHAR m_ImageDescription[31];

	TCHAR m_ImagePath[101];

BEGIN_COLUMN_MAP(CspGetImageAccessor)

	COLUMN_ENTRY(1, m_ImageId)

	COLUMN_ENTRY(2, m_ImageDescription)

	COLUMN_ENTRY(3, m_ImagePath)

END_COLUMN_MAP()



BEGIN_PARAM_MAP(CspGetImageAccessor)

	SET_PARAM_TYPE(DBPARAMIO_OUTPUT)

	COLUMN_ENTRY(1, m_RETURNVALUE)

	SET_PARAM_TYPE(DBPARAMIO_INPUT)

	COLUMN_ENTRY(2, m_id)

END_PARAM_MAP()

…

3. Let’s use it!
Now that the our data access class is done, let’s use it.
Following is a method that returns the record that
can be used by an automation client. Notice that after you call the open
fucntion you mose also call movefirst to fill your memeber variables. Also, you must close both
the session(m_session) and the command object.

//IDL

[id(1), helpstring("method GetImageRecord")] HRESULT GetImageRecord([in]  LONG id,  [in, out] BSTR * path, [in, out] BSTR * disc);
//CPP
STDMETHODIMP CIDLTempObj::GetImageRecord(LONG id, BSTR * path, BSTR  *disc)

{

	CspGetImage tempDB;

	HRESULT hr ;

	tempDB.m_id = id;

	hr = tempDB.Open();

	if(FAILED(hr))

	{

		return E_FAIL;

	}

	hr = tempDB.MoveFirst();

	if(FAILED(hr))

	{

		return E_FAIL;

	}

	CComBSTR tempbstr = tempDB.m_ImagePath;

	tempbstr.CopyTo(path);

	tempbstr = tempDB.m_ImageDescription;

	tempbstr.CopyTo(disc);

	tempDB.Close(); tempDB.m_session.Close();

	

	return S_OK;

}