Hi,

I'd like to store results temporarily to be available for next statements. I the documentation I found the SQL_BUFFER_RESULT option but I cannot get it to work.
If I add the SQL_BUFFER_RESULT to my query it does not complain. But how can I refer to the  buffered data in my next query?

Thanks in advance

My reading of the documentation is that it buffers the results from the query you are making in a temporary table, thus allowing the table(s) from which the query are derived to be safely released / unlocked earlier.  It does not provide a facility to perform a query once and pass through the result set several times.

Just stating the keyword is enough to run the extra functionallity, which does mean that your MySQL is going to need more memory / swap space.

Than I misunderstood the documentation about the SQL_BUFFER_RESULT. Is there an other way to store results between statements?

There are times that I write applications that select from databases and provide two or more sets out outputs based on the same query.  If necessary in such cases, I run the query once and store the pertinant information into an array, list or hash (depending on the case and the language) from which the second and subsequent outputs can be generated.

If you have a case where a complex query run (say) once a day provides an information feed for the following 24 hours, there's nothing to stop you running a query and writing a new table from your application, with than new table being referenced for the 24 hours.  This is a widely accepted and quite common practise - although from your original question, I don't think you want this degree of persistance.

The second option you described sounds okee to me. Since a few days, I'm using version 4.1. I did not worked with the CREATE TABLE ... LIKE statement, but that should do the job I think.

thanks a lot!

---