How to execute a "where in" query with rust and oracle?

Question

I have found an example from the oracle crate documentation:

let sql = "select ename, sal, comm from emp where deptno = :1";
let rows = conn.query(sql, &[&30])?;

But I have a list of values: client_ids: Vec<String>.
Can I bind a vec/slice value and execute a where in / any query?

Answer 1

To execute a WHERE IN query in Rust and Oracle using the oracle crate, you cannot simply bind a Vec or slice directly in the SQL as a parameter. However, you can dynamically construct the SQL based on the number of elements in the Vec and then pass the values ​​as parameters.

I think it would be something like this:

use oracle::{Connection, Result};

fn main() -> Result<()> {
// Connection example
let conn = Connection::connect("user", "password", "host:port/dbname")?;

// List of values ​​you want to use in WHERE IN
let client_ids = vec!["123".to_string(), "456".to_string(), "789".to_string()];

// Generate the placeholders dynamically
let placeholders: Vec<String> = (1..=client_ids.len())
.map(|i| format!(":{}", i))
.collect();
let placeholders_str = placeholders.join(", ");

// Dynamic SQL with WHERE IN
 let sql = format!(
 "SELECT column1, column2 FROM table_name WHERE client_id IN ({})",
 placeholders_str
 );

 // Converts `Vec<String>` to `Vec<&str>` for parameters
 let params: Vec<&str> = client_ids.iter().map(|s| s.as_str()).collect();

 // Execute the query
 let rows = conn.query(&sql, &params)?;

 // Process the results
 for row_result in rows {
 let row = row_result?;
 let column1: String = row.get("column1")?;
 let column2: String = row.get("column2")?;
 println!("column1: {}, column2: {}", column1, column2);
 }

Ok(())
}