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.
> **************************************************
>
______________________________________________
[email protected] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
