Last active
February 19, 2025 17:33
-
-
Save dertin/d5e6b031cb86fbe71c6d2272c764dedd to your computer and use it in GitHub Desktop.
Efficient Connection Pooling in ODBC-MSSQL (odbc-api) - unixODBC 2.3.12pre
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [package] | |
| name = "rust-pooldb" | |
| version = "0.1.0" | |
| edition = "2021" | |
| [dependencies] | |
| tokio = { version = "1.29.1", features = ["full"] } | |
| odbc-sys = "0.21.4" | |
| odbc-api = "0.57.0" |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ODBC] | |
| Trace=Yes | |
| Trace File=/tmp/sql.log | |
| Pooling=Yes | |
| PoolMaxSize=15 | |
| PoolWaitTimeout=30 | |
| [ODBC_Driver_18_for_SQL_Server_Pool] | |
| Description=Microsoft ODBC Driver 18 for SQL Server - POOL | |
| Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.2.1 | |
| UsageCount=1 | |
| CPTimeout=180 | |
| [ODBC_Driver_18_for_SQL_Server] | |
| Description=Microsoft ODBC Driver 18 for SQL Server | |
| Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.2.1 | |
| UsageCount=1 | |
| CPTimeout=0 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| use odbc_sys::{AttrConnectionPooling, AttrCpMatch}; | |
| use odbc_api::{Connection, ConnectionOptions, Environment, IntoParameter}; | |
| use std::{sync::OnceLock}; | |
| // Q: To work with threads do I need to share a single Environment ? | |
| 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) | |
| } | |
| // Q: If I connect multiple times from different threads, are the connections reused? According to ODBC and Driver ? | |
| fn connection_pooling_mssql<'a>() -> Connection<'a> { | |
| let env = environment_pooling_mssql(); | |
| let connection_string = " | |
| Driver={ODBC_Driver_18_for_SQL_Server_Pool};\ | |
| 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(); | |
| // Q: Is this safe to send to a thread? Do I need to add Mutex? | |
| let conn = unsafe { conn.promote_to_send() }; | |
| // | |
| // Q: The connections are being reused, how can I verify it? | |
| // | |
| let handle = std::thread::spawn(move || { | |
| // If I create a high number of threads => 10000 and connect in each thread, an error is thrown. | |
| // all connections will fail due to timeout login. | |
| // see full log => https://gist.github.com/dertin/da9e195e4cf65a021b1e431fc21e5b97 | |
| // Q: Where would it be correct to connect, inside or outside the thread? | |
| // let conn: Connection<'_> = connection_pooling_mssql(); | |
| 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() {} |
Hello @abernardo88, yes I'll add it soon. However it's not finished yet, leave some questions in the comments of the code that should be resolved before it can be used.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello I'm trying to implement the same logic, can you please share the cargo file? Thanks!