[R] sqlSave() and rownames=TRUE makes my Rgui crash

[Duncan Murdoch]

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