R help - Jun 2006 - sqlSave() and rownames=TRUE makes my Rgui crash

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
> version
               _                                        
platform       i386-pc-mingw32                          
arch           i386                                     
os             mingw32                                  
system         i386, mingw32                            
status         Patched                                  
major          2                                        
minor          3.0                                      
year           2006                                     
month          05                                       
day            11                                       
svn rev        38024                                    
language       R                                        
version.string Version 2.3.0 Patched (2006-05-11 r38024)

Pierre Lapointe


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

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

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

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