[R] How to create temporary table in MySQL

[Andrew Ziem]

Creating a temp table isn't completely intuitive with MySQL 5 and R
2.8..1, but it can be done.

> library(RMySQL)
Loading required package: DBI
> con <- dbConnect(dbDriver("MySQL"), dbname = "foo", user="me",password="secret")
> x<- data.frame(1:10)
> colnames(x) <-c("x")
> dbWriteTable(con, "#x", x, verbose=TRUE,row.names=FALSE, overwrite=T) # fails
Error in mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not run statement: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '(
	x bigint
)' at line 2)
[1] FALSE
Warning message:
In mysqlWriteTable(conn, name, value, ...) :
  could not create table: aborting mysqlWriteTable
> dbGetQuery(con, "create temporary table x ( x int);") # workaround
NULL
> dbWriteTable(con, "x", x, verbose=TRUE,row.names=FALSE, overwrite=T)
[1] TRUE
> dbGetQuery(con, "select * from x;")
    x
1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10 10
> dbGetQuery(con, "drop table x;") # clean up
NULL




Andrew