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

[Marc Schwartz]

On Apr 1, 2010, at 9:25 AM, Harlan Harris wrote:

> 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


I can certainly replicate the error with as.Date():

R version 2.10.1 Patched (2010-03-10 r51274)

32 bit on OSX 10.6.3

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

However, also using the AT ODBC driver for Oracle 11G, along with RODBC 1.3-1, I cannot replicate the problem that you are having with blank dates. Such empty dates in my queries come back as NAs and have for quite some time. Without tracking through code, it would be reasonable to presume that blank dates are being converted to NA's before the loop above is encountered, for example in the call to odbcFetchRows(). 

As an additional thought, if you are calling a lower level query function directly and have modified any of the defaults for arguments such as nullstring or na.strings, that could obviously have an effect as well.

There have been some "quirky" things occurring with Oracle and ODBC connections of late and I would recommend that you try both connecting to Oracle and subsequent queries using the general form:

db <- odbcConnect(dsn, uid = "XXXXX", pwd = "XXXX",
                  case = "toupper", rows_at_time = 1)

sqlQuery(db, Query, rows_at_time = 1)  


The key above is the use of "rows_at_time = 1". It would appear that at least some ODBC drivers have trouble with the default value of 100, which can cause unpredictable results in the query. I have had this issue on my system, not with dates, but with getting inconsistent numbers of rows coming back.

See if that might help.  Needless to say, this date related issue would have been reported a long time ago by others if there were a chronic problem in RODBC itself. 

Finally, given our prior exchange, I also presume that you are not using the beta 64 bit AT driver and are using the release 32 bit version as I do? If the former and none of the above is helpful, then you should consider reporting a bug to AT.

Marc Schwartz