[prev in list] [next in list] [prev in thread] [next in thread] 

List:       r-sig-db
Subject:    Re: [R-sig-DB] RODBC:sqlQuery() choking on null date in Oracle
From:       Marc Schwartz <marc_schwartz () me ! com>
Date:       2010-04-01 16:39:17
Message-ID: A4999DB9-6727-440A-B21E-ED0C162953F3 () me ! com
[Download RAW message or body]

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

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@stat.math.ethz.ch
https://stat.ethz.ch/mailman/listinfo/r-sig-db


[prev in list] [next in list] [prev in thread] [next in thread]