Ritter, Christian C MCIL-CTGAS
2002-Dec-18 16:41 UTC
[R] A little problem handling logicals in RMySQL under R1.6.1
There is a little problem in handling logicals in RMySQL: # here is the MySQL connection> con<MySQLConnection:(1816,0)> # here is the data frame> print(a<-data.frame(x=c(TRUE,FALSE),y=c(FALSE,TRUE)))x y 1 TRUE FALSE 2 FALSE TRUE # as promised, the two data frame columns are identified as logicals and # the field types are set to tinyint> field.types <- sapply(a, dbDataType, dbObj = con) > field.typesx y "tinyint" "tinyint" # However, in WriteTable, nothing is done to convert the logicals to 0s and 1s. # And logically, the infile is written in TRUE and FALSE and finally, in MySQL # all becomes zero. Here is what we get in MySQL: # row_names x y # 1 0 0 # 2 0 0 # # So, logically this is what we get back in R:> dbWriteTable(con,"test",a)[1] TRUE> aa<-dbReadTable(con,"test") > aax y 1 0 0 2 0 0># and this is clearly not what we want... # # as a crude patch I added for (i in 1:length(value)) if (class(value[[i]])=="logical") class(value[[i]])<-"integer" # before i <- match("row.names", ... in mysqlWriteTable # but others have much more talent in writing clean code than I ... #> mysqlWriteTablecrudepatch(con,"test",a,overwrite=TRUE)[1] TRUE # and> aa<-dbReadTable(con,"test") > aax y 1 1 0 2 0 1 # and this IS what we want. platform i386-pc-mingw32 arch i386 os mingw32 system i386, mingw32 status major 1 minor 6.1 year 2002 month 11 day 01 language R Christian Ritter Functional Specialist Statistics Shell Coordination Centre S.A. Monnet Centre International Laboratory, Avenue Jean Monnet 1, B-1348 Louvain-La-Neuve, Belgium Tel: +32 10 477 349 Fax: +32 10 477 219 Email: christian.ritter@shell.com Internet: http://www.shell.com/chemicals [[alternate HTML version deleted]]
David James
2002-Dec-18 22:50 UTC
[R] A little problem handling logicals in RMySQL under R1.6.1
Hi Christian, Thanks a lot for the bug report and the fix. I ended up modifying your fix only very slightly: for(i in seq(along = value)){ if(is(value[[i]], "logical")) value[[i]] <- as(value[[i]], "integer") } [the idiom class(x) <- "foo", when using library(methods) is "strongly deprecated" -- see help("class", package = "methods").] Regards, -- David Ritter, Christian C MCIL-CTGAS wrote:> There is a little problem in handling logicals in RMySQL: > > # here is the MySQL connection > > con > <MySQLConnection:(1816,0)> > > # here is the data frame > > print(a<-data.frame(x=c(TRUE,FALSE),y=c(FALSE,TRUE))) > x y > 1 TRUE FALSE > 2 FALSE TRUE > > # as promised, the two data frame columns are identified as logicals and > # the field types are set to tinyint > > field.types <- sapply(a, dbDataType, dbObj = con) > > field.types > x y > "tinyint" "tinyint" > > # However, in WriteTable, nothing is done to convert the logicals to 0s and 1s. > # And logically, the infile is written in TRUE and FALSE and finally, in MySQL > # all becomes zero. Here is what we get in MySQL: > # row_names x y > # 1 0 0 > # 2 0 0 > # > # So, logically this is what we get back in R: > > dbWriteTable(con,"test",a) > [1] TRUE > > aa<-dbReadTable(con,"test") > > aa > x y > 1 0 0 > 2 0 0 > > > # and this is clearly not what we want... > # > # as a crude patch I added for (i in 1:length(value)) if (class(value[[i]])=="logical") class(value[[i]])<-"integer" > # before i <- match("row.names", ... in mysqlWriteTable > # but others have much more talent in writing clean code than I ... > # > > mysqlWriteTablecrudepatch(con,"test",a,overwrite=TRUE) > [1] TRUE > # and > > aa<-dbReadTable(con,"test") > > aa > x y > 1 1 0 > 2 0 1 > # and this IS what we want. > > platform i386-pc-mingw32 > arch i386 > os mingw32 > system i386, mingw32 > status > major 1 > minor 6.1 > year 2002 > month 11 > day 01 > language R > > > > Christian Ritter > Functional Specialist Statistics > Shell Coordination Centre S.A. > Monnet Centre International Laboratory, Avenue Jean Monnet 1, B-1348 Louvain-La-Neuve, Belgium > > Tel: +32 10 477 349 Fax: +32 10 477 219 > Email: christian.ritter at shell.com > Internet: http://www.shell.com/chemicals > > > [[alternate HTML version deleted]] > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > http://www.stat.math.ethz.ch/mailman/listinfo/r-help-- David A. James Statistics Research, Room 2C-253 Phone: (908) 582-3082 Bell Labs, Lucent Technologies Fax: (908) 582-3340 Murray Hill, NJ 09794-0636