dertin · February 19, 2025 17:33 · Jul 23, 2023 · Jul 16, 2023 · Jul 16, 2023 · Jul 15, 2023
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -80,12 +80,10 @@ fn odbc_mssql() -> Result<(), Box<dyn std::error::Error>> {
             }
 
             // Connection string for MSSQL
-            let connection_string = "
-            Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
-            Server=0.0.0.0;\
-            UID=SA;\
-            PWD=Pepe1234;TrustServerCertificate=Yes;Database=mydatabase;
-            ";
+            let connection_string = "Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
+                Server=0.0.0.0;\
+                UID=SA;\
+                PWD=Pepe1234;TrustServerCertificate=Yes;Database=mydatabase;";
 
             let conn: Connection<'_> = connection_pooling(connection_string);
             // let conn = unsafe { conn.promote_to_send() };

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -6,9 +6,14 @@ const NUM_THREADS: u32 = 10;      // number of threads to spawn
 const CONDVAR_FLAG: bool = false; // wait for all threads to start then connect to ODBC all at the same time.
 const CONN_IDLE_PAUSE: u64 = 30;  // seconds to stay alive connection within each thread
 
+// Behaviors:
+
+// It's not a sure thing, just a rough reference. 
+// I have to keep investigating
+
 // if CONDVAR_FLAG == true :
 // PoolMaxSize >= NUM_THREADS ==> OK
-// PoolMaxSize < NUM_THREADS ==> [[unixODBC][Driver Manager]Connection pool at capacity and the wait has timed out]
+// PoolMaxSize < NUM_THREADS ==> OK (if the difference is not much) || [[unixODBC][Driver Manager]Connection pool at capacity and the wait has timed out]
 
 // if CONDVAR_FLAG == false and (CONN_IDLE_PAUSE + CPTimeout) > PoolWaitTimeout :
 // PoolMaxSize >= NUM_THREADS ==> OK

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -1,86 +1,116 @@
-use odbc_sys::{AttrConnectionPooling, AttrCpMatch};
-use odbc_api::{Connection, ConnectionOptions, Environment, IntoParameter};
-use std::{sync::OnceLock};
+use odbc_api::{Connection, ConnectionOptions, Environment, IntoParameter, sys::{AttrConnectionPooling, AttrCpMatch}};
+use std::{sync::OnceLock, time::Duration};
+use std::sync::{Arc, Condvar, Mutex};
 
-// 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();
+const NUM_THREADS: u32 = 10;      // number of threads to spawn
+const CONDVAR_FLAG: bool = false; // wait for all threads to start then connect to ODBC all at the same time.
+const CONN_IDLE_PAUSE: u64 = 30;  // seconds to stay alive connection within each thread
+
+// if CONDVAR_FLAG == true :
+// PoolMaxSize >= NUM_THREADS ==> OK
+// PoolMaxSize < NUM_THREADS ==> [[unixODBC][Driver Manager]Connection pool at capacity and the wait has timed out]
+
+// if CONDVAR_FLAG == false and (CONN_IDLE_PAUSE + CPTimeout) > PoolWaitTimeout :
+// PoolMaxSize >= NUM_THREADS ==> OK
+// PoolMaxSize < NUM_THREADS ==> [[unixODBC][Driver Manager]Connection pool at capacity and the wait has timed out]
+
+fn connection_pooling<'a>(connection_string: &str) -> Connection<'a> {
+
+    // Environment initialized only once and safe to share between threads
+    let env = {
+        pub static ENV: OnceLock<Environment> = OnceLock::new();
+
+        ENV.get_or_init(|| unsafe {
+
+            println!("Environment for connection pooling");
 
-    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;\
-    ";
+            env
+        })
+    };
 
     println!("Connection MSSQL pool");
-
-    let conn = env
-        .connect_with_connection_string(connection_string, ConnectionOptions::default())
+
+    let conn: Connection<'a> = env
+        .connect_with_connection_string(
+            connection_string,
+            ConnectionOptions {
+                login_timeout_sec: Some(15),
+            },
+        )
         .unwrap();
+
     conn
 }
 
-#[tokio::test]
-async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
-    let mut handles = vec![];
+fn odbc_mssql() -> Result<(), Box<dyn std::error::Error>> {
 
-    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() };
+
+    let counter = Arc::new((Mutex::new(0), Condvar::new()));
+    let mut handles = Vec::new();
 
-        //
-        // Q: The connections are being reused, how can I verify it?
-        //
+     for _ in 0..NUM_THREADS {
+        let counter_clone = Arc::clone(&counter);
         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();
+            if CONDVAR_FLAG { 
+                // Wait for all threads to start then connect to ODBC all at the same time.
+                println!("Thread {:?} waits for all threads", std::thread::current().id());
+
+                let (lock, cvar) = &*counter_clone;
+                let mut count = lock.lock().unwrap();
+                *count += 1;
+
+                if *count < NUM_THREADS {
+                    count = cvar.wait(count).unwrap();
+                } else {
+                    cvar.notify_all(); // Notify all threads to start
+                }
+            }
+
+            // Connection string for MSSQL
+            let connection_string = "
+            Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
+            Server=0.0.0.0;\
+            UID=SA;\
+            PWD=Pepe1234;TrustServerCertificate=Yes;Database=mydatabase;
+            ";
+
+            let conn: Connection<'_> = connection_pooling(connection_string);
+            // let conn = unsafe { conn.promote_to_send() };
 
-            let title = 1;
+            let mut prepared = conn.prepare("SELECT * FROM mytable WHERE id=?;").unwrap();
 
-            match prepared.execute(&IntoParameter::into_parameter(title)) {
+            match prepared.execute(&IntoParameter::into_parameter(1)) {
                 Err(e) => println!("{}", e),
                 Ok(None) => println!("No result set generated."),
                 Ok(Some(_cursor)) => {}
             };
 
+            // Stay alive for 10 seconds
+            std::thread::sleep(Duration::from_secs(CONN_IDLE_PAUSE));
+
+            // Check if connection is dead
             assert!(!conn.is_dead().unwrap());
         });
-
+        
         handles.push(handle);
     }
 
     for handle in handles {
+        println!("Thread {:?} started", handle.thread().id());
         handle.join().unwrap();
     }
 
     Ok(())
 }
 
-fn main() {}
+fn main() {
+    odbc_mssql().unwrap();
+}
diff --git a/odbcinst.ini b/odbcinst.ini
@@ -1,7 +1,18 @@
-[ODBC Driver 18 for SQL Server]
-Description=Microsoft ODBC Driver 18 for SQL Server
+[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
-Pooling=Yes
 CPTimeout=180
-PoolMaxSize=15
+
+[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
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -24,7 +24,7 @@ fn connection_pooling_mssql<'a>() -> Connection<'a> {
     let env = environment_pooling_mssql();
 
     let connection_string = "
-    Driver={ODBC Driver 18 for SQL Server};\
+    Driver={ODBC_Driver_18_for_SQL_Server_Pool};\
     Server=0.0.0.0;\
     UID=SA;\
     PWD=12345678;TrustServerCertificate=Yes;Database=mydatabase;\

diff --git a/odbcinst.ini b/odbcinst.ini
@@ -2,6 +2,6 @@
 Description=Microsoft ODBC Driver 18 for SQL Server
 Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.2.1
 UsageCount=1
-Pooling = Yes
-CPTimeout = 180
-PoolMaxSize = 15
+Pooling=Yes
+CPTimeout=180
+PoolMaxSize=15
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -52,7 +52,7 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
         //
         // Q: The connections are being reused, how can I verify it?
         //
-        let handle = thread::spawn(move || {
+        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.

diff --git a/Cargo.toml b/Cargo.toml
@@ -0,0 +1,9 @@
+[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"
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -2,7 +2,7 @@ use odbc_sys::{AttrConnectionPooling, AttrCpMatch};
 use odbc_api::{Connection, ConnectionOptions, Environment, IntoParameter};
 use std::{sync::OnceLock};
 
-// To work with threads do I need to share a single Environment ?
+// 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();
 
@@ -19,7 +19,7 @@ fn environment_pooling_mssql<'a>() -> &'a Environment {
 
     ENV.get_or_init(init_env)
 }
-// If I connect multiple times from different threads, are the connections reused? According to ODBC and Driver ?
+// 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();
 

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -45,14 +45,19 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
     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() };
+
         //
-        // The connections are being reused, how can I verify it?
+        // Q: The connections are being reused, how can I verify it?
         //
         let handle = thread::spawn(move || {
+
             // If I create a high number of threads => 10000 and connect in each thread, an error is thrown.
-            // after a number of threads establishing ODBC connections, all connections will fail due to timeout login.
+            // 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();

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -51,7 +51,7 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
         //
         let handle = thread::spawn(move || {
             // If I create a high number of threads => 10000 and connect in each thread, an error is thrown.
-            // after a number of threads establishing ODBC connections, all connections will fail.
+            // after a number of threads establishing ODBC connections, all connections will fail due to timeout login.
             // see full log => https://gist.github.com/dertin/da9e195e4cf65a021b1e431fc21e5b97
             // let conn: Connection<'_> = connection_pooling_mssql();
 

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -51,7 +51,7 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
         //
         let handle = thread::spawn(move || {
             // If I create a high number of threads => 10000 and connect in each thread, an error is thrown.
-            // after a number of iterations establishing ODBC connections, all connections will fail.
+            // after a number of threads establishing ODBC connections, all connections will fail.
             // see full log => https://gist.github.com/dertin/da9e195e4cf65a021b1e431fc21e5b97
             // let conn: Connection<'_> = connection_pooling_mssql();
 

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -50,8 +50,8 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
         // The connections are being reused, how can I verify it?
         //
         let handle = thread::spawn(move || {
-            // If I connect to each thread, an error is thrown. 
-            // After a number of iterations establishing ODBC connections, all connections will fail.
+            // If I create a high number of threads => 10000 and connect in each thread, an error is thrown.
+            // after a number of iterations establishing ODBC connections, all connections will fail.
             // see full log => https://gist.github.com/dertin/da9e195e4cf65a021b1e431fc21e5b97
             // let conn: Connection<'_> = connection_pooling_mssql();
 

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -52,7 +52,7 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
         let handle = thread::spawn(move || {
             // If I connect to each thread, an error is thrown. 
             // After a number of iterations establishing ODBC connections, all connections will fail.
-            // see file: panic_conn_in_thread.log
+            // see full log => https://gist.github.com/dertin/da9e195e4cf65a021b1e431fc21e5b97
             // let conn: Connection<'_> = connection_pooling_mssql();
 
             let mut prepared = conn.prepare("SELECT * FROM mytable WHERE id=?;").unwrap();

diff --git a/panic_conn_in_thread.log b/panic_conn_in_thread.log
diff --git a/panic_conn_in_thread.log b/panic_conn_in_thread.log
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -50,6 +50,11 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
         // The connections are being reused, how can I verify it?
         //
         let handle = thread::spawn(move || {
+            // If I connect to each thread, an error is thrown. 
+            // After a number of iterations establishing ODBC connections, all connections will fail.
+            // see file: panic_conn_in_thread.log
+            // let conn: Connection<'_> = connection_pooling_mssql();
+
             let mut prepared = conn.prepare("SELECT * FROM mytable WHERE id=?;").unwrap();
 
             let title = 1;

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -2,6 +2,7 @@ use odbc_sys::{AttrConnectionPooling, AttrCpMatch};
 use odbc_api::{Connection, ConnectionOptions, Environment, IntoParameter};
 use std::{sync::OnceLock};
 
+// 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();
 
@@ -18,7 +19,7 @@ fn environment_pooling_mssql<'a>() -> &'a Environment {
 
     ENV.get_or_init(init_env)
 }
-
+// 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();
 
@@ -42,6 +43,7 @@ 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() };
         //

diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -44,7 +44,9 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
     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();
 

diff --git a/odbcinst.ini b/odbcinst.ini
@@ -3,4 +3,5 @@ Description=Microsoft ODBC Driver 18 for SQL Server
 Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.2.1
 UsageCount=1
 Pooling = Yes
-CPTimeout = 180
+CPTimeout = 180
+PoolMaxSize = 15
diff --git a/odbcinst.ini b/odbcinst.ini
@@ -1,4 +1,6 @@
 [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
+UsageCount=1
+Pooling = Yes
+CPTimeout = 180
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -42,7 +42,7 @@ async fn test_odbc_pool_mssql() -> Result<(), Box<dyn std::error::Error>> {
     let mut handles = vec![];
 
     for _ in 0..10 {
-        let conn: odbc_api::connection::Connection<'_> = connection_pooling_mssql();
+        let conn: Connection<'_> = connection_pooling_mssql();
         let conn = unsafe { conn.promote_to_send() };
 
         let handle = thread::spawn(move || {

diff --git a/odbcinst.ini b/odbcinst.ini
@@ -0,0 +1,4 @@
+[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
diff --git a/test_odbc_pool_mssql.rs b/test_odbc_pool_mssql.rs
@@ -0,0 +1,72 @@
+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: odbc_api::connection::Connection<'_> = connection_pooling_mssql();
+        let conn = unsafe { conn.promote_to_send() };
+
+        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() {}