dbSetDataMappings with DBI.RODBC

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