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
