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