RODBC sqlSave failure when writing datetime

49 Views Asked by At

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.

0

There are 0 best solutions below