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

From: Lapointe, Pierre <Pierre.Lapointe_at_nbf.ca>
Date: Sat 10 Jun 2006 - 00:08:53 EST

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

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 01:29:30 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.