[R-sig-DB] RODBC and type.convert

[Joran Elias]

I sent the message below several months ago, but received no reply. I'm
trying once again, just for good measure.

If it helps any, the behavior I'm seeing below is in R 3.1.2, RODBC 1.3-10,
on OS X Mavericks using the Actual Tech drivers on an Oracle db.

My specific interest is if there are any platform/driver/db combinations
where the column information retrieved into cData in the function
sqlGetResults is unreliable enough that we cannot trust a value of "char"
or "varchar". As it stands, when I query a table with a column containing
values like "01" and "D1", I'm at the whim of the specific result set and
type.convert as to what happens. If my result set contains only "01",
type.convert gives me the integer 1. If my result set contains "D1" I get a
character/factor as you might expect.

Many thanks!

- Joran

On Fri, Sep 19, 2014 at 2:21 PM, Joran Elias <joran.elias using gmail.com> wrote:

> I know from the documentation that RODBC applies type.convert to columns
> (excepting some date and date time classes). In particular, the section
> from 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]]),
>                                           na.strings = na.strings,
>                                           as.is = !stringsAsFactors,
>                                           dec = dec)
>
> This means that RODBC is fairly aggressive about converting character
> columns to numeric when compared to ROracle:
>
> > str(RODBC::sqlQuery(odbc_con,"select '01' as val from dual"))
> 'data.frame': 1 obs. of  1 variable:
>  $ VAL: int 1
> > str(ROracle::dbGetQuery(roracle_con,"select '01' as val from dual"))
> 'data.frame': 1 obs. of  1 variable:
>  $ VAL: chr "01"
>
> and further, RODBC will return result sets of different types from the
> same db table depending on what values happen to appear. If there is a
> character column with values "01" and "D1", it might sometimes return a
> factor and other times an integer.
>
> I presume there must be some reason why in the code above cData$type
> values of "varchar" or "char" are somehow unreliable across different
> database platforms (or within the ODBC standard) which would preclude
> simply doing something like:
>
> if(cData$type[i] %in% c("varchar","char")) {#Do something consistent, like
> always leave as character or always convert to factors}
>
> I was wondering if someone could enlighten me as to what that reason might
> be?
>
> Thanks!
>
> - Joran
>
>
>
>

	[[alternative HTML version deleted]]