dcsimg

Using Stored Procedures with ATL

WEBINAR:
On-Demand

Desktop-as-a-Service Designed for Any Cloud ? Nutanix Frame


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

2. Select a datasoure for your object

3. Create a connection string to your database

4. Choose the strored procedure

5. Choose a proper name for your object

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 < CAccessor < CspGetImageAccessor > >
{
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 < CAccessor < CspGetImageAccessor > > ::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;
}


Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date