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

From: Lapointe, Pierre <Pierre.Lapointe_at_nbf.ca>
Date: Fri 09 Jun 2006 - 23:42:57 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.

**It still crashes without DBI

>>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.

**Good suggestion, however, I'm not sure how to pass a table through an sql statement. From this archived doc,
http://finzi.psych.upenn.edu/R/Rhelp02a/archive/10073.html I tried this using a dataframe instead of a single number.

But I get this error:

#test
chan <- odbcConnect("MySQL51", uid="root", pwd="momie") #default database=fbn
query <- paste("INSERT INTO example VALUES ('",dframe,"')",sep="") sqlQuery(chan,query)
close(chan)

[1] "[RODBC] ERROR: Could not SQLExecDirect"

[2] "S1T00 1136 [MySQL][ODBC 3.51 Driver][mysqld-5.0.21-community-nt]Column count doesn't match value count at row 1"

>>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



AVIS DE NON-RESPONSABILITE: Ce document transmis par courrie...{{dropped}}

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 Fri Jun 09 23: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:52 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.