running a mysql query inside a loop of another without a sync error

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.

--
Thanks and best regards,
Delan Azabani
http://azabani.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: running a mysql query inside a loop of another without a syncerror

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.ht ml
###
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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: running a mysql query inside a loop of another without a sync error

From 10000 feet, what you are attempting to do looks like it would be
very easily accomplished with a join, leaving you with 1 result set to
process. I realize that isn't the question, but it might be a
solution.

- michael dykman

On Wed, Jan 19, 2011 at 9:54 PM, 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 =3D mysql_use_result(mysql);
> while (row =3D mysql_fetch_row(res)) {
> =A0 =A0MYSQL_RES *res2;
> =A0 =A0MYSQL_ROW row2;
> =A0 =A0mysql_query(mysql, "some other select query using an id from the
> first");
> =A0 =A0res2 =3D mysql_use_result(mysql);
> =A0 =A0/* ... */
> =A0 =A0mysql_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 appreciat=
ed.
>
> --
> Thanks and best regards,
> Delan Azabani
> http://azabani.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg