When I am trying to write a dataframe using RODBC::sqlSave (RODBC version 1.3-20) to a MySQL database I am getting an error. For example
Create test table in MySQL, with datetimes in various formats
CREATE TABLE fractest( c1 DATETIME(0), c2 DATETIME(2), c3 DATETIME(4) );
Then running the following in R I get:
> x <- data.frame(c1 = Sys.time(),c2 = Sys.time(),c3 = Sys.time())
> conn <- RODBC::odbcConnect('MyDatabase')
> RODBC::sqlSave(
channel = conn,
dat = x,
append = TRUE,
tablename = 'fractest',
rownames = FALSE,
safer = TRUE,
fast = TRUE
)
Error in if (any(notOK <- (coldata[, 3L] == 0L))) { :
missing value where TRUE/FALSE needed
Stepping through the RODBC, looks like the issue is with sqlColumns returning COLUMN_SIZE as NA
> RODBC:::sqlColumns(conn, 'fractest')[c(4L, 5L, 7L, 9L)]
COLUMN_NAME DATA_TYPE COLUMN_SIZE DECIMAL_DIGITS
1 c1 93 NA NA
2 c2 93 NA NA
3 c3 93 NA NA
Running on an older version of RODBC (1.3-17) gives me
> RODBC:::sqlColumns(conn, 'fractest')[c(4L, 5L, 7L, 9L)]
COLUMN_NAME DATA_TYPE COLUMN_SIZE DECIMAL_DIGITS
1 c1 93 19 0
2 c2 93 19 0
3 c3 93 19 0
and sqlSave works.
I have managed to get it to work by setting argument fast = FALSE, but that is slower and I would like to keep it running fast. Does anyone know of a another way around this.