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 ______________________________________________ R-help@stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html