artyom-beilis / cppcms

CppCMS Framework
Other
450 stars 111 forks source link

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

Open esidebo opened 5 years ago

esidebo commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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 ;