[R] Date: Sun, 1 Dec 2002 13:23:04 -0600

[David James]

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
> _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._