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]]


