cppdb:mysql calling stored procedures more than once leads to 'Commands out of sync'

[esidebo]

I'm using cppdb and trying to call a stored procedure on my mysql database like so

cppdb::result res = session << "call my_stored_proc()"

This works on the first call, but if I try to call again (after having called res.clear() or stepping through res.next()) I get a cppdb::mysql_backend::cppdb_myerror exception with the message

what(): cppdb::mysql::Commands out of sync; you can't run this command now.

I have no problem calling the stored procedure multiple times from the mysql prompt, or using queries. After searching the web my best guess is that this might have to do with called procedures returning two result sets, one being a status code, which then is perhaps left dangling (not processed)? I would like to avoid the workaround of closing and reopening the connection, which I suppose hurt performance since that cause cached statements to be cleared.

[artyom-beilis]

Have you tried to use this: http://cppcms.com/sql/cppdb/classcppdb_1_1session.html#a0f366b9d4708fb7674f0637c133fc825

statement cppdb::session::create_statement

use non-prepared statement, since there is likely no advatange of using prepared statement and stored procedure call.

[esidebo]

Thank you for the quick reply. Do you mean like this?

std::string q{"call get_blankdata()"};
cppdb::statement st = sql.create_statement(q);
cppdb::result res = st.query(); //  succeeds (first call)
res.clear();
st.reset();
cppdb::result res2 = st.exec(); // out-of-sync exception

Unfortunately, I get the same error.

[artyom-beilis]

Can you please put a simple example of SQL procedure (I'm not really familiar with writing ones) so I can test it.

Thanks

[esidebo]

If you have a table called 'tab' you can store the SELECT * FROM tab query as a procedure by putting the following in a file and then sourcing that from the mysql prompt. Then test it by doing doing call my_stored_proc();.

DROP procedure IF EXISTS `my_stored_proc`;
DELIMITER //
CREATE PROCEDURE my_stored_proc()
BEGIN
SELECT * FROM tab;
END //
DELIMITER ;