[R-sig-DB] RODBC:sqlQuery() choking on null date in Oracle database
Harlan Harris
h@r|@n @end|ng |rom h@rr|@@n@me
Thu Apr 1 16:25:24 CEST 2010
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]]
More information about the R-sig-DB
mailing list