[R-sig-DB] Re: dbSetDataMappings with DBI.RODBC

[David James]

Hi,

David Kane  <David Kane wrote:
> Many thanks to all the participants in the R Special Interest Group on Database
> Interfaces, especially David James and Michael Lapsley, for constructing such a
> cool set of tools. We are currently experimenting with the latest version of
> DBI/DBI.RODBC/RODBC using R 1.4.0 patched with SQL Server 7. Everything is
> working as it should, so far.
> 
> Our main remaining concern is with dbSetDataMappings. The documentation reports
> that "No driver has yet implemented this functionality." My questions:
> 
> 1) Is this still true?

Unfortunately, yes (AFAIK).

> 
> 2) Are there any plans to change this in RODBC in the near future?
> 
> 3) How hard would it be for us to try and implement this ourselves? We would be
> more than willing to give this a shot with or without some adult
> supervision. Any pointers would be much appreciated.
> 
> Note that the conversion problem is tripping us up in at least two ways. First,
> items like factors and POSIX dates are not converted appropriately. Second
> (although we might be doing something wrong) conversion of base types (see the
> description below) is not so automatic.
> 
> Description:
> 
>      Sets one or more conversion functions to handle the translation 
>      of DBMS data types to R/S objects.   This is only needed for
>      non-primitive data, since all DBI drivers  handle the common base
>      types (integers, numeric, strings, etc.)
> 
> For example, we have a variable "cusip" that is VARCHAR 10 in SQL Server. When we
> select a subset of the data for which "cusip" happens to only include values
> that look like numbers (with no decimals), the variable in R is of class
> integer. I am not asserting that this is unreasonable, but it was somewhat
> surprising. Of course, what we want is for the R type to be determined by the
> SQL Server type regardless of what the variables might look like in any particular
> subset. 
> 

I believe RODBC imports data from the DBMS as character
data, regardless of the type there, then it invokes 
.Internal(type.convert(...)) on the R character vectors which does the 
actual data mapping at that point (the idea is similar to read.table).

> Thanks in advance for any pointers or suggestions.

A workaround would be to post-process the data.frames
produced by fetch().  One could pass to fetch() a list of 
conversion functions to key off the field name (as below) or
field type, or some other meta-data.

The following example overloads the fetch() method to allow specific 
fields to be converted by user-specified functions (in the example
we convert a numeric field to an ordered factor):

res <- dbSendQuery(con, "select * from cars")  ## cars is from data(cars)

# build a list with conversion functions for those fields we're want
# to change ("speed").  The overloaded fetch() method looks for an argument
# called "post" and uses its names to convert those fields on the data.frame.

d <- fetch(res, post = list(speed = function(x) ordered(x, levels = sort(x)))

> sapply(d, class)
$speed
[1] "ordered" "factor" 

$dist
[1] "integer"

setMethod("fetch", "ODBCResult",
   def = function(res, n = 0, ...) {
      if(n<0) n <- 0
      ch <- res using Id[2]
      d <- sqlGetResults(ch, max = n)

      ## post process the fields of the output data.frame
      ## (may be able to use meta-data instead of field name)

      post <- list(...)$post
      if(!is.null(post) && is.list(post)){
         dnames <- names(d)
         for(fname in names(post))
            if(fname %in% dnames)
               d[[fname]] <- post[[fname]](d[[fname]])
      }
      d
   },
   valueClass = "data.frame"
)

One problem with this approach is that fetch() may be invoked repeatedly 
to fetch subsets of the result set, so the levels built at each invocation
will only include levels that appear during that fetch.  One could think
of using closures as converters to better manage data across invocations
of fetch(), but it doesn't seem very clean.

Doug Bates, Tim Keitt, Frank Harrell, and others have already brought up 
some of these issues in this list precisely in the context of factors -- 
e.g., you may need to store the levels of a factor outside the table where 
the factor itself resides.  I believe MS SQL server has facilities to 
define new types -- it may be worthwhile looking into that...

> 
> Regards,
> 
> Dave Kane

-- 
David A. James
Statistics Research, Room 2C-253            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636