I want to connect to MSSQL using ODBC (odbc-api) at the beginning of my server application created with hyper.rs.
I want to have a connection pool and obtain a connection within each request, which may be executed in different threads.
My question is to understand what needs to be done at the global level and what needs to be sent to each thread in order to reuse connections and avoid opening and closing different connections that could make connection management less efficient.
Please note that the application may receive a high number of requests per second.
I have simplified it in this code, without considering the presence of hyper.rs and other elements of the final implementation, but I believe it serves to give an idea:
use odbc_sys::{AttrConnectionPooling, AttrCpMatch};
use odbc_api::{Connection, ConnectionOptions, Environment, IntoParameter};
use std::{sync::OnceLock};
fn environment_pooling_mssql<'a>() -> &'a Environment {
pub static ENV: OnceLock<Environment> = OnceLock::new();
fn init_env() -> Environment {
unsafe {
println!("Environment for MSSQL pool");
Environment::set_connection_pooling(AttrConnectionPooling::DriverAware).unwrap();
let mut env = Environment::new().unwrap();
env.set_connection_pooling_matching(AttrCpMatch::Strict)
.unwrap();
env
}
}
ENV.get_or_init(init_env)
}
fn connection_pooling_mssql<'a>() -> Connection<'a> {
let env = environment_pooling_mssql();
let connection_string = "
Driver={ODBC Driver 18 for SQL Server};\
Server=0.0.0.0;\
UID=SA;\
PWD=12345678;TrustServerCertificate=Yes;Database=mydatabase;\
";
println!("Connection MSSQL pool");
let conn = env
.connect_with_connection_string(connection_string, ConnectionOptions::default())
.unwrap();
conn
}
#[tokio::test]
async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
let mut handles = vec![];
for _ in 0..10 {
let conn: Connection<'_> = connection_pooling_mssql();
let conn = unsafe { conn.promote_to_send() };
//
// The connections are being reused, how can I verify it?
//
let handle = thread::spawn(move || {
let mut prepared = conn.prepare("SELECT * FROM mytable WHERE id=?;").unwrap();
let title = 1;
match prepared.execute(&IntoParameter::into_parameter(title)) {
Err(e) => println!("{}", e),
Ok(None) => println!("No result set generated."),
Ok(Some(_cursor)) => {}
};
assert!(!conn.is_dead().unwrap());
});
handles.push(handle);
}
for handle in handles {
handle.join().unwrap();
}
Ok(())
}
fn main() {}
https://gist.github.com/dertin/d5e6b031cb86fbe71c6d2272c764dedd
I appreciate if you can give me a code review.
Thanks, any comments are welcome.