CRecordSet and stored procedure that returns a recordset

[Sir Fredrick]

Hello,
I have a very simple stored procedure:

CREATE PROCEDURE sp_getcurrentmonth
AS SELECT TOP 1 month_id FROM lockdown_schedule WHERE lockdown_date + 7 > GETDATE() ORDER BY lockdown_date asc
GO

When I execute the procedure in the query analyzer, it returns 1 row with the number I'm expecting.

However when I use the CRecordset open:
m_rs.Open(CRecordset::forwardOnly,"EXECUTE sp_getcurrentmonth",CRecordset::executeDirect|CRecordset::readOnly)
it crashes. Running through the debugger I can see that this is because m_nFields is 0.

Am I missing a step here? Why would the number of fields be zero? it should be one.

[Tron]

Check out this excellent tool. It may point you in the right direction.

Query Tool (using ODBC)

http://home.att.net/~gpoulose/

[Sir Fredrick]

Thanks. I'd actually already looked at that, but I haven't really been able to get anywhere with it...it's a lot more complex than I'd like.

[Sir Fredrick]

That is to say, I had looked at the query tool. I just noticed the classwizard on that page, and I've poked around with that a bit. The problem is that I'm not using the recordset class this way. I'm using it as a flexible one-size-fits-all solution, so that I don't have to create a seperate class for every recordset I need. Seriously, my application would need at least 30 CRecordSet classes like that. The way I'm doing it, I just call Open or ExecuteSQL (through the CDatabase member), and then call GetFieldValue. I'd like to be able to do that, except with a stored procedure. I don't want to use member variables inside CRecordSet to store the values.

[Sir Fredrick]

I changed my call to the SP to "{? = CALL sp_getcurrentmonth}" inside the Open function call, and now it gives me the message:
COUNT field incorrect or syntax error

[Sir Fredrick]

Help! please!

[Zim327]

from the msdn:
ExecuteSQL does not return data records. If you want to operate on records, use a recordset object instead.

so you can't use ExecuteSQL to return a recordset.
You should use the stored procedure tool that Tron gave you.
As long as your stored procedures are working properly, the tool will generate all the necessary code for you and then you just add the cpp and h files to your project.
It's fairly easy to use and it will save you a lot of time and frustration.
And yes, if you have 30 sp's then you'll have 30 classes to add, but if it works who cares? The tool allows you to easily modify the code if the sp changes.

AFAIK, using the tool is the easiest way out. I don't know if its possible to make 1 Crecordset class that can handle multiple stored procedures. I'm fairly certain you can only use 1 recordset for 1 stored procedure.

[Sir Fredrick]

Thanks Zim, but I really hope that's not the case (or I hope I can find a way to modify my CRecordSet derived class to handle this).

I designed my app with the understanding that it will not always be my project. It may ultimately end up in the hands of someone who does not even know anything about MFC or necessarily C++, so the goal was to design it in such a way that it's highly extensible, flexible, OO, small and reusable. Ideally, adding a new procedure or field or table should only require one extra line of code, not an extra class.
The instructions left for future programmers are like:
"Double-click on XXX function in ZZZ class. Scroll to the bottom of the function and add the line:
m_myObject.AddInformation("yourinformationgoeshere");
replace the part in quotes with the name of your (whatever)."

This model works perfectly right now using a single CRecordSet class to control all database access, when accessing fields and views directly.
My goal however is to take this one step further using stored procedures, so that all the SQL is on the server, thereby increasing performance while making it more of a "thin client" solution where the server has more control over the data, so a hijacked or crashing client going bezerk can't do any real harm by bypassing the protection measures in place on the client side.

[Sir Fredrick]

One last try to get some help on this...

[Tron]

I would use this or mimic it.

QueryDef - a complete implementation of a dynamic recordset

http://www.codeguru.com/Cpp/data/mfc...cle.php/c1163/

[Sir Fredrick]

I really thought this was going to be the answer...but the sample app crashes when I tell it to execute the stored procedure...I'll keep poking around at it though.

[Tron]

I think there are some other dynamic query examples out there. You're probably close just need the correct last piece.

Good luck!

[Sir Fredrick]

I got it!
That stored procedure class got me close to the answer but also put me on the wrong track a bit. The piece it gave me was:
return _T("{? = CALL sp_getcurrentmonth}");

Which brought me close to the answer because I thought you called a stored procedure with EXEC, not with that crazy {? = CALL stuff.

But that class assumes you're trying to access a parameter the procedure returns (and this doesn't return any parameters), so when I made a call that way there were no results to return.

So, the way to call a stored procedure that returns a recordset, is simply:
"{CALL sp_whatever}"

That's it. It works like a charm now. I still haven't found any documentation that clearly spells this out, I was just operating on a hunch.

Thanks so much for your help guys.

Here's how I did it in my particular example:

theApp.m_dbStatistics.Open("{CALL sp_getcurrentmonth}");
theApp.m_dbStatistics.GetFieldValue("month_id", m_sMonth);

Where m_dbStatistics is my CRecordset derived class.

[Bob Carleon]

Look at that! More than 14 years later and I just implemented this! Thanks so much, you saved me a boatload of time! Works like a charm. I use this on a base CRecordSet and it works great.
It also seems to work if you need to pass a parameter- although I need to test this fully.
CString strMonth;
if(rs.IsOpen() )
rs.Close();
try
{
rs.Open(AFX_DB_USE_DEFAULT_TYPE,"{CALL sp_getcurrentmonth("yo Dude")}");
if(!rs.IsEOF() )
{
rs.GetFieldValue("month_id", strMonth);
}
}
catch(CDBException * e)
{
CString err;
err.Format("Database Error - %s",e->m_strError);
e->Delete();
CProgressDlg:isplayErrorDialog( err, true, bStartedAsService );
g_MajorConnection.Close();
return 0;
}
rs.Close();

[VictorN]

Look at that! More than 14 years later and I just implemented this! Thanks so much, you saved me a boatload of time! Works like a charm. I use this on a base CRecordSet and it works great.
It also seems to work if you need to pass a parameter- although I need to test this fully.

	CString strMonth;
	if(rs.IsOpen() )
		rs.Close();
	try
	{
		rs.Open(AFX_DB_USE_DEFAULT_TYPE,"{CALL sp_getcurrentmonth("yo Dude")}");
		if(!rs.IsEOF() )
		{
			rs.GetFieldValue("month_id", strMonth);
		}
	}
	catch(CDBException * e)
	{
		CString err;
		err.Format("Database Error - %s",e->m_strError);
		e->Delete();
		CProgressDlg::DisplayErrorDialog( err, true, bStartedAsService );
		g_MajorConnection.Close();
		return 0;
	}
	rs.Close();

Congrats!
Please, when you next time will post here some code then use the CODE tags!