[R] sqlSave() and rownames=TRUE makes my Rgui crash
Duncan Murdoch
murdoch at stats.uwo.ca
Fri Jun 9 15:19:40 CEST 2006
On 6/9/2006 8:51 AM, Lapointe, Pierre wrote:
> Hello,
>
> I created a table in MySQL with this command
>
> CREATE TABLE example (pk INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(pk),
> id VARCHAR(30),col1 VARCHAR(30),col2 VARCHAR(30))
>
> ### In R, I can connect to this table:
>
> library(DBI)
> library(RODBC)
> chan <- odbcConnect("MySQL51", uid="root", pwd="xxx")
> first <- sqlQuery(chan, "select * from example")
> close(chan)
> First
> #[1] pk id col1 col2
> #<0 rows> (or 0-length row.names)
>
> ### This is the table I'm trying to save:
> dframe <-data.frame(matrix(1:6,2,3))
> colnames(dframe)=c("id","col1","col2")
> dframe
> # id col1 col2
> #1 1 3 5
> #2 2 4 6
>
> ### But this makes Rgui crash and close
> chan <- odbcConnect("MySQL51", uid="root", pwd="xxx")
> sqlSave(chan, dframe, tablename="example", rownames = FALSE, append=T)
> close(chan)
>
> ### With rownames = T and safer=F, it works, but I loose the
> autoincrementing PK in MySQL
> chan <- odbcConnect("MySQL51", uid="root", pwd="momie") #default
> database=fbn
> sqlSave(chan, dframe, tablename="example", rownames = T,
> addPK=T,append=T,safer=F)
> close(chan)
>
> Any idea?
>
> I'm on win2K, MySQL version 5.0.21-community-nt
I don't know why you're using DBI; perhaps it interferes with RODBC somehow.
If that's not it, then you might want to try lower level methods than
sqlSave: perhaps use sqlQuery to send an INSERT command to the
database. Build up from there.
You might also want to look at the thread "Fast update of a lot of
records in a database?" from around May 20, though it was talking about
updates rather than insertions.
Duncan Murdoch
More information about the R-help
mailing list