Struct oracle::Statement

pub struct Statement { /* private fields */ }

Statement

Implementations

impl Statement

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

Closes the statement before the end of lifetime.

pub fn query(&mut self, params: &[&dyn ToSql]) -> Result<ResultSet<'_, Row>>

Executes the prepared statement and returns a result set containing Rows.

See Query Methods.

pub fn query_named( &mut self, params: &[(&str, &dyn ToSql)] ) -> Result<ResultSet<'_, Row>>

Executes the prepared statement using named parameters and returns a result set containing Rows.

See Query Methods.

pub fn query_as<T>(&mut self, params: &[&dyn ToSql]) -> Result<ResultSet<'_, T>>

where T: RowValue,

Executes the prepared statement and returns a result set containing RowValues.

See Query Methods.

pub fn into_result_set<T>( self, params: &[&dyn ToSql] ) -> Result<ResultSet<'static, T>>

where T: RowValue,

Executes the prepared statement and returns a result set containing RowValues.

This is the same as Statement::query_as(), but takes ownership of the Statement.

See Query Methods.

pub fn query_as_named<T>( &mut self, params: &[(&str, &dyn ToSql)] ) -> Result<ResultSet<'_, T>>

where T: RowValue,

Executes the prepared statement using named parameters and returns a result set containing RowValues.

See Query Methods.

pub fn into_result_set_named<T>( self, params: &[(&str, &dyn ToSql)] ) -> Result<ResultSet<'static, T>>

where T: RowValue,

Executes the prepared statement using named parameters and returns a result set containing RowValues.

This is the same as Statement::query_as_named(), but takes ownership of the Statement.

See Query Methods.

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

Gets one row from the prepared statement using positoinal bind parameters.

See Query Methods.

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

Gets one row from the prepared statement using named bind parameters.

See Query Methods.

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

where T: RowValue,

Gets one row from the prepared statement as specified type using positoinal bind parameters.

See Query Methods.

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

where T: RowValue,

Gets one row from the prepared statement as specified type using named bind parameters.

See Query Methods.

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

Binds values by position and executes the statement. It will retunrs Err when the statemnet is a select statement.

See also Connection::execute.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

// execute a statement without bind parameters
let mut stmt = conn
    .statement("insert into emp(empno, ename) values (113, 'John')")
    .build()?;
stmt.execute(&[])?;

// execute a statement with binding parameters by position
let mut stmt = conn
    .statement("insert into emp(empno, ename) values (:1, :2)")
    .build()?;
stmt.execute(&[&114, &"Smith"])?;
stmt.execute(&[&115, &"Paul"])?;  // execute with other values.

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

Binds values by name and executes the statement. It will retunrs Err when the statemnet is a select statement.

See also Connection::execute_named.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

// execute a statement with binding parameters by name
let mut stmt = conn
    .statement("insert into emp(empno, ename) values (:id, :name)")
    .build()?;
stmt.execute_named(&[("id", &114),
                     ("name", &"Smith")])?;
stmt.execute_named(&[("id", &115),
                     ("name", &"Paul")])?; // execute with other values.

pub fn bind_count(&self) -> usize

Returns the number of bind variables in the statement.

In SQL statements this is the total number of bind variables whereas in PL/SQL statements this is the count of the unique bind variables.

let conn = Connection::connect("scott", "tiger", "")?;

// SQL statements
let stmt = conn.statement("select :val1, :val2, :val1 from dual").build()?;
assert_eq!(stmt.bind_count(), 3); // val1, val2 and val1

// PL/SQL statements
let stmt = conn.statement("begin :val1 := :val1 || :val2; end;").build()?;
assert_eq!(stmt.bind_count(), 2); // val1(twice) and val2

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

Returns the names of the unique bind variables in the statement.

The bind variable names in statements are converted to upper-case.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

let stmt = conn.statement("BEGIN :val1 := :val2 || :val1 || :aàáâãäå; END;").build()?;
assert_eq!(stmt.bind_count(), 3);
let bind_names = stmt.bind_names();
assert_eq!(bind_names.len(), 3);
assert_eq!(bind_names[0], "VAL1");
assert_eq!(bind_names[1], "VAL2");
assert_eq!(bind_names[2], "AÀÁÂÃÄÅ");

pub fn bind<I>(&mut self, bindidx: I, value: &dyn ToSql) -> Result<()>

where I: BindIndex,

Set a bind value in the statement.

The position starts from one when the bind index type is usize. The variable name is compared case-insensitively when the bind index type is &str.

Examples

let conn = Connection::connect("scott", "tiger", "")?;
let mut stmt = conn.statement("begin :outval := upper(:inval); end;").build()?;

// Sets NULL whose data type is VARCHAR2(60) to the first bind value.
stmt.bind(1, &OracleType::Varchar2(60))?;

// Sets "to be upper-case" to the second by its name.
stmt.bind("inval", &"to be upper-case")?;

stmt.execute(&[])?;
let outval: String = stmt.bind_value(1)?;
assert_eq!(outval, "TO BE UPPER-CASE");

pub fn bind_value<I, T>(&self, bindidx: I) -> Result<T>

where I: BindIndex, T: FromSql,

Gets a bind value in the statement.

The position starts from one when the bind index type is usize. The variable name is compared case-insensitively when the bind index type is &str.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

// Prepares "begin :outval := upper(:inval); end;",
// sets NULL whose data type is VARCHAR2(60) to the first bind variable,
// sets "to be upper-case" to the second and then executes it.
let mut stmt = conn.statement("begin :outval := upper(:inval); end;").build()?;
stmt.execute(&[&OracleType::Varchar2(60),
             &"to be upper-case"])?;

// Get the first bind value by position.
let outval: String = stmt.bind_value(1)?;
assert_eq!(outval, "TO BE UPPER-CASE");

// Get the first bind value by name.
let outval: String = stmt.bind_value("outval")?;
assert_eq!(outval, "TO BE UPPER-CASE");

pub fn returned_values<I, T>(&self, bindidx: I) -> Result<Vec<T>>

where I: BindIndex, T: FromSql,

Gets values returned by RETURNING INTO clause.

When the bindidx ponints to a bind variable out of RETURNING INTO clause, the behavior is undefined.

Examples

let conn = Connection::connect("scott", "tiger", "")?;

// create a table using identity column (Oracle 12c feature).
conn.execute("create table people (id number generated as identity, name varchar2(30))", &[])?;

// insert one person and return the generated id into :id.
let stmt = conn.execute("insert into people(name) values ('Asimov') returning id into :id", &[&None::<i32>])?;
let inserted_id: i32 = stmt.returned_values("id")?[0];
println!("Asimov's ID is {}", inserted_id);

// insert another person and return the generated id into :id.
let stmt = conn.execute("insert into people(name) values ('Clark') returning id into :id", &[&None::<i32>])?;
let inserted_id: i32 = stmt.returned_values("id")?[0];
println!("Clark's ID is {}", inserted_id);

// delete all people and return deleted names into :name.
let stmt = conn.execute("delete from people returning name into :name", &[&OracleType::Varchar2(30)])?;
let deleted_names: Vec<String> = stmt.returned_values("name")?;
for name in deleted_names {
    println!("{} is deleted.", name);
}

// cleanup
conn.execute("drop table people purge", &[])?;

pub fn row_count(&self) -> Result<u64>

Returns the number of rows fetched when the SQL statement is a query. Otherwise, the number of rows affected.

Examples

// number of affected rows
let stmt = conn.execute("update TestStrings set StringCol = StringCol where IntCol >= :1", &[&6])?;
assert_eq!(stmt.row_count()?, 5);

// number of fetched rows
let mut stmt = conn
    .statement("select * from TestStrings where IntCol >= :1")
    .build()?;
assert_eq!(stmt.row_count()?, 0); // before fetch
let mut nrows = 0;
for _ in stmt.query(&[&6])? {
  nrows += 1;
}
assert_eq!(stmt.row_count()?, nrows); // after fetch

// fetch again using same stmt with a different bind value.
let mut nrows = 0;
for _ in stmt.query(&[&4])? {
  nrows += 1;
}
assert_eq!(stmt.row_count()?, nrows); // after fetch

pub fn implicit_result(&self) -> Result<Option<RefCursor>>

Returns the next implicit result returned by dbms_sql.return_result() in a PL/SQL block or a stored procedure.

This feature is available when both the client and server are 12.1 or higher.

Examples

let sql = r#"
declare
  cursor1 SYS_REFCURSOR;
  cursor2 SYS_REFCURSOR;
begin
  open cursor1 for select StringCol from TestStrings where IntCol = :1;
  -- return the first result set
  dbms_sql.return_result(cursor1);

  open cursor2 for select StringCol from TestStrings where IntCol = :2;
  -- return the second result set
  dbms_sql.return_result(cursor2);
end;
"#;

let mut stmt = conn.statement(sql).build()?;
stmt.execute(&[&1, &2])?;

// Get the first result set.
let mut opt_cursor = stmt.implicit_result()?;
assert!(opt_cursor.is_some());
let mut cursor = opt_cursor.unwrap();
assert_eq!(cursor.query_row_as::<String>()?, "String 1");

// Get the second result set.
let mut opt_cursor = stmt.implicit_result()?;
assert!(opt_cursor.is_some());
let mut cursor = opt_cursor.unwrap();
assert_eq!(cursor.query_row_as::<String>()?, "String 2");

// No more result sets
let mut opt_cursor = stmt.implicit_result()?;
assert!(opt_cursor.is_none());

pub fn statement_type(&self) -> StatementType

Returns statement type

pub fn is_query(&self) -> bool

Returns true when the SQL statement is a query.

pub fn is_plsql(&self) -> bool

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

pub fn is_ddl(&self) -> bool

Returns true when the SQL statement is DDL (data definition language).

pub fn is_dml(&self) -> bool

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

pub fn is_returning(&self) -> bool

Returns true when the SQL statement has a RETURNING INTO clause.

pub fn last_row_id(&self) -> Result<Option<String>>

Returns the rowid of the last row that was affected by the statement.

let mut stmt = conn
    .statement("insert into TestDates values(100, sysdate, null)")
    .build()?;
stmt.execute(&[])?;
// get the rowid inserted by stmt
let rowid1 = stmt.last_row_id()?;
// get the rowid from database
let rowid2 = conn.query_row_as::<String>("select rowid from TestDates where IntCol = 100", &[])?;
assert_eq!(rowid1, Some(rowid2));

pub fn oci_attr<T>( &self ) -> Result<<<T::DataType as DataType>::Type as ToOwned>::Owned>

where T: OciAttr<HandleType = Stmt>, T::Mode: ReadMode,

Gets an OCI handle attribute corresponding to the specified type parameter See the oci_attr module for details.

pub fn set_oci_attr<T>( &mut self, value: &<T::DataType as DataType>::Type ) -> Result<()>

where T: OciAttr<HandleType = Stmt>, T::Mode: WriteMode,

Sets an OCI handle attribute corresponding to the specified type parameter See the oci_attr module for details.

Trait Implementations

impl Debug for Statement

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.