[R] RODBC under Windows

Prof Brian D Ripley ripley at stats.ox.ac.uk
Wed Mar 20 08:54:33 CET 2002

On Tue, 19 Mar 2002 apjaworski at mmm.com wrote:

> Hi,
> I have been just playing with the ODBC connectivity under Windows.  I have
> never done this before and I thought I would go through some examples in
> the "Data Import/Export" manual and learn how to setup data connections
> while doing that.  I ran into a small problem and I am not sure if this is
> something I am doing wrong or a bug or, perhaps, a feature of the RODBC
> package.
> Here is what I did:
> (1) I created an empty Excel file (workbook).
> (2) I setup this file as a data source named "Excel Test" using Data
> Sources(ODBC) under Control Panel/Administrative Tools (I am using
> Win2000).
> (3) In R I did
>      cc <- odbcConnect("Excel Test")
>      data(USArrests)
>      sqlSave(cc, USArrests, rownames="States")
>      sqlTables(cc)                                 # shows the Sheet1$ and
> USArrests$ as system tables and USArrests as a table
>      sqlQuery(cc, "select state, murder from USArrests")          # works
> fine
>      sqlQuery(cc, "select state, murder from USArrests where rape>30")
> This last statement gives me a type mismatch error.  After disconnecting
> and inspecting the Excel file I discovered that all the values were saved
> as text.
> I thought that this was peculiar to Excel so I repeated the whole procedure
> with an Access database.  The same thing - all the data were saved as text.
> I can go to the database and change them by hand to numbers and then the
> last query above works fine.
> Am I doing something wrong?  Is this how things are supposed to work?

It is how RODBC works if the table does not exist.  The type mismatch error
is peculiar to VBA-based databases: I met it with Access.

I did make a version of RODBC that selected a type more sensibly and
submitted it to Michael Lapsley.  It is tricky though, as for example the
ODBC driver for MySQL lied when interrogated about its types.

Brian D. Ripley,                  ripley at 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

r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch

More information about the R-help mailing list