getTable {RMySQL}R Documentation

Convenience functions for Importing/Exporting DBMS tables into R/S.

Description

These functions mimic their R/S counterpart get, assign, exists, and remove, except that they generate SQL code that gets executed in a relational database engine. They all work over a RDBMS connection, and an SQL table name (their semantics have been tentatively extended a bit to preserve row.names).

Usage

  getTable(con, name,  ...)
  assignTable(con, name, value, row.names = T, ...)
  existsTable(con, name, ...)
  removeTable(con, name, ...)

Arguments

con a database connection object.
name a character string specifying the SQL table name.
value an R/S data.frame (or coerceable to data.frame).
row.names in the case of getTable, this argument can be a string or an index specifying the column in the SQL table to be used as row.names in the output data.frame (a NULL, "", or 0 specifies that no column should be used as row.names in the output).
In the case of assignTable, this argument should be a logical specifying whether the row.names should be appended to the output RDBMS table; if TRUE, the extra field will be named "row_names").
overwrite a logical specifying whether to overwrite an existing table or not. Its default is FALSE.
append a logical specifying whether to append to an existing table in the RDBMS. Its default is FALSE.
... any optional arguments that the underlying database driver supports.

Value

getTable returns a data.frame; the other functions return TRUE or FALSE denoting whether the operation was successful or not.

Side Effects

An SQL statement is generated and executed on the RDBMS; the result set it produces is fetched in its entirety. These operations may failed if the underlying database driver runs out of available connections and/or result sets.

The semantics of assign are slightly extended to allow appending to an existing table. Also note that constraints on the remote table may prevent certain assignments (e.g., appending data to a table with a primary key field could fail if there are duplicate values in the corresponding data frame column).

Note

The translation of identifiers between R/S and SQL is done through calls to make.names and make.SQL.names, but we cannot guarantee that the conversion is reversible. For details see make.SQL.names.

References

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

See Also

get assign exists remove dbConnect MySQL

Examples

con <- dbConnect("MySQL", group = "vitalAnalysis")
assignTable(con, "fuel_frame", fuel.frame)
removeTable(con, "fuel_frame")
if(existsTable(con, "RESULTS")){
   assignTable(con, "RESULTS", results2000, append = T)
else
   assignTable(con, "RESULTS", results2000)
}
[Package Contents]