[R-sig-DB] RBI and front-ends to RODBC and RPgSQL
David James
dj @end|ng |rom re@e@rch@be||-|@b@@com
Tue Dec 11 16:51:27 CET 2001
Hi,
A few weeks back, I suggested that we could implement the database
interface (DBI) that Tim initially wrote in Rdbi using the new style
classes and methods. Also, I thought that having a DBI front-end
to some of the existing packages would make it easier for the R-SIG-DB
to try the proposed DBI with real data. Thus, Kurt has agreed to put
the 3 packages below in the 1.4.0/Other/Devel area.
1. DBI -- The API for the database interface. This package
consists of virtual class definitions and their generic functions,
and it requires the "methods" package. The API is fully described
in the file "DBI.pdf", found in the package directory (i.e., once
DBI is installed). That document identifies a number of open
issues that we should close before we make the DBI "official",
and it would be very useful if you could comment on (at least)
those issues.
2. DBI.RODBC -- this is a DBI front-end to the current RODBC (0.8-3).
This package requires the DBI package above, and it implements the
DBI on top of existing RODBC (but see the caveats below).
3. DBI.RPgSQL -- similarly, this is a DBI front-end to the existing
RPgSQL (1.0-0), and it also requires the DBI package.
(I should have implemented front-ends for Torsten's mSQL, and MySQL,
etc., but there's only so much time...)
What the R-SIG-DB needs to do is to experiment with these packages
and make suggestions and/or modifications to ensure we're not
leaving out important functionality in the API. It's probably time
to freeze the API soon, so that we can move to implement other things
on top of it (e.g., the attach() that Duncan has already working).
Sample R Session
----------------
##
## attach the appropriate library
##
library(DBI.RPgSQL) ## this attaches methods, RPgSQL (if needed)
library(DBI.RODBC) ## this attaches methods, RODBC (if needed)
##
## instantiate one or both driver
##
pg <- RPgSQL() ## equivalent to pg <- dbDriver("RPgSQL")
od <- RODBC() ## equivalent to od <- dbDriver("RODBC")
##
## print/show/summary methods to print a one-line object identification
## and produce a brief description (meta-data) of an object
##
> print(pg)
<PgSQLDriver:(27642)>
> summary(pg)
PgSQLDriver
Id = (27642)
driver.name = RPgSQL
driver.version = 1.0-0
DBI.version = 0.1-2
client.version = NA
max.connections = 1
> show(od)
<ODBCDriver:(27642)>
> summary(od)
ODBCDriver
Id = (27642)
driver.name = RODBC
driver.version = 0.8-3
DBI.version = 0.1-2
client.version = NA
max.connections = 16
##
## connect to a database
##
> dbConnect(pg, "user", "password", "dbname")
<PgSQLConnection:(27642,0)>
> pg
<PgSQLDriver:(27642)>
## Oops! I forgot to assign the connection --
pcon <- dbListConnections(pg) ## (RPgSQL returns one connection obj)
> summary(pcon)
PgSQLConnection
dbname = rsdbi
user = rsdbi
status = 0
open = TRUE
server.version = NA
> ocon <- dbConnect(od, "dsn", "uid", "pwd")
> summary(ocon)
ODBCConnection
server.version = NA
results = NA
##
## convenience methods on connection objects:
##
## dbLisTables(), dbExistsTable(), dbReadTable(), dbWriteTable(), and
## dbRemoveTable()
> dbListTables(pcon)[10:14]
[1] "USArrests" "USJudgeRatings" "USPersonalExpenditure"
[4] "VADeaths" "airmiles"
> dbListTables(ocon)[1:5]
[1] "Formaldehyde" "HairEyeColor" "InsectSprays" "LifeCycleSavings"
[5] "OrchardSprays"
> dbExistsTable(ocon, "quakes")
[1] TRUE
> dbExistsTable(pcon, "quakes")
[1] FALSE
> dbWriteTable(pcon, "quakes", dbReadTable(ocon, "quakes"))
> dbRemoveTable(ocon, "quakes")
[1] TRUE
##
## Queries, result sets.
##
> prs <- dbSendQuery(pcon, "select * from quakes") ## leave output in dbms
> prs
<PgSQLResult:(27642,0,0)>
> summary(prs)
PgSQLResult
rows = 1000
cols = 5
field.names = long, lat, depth, mag, stations
hasCompleted = NA
> dbColumnInfo(prs) ## column info of the result set
field.name field.type R.type Nullable
1 long 701 numeric NA
2 lat 701 numeric NA
3 depth 23 integer NA
4 mag 701 numeric NA
5 stations 23 integer NA
> x <- fetch(ors, n = 0) ## bring entire output in
## combine dbSendQuery()/fetch() in one operation
y <- dbGetQuery(ocon, "select * from quakes")
##
## free up resources
##
> dbClearResult(prs)
[1] TRUE
> dbDisconnect(pcon)
[1] TRUE
> dbDisconnect(ocon)
[1] TRUE
> dbUnloadDriver(pg)
[1] TRUE
> dbUnloadDriver(od)
[1] TRUE
#################################################################
Caveats:
--------
I ran into some problems with the RODBC package where I could
not get sqlSave() to work when invoked from within a function
(it may have something to do with calls to substitute()) thus
dbWriteTable() fails with an ODBC connection. I'd appreciate
help on the correct usage of RODBC's sqlSave(). Notice that I
ran into similar problems with sqlDrop(), but I was able to get
it working by using do.call() to invoke sqlDrop().
--
David A. James
Statistics Research, Room 2C-253 Phone: (908) 582-3082
Bell Labs, Lucent Technologies Fax: (908) 582-3340
Murray Hill, NJ 09794-0636
More information about the R-sig-DB
mailing list