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

[Lapointe, Pierre]

On 6/9/2006 9:42 AM, Lapointe, Pierre wrote:
> 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.

You can't.  You can only insert one record at a time this way in 
general, but MySQL allows multiple inserts on one line.  So it's a lot 
of work, but you might figure out what's causing your crash.


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

You'd want something like

inserts <- with(dframe, paste("('", id, "','", col1, "','", col2, "')", 
sep="", collapse=",")
query <- paste("INSERT INTO example(id, col1, col2) VALUES", inserts)
sqlQuery(chan, query)

(This isn't even tested to see if I got the syntax right, and it's 
probably not legal syntax on other databases.  For those you could put
together multiple  INSERT statements.)

Nice workaround, but I'd be reluctant to use it for the same reason I'd
prefer not to use RMySQL: I'd like my R code to be easily adaptable in case
I port my DB to let's say PostgreSQL.  Using RODBC, I would probably only
have to change the DSN to make it work.

Pierre Lapointe

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