List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 20 2011 4:59am
Subject:Re: running a mysql query inside a loop of another without a sync
error
View as plain text  
Hello Delan,

On 1/19/2011 21:54, Delan Azabani wrote:
> Hi all,
>
> I'm using MySQL with C in a CGI application. I hope this is the right
> list to ask for help.
>
> If I have this simplified code:
>
> MYSQL_RES *res;
> MYSQL_ROW row;
> mysql_query(mysql, "some select query");
> res = mysql_use_result(mysql);
> while (row = mysql_fetch_row(res)) {
>      MYSQL_RES *res2;
>      MYSQL_ROW row2;
>      mysql_query(mysql, "some other select query using an id from the
> first");
>      res2 = mysql_use_result(mysql);
>      /* ... */
>      mysql_free_result(res2);
> }
> mysql_free_result(res);
>
> Whenever I run the second query, inside the loop, I get the nasty
> 'commands out of sync' error. How can I run a select query while in a
> loop fetching rows from another select query? Or, do I have to fetch all
> the rows completely first and store them in memory (which wouldn't be
> very 'nice' to do)?
>
> If someone could help me with this problem, it would be greatly appreciated.
>

The mysql object you are using for your connection can only have one 
active query or result on it at a time. To have two sets of results 
working, you need a second independent connection to the MySQL server

http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
###
MYSQL

This structure represents a handle to one database connection. It is 
used for almost all MySQL functions. You should not try to make a copy 
of a MYSQL structure. There is no guarantee that such a copy will be 
usable.
###


http://dev.mysql.com/doc/refman/5.5/en/mysql-real-connect.html
###
The first parameter should be the address of an existing MYSQL 
structure. Before calling mysql_real_connect() you must call 
mysql_init() to initialize the MYSQL structure. You can change a lot of 
connect options with the mysql_options() call. See Section 22.9.3.49, 
“mysql_options()”.
###

http://dev.mysql.com/doc/refman/5.5/en/threaded-clients.html
###
Two threads can't send a query to the MySQL server at the same time on 
the same connection. In particular, you have to ensure that between 
calls to mysql_query() and mysql_store_result() no other thread is using 
the same connection.
###

This same rule applies to attempting to process more than one query on 
the same connection. You must complete the first query before starting 
the second or you must open a separate connection to handle the second 
query.

Yours,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
running a mysql query inside a loop of another without a sync errorDelan Azabani20 Jan
  • Re: running a mysql query inside a loop of another without a syncerrorMySQL)20 Jan
  • Re: running a mysql query inside a loop of another without a sync errorMichael Dykman20 Jan