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

From: Duncan Murdoch <murdoch_at_stats.uwo.ca>
Date: Fri 09 Jun 2006 - 23:19:40 EST

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 Received on Sat Jun 10 00:55:15 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Sun 11 Jun 2006 - 05:34:53 EST.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.