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
