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

[Prof Brian Ripley]

On Wed, 16 Jan 2002, David James wrote:

> 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).

It certainly used to. I once sent patches to Michael to do otherwise, but
I don't think they got incorporated.  But it's hard to be general: SQL
allows a very large range of types, much larger than R's.


-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272860 (secr)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595