File: README.orig

package info (click to toggle)
rodbc 1.1.3-1
  • links: PTS
  • area: main
  • in suites: sarge
  • size: 380 kB
  • ctags: 101
  • sloc: ansic: 1,126; makefile: 64; sh: 29
file content (162 lines) | stat: -rw-r--r-- 6,101 bytes parent folder | download | duplicates (3)
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
RODBC is a package to provide connectivity to databases
supporting the ODBC interface.  The package should be platform
independant and provide access to any database for which a driver
exists.  The columns are limited to 255 characters and binary data
is not supported. 

See below for installation instructions.
The willl only compile under R > 1. 

author: Michael Lapsley <lapsley@ukshells.co.uk>
        Michael Lapsley <mlapsley@sthelier.sghms.ac.uk>
*************************************************************************

THERE IS NO WARRANTY OF ANY KIND, NO IMPLIED MERCHANTIBILITY OR
IMPLIED FITNESS OF PURPOSE.

I have no programming training and the software may contain serious
bugs: use is entirely at the users' risk.

In particular I **STRONGLY** advise that dsn access to vital database
tables are set without write permissions.  Functions in the library
zero or delete tables and if wrongly called either due to user error
or a bug there may be permanent data loss.  Do not get in touch with
me if your company's financial records are deleted or the data from
a huge research grant are corrupted!


This is ALPHA software.  Please let me know about bugs, poor 
design, or ideas for enhancement.
*************************************************************************

'RODBC' is free software; you can redistribute  it and/or modify it under 
the terms of the GNU General Public License as published by the Free 
Software Foundation; either version 2, or (at your option) any later 
version.


A copy of the GNU General Public License is available on the World Wide
Web at http://www.gnu.org/copyleft/gpl.html.  You can also obtain it by
writing to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge,
MA 02139, USA.

*************************************************************************

ACKNOWLEDGEMENTS:  The library was inspired by RmSQL by 
Torsten Hothorn <hothorn@amadeus.statistik.uni-dortmund.de>,
with some code and documentation showing its lineage from RmSQL.
The bugs are mine, however.
Prof Brian Ripley provided help and advice especially on making the 
code MS Windows compliant and on efficiency-tuning some functions.  

*************************************************************************

INSTALLATION:

R INSTALL RODBC

Under unix the package was developed using unixODBC available
from genix.net/unixODBC and mysql from www.mysql.com
iODBC also works.  The package has been used to query MySQL, oracle
postgreSQL and MSaccess with success.  Other database back ends
should work as long as a driver for your driver manager is available
for your platform.  Using ODBC-ODBC-bridge from Easysoft (see link
at www.unixODBC.org) I have used R (linux) to interrogate MSaccess
over a LAN.

Note that under version of unixODBC (1.7) the txt driver does not work.
In myodbc 2.50.26 there is a makefile bug which does not build the shared library.
This has to be done by hand after the main build,

ODBC Concepts.

The user defines a data source name (DSN) which identifies the database,
server and driver that the application can connect with.

Under unix this is via the file .odbc.ini:  mine as follows.
 
[test]
Description	= myodbc
Driver		= myodbc
Trace		= No
TraceFile		= 
Server		= localhost
Port		= 3306
Socket		= 
Database	= test

[results]
Description	= myodbc
Driver		= myodbc
Trace		= No
TraceFile		= 
Server		= localhost
Port		= 3306
Socket		= 
Database	= results

(the Driver = myodbc line is resolved in a system wide config file)

Under MS Windows: (thanks to David Middleton)

Under Microsoft OSs ODBC is often installed by applications that can
use this interface.  A freely available (in the MS sense not the copyleft
sense) implementation can be downloaded from
http://www.microsoft.com/data/odbc/.  Updated drivers are also available
here for several data sources.

DSNs are usually set up using the "ODBC data sources" control panel,
previously known as the "ODBC Administrator".  There are other options:
the Perl module Win32::ODBC has routines for setting up DSNs."


One can now issue the command:

channel<-odbcConnect("test","user","password",case="<DATABASE SPECIFIC>")
or
channel<-odbcConnect("results","user","password",case="<DB SPECIFIC>")

Note that the password is defined in the database backend,
not (necessarily/usually) the dsn.

Various ideas:

dataframe<-sqlQuery(channel,"select * from table where ...")
sqlSave(channel,dataframe)

The above covers 90% of usage.

sqlCopy(readChannel,"select * from HugeSlowDatabase where ..complicatedCondition..",
 workingTable,writeChannel)
sqlQuery(writeChannel,workingSelects)....

sqlSave will write into an existing table, or create one with columns of
type varchar(255) if necessary.  sqlCopyTable will copy the table structure
within the limitations of odbc, which is a lowest common denominator.
If you need to use your database's unique features you can always do it
manually with sqlQuery.

There are a number of database specific issues that frustrate the attempt
to have a truly generic interface.  The handling of case varies: oracle
translates everting to upper case, Postgres to lower case, and MySql
leaves as is.  This cannot be autodetected and must be specified in
odbcConnect with the case parameter, which accepts the names of common 
databases or 'tolower|toupper'.   
Where column names are translated information is lost.
Optionally row and column names can be stored in the tables
with sqlSave.  This strategy fails if the engine does not return
rows in the order in which they are saved.  sqlFetch will attempt to
restore them if asked; your mileage will vary.  In general this is not
a good idea, but I can think of no other.  Suggestions?

Some databases also have pseudocolumns eg oracle and postgres.
Attempts to save a dataframe contining these will fail:  conversly
attempts to sqlUpdate a dataframe _without_ them will fail.

Finally, I have paid little attention to record locking issues.
Anyone in a multiuser environment should keep an eye out for
concurrency problems and let me know if they arise.
**********************************