ROracle (version 0.5-12)

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.

Usage

dbPrepareStatement(conn, statement, bind, ...)
  dbExecStatement(ps, data, ...)

Arguments

conn

a database connection

statement

a string with an SQL statement, possibly with embedded column number specifications of the form :columnNum (e.g., :1,:2,:6) for binding those columns in the data argument to dbExecStatement.

bind

a character vector parallel to the column specifications describing their R classes (e.g., "character", "numeric"). You may supply a data.frame, in which case bind= is set to sapply(data, class).

ps

a prepared statement object as produced by dbPrepareStatement.

data

a data.frame whose columns are to be bound to the SQL statement.

...

other arguments are passed to the driver implementation. For instance, the argument ora.buf.size is used to specify the size of Oracle's internal binding buffers (ROracle sets these to 500 elements by default).

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.

synopsis

dbPrepareStatement(conn, statement, bind, ...) dbExecStatement(ps, data, ...)

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.

Details

Prepared statements are SQL statements that are parsed and cached by the server 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., ?.

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 R names are not legal SQL names (e.g., R names with dots "." in them).

See Also

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

Examples

con <- dbConnect("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)