1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
ODBC is a package to provide connectivity to databases supporting the
ODBC interface. The package itself is platform independent and
provide access to any database for which a driver exists. It has been
tested with MySQL and PostgreSQL on both Linux and Windows (and to
those DBMSs on Linux hosts from R under Windows), Microsoft Access,
SQL Server and Excel spreadsheets (read-only), and users have reported
success with connections to Oracle and DBase. We have also used
SQLite-3.3.4 with sqliteodbc-0.67 for simple tasks.
Basic usage is covered in the R Data Import/Export manual.
Do be aware of the limitations on table and column names of various
databases and drivers. SQL supports alphanumeric plus _ and up to 128
characters (but some DBMSs only support 32), and '.' and ' ' are
disallowed. However, Access allows spaces, and Excel allows '.' in
column names in Excel which appear to get mapped to '#' by the driver.
It is your job and not the RODBC maintainers' to know about your DBMS
and ODBC driver, and it is inappropriate to ask them about such
questions.
ODBC under Windows
==================
ODBC is often installed by applications that can use this interface
(it is part of MDAC). A freely available (in the free-of-charge not
the copyleft sense) implementation can be downloaded from
http://www.microsoft.com/data/odbc/. Updated drivers are also
available there for several data sources. A free version called MSDE
2000 of SQL Server 2000 with ODBC driver is available at
http://www.microsoft.com/sql/msde/.
DSNs are usually set up using the "ODBC data sources" control panel
('Data Sources (ODBC)' in the 'Administrative Tools' section on 2000/XP).
If using RGui a DSN can be prepared via dialog boxes using
R> channel <- odbcDriverConnect("")
and this is often the simplest way for new users to ODBC or for
rarely-used data sources. There are convenience functions
odbcConnectAccess/Dbase/Excel to ease connecting to one of these systems.
ODBC under Unix/Linux
=====================
An ODBC driver manager needs to be installed, as well as an ODBC
driver for each database to be used. Testing is done using unixODBC
(http://www.unixODBC.org), but iOBDC (http://www.iODBC.org) has also
been used. The RODBC package is installed in the standard way (R CMD
INSTALL RODBC) and needs the ODBC driver manager header files and
library (-lodbc or -lodbc32 or -liodbc) to be available. Use the
configure options --with-odbc-include and --with-odbc-lib or
environment variables ODBC_INCLUDE and ODBC_LIBS to set the include
and library paths as needed.
A specific ODBC driver manager can be specified by the --with-odbc-manager
configure option, with likely values odbc or iodbc. If this is done for
odbc and the program odbc_config is found, it is used to set the libpath
as a last resort (it is often wrong), and to add any additional CFLAGS.
(Note that it does not contain information on the include path.)
DSNs are described in files: the user's file is ~/.odbc.ini. Mine is:
gannet% cat .odbc.ini
[test]
Description = myodbc
Driver = myodbc3
Trace = No
TraceFile =
Server = localhost
Port = 3306
Socket =
Database = test
[testpg]
Description = testpg
Driver = PostgreSQL
Trace = No
TraceFile =
ServerName = localhost
UserName = ripley
Port = 5432
Socket =
Database = testdb
ReadOnly = 0
[test]
Description = sqlite3
Driver = sqlite3
Database = /tmp/mysqlite3.db
This can be used via
R> channel <- odbcConnect("test")
as MySQL provides open access to the 'test' database, but for real
examples you will need to provide a uid and pwd entry as in
R> channel <- odbcConnect("test", uid="ripley", pwd="secret")
More sophisticated connections, e.g. to remote machines, can be built
using odbcDriverConnect or as DSNs.
Note that you need to ensure that .../etc/odbcinst.ini contains the
rest of the driver information. (UnixODBC uses /usr/local/etc/odbcinst.ini
and iODBC /etc/odbcinst.ini, by default. However, some RPM-based installations
will differ.) We have (with paths differing by platform)
gannet% cat /usr/local/etc/odbcinst.ini
[myodbc3]
Description = MySQL driver.
Driver = /usr/local/lib/libmyodbc3.so
Setup = /usr/local/liblibmyodbc3S.so
FileUsage = 1
[PostgreSQL]
Description = PostgreSQL driver
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
[sqlite3]
Description = sqliteodbc-0.67
Driver = /usr/local/lib/libsqlite3odbc.so
FileUsage = 1
for use with MySQL using the MyODBC 3.51 driver (both from
http://www.mysql.com), and PostgreSQL using the driver from the
psqlODBC project (http://gborg.postgresql.org/project/psqlodbc). For
PostgreSQL, CommLog = 1 adds user-level logging. The Sqlite ODBC
driver is from http://www.ch-werner.de/sqliteodbc/.
One quirk to be watched is the use of connections to the DBMS via the
Unix sockets vs ports. The PostgreSQL driver bundled with unixODBC
will use Unix sockets to 'localhost', but this driver seems unreliable
(see the ChangeLog). The current driver will only use a TCP/IP port,
and to use that needs postmaster started with the -i flag (which is
not the default) and with tcp/ip enabled in the configuration file
(which is the default). The PostgreSQL drivers are seriously
under-documented (but some older docs exists at
http://www.postgresql.org/docs/7.2/static/odbc.html)! It seems that
recently they default to readonly, so ReadOnly = 0 has to be added as
above.
Users of unixODBC and iODBC can use gODBCConfig and iodbcadm-gtk
repspectively to prepare user and systems DSNs: they are reworkings of
the Window's ODBC administation multi-tabbed dialog box. Both write in
~/.odbc.ini, in slightly different formats.
64-bit versions
===============
Microsoft altered some of the ODBC types to allow 64-bit operation on
Windows Server 2003 and later. However, they did not change the corresponding
ODBC declaration. MS increased the ODBCVER to 3.52, but it seems few of
those who adopted the change did so.
As from RODBC 1.1-6, the new types are used, and configure defines
replacements if they are not found. This works fine on MinGW and iodbc,
which will use 64-bit types if ODBC was compiled to use them. However,
on unixODBC on a system with 64-bit longs, whether 64-bit types are
declared in the headers depends on the define BUILD_REAL_64_BIT_MODE.
So if unixODBC was compiled with that defined, RODBC also needs to be.
It seems that most systems (including Fedora Core 3/4/5) do not define the
macro, but Debian does (and, I am told, has altered the headers so the
default is to assume it is defined so no further action is then needed).
|
