MySQL {RMySQL} | R Documentation |
This function creates and initializes a MySQL client. It returns an driver object that allows you to connect to one or several MySQL servers.
MySQL(max.con = 10, fetch.default.rec = 500, force.reload = F)
max.con |
maximum number of connections that we intended to have open. This can be up to 100, a limit defined at compilation time. Strictly speaking this limit applies to MySQL servers, but clients can have (at least in theory) more than this. |
fetch.default.rec |
number of records to fetch at one time from the database.
(The fetch method uses this number as a default.)
|
force.reload |
should we reload (reinitialize) the client code?
Setting this to TRUE allows you to change
default settings. Notice that all connections should be closed
before re-loading.
|
This object is a singleton, that is, on subsequent invocations it returns the same initialized object.
This implementation allows you to connect to multiple host servers and run multiple connections on each server simultaneously.
An object MySQLDriver
that extends
dbDriver
and
dbObjectId
.
This object is required to create connections
to one or several MySQL database engines.
The R/S-Plus client part of the database communication is initialized,
but note that connecting to the database engine needs to be done through
calls to dbConnect
.
The preferred method to pass authentication parameters to the
server (e.g., user, password, host) is through the MySQL personal
configuration file `$HOME/.my.cnf' (or `c:/my.cnf' under Windows).
Since specifying passwords on calls to
dbConnect
is a very bad idea (and so is specifying passwords through
shell variables),
the client code parses the configuration file `$HOME/.my.cnf';
this file consists of zero or more sections, each starting with a
line of the form [section-name]
, for instance
$ cat $HOME/.my.cnf
# this is a comment
[client]
user = dj
host = localhost
[rs-dbi]
database = s-data
[lasers]
user = opto
database = opto
password = pure-light
host = merced
...
[iptraffic]
host = data
database = iptraffic
This file should be readeable only by you.
Inside each section, MySQL parameters may be specified one per line
(e.g., user = opto
).
The R/S MySQL implementation always parses the [client]
and [rs-dbi]
sections, but you may define you own
project-specific sections to tailor its environment;
if the same parameter appears more than once, the last
(closer to the bottom) occurrence is used.
If you define a section, say, [iptraffic]
,
then instead of including all these parameters in the
call to dbConnect
, you simply supply the
name of the group
,
e.g., dbConnect(mgr, group = "iptraffic")
.
The most important parameters are user
,
password
, host
,
and dbname
.
See the Omega Project for Statistical Computing at http://www.omegahat.org for more details on the R/S database interface.
See the documentation at the MySQL Web site http://www.mysql.com for details.
David A. James
On database managers:
On connections, SQL statements and resultSets:
dbConnect
dbDisconnect
dbSendQuery
dbGetQuery
fetch
dbClearResult
On transaction management:
On meta-data:
summary
dbGetInfo
dbGetDBIVersion
dbListTables
dbListConnections
dbListResults
dbColumnInfo
dbGetException
dbGetStatement
dbHasCompleted
dbGetRowCount
dbGetAffectedRows
# create a MySQL instance and create one connection. > m <- dbDriver("MySQL") <MySQLDriver:(4378)> # open the connection using user, passsword, etc., as # specified in the "[iptraffic]" section of the # configuration file \file{\$HOME/.my.cnf} > con <- dbConnect(m, group = "iptraffic") > rs <- dbSendQuery(con, "select * from HTTP_ACCESS where IP_ADDRESS = '127.0.0.1'") > df <- fetch(rs, n = 50) > dbHasCompleted(rs) [1] FALSE > df2 <- fetch(rs, n = -1) > dbHasCompleted(rs) [1] TRUE > dbClearResult(rs) > dim(dbGetQuery(con, "show tables")) [1] 74 1 > dbListTables(con)