MySQL {RMySQL} | R Documentation

## Instantiate a MySQL client from the current R session

### Description

This function creates and initializes a MySQL client. It returns an driver object that allows you to connect to one or several MySQL servers.

### Usage

    MySQL(max.con = 16, fetch.default.rec = 500, force.reload = FALSE)

### Arguments

`max.con` | maximum number of connections that are intended to have open at one time. There's no intrinic limit, since strictly speaking this limit applies to MySQL servers, but clients can have (at least in theory) more than this. Typically there are at most a handful of open connections, thus the internal `RMySQL` code uses a very simple linear search algorithm to manage its connection table.
`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 the client code be reloaded (reinitialize)? Setting this to `TRUE` allows you to change default settings. Notice that all connections should be closed before re-loading.

### Details

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.

### Value

An object `MySQLDriver` that extends `dbDriver` and `dbObjectId`. This object is required to create connections to one or several MySQL database engines.

### Side Effects

The R client part of the database communication is initialized, but note that connecting to the database engine needs to be done through calls to `dbConnect`.

### User authentication

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 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`.

### References

See stat.bell-labs.com/RS-DBI for more details on the R/S-Plus database interface.

See the documentation at the MySQL Web site http://www.mysql.com for details.

### Note

The `dbname` cannot go in the `[client]` section, but you may safely include it under the `[rs-dbi]` section or one you define yourself.

### Author(s)

David A. James

### See Also

On database managers:

`dbDriver` `dbUnloadDriver`

On connections, SQL statements and resultSets:

`dbConnect` `dbDisconnect` `dbSendQuery` `dbGetQuery` `fetch` `dbClearResult`

On transaction management:

`dbCommit` `dbRollback`

On meta-data:

`summary` `dbGetInfo` `dbGetDBIVersion` `dbListTables` `dbListConnections` `dbListResults` `dbColumnInfo` `dbGetException` `dbGetStatement` `dbHasCompleted` `dbGetRowCount` `dbGetAffectedRows`

### Examples

    ## Not run:
    # 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)
    ## End(Not run)

[Package RMySQL version 0.6-0 Index]