R help - Dec 2002 - 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.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@shell.com
Internet: http://www.shell.com/chemicals


	[[alternate HTML version deleted]]

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