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

[Lapointe, Pierre]

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