DBI (version 0.1-10)

dbSendQuery-methods: Execute a statement on a given database connection

Description

Methods for submiting and executing arbitrary SQL statements on a specific connection, clearing result sets, and extracting exception information from the DBMS.

Usage

dbSendQuery(conn, statement, ...)
  dbGetQuery(conn, statement, ...)
  dbClearResult(res, ...)
  dbGetException(conn, ...)

Arguments

conn

a connection object.

statement

a character vector of length 1 with the SQL statement.

res

a result set object (i.e., the value of dbSendQuery).

...

database-specific parameters may be specified.

Value

• dbSendQuery returns a result set object, i.e., an object that inherits from DBIResult; if the statement generates output (e.g., a SELECT statement) the result set can be used with fetch to extract records.

  dbGetQuery returns a data.frame with the output (if any) of the query.

  dbClearResult returns a logical indicating whether clearing the result set was successful or not. dbGetException returns a list with elements errNum (an integer error number) and errMsg (a character string) describing the last error in the connection conn.

Side Effects

The statement is submitted for synchronous execution to the server connected through the conn object. The DBMS executes the statement, possibly generating vast amounts of data. Where these data reside is driver-specific: some drivers may choose to leave the output on the server and transfer them piecemeal to R/S-Plus, others may transfer all the data to the client -- but not necessarily to the memory that R/S-Plus manages. See the individual drivers' dbSendQuery method for implementation details.

Details

The function dbSendQuery only submits and synchronously executes the SQL statement to the database engine. It does not extracts any records --- for that you need to use the function fetch (make sure you invoke dbClearResult when you finish fetching the records you need).

The function dbGetQuery does all these in one operation (submits the statement, fetches all output records, and clears the result set).

dbClearResult frees all resources (local and remote) associated with a result set. It some cases (e.g., very large result sets) this can be a critical step to avoid exhausting resources (memory, file descriptors, etc.)

References

See the Database Interface definition document DBI.pdf in the base directory of this package or http://developer.r-project.org/db.

See Also

dbDriver dbConnect fetch dbCommit dbGetInfo dbReadTable

Examples

drv <- dbDriver("MySQL")
con <- dbConnect(drv)
res <- dbSendQuery(con, "SELECT * from liv25")
data <- fetch(res, n = -1)