ROracle (version 0.5-3)

dbPrepareStatement-methods: Create a prepared SQL statement for repeated execution

Description

These methods parse and cache SQL statements and binds R data for repeated execution.

Value

• An object whose class extends DBIPreparedStatement.

  In the current ROracle implementation the OraPreparedStatement class specializes (extends) OraResultSet, thus prepared statment objects inherit all result set methods, e.g., fetch, dbClearResult, dbGetStatement, dbGetRowsAffected.

warning

Typically changes to the RDMBS made through prepared statements are not committed implicitly -- the user needs to issue calls to dbCommit(conn).

In the case of ROracle, committing the changes does not close the prepared statement, but this behavior is an extension to the ANSI/ISO SQL99 standard.

keywords

methods

Details

Prepared statements are SQL statements that are parsed and cached to increase performance when the SQL code is to be executed repeatedly but with different data.

There are three distinct operations involved with prepared statements: parsing and caching the SQL statement, binding data.frame columns to the SQL, and executing the code (possibly repeatedly).

The function dbPrepareStatement takes a connection where to parse and cache the SQL code. Part of this operation is to embed references to data.frame column numbers in the SQL code and to specify their classes through the bind= argument. The ROracle package uses :n inside the SQL statement to bind the $n'th$ column, but other RDBMSs use the question mark to signal a place holder, e.g., ?n.

The object that dbPrepareStatement produces is then used together with a data.frame (which should agree with the bound specification) in calls to dbExecStatement to be executed for each row of the data.frame. This can be repeated with new data.

Embedding column names, instead of column numbers, is not supported, since some valid S names are not legal SQL names (e.g., S names with dots "." in them).

See Also

DBIPreparedStatement-class OraPreparedStatement-class OraResult-class dbSendQuery dbGetQuery dbGetInfo summary

Examples

con <- dbConnection("Oracle", "user/password")

  ps <- dbPrepareStatement(con, 
           "INSERT into QUAKES (lat, long1, mag) VALUES (:1, :2, :4)",
           bind = c("numeric", "numeric", "numeric"))

  dbExecStatement(ps, data = quakes)
  dbExecStatement(ps, data = more.quakes)
  ...
  dbExecStatement(ps, data = yet.more.quakes)

  ## how many rows have we (tentatively) inserted?
  summary(ps)

  ## everything looks fine, so let's commit and wrap up
  dbCommit(con)
  dbClearResult(ps)