[R-sig-DB] RODBC:sqlQuery() choking on null date in Oracle database

[Harlan Harris]

Hello,

I'm running R 2.10.1, RODBC 13.1, Actual Technologies ODBC, etc., to connect
to an Oracle 10g database. Ran across a problem today where a query was
crashing because of a null value in the database. The column is of type
Date, and is allowed to be null.

When I query this field, I get the following error:

Error in charToDate(x) :
  character string is not in a standard unambiguous format

I traced it to this block of code in the function sqlGetResults():

        for (i in seq_len(cols)) {
            if (is.character(data[[i]]) && nchar(enc))
                data[[i]] <- iconv(data[[i]], from = enc)
            if (as.is[i] || is.list(data[[i]]))
                next
            if (is.numeric(data[[i]]))
                next
            if (cData$type[i] == "date")
                data[[i]] <- as.Date(data[[i]])
            else if (cData$type[i] == "timestamp")
                data[[i]] <- as.POSIXct(data[[i]])
            else data[[i]] <- type.convert(as.character(data[[i]]),
                as.is = !stringsAsFactors, dec = dec)
        }

In this case, data[[i]][1] is of type character, str_length 0. Indeed, that
data type causes as.Date to throw an error.

> as.Date("")
Error during wrapup: character string is not in a standard unambiguous
format

This should not happen. The correct behavior is for zero-length dates to be
converted into as.Date(NA), I think.

(That is, it's OK if as.Date() throws an error, but it is definitely not OK
that RODBC allows correctly-formed data to throw an error!)

Null fields of non-Date types (character, integer) seem to work fine.

Is there a workaround?

Thank you!

 -Harlan

	[[alternative HTML version deleted]]