f



MySQL stored procedures (MySQL Connector)

Dear Friends,

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

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

The problem is, 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?

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

All the best,
Przemek M. Zawada
0
9/18/2010 2:58:24 AM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

1 Replies
566 Views

Similar Articles

[PageSpeed] 13

"Przemek M. Zawada" <przemek.zawada@gmail.com> wrote:

> I've received lately quite common (as I've researched) error, which
> occurs as following:
> Commands out of sync; you can't run this command now
>
> The problem is, that I'm not able to determine whether I'm doing
> something wrong, my database server is to old or anything else.

....

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

I suggest to study the manual.

A stored procedure can return many result sets and you have to call
mysql_next_result() (or the equivalent) until it signals "no more
result sets". Only then you can execute the next SQL statement in
this connection.

http://dev.mysql.com/doc/refman/5.1/en/mysql-next-result.html

and also

http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-basic.html#connector-j-examples-stored-procedure


XL
0
Axel
9/19/2010 6:16:01 PM
Reply: