How to create temporary table in MySQL

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