Using Stored Procedures with ATL

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


Comments

  • stored procedure with table creation syntax for all tables..

    Posted by Legacy on 02/18/2004 12:00am

    Originally posted by: dinesh

    Hi!
    Iam trying to create a tmp_table through stored Procedure in oracle.
    Procedure
    The stored procedure with table creation syntax for all tables. Change the table name_tmp for tables which is used in my_database.

    Is there anybody who could get the exact syntax for the above mentioned query..

    Thanks & regards,
    Dinesh

    Reply
  • stored procedure with table creation syntax for all tables..

    Posted by Legacy on 02/18/2004 12:00am

    Originally posted by: dinesh

    Hi!
    Iam trying to create a tmp_table through stored Procedure in oracle.
    Procedure
    The stored procedure with table creation syntax for all tables. Change the table name_tmp for tables which is used in my_database.

    Is there anybody who could get the exact syntax for the above mentioned query..
    If so kindly reply to my mail id..
    Thanks & regards,
    Dinesh

    Reply
  • source code available?

    Posted by Legacy on 07/16/2003 12:00am

    Originally posted by: Stan

    Could you please zip the actual source files for reference? Thanks!

    Reply
  • Stored proc returns REF cursor. Did anyone have the same trouble?

    Posted by Legacy on 09/14/2001 12:00am

    Originally posted by: Mike Uchitelev

    Hello everyone,

    Let me explain the problem I have: I created an OLE DB consumer (ATL-based) that calls stored procedure, which returns as an argument REF Cursor. This is recommended by Oracle OLE DB documentation, if someone wants to get entire recordset should use REF Cursor. Well.
    1. I wrote Package, placed Procedure there.
    2. I am about to pass two arguments to this procedure, and get one (rowset, recordset);
    3. I wrote the code first in VB that calls it through ADO. For sure I set ADO.Command property PLSQLSet to 1. Everything as recommended. VB code works perfectly.
    4. Then I switched back to VC++, defined parameter clause BEGIN_PARAM_MAP/END_PARAM_MAP, defined recordset clause BEGIN_COLUMN_MAP/END_COLUMN_MAP. Everything as Oracle doctor prescribed.
    5. Before calling CCommand<>::Open placed CDBPropSet for ORAPROPSET_COMMANDS, and for sure set ORAPROP_PLSQLRSet to true via AddProperty.
    Huh.... It doesn't work. I just don't know why.
    Connection Established, ICommand->CreateCommand returned 0, which is perfectly right, but then.... When Execute is called everything is out.

    Just another thing to add. I created procedure with no parameters to pass and it works fine with MSDAORA, not OraOLEDB, but when I add some parameters it crashes. OracleTracer somehow confused me about the properties:
    ICommandPropertiesOracleImpl::GetProperties(hr=40eda). Some kind of mix. Two pretty different thing at the same time.

    Any ideas, concerns would be greatfully appreciated.

    Mike.

    Reply
  • How to use proceedure of insertion

    Posted by Legacy on 08/30/2001 12:00am

    Originally posted by: Manpreet

    Hi!!!
    This project was a nice help for me to use proceedure.I have created a proceedure of inserting a value but it gives the error of rowset please tell me how to use it.
    I will be very thankul to you.
    With Regards
    Chadha

    Reply
  • Using stored procedures with ATL

    Posted by Legacy on 12/04/2000 12:00am

    Originally posted by: Marc lhost

    If you have a Transact-SQL statement that prints out from you stored procedure then this print will interfere with the expected resultset and your code will crash.
    
    

    Any UPDATE, INSERT, DELETE and so on will interfere.

    You must turn off the messages indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

    To deactivate them (action is performed but no verbose) you must put
    SET NOCOUNT ON
    at the beginning of your stored procedure

    Reply
  • Making Database connection using OLEDB global in ATLCOM

    Posted by Legacy on 09/08/2000 12:00am

    Originally posted by: Vikas

    I have been regular visitor to your site.
    I would like to know that in ATL COM how to make Connection to database using OLEDB global so that consumers don't have to make connection to database. Also would like to know how to insert a DBTIMESTAMP entry into datetime field in database

    Reply
  • how about capturing errors occurred in stored procedures that do not have any output parameters?

    Posted by Legacy on 08/11/2000 12:00am

    Originally posted by: Chakree

    I have a stored declared as :
    
    spname
    (vcaddr1 varchar(10),
    vcaddr2 varchar(20),
    vcaddr3 varchar(50))

    This does not have any output parameter as you can see.
    Now I want to capture the error code that would be returned by my stored procedure. I am using VC 6.0 and SQL Server 7.0. Please kindly guide me to trap the return values returned by a stored procedure without using a parameter.

    Regards
    Chakree

    Reply
  • Examples regarding Access

    Posted by Legacy on 04/24/2000 12:00am

    Originally posted by: JC

    Anybody can provide examples using stored procedure with MS Access?
    Thanks

    Reply
  • What about Stored Procedures returning multiple Record Sets....

    Posted by Legacy on 10/05/1999 12:00am

    Originally posted by: Amit

    Thanx,The code script was of great help...

    The ATL Wizard generates classes based on CAccessor. But CAccessor does not help us in getting results of stored procedure returning varying result sets .

    For example:
    select * from table1

    CDynamicParameterAccessor accessor template helps us in retrieving multiple result sets... for which iam not able to get a work around.

    Can you suggest some ideas ....

    Reagrds
    Amit

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Flash technology is becoming more prominent in the storage industry. Offering superior speed and reliability when compared to traditional hard disk drives – flash storage is a flexible and increasingly cost-effective technology that can be used to optimize enterprise storage environments. This ebook explores the many uses and benefits of flash storage technology in the enterprise. Check it out to discover and learn all you need to: Optimize storage performance Leverage server flash as storage cache …

  • Live Event Date: August 20, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT When you look at natural user interfaces as a developer, it isn't just fun and games. There are some very serious, real-world usage models of how things can help make the world a better place – things like Intel® RealSense™ technology. Check out this upcoming eSeminar and join the panel of experts, both from inside and outside of Intel, as they discuss how natural user interfaces will likely be getting adopted in a wide variety …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds