Date: Sun, 1 Dec 2002 13:23:04 -0600
Hi James,
Yes, it's possible to define TEMPORARY tables using RMySQL, but you
need to be aware that TEMPORARY tables are by default in-memory
and always thread-specific (a TEMPORARY table created in one
connection/thread is not visible outside that thread/connection).
In addition, in version of MySQL prior to 4.0 you find the following
warning (Appendix A.6.3, MySQL
"You can't use temporary tables more than once in the same query.
For example, the following doesn't work:
SELECT * from temporary_table, temprary_table as t1;
We plan to fix the above in 4.0."
Make sure you use dbSendQuery() over the same connection (this will ensure
the queries are handled by the same thread in the MySQL server). [dbGetQuery()
opens an extra connection if the current connection has a pending result set.]
--
David
James Chapman wrote:
Hi,
I've run into a bit of a problem with using the RMySQL library. I've
been attempting to create a temporary table in a MySQL database and then
perform a SELECT with a WHEREs clause on the table. In effect perform a
SELECT with a subSELECT.
The problem is that it never seems to be able to find the table after I
create it. I've been using the dbSendQuery method to create the table
since this seems to be the one too use for sending all forms of SQL
commands.
Is it possible that MySQL is dropping the temporary table every time a
new dbSendQuery is run? Or (more likely) am I using the library
improperly.
Thanks,
James
... asset pricing is paradoxical. On the one hand, the theory is so
persuasive that it is widely believed to be correct, to the point that
business, government and even jurisdiction appeal to it. Yet there is
little evidence that the theory explains the past, let alone that it
predicts the future.
---The Paradox of Asset Pricing
by Peter Bossaerts
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !) To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._