RMySQL (version 0.4-6)

dbConnect: Create a connection to a RDBMS

Description

Connect to a RDBMS going through the appropriate authorization procedure

Usage

dbConnect(mgr, ...)

Arguments

mgr

a dbManager object, a character string specifying the RDBMS, e.g., "MySQL", "Oracle", "Informix", or another dbConnect object.

...

authorization arguments needed by the RDBMS instance; these typically include user, password, dbname, host, port, etc. For details see the appropriate dbManager.

Value

• An object that extends dbConnect and dbObjectId in a database-specific manner. For instance dbConnect("MySQL") produces an object of class MySQLConnection.

  This object is used to direct SQL commands to the database engine.

Side Effects

A connection between R/S and the database server is established, and the R/S program becodes a client of the database engine. Typically the connections is through the TCP/IP protocol, but this will depend on vendor-specific details.

notes

Make sure you close the connection using close(con) when it is not longer needed.

References

See the Omega Project for Statistical Computing at http://www.omegahat.org for more details on the R/S database interface.

Details

Some implementations (e.g., RS-MySQL, RS-Oracle) may allow you to have multiple connecions open, so you may invoke this function repeatedly assigning its output to different objects.

See Also

On database managers:

dbManager MySQL load unload

On connections, SQL statements and resultSets:

dbExecStatement dbExec fetch quickSQL

On transaction management:

commit rollback

On meta-data:

describe getVersion getDatabases getTables getFields getCurrentDatabase getTableIndices getException getStatement hasCompleted getRowCount getAffectedRows getNullOk getInfo

Examples

# create a MySQL instance and create one connection.
> m <- dbManager("MySQL")
> m
MySQLManager id = (7269) 

# open the connection using user, passsword, etc., as
# specified in the file \file{\$HOME/.my.cnf}
> con <- dbConnect(m)    

# Let's look at the status of the manager
> describe(m)
MySQLManager id = (7269) 
  Max  connections: 16 
  Conn. processed: 1 
  Default records per fetch: 500 

# Run an SQL statement by creating first a resultSet object
> rs <- dbExecStatement(con, 
         statement = "SELECT w.laser_id, w.wavelength, p.cut_off 
                      FROM WL w, PURGE P
                      WHERE w.laser_id = p.laser_id
                      SORT BY w.laser_id")
> rs
MySQLResultSet id = (12629,1,3)

# we now fetch records from the restulSet into a data.frame
> data <- fetch(rs, n = -1)   # extract all rows
> dim(data)
[1] 1779  18