MySQL Connector/C++ and multiple stored procedures execution

[Przemek Zawada]

Dear Friends,

I've received lately quite common (as I've researched) error, which occurs as following SQLException:

Commands out of sync; you can't run this command now

The problem is a bit complicated, that I'm not able to determine whether I'm doing something wrong, my database server is to old or anything else.

I've discovered, that when I'm sending queries to database,where I request for database VIEW mode, I'm able to loop them lots of times but when I request for stored procedure I receive an error.

My query function looks like so:

bool Database_Object::Query(sql::SQLString queryString, sql::ResultSet **resultSet) {
if(this->mysqlConnection != NULL) {
try {
this->mysqlStatement = this->mysqlConnection->createStatement();
if(this->mysqlStatement != NULL) {
if((*(resultSet) = this->mysqlStatement->executeQuery(queryString)) != NULL) {
delete this->mysqlStatement;
return true;
} else return false;
}
} catch( sql::SQLException &e ) {};
return false;
}

Of course in function where I work with ResultSet* after while(next()) loop I execute the resultSet->close().

The fact is, that while I'm not calling any stored procedures, my MYSQL log shows that my application starts and ends and sql::Connection opens and closes (while i "delete" the *Connection), ie:

Connect rally@192.168.0.66 on
Query set autocommit=1
Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
Query SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
Query USE `RR_virtual`
Query SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
Query SELECT * FROM Team_Select_Teams T
Query SELECT * FROM Stage_Select_Active T
Quit [QQQ]

When I execute any stored procedure, my app ends but the "Quit" [QQQ] line in log file does not appear. What am I doing wrong?

I've found one topic here regarding the "sync exception", but it was left without any answers. I was wondering, whether the problem is actually solved in Connector/C++ - I have used the Connector/.NET in VC++ and as far as I remember I did not receive such exceptions, but maybe I did not use stored procedures so much.

The fact is, just after stored procedure call is fired, I'm not able to work with Connection handle anymore - after reconnecting, it works perfectly, but I presume the reconnection is not the solution for my problem ;)

Fortunately all other things are working fine except the stored procedures.
Any suggestions would be appropriated.

All the best,
Przemek M. Zawada

PS. Regarding environmental details:

MySQL Server Version
* Server version: 5.1.48-log FreeBSD port: mysql-server-5.1.48

MySQL Connector/C++ Version
* mysql-connector-c++-1.1.0

OS Details:
FreeBSD bsd2010.pmz 8.0-RELEASE FreeBSD 8.0-RELEASE #0: Sat Nov 21 15:48:17 UTC 2009 [e-mail]:/usr/obj/usr/src/sys/GENERIC i386
gcc version 4.2.1 20070719 [FreeBSD]