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

[Joran Elias]

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]]