mysql++ and stored procedures 2007-06-12 - By Jim Wallace
Back
I call them all them time. There are a couple gotcha about using stored
procs. First you need to setup to get multiple reset sets, which is a
MySQL requirement:
try
{
m_conn->set_option(Connection::opt_multi_statements,
true); // need this for calling SPs
m_conn->connect( m_dbName.c_str(), dbServer.c_str(),
dbUser.c_str(), dbPassword.c_str(), dbPort );
if(m_conn->connected())
{
m_conn->set_option(Connection::Option::opt_reconnect, true);
}
}
catch (const mysqlpp::Exception& er)
...
Then if you call a SP, you usually get two results sets back, the second
one always empty. You must pull off this empty result set, or else the
connection will break on the *next* SQL call.
Getting OUT parameters can be done like this:
query << "CALL " << m_kanevaDbName <<
".apply_transaction_and_details_to_user_balance( %0, %1, %2q, %3q, %4,
%5, @(protected), @(protected), @(protected) ); SELECT @(protected), @(protected), @(protected);";
This will return two results sets, first for the SP, and one for the
SELECT.
-- --Original Message-- --
From: William F. Dowling [mailto:william.dowling@(protected)]
Sent: Tuesday, June 12, 2007 5:40 PM
To: plusplus@(protected)
Subject: mysql++ and stored procedures
Does mysql++ handle stored procedures? I didn't see anything in the
docs. When I tried, I an exception was thrown with this message:
PROCEDURE test.get_times_cited can't return a result set in the given
context
I'm new to mysql++, and fairly new to mysql too, so this is probably
something really simple; I just don't know how to fix the complaint.
Details:
This select statement works fine:
select count(src_ck) from cites where cites.cite_ck='ck2';
+-- ---- ---- --+
| count(src_ck) |
+-- ---- ---- --+
| 1 |
+-- ---- ---- --+
1 row in set (0.11 sec)
I created this procedure:
create procedure get_times_cited(ck varchar(32)) select count(src_ck)
from cites where cites.cite_ck=ck;//
I can call it from a command line client:
call get_times_cited('ck2');
+-- ---- ---- --+
| count(src_ck) |
+-- ---- ---- --+
| 1 |
+-- ---- ---- --+
1 row in set (0.11 sec)
I coded it like this:
try {
mysqlpp::Query query = con.query();
// (*)
// query << "select count(src_ck) from cites where
cites.cite_ck="
// << mysqlpp::quote << ck;
query << "call get_times_cited(" << mysqlpp::quote << ck <<
")";
mysqlpp::Result res = query.store();
if (res) {
mysqlpp::Row row = res.at(0); // count returns one row
*result = row.at(0).get_string();
}
}
catch (const mysqlpp::BadQuery& er) {
// Handle any query errors
std::cerr << "Query error: " << er.what() << std::endl;
}
That works fine if I use the two lines after the (*) comment, but throws
an exception as I've coded it here.
Any help would be gratefully appreciated. Thanks,
Will
--
William F Dowling
william.dowling@(protected)
www.scientific.thomson.com
--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsub=jwallace@(protected)
--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsub=mysql@(protected)
|
|
