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

[Duncan Murdoch]

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

Duncan Murdoch

> [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 courrier electronique est destine uniquement a la personne ou a l'entite a qui il est adresse et peut contenir des renseignements confidentiels et assujettis au secret professionnel. La confidentialite et le secret professionnel demeurent malgre l'envoi de ce document a la mauvaise adresse electronique. Si vous n'etes pas le destinataire vise ou la personne chargee de remettre ce document a son destinataire, veuillez nous en informer sans delai et detruire ce document ainsi que toute copie qui en aurait ete faite. Toute distribution, reproduction ou autre utilisation de ce document est strictement interdite. De plus, le Groupe Financiere Banque Nationale et ses filiales ne peuvent pas etre tenus responsables des dommages pouvant etre causes par des virus ou des erreurs de transmission.
> 
> DISCLAIMER: This documentation transmitted by electronic mail is intended for the use of the individual to whom or the entity to which it is addressed and may contain information which is confidential and privileged. Confidentiality and privilege are not lost by this documentation having been sent to the wrong electronic mail address. If you are not the intended recipient or the person responsible for delivering it to the intended recipient please notify the sender immediately and destroy this document as well as any copies of it. Any distribution, reproduction or other use of this document is strictly prohibited. National Bank Financial Group and its affiliates cannot be held liable for any damage that may be caused by viruses or transmission errors.
> **************************************************
>