Hi Monty,
> Why do you need to retrieve so many rows to the client? Can't you use
> LIMIT to restrict the number of result rows?
The point is that the program scans the entire table and process each row
in a diffrent way, executing other queryes inside the main SQLFetch() loop.
Someting like this:
SQLExecute(hstmt1,query1)
while(SQLFetchRow(hstmt1) == SQL_SUCCESS )
{
...................................
...................................
SQLExecute(hstmt2,query2)
while(SQLFetchRow(hstmt2) == SQL_SUCCESS )
{
......................
}
}.
Perhaps the code should be rewritten, to reflect the right way
to write code that uses database, but in my source code i
don't use ODBC directly, it is embedded, I have someting like this:
dbSetKey(Db,"table1","column1,column2");
.............................................
dbSetValue("table1.column1", value);
...........................................
err=dbStart(Db,"table1")
while(!err)
{
.....................................
.....................................
err=dbStart(Db,"table2")
while(!err)
{
....................................
dbNext(Db,"table2");
}
dbNext(Db,"table1");
}
dbStart() build the right query with the where condition and order by
using ODBC.
For each running program I have one instance of mysqld connected to.
There are up to 300 programs written in this way and they
works, I can't modify them in a few time.
>
> Are you sure you close the statement before doing another ?
> The reason this happens is that mysql_free_result() is not called
> for the previous query and there is still on the connections data
> for the previous query.
>
> Executing mysql_free_result() will quickly discard all rows from the
> server so that you can issue a new command against it.
>
Reading the manual :( It seems that i can do this ONLY using
mysql_store_result(), because each mysqld instance can do
only one query at a time, is it correct ?
If yes, I need some way to reduce the client memory usage,
processing big tables.
I can implement something build a select with limit n1,2n
re-run the query when SQLFetchRow() fails.
I don't have notice how the other databases works in this cases.
In my opinion the right way to work around this problem should be
to use a temporary file to store results for the big tables, forcing this
option with a parameter as SQL_BIG_TABLES=1, in this case
mysql_store_result() can store the result in a file, i can issue the
SQLFreeResult() and build another query.
What do you think about ?
> Which version of MyODBC do you use?
myodbc-2.50.22
> If you have an very old MyODBC versions which includes an old
> mysqlclient lib this may explain your problems; Older
> mysql_free_result() versions didn't read the old result from the
> connections and because of this one would get the error message you
> got if one didn't read all rows from the result before calling
> mysql_query() again.
Thanks, Monty
marcello
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php