RODBC-R语言在windows下连接Oracle资源-CSDN文库

Oracle，

R，R语言

需积分: 9 72 浏览量 2015-09-14 11:08:19 上传评论收藏 1.1MB GZ 举报

共90个文件

rd：17个

rr：10个

save：10个

资源推荐

资源详情

资源评论

收起资源包目录

RODBC_1.3-12.tar.gz （90个子文件）

RODBC

GPL-2 18KB

configure.win 33B

vignettes

winDSN1.png 18KB

macAccess.png 92KB

linuxDSN.png 38KB

Oracle.png 14KB

MySQL.png 25KB

linuxDSNsqlite.png 30KB

macODBC.png 50KB

winDSN2.png 18KB

Access.png 13KB

RODBC.Rnw 72KB

SQLserver.png 34KB

NAMESPACE 836B

LICENCE.note 616B

DESCRIPTION 745B

src

config.h.win 2KB

RODBC.c 45KB

Makevars.win 35B

config.h.in 2KB

Makevars.in 47B

inst

doc

RODBC.pdf 637KB

RODBC.Rnw 72KB

tests.R 17KB

LC_MESSAGES

R-RODBC.mo 3KB

RODBC.mo 3KB

LC_MESSAGES

R-RODBC.mo 3KB

RODBC.mo 2KB

LC_MESSAGES

R-RODBC.mo 4KB

RODBC.mo 3KB

en@quot

LC_MESSAGES

R-RODBC.mo 4KB

RODBC.mo 2KB

RODBC.R 10KB

sql.R 33KB

win.R 4KB

TypeInfo.R 4KB

configure 140KB

build

vignette.rds 211B

configure.ac 3KB

man

odbc.Rd 3KB

setSqlTypeInfo.Rd 2KB

RODBC-internal.Rd 1KB

sqlColumns.Rd 6KB

sqlDrop.Rd 2KB

RODBC-package.Rd 868B

sqlTypeInfo.Rd 2KB

odbcSetAutoCommit.Rd 2KB

odbcDataSources.Rd 1KB

odbcClose.Rd 1KB

sqlQuery.Rd 7KB

odbcConnect.Rd 9KB

sqlTables.Rd 6KB

odbcGetInfo.Rd 2KB

sqlSave.Rd 7KB

sqlFetch.Rd 4KB

sqlCopy.Rd 2KB

da.po 3KB

R-de.po 4KB

RODBC.pot 2KB

pl.po 4KB

R-RODBC.pot 2KB

R-pl.po 4KB

R-da.po 4KB

de.po 3KB

tests

mysql.Rout.save 25KB

excel.Rout.save 15KB

access.RR 2KB

DB2.RR 3KB

Oracle.Rout.save 26KB

mysql-win.RR 3KB

SQLServer.RR 3KB

postgresql.RR 3KB

postgresql.Rout.save 20KB

mimer.RR 3KB

mimer.Rout.save 26KB

Master.R 1KB

Oracle.RR 3KB

sqlite3.RR 3KB

SQLServer.Rout.save 28KB

access.Rout.save 15KB

Notes 618B

mysql.RR 3KB

sqlite3.Rout.save 22KB

excel.RR 1KB

DB2.Rout.save 27KB

mysql-win.Rout.save 24KB

hills.xls 16KB

cleanup 103B

MD5 5KB

ChangeLog 18KB

ODBC Connectivity

by Brian Ripley

Department of Statistics, University of Oxford

ripley@stats.ox.ac.uk

June 29, 2015

Package RODBC implements ODBC database connectivity. It was originally written by

Michael Lapsley (St George’s Medical School, University of London) in the early days of R

(1999), but after he disappeared in 2002, it was rescued and since much extended by Brian

Ripley. Version 1.0-1 was released in January 2003, and RODBC is nowadays a mature

and much-used platform for interfacing R to database systems.

Thanks to Marc Schwartz for contributing some of the experiences here. See also the

archives of the R-sig-db mailing list.

1 ODBC Concepts

ODBC aims to provide a common API for access to SQL

-based database management

systems (DBMSs) such as MySQL

, PostgreSQL, Microsoft Access and SQL Server, DB2,

Oracle and SQLite. It originated on Windows in the early 1990s, but ODBC driver man-

agers unixODBC and iODBC are nowadays available on a wide range of platforms (and iODBC

shipped with versions of OS X prior to 10.9). The connection to the particular DBMS

needs an ODBC driver : these may come with the DBMS or the ODBC driver manager

or be provided separately by the DBMS developers, and there are third-party

developers

such as Actual Technologies, Easysoft and OpenLink. (This means that for some DBMSs

there are several diﬀerent ODBC drivers available, and they can behave diﬀerently.)

Microsoft provides drivers on Windows for non-SQL database systems such as DBase and

FoxPro, and even for ﬂat ﬁles and Excel spreadsheets. Actual Technologies sell a driver

for OS X that covers (some) Excel spreadsheets and ﬂat ﬁles.

A connection to a speciﬁc database is called a Data Source Name or DSN (see http:

//en.wikipedia.org/wiki/Database_Source_Name). See Appendix B for how to set up

DSNs on your system. One of the greatest advantages of ODBC is that it is a cross-

platform client-server design, so it is common to run R on a personal computer and access

SQL is a language for querying and managing data in databases—see http://en.wikipedia.org/

wiki/SQL.

and its fork, MariaDB

but there are close links between unixODBC and Easysoft, and iODBC and OpenLink.

data on a remote server whose OS may not even be known to the end user. This does

rely on suitable ODBC drivers being available on the client: they are for the major cross-

platform DBMSs, and some vendors provide ‘bridge’ drivers, so that for example a ‘bridge’

ODBC driver is run on a Linux client and talks to the Access ODBC driver on a remote

Windows machine.

ODBC provides an abstraction that papers over many of the diﬀerences between DBMSs.

That abstraction has developed over the years, and RODBC works with ODBC version 3.

This number describes both the API (most drivers nowadays work with API 3.51 or 3.52)

and capabilities. The latter allow ODBC drivers to implement newer features partially or

not at all, so some drivers are much more capable than others: in the main RODBC works

with basic features. ODBC is a superset of the ISO/IEC 9075-3:1995 SQL/CLI standard.

A somewhat biased overview of ODBC on Unix-alikes can be found at http://www.

easysoft.com/developer/interfaces/odbc/linux.html.

2 Basic Usage

Two groups of functions are provided in RODBC. The mainly internal odbc* commands

implement low-level access to C-level ODBC functions with similar

names. The sql*

functions operate at a higher level to read, save, copy and manipulate data between data

frames and SQL tables. The two low-level functions which are commonly used make or

break a connection.

2.1 Making a connection

ODBC works by setting up a connection or channel from the client (here RODBC) to the

DBMSs as speciﬁed in the DSN. Such connections are normally used throughout a session,

but should be closed explicitly at the end of the session—however RODBC will clear up

after you if you forget (with a warning that might not be seen in a GUI environment).

There can be many simultaneous connections.

The simplest way to make a connection is

library(RODBC)

ch <- odbcConnect("some dsn")

and when you are done with it,

close(ch)

# or if you prefer

odbcClose(ch)

The connection object ch is how you specify one of potentially many open connections,

and is the ﬁrst argument to all other RODBC functions. If you forget the details, printing

it will give some summary information.

in most cases with preﬁx SQL replacing odbc.

If the DBMS user and password are needed and not stored in the DSN, they can be supplied

by e.g.

ch <- odbcConnect("some dsn", uid = "user", pwd = "****")

Users of the R GUI under Windows

have another possibility: if an incompletely speciﬁed

DSN is given, the driver-speciﬁc Data Source dialog box will pop up to allow it to be

completed.

More ﬂexibility is available via function odbcDriverConnect, which works with a connec-

tion string. At its simplest it is

"DSN=dsn;UID=uid;PWD=pwd"

but it can be constructed without a DSN by specifying a driver directly via DRIVER=,

and more (in some cases many more) driver-speciﬁc parameters can be given. See the

documentation for the driver (and Appendix A) for more details.

2.2 Reading from a database

where ‘database’ can be interpreted very widely, including for example Excel spreadsheets

and directories of ﬂat ﬁles.

The simplest and most common use of RODBC is to extract data from databases held on

central database servers. Such access is read-only, and this can be enforced by settings in

the DSN or via permission settings (also known as privileges) on the database.

To ﬁnd out what tables are accessible from a connection ch, use

sqlTables(ch)

Some drivers will return all visible table-like objects, not just those owned by you. In that

case you may want to restrict the scope by e.g.

sqlTables(ch, tableType = "TABLE")

sqlTables(ch, schema = "some

pattern")

sqlTables(ch, tableName = "some pattern")

The details are driver-speciﬁc but in most cases some pattern can use wildcards

with

underscore matching a single character and percent matching zero or more characters.

Since underscore is a valid character in a table name it can be handled literally by preceding

it by a backslash—but it is rarely necessary to do so.

A table can be retrieved as a data frame by

res <- sqlFetch(ch, "table name")

If it has many rows it can be retrieved in sections by

res <- sqlFetch(ch, "table name", max = m)

res <- sqlFetchMore(ch, "table name", max = m)

...

This does not work from Rterm.exe.

these are the SQL wildcards used for example in LIKE clauses.

It is often necessary to reduce the data to be transferred: we have seen how to subset rows,

but it can be more eﬀective to restrict the columns or to return only rows meeting some

conditions. To ﬁnd out what columns are available, use sqlColumns, for example

> sqlColumns(ch, "USArrests")

TABLE CAT TABLE SCHEM TABLE NAME COLUMN NAME DATA TYPE TYPE NAME COLUMN SIZE

1 ripley <NA> USArrests State 12 varchar 255

2 ripley <NA> USArrests Murder 8 double 15

3 ripley <NA> USArrests Assault 4 integer 10

4 ripley <NA> USArrests UrbanPop 4 integer 10

5 ripley <NA> USArrests Rape 8 double 15

...

Then an SQL Query can be used to return part of the table, for example (MySQL on

Linux)

> sqlQuery(sh, paste("SELECT State, Murder FROM USArrests",

+ "WHERE Rape > 30 ORDER BY Murder"))

State Murder

1 Colorado 7.9

2 Arizona 8.1

3 California 9.0

4 Alaska 10.0

5 New Mexico 11.4

6 Michigan 12.1

7 Nevada 12.2

8 Florida 15.4

Note that although there are standards for SQL, all the major producers of DBMSs have

their own dialects, so for example on the Oracle and DB2 systems we tested this query

had to be given as

> sqlQuery(ch, paste('SELECT "State", "Murder" FROM "USArrests"',

+ 'WHERE "Rape" > 30 ORDER BY "Murder"'))

or even in upper case. Describing how to extract data from databases is the forte of the

SQL language, and doing so eﬃciently is the aim of many of the DBMSs, so this is a very

powerful tool. To learn SQL it is best to ﬁnd a tutorial speciﬁc to the dialect you will

use; for example Chapter 3 of the MySQL manual is a tutorial. A basic tutorial which

covers some common dialects

can be found at http://www.1keydata.com/sql/sql.html:

tutorials on how to perform common tasks in several commonly used DBMSs are available

at http://sqlzoo.net/.

2.3 Table Names

SQL-92 expects both table and column names to be alphanumeric plus underscore, and

RODBC does not in general support vendor extensions (for example Access allows spaces).

There are some system-speciﬁc quoting schemes: Access and Excel allow table names to

be enclosed in [ ] in SQL queries, MySQL (by default) quotes via backticks, and most

other systems use the ANSI SQL standard of double quotes.

MySQL, Oracle and Microsoft SQL Server.

The odbcConnnect function allows the speciﬁcation of the quoting rules for names RODBC

itself sends, but sensible defaults

are selected. Users do need to be aware of the quoting

issue when writing queries for sqlQuery themselves.

Note the underscore is a wildcard character in table names for some of the functions, and

so may need to be escaped (by backslash) at times.

Normally table names containing a period are interpreted as references to another

schema (see below): this can be suppressed by opening the connection with argument

interpretDot = FALSE.

2.4 Types of table

The details are somewhat DBMS-speciﬁc, but ‘tables’ usually means ‘tables, views or

similar objects’.

In some systems ‘tables’ are physical objects (ﬁles) that actually store data—Mimer calls

these base tables. For these other ‘tables’ can be derived that present information to the

user, usually called ‘views’. The principal distinctions between a (base) table and a view

are

• Using DROP on a table removes the data, whereas using it on a view merely removes

the convenient access to a representation of the data.

• The access permission (privilege) of a view can be very diﬀerent from those of a

table: this is commonly used to hide sensitive information.

A view can contain a subset of the information available in a single table or combine

information from two or more tables.

Further, some DBMSs distinguish between tables and views generated by ordinary users

and system tables used by the DBMS itself. Where present, this distinction is reﬂected in

the result of sqlTable() calls.

Some DBMSs support synonyms and/or aliases which are simply alternative names for an

existing table/view/synonym, often those in other schemas (see below).

Typically tables, views, synonyms and aliases share a name space and so must have a name

that is unique (in the enclosing schema where schemas are implemented).

3 Writing to a Database

To create or update a table in a database some more details need to be considered. For

some systems, all table and column names need to be lower case (e.g. PostgreSQL, MySQL

on Windows) or upper case (e.g. some versions of Oracle). To make this a little easier, the

backticks for MySQL, [ ] for the Access and Excel convenience wrappers, otherwise ANSI double

quotes.

评论收藏

内容反馈

woaisongren

粉丝: 1
资源: 3

RODBC-R语言在windows下连接Oracle

windows下R语言环境安装及访问oracle例子

Windows-Server-xx-下ASP程序连接ORACLE数据库驱动错误WEB服务器-电脑资料.doc

oracle官网 jdk-8u241-windows-x64

instantclient-basic-windows.x64-11.2.0.4.0 oracle数据连接plsql工具

利用python-oracledb库连接Oracledb数据库，使用示例

oracle远程连接服务器出现 ORA-12170 TNS:连接超时 解决办法

JRockit （jrockit-jdk1.6.0_45-R28.2.7-4.1.0-windows-ia32.exe）

oracle官方精简64位客户端-instantclient-basic-windows.x64-11.2.0.2.0

Java-中有两种方式连接oracle数据库-个人成功例子

PLSQL+Developer+V9.0.0.1601-CRACKED--pojie-连接Oracle数据库工具

OracleClient-18C Oracle客户端，包括windows和Linux

Oracle数据库连接工具-Oracle SQL Handler(for windows)

ojdbc6--oracle数据库连接驱动.rar

oracle数据库客户端 instantclient-basic-windows.x64-19.19.0.0.0dbru

JRockit （jrockit-jdk1.6.0_45-R28.2.7-4.1.0-windows-x64.exe）

oracle-instantclient19.10-sqlplus-base-devel(x86 arm架构)

instantclient-basic-windows.x64-12.1.0.2.0 解决Navicat 15 64位 连Oracle 11.2.0.4

Deploying JRE (Native Plug-in) for Windows Clients in Oracle EBS

OracleClient-19C Oracle客户端，包括windows和Linux

OracleClient-21C Oracle客户端，包括windows和Linux

STK11.2安装包网盘链接及提取码.txt

中国1：100万土壤数据集

Access 2010数据库引擎

各类三维点云下载数据集

eplan 正泰部件库 包括：万断路器、塑壳、微断、接触器和热继电器

HWSD数据库土壤中文名称

2022年最新整理《康熙字典》最全的数据库

安装64位的ODBC 驱动器，解决ODBC Microsoft Access Driver 网络访问已中断问题

AccessDatabaseEngine 2007

最新资源

oracle远程连接服务器出现 ORA-12170 TNS:连接超时解决办法

instantclient-basic-windows.x64-12.1.0.2.0 解决Navicat 15 64位连Oracle 11.2.0.4

eplan 正泰部件库包括：万断路器、塑壳、微断、接触器和热继电器