[R] RODBC under Windows

[apjaworski@mmm.com]

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?

Thanks in advance,

Andy

__________________________________
Andy Jaworski
Engineering Systems Technology Center
3M Center, 518-1-01
St. Paul, MN 55144-1000
-----
E-mail: apjaworski at mmm.com
Tel:  (651) 733-6092
Fax:  (651) 736-3122

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