2
\$\begingroup\$

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.

\$\endgroup\$
4
  • \$\begingroup\$ If I understood yo right, your code is incomplete at the moment, which makes it off topic. \$\endgroup\$
    – convert
    Commented Jul 10, 2023 at 10:43
  • \$\begingroup\$ @convert I have found different comments on this topic. On Stack Overflow, it's considered off-topic because it involves code review, whereas here it's considered off-topic because code is incomplete. The provided code is functional and complete. With the knowledge gained from working with this code if you can give me a code review. I plan to implement something more extensive in the future. You can find the code at this link: gist.github.com/dertin/d5e6b031cb86fbe71c6d2272c764dedd You can see comments in the code that I need a review on. \$\endgroup\$
    – dertin
    Commented Jul 10, 2023 at 20:23
  • \$\begingroup\$ I have no idea abou the program language used, so can´t give any review. If you want to have you curent code reviewed you should explain what it is doing. \$\endgroup\$
    – convert
    Commented Jul 10, 2023 at 20:50
  • \$\begingroup\$ @convert it's in the title. Efficient Connection Pooling in ODBC-MSSQL with Rust (odbc-api) \$\endgroup\$
    – dertin
    Commented Jul 11, 2023 at 21:35

1 Answer 1

2
\$\begingroup\$

I am the author of the odbc-api Rust ODBC bindings.

I would suggest returning a reference to an environment with a static lifetime from environment_pooling_mssql. Similarly Connection<'a> could then become Connection<'static>.

To tackle your actual question about what needs to be sent between threads. Short answer: Nothing. The ODBC driver manager now does connection pooling behind the scenes. Your application just always "creates" new connections. Whether the connection is truly created or just reused is for the driver manager to decide. Apart from specifying it during the creation of the ODBC Environment.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.