Greetings all,
I am running R 2.5.1, RMySQL 0.6 , and DBI 0.2-3 on Windows XP
Like others, I am having trouble with NA/Null value conversions between R
and a MySQL database via DBI, but I could not find my exact problem in the
archives. Most of the time NA values in R get transferred correctly to the
database and back again, unless (apparently) if they are in the last column
of the table being saved.
For example:
> test=dbConnect(MySQL(), user="myname",
host="localhost",
dbname="test") # Connect to
database> set.seed(1); x=data.frame(matrix(round(rnorm(16),1)*10,nrow=4));
x[c(1,4),c(2,4)]=NA; x #generate a table with some missing values
X1 X2 X3 X4
1 -6 NA 6 NA
2 2 -8 -3 -22
3 -8 5 15 11
4 16 NA 4 NA>
If I write that to the database and read it back:
> dbWriteTable(test,"x",x,overwrite=T,row.names=F)
[1] TRUE
> dbGetQuery(test,"SELECT * FROM x")
X1 X2 X3 X4
1 -6 NA 6 0
2 2 -8 -3 -22
3 -8 5 15 11
4 16 NA 4 0
The NAs in column 2 are successfully transferred, but the NAs in column 4
are changed to zeros. If I add another column and repeat:
> set.seed(1); x=data.frame(matrix(round(rnorm(20),1)*10,nrow=4));
x[c(1,4),c(2,4)]=NA; x
X1 X2 X3 X4 X5
1 -6 NA 6 NA 0
2 2 -8 -3 -22 9
3 -8 5 15 11 8
4 16 NA 4 NA 6>
> dbWriteTable(test,"x",x,overwrite=T,row.names=F)
[1] TRUE
> dbGetQuery(test,"SELECT * FROM x")
X1 X2 X3 X4 X5
1 -6 NA 6 NA 0
2 2 -8 -3 -22 9
3 -8 5 15 11 8
4 16 NA 4 NA 6>
>
The NAs in column 4 are maintained.
The pattern continues if I add more NAs in the rightmost column, regardless
of how many columns there are. Any ideas as to what is going on? Is this a
bug? I did look at the table stored in the MySQL database via Toad and the
inner columns correctly have {NULL} values in the NA fields, but the
rightmost column has zeros. So it seems that the problem occurs when the
data is written to the database and not when it is retrieved.
Thanks for any help,
Adam
[[alternative HTML version deleted]]