[R] RODBC under Windows

[Prof Brian D Ripley]

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