RE: mysql++ and stored procedures - Jim Wallace - com.mysql.lists.plusplus

4 messages in com.mysql.lists.plusplusRE: mysql++ and stored procedures

From	Sent On	Attachments
William F. Dowling	12 Jun 2007 14:39
Jim Wallace	12 Jun 2007 15:15
Warren Young	13 Jun 2007 02:05
William F. Dowling	13 Jun 2007 08:30

Subject:	RE: mysql++ and stored procedures
From:	Jim Wallace (jwal...@kaneva.com)
Date:	06/12/2007 03:15:39 PM
List:	com.mysql.lists.plusplus

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, @rc, @newBalance, @tranId ); SELECT @rc, @newBalance, @tranId;";

This will return two results sets, first for the SP, and one for the SELECT.

-----Original Message----- From: William F. Dowling [mailto:will...@thomson.com] Sent: Tuesday, June 12, 2007 5:40 PM To: plus...@lists.mysql.com 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,