[R] 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 135
#2 246
### 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
day11
svn rev38024
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}}
__
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
Re: [R] sqlSave() and rownames=TRUE makes my Rgui crash
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 135 #2 246 ### 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 __ 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
Re: [R] sqlSave() and rownames=TRUE makes my Rgui crash
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 135
#2 246
### 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}}
__
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
Re: [R] sqlSave() and rownames=TRUE makes my Rgui crash
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 135
#2 246
### 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.
**
__
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
Re: [R] sqlSave() and rownames=TRUE makes my Rgui crash
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 135
#2 246
### 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
