Struct oracle::Batch

pub struct Batch<'conn> { /* private fields */ }

Statement batch, which inserts, updates or deletes more than one row at once

Batching is efficient when the network distance between the client and the server is long. When a network round trip requires 1ms, inserting 10k rows using Statement consumes at least 10s excluding time spent in the client and the server. If 1000 rows are sent in a batch, it decreases to 10ms.

Usage

1. conn.batch(sql_stmt, batch_size).build() to create Batch.
2. append_row() for each row. Rows in the batch are sent to the server when the number of appended rows reaches the batch size.
   Note: The “batch errors” option mentioned later changes this behavior.
3. execute() in the end to send rows which have not been sent by append_row().

let sql_stmt = "insert into TestTempTable values(:1, :2)";
let batch_size = 100;
let mut batch = conn.batch(sql_stmt, batch_size).build()?;
for i in 0..1234 { // iterate 1234 times.
    // send rows internally every 100 iterations.
    batch.append_row(&[&i, &format!("value {}", i)])?;
}
batch.execute()?; // send the rest 34 rows.
// Check the number of inserted rows.
assert_eq!(conn.query_row_as::<i32>("select count(*) from TestTempTable", &[])?, 1234);

Error Handling

There are two modes when invalid data are in a batch.

1. Stop executions at the first failure and return the error information.
2. Execute all rows in the batch and return an array of the error information.

Default Error Handling

append_row() and execute() stop executions at the first failure and return the error information. There are no ways to know which row fails.

let sql_stmt = "insert into TestTempTable values(:1, :2)";
let batch_size = 10;
let mut batch = conn.batch(sql_stmt, batch_size).build()?;
batch.append_row(&[&1, &"first row"])?;
batch.append_row(&[&2, &"second row"])?;
batch.append_row(&[&1, &"first row again"])?; // -> ORA-00001: unique constraint violated.
batch.append_row(&[&3, &"third row ".repeat(11)])?; // -> ORA-12899: value too large for column
batch.append_row(&[&4, &"fourth row"])?;
let result = batch.execute();
match result {
    Err(Error::OciError(dberr)) => {
        assert_eq!(dberr.code(), 1);
        assert!(dberr.message().starts_with("ORA-00001: "));
    }
    _ => panic!("Unexpected batch result: {:?}", result),
}

// Check the inserted rows.
let mut stmt = conn
    .statement("select count(*) from TestTempTable where intCol = :1")
    .build()?;
assert_eq!(stmt.query_row_as::<i32>(&[&1])?, 1);
assert_eq!(stmt.query_row_as::<i32>(&[&2])?, 1);
assert_eq!(stmt.query_row_as::<i32>(&[&3])?, 0);
assert_eq!(stmt.query_row_as::<i32>(&[&4])?, 0);

Error Handling with batch errors

Note: This feature is available only when both the client and the server are Oracle 12.1 or upper.

BatchBuilder::with_batch_errors changes the behavior of Batch as follows:

• execute() executes all rows in the batch and return an array of the error information with row positions in the batch when the errors are caused by invalid data.
• append_row() doesn’t send rows internally when the number of appended rows reaches the batch size. It returns an error when the number exceeds the size instead.

let sql_stmt = "insert into TestTempTable values(:1, :2)";
let batch_size = 10;
let mut batch = conn.batch(sql_stmt, batch_size).with_batch_errors().build()?;
batch.append_row(&[&1, &"first row"])?;
batch.append_row(&[&2, &"second row"])?;
batch.append_row(&[&1, &"first row again"])?; // -> ORA-00001: unique constraint violated.
batch.append_row(&[&3, &"third row ".repeat(11)])?; // -> ORA-12899: value too large for column
batch.append_row(&[&4, &"fourth row"])?;
let result = batch.execute();
match result {
    Err(Error::BatchErrors(mut errs)) => {
        // sort by position because errs may not preserve order.
        errs.sort_by(|a, b| a.offset().cmp(&b.offset()));
        assert_eq!(errs.len(), 2);
        assert_eq!(errs[0].code(), 1);
        assert_eq!(errs[1].code(), 12899);
        assert_eq!(errs[0].offset(), 2); // position of `[&1, &"first row again"]`
        assert_eq!(errs[1].offset(), 3); // position of `[&3, &"third row ".repeat(11)]`
        assert!(errs[0].message().starts_with("ORA-00001: "));
        assert!(errs[1].message().starts_with("ORA-12899: "));
    }
    _ => panic!("Unexpected batch result: {:?}", result),
}

// Check the inserted rows.
let mut stmt = conn
    .statement("select count(*) from TestTempTable where intCol = :1")
    .build()?;
assert_eq!(stmt.query_row_as::<i32>(&[&1])?, 1);
assert_eq!(stmt.query_row_as::<i32>(&[&2])?, 1);
assert_eq!(stmt.query_row_as::<i32>(&[&3])?, 0); // value too large for column
assert_eq!(stmt.query_row_as::<i32>(&[&4])?, 1);

Affected Rows

Note: This feature is available only when both the client and the server are Oracle 12.1 or upper.

Use BatchBuilder::with_row_counts and Batch::row_counts to get affected rows for each input row.

let sql_stmt = "update TestTempTable set stringCol = :stringCol where intCol >= :intCol";
let mut batch = conn.batch(sql_stmt, 3).with_row_counts().build()?;
batch.append_row_named(&[("stringCol", &"a"), ("intCol", &9)])?; // update 1 row
batch.append_row_named(&[("stringCol", &"b"), ("intCol", &7)])?; // update 3 rows
batch.append_row_named(&[("stringCol", &"c"), ("intCol", &5)])?; // update 5 rows
batch.execute()?;
assert_eq!(batch.row_counts()?, &[1, 3, 5]);

Bind Parameter Types

Parameter types are decided by the value of Batch::append_row, Batch::append_row_named or Batch::set; or by the type specified by Batch::set_type. Once the type is determined, there are no ways to change it except the following case.

For user’s convenience, when the length of character data types is too short, the length is extended automatically. For example:

let mut batch = conn.batch(sql_stmt, batch_size).build()?;
batch.append_row(&[&"first row"])?; // allocate 64 bytes for each row
batch.append_row(&[&"second row"])?;
//....
// The following line extends the internal buffer length for each row.
batch.append_row(&[&"assume that data length is over 64 bytes"])?;

Note that extending the internal buffer needs memory copy from existing buffer to newly allocated buffer. If you know the maximum data length, it is better to set the size by Batch::set_type.

Implementations

impl<'conn> Batch<'conn>

pub fn close(&mut self) -> Result<()>

Closes the batch before the end of its lifetime.

pub fn append_row(&mut self, params: &[&dyn ToSql]) -> Result<()>

pub fn append_row_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<()>

pub fn execute(&mut self) -> Result<()>

pub fn bind_count(&self) -> usize

Returns the number of bind parameters

let sql_stmt = "insert into TestTempTable values(:intCol, :stringCol)";
let mut batch = conn.batch(sql_stmt, 100).build()?;
assert_eq!(batch.bind_count(), 2);

pub fn bind_names(&self) -> Vec<&str>

Returns an array of bind parameter names

let sql_stmt = "insert into TestTempTable values(:intCol, :stringCol)";
let batch = conn.batch(sql_stmt, 100).build()?;
assert_eq!(batch.bind_names(), &["INTCOL", "STRINGCOL"]);

pub fn set_type<I>(&mut self, bindidx: I, oratype: &OracleType) -> Result<()>where
    I: BatchBindIndex,

Set the data type of a bind parameter

let sql_stmt = "insert into TestTempTable values(:intCol, :stringCol)";
let mut batch = conn.batch(sql_stmt, 100).build()?;
batch.set_type(1, &OracleType::Int64)?;
batch.set_type(2, &OracleType::Varchar2(10))?;

pub fn set<I>(&mut self, index: I, value: &dyn ToSql) -> Result<()>where
    I: BatchBindIndex,

Set a parameter value

let sql_stmt = "insert into TestTempTable values(:intCol, :stringCol)";
let mut batch = conn.batch(sql_stmt, 100).build()?;
// The below three lines are same with `batch.append_row(&[&100, &"hundred"])?`.
batch.set(1, &100)?; // set by position 1
batch.set(2, &"hundred")?; // set at position 2
batch.append_row(&[])?;
// The below three lines are same with `batch.append_row(&[("intCol", &101), ("stringCol", &"hundred one")])?`
batch.set("intCol", &101)?; // set by name "intCol"
batch.set("stringCol", &"hundred one")?; // set by name "stringCol"
batch.append_row(&[])?;
batch.execute()?;
let sql_stmt = "select * from TestTempTable where intCol = :1";
assert_eq!(conn.query_row_as::<(i32, String)>(sql_stmt, &[&100])?, (100, "hundred".to_string()));
assert_eq!(conn.query_row_as::<(i32, String)>(sql_stmt, &[&101])?, (101, "hundred one".to_string()));

pub fn row_counts(&self) -> Result<Vec<u64>>

Returns the number of affected rows

See “Affected Rows”

pub fn statement_type(&self) -> StatementType

Returns statement type

pub fn is_plsql(&self) -> bool

Returns true when the SQL statement is a PL/SQL block.

pub fn is_dml(&self) -> bool

Returns true when the SQL statement is DML (data manipulation language).

Trait Implementations

impl<'conn> Drop for Batch<'conn>

fn drop(&mut self)

Executes the destructor for this type.