Connection Pooling support

[AWADHAMBIKA]

I tried to use r2d2 package and r2d2_odbc but there is a collision in carate usage with the same name. How to enable connection pooling? Does this driver support connection pooling?

The code I tried: extern crate odbc_safe; // Use this crate and set environment variable RUST_LOG=odbc to see ODBC warnings extern crate env_logger; extern crate odbc;

use odbc_safe::AutocommitOn; use std::{thread, io};

use std::time::{SystemTime}; use std::sync::{Arc, Mutex}; use std::collections::HashMap;

use odbc::*; use odbc::ResultSetState::Data; use odbc_safe::Statement; use odbc::DiagnosticRecord;

fn main() {

env_logger::init();

match connect() {
    Ok(()) => println!("Success"),
    Err(diag) => println!("Error: {}", diag),
}

} fn connect() -> std::result::Result<(), DiagnosticRecord> {

let mut buffer = String::new();
println!("Please enter connection string: ");
io::stdin().read_line(&mut buffer).unwrap();
let manager = ODBCConnectionManager::new(&buffer);

let pool = r2d2::Pool::builder()
    .max_size(32)
    .build(manager)
    .unwrap();
let pool_conn = pool.get().unwrap();

let conn = pool_conn.raw() as Connection<AutocommitOn>;
execute_statement(&conn)

}

fn execute_statement(conn: &Connection) -> Result<()> { let stmt = Statement::with_parent(conn)?;

let mut sql_text = String::new();
println!("Please enter SQL statement string: ");
io::stdin().read_line(&mut sql_text).unwrap();

match stmt.exec_direct(&sql_text)? {
    Data(mut stmt) => {
        let cols = stmt.num_result_cols()?;
        while let Some(mut cursor) = stmt.fetch()? {
            for i in 1..(cols + 1) {
                match cursor.get_data::<&str>(i as u16)? {
                    Some(val) => print!(" {}", val),
                    None => print!(" NULL"),
                }
            }
            println!();
        }
    }
    NoData(_) => println!("Query executed, no data returned"),
}

Ok(())

}

[Koka]

Hello, you should not use odbc_safe directly, try using odbc safe reexport at odbc::safe instead - it guaranteed to have the same odbc_safe version as main crate do and be compatible

[extrawurst]

@Koka does this mean then the driver does connection pooling?

[AWADHAMBIKA]

Yes, the driver does support connection pooling. I have tested and found to be working fine.

[RKochenderfer]

I appear to be having the same issue I'm using odbc = "0.17.0", r2d2 = "0.8.9", and r2d2_odbc = "0.5.0". I've removed odbc_safe from my code and am using odbc::safe now but am still getting

error[E0308]: mismatched types
  --> src\handler.rs:13:39
   |
13 |     let stmt = Statement::with_parent(conn)?;
   |                                       ^^^^ expected struct `odbc::Connection`, found struct `odbc::connection::Connection`
   |
   = note: expected reference `&odbc::Connection<'_, _>`
              found reference `&odbc::connection::Connection<'_, odbc::odbc_safe::AutocommitOn>`
   = note: perhaps two different versions of crate `odbc` are being used?

Here is the function where the error is throwing including the use statements

use odbc::*;
use odbc::safe::AutocommitOn;
use r2d2_odbc::{ODBCConnectionManager};
use r2d2::PooledConnection;
use crate::models::connection_pools::ConnectionPools;
use crate::models::select_request::SelectRequest;
use odbc::ResultSetState::Data;

fn perform_tmm_query<'a> (connection_pools: &ConnectionPools, request: &SelectRequest) -> Result<Statement<'a, 'a, Prepared, NoResult, safe::AutocommitOn>> {
    let pool_conn = connection_pools.tmm10_pool.get().unwrap();
    let conn = pool_conn.raw();

    let stmt = Statement::with_parent(conn)?;
    let mut stmt = stmt.prepare(&request.get_query())?;

    // Bind all the arguments to the sql query
    for (i, arg) in request.args.iter().enumerate() {
        stmt.bind_parameter(i as u16, arg);
    }

    let stmt = if let Data(mut stmt) = stmt.execute()? {
        if let Some(mut cursor) = stmt.fetch()? {
            println!("{}", cursor.get_data::<String>(1)?.unwrap());
        }
        stmt.close_cursor()?
    } else {
        panic!("SELECT statement returned no result set")
    };

    stmt.reset_parameters()
}

[AWADHAMBIKA]

Your cargo.toml file should have following entries only under [dependencies] r2d2 = "0.8" odbc = "0.17.0" odbc-safe = "0.5.0" r2d2_odbc = "0.4.1"

Sample Code:

extern crate r2d2; extern crate r2d2_odbc; extern crate odbc;

use std::thread; use r2d2_odbc::ODBCConnectionManager; use odbc::*; use odbc::ResultSetState::Data;

fn main() { let manager = ODBCConnectionManager::new("DSN=sample;DATABASE=sample;hostname=asds.asdf.com;PORT=5000;UID=sample;PWD=sample;"); let pool = r2d2::Pool::new(manager).unwrap();

let mut children = vec![];
for i in 0..10i32 {
    let pool = pool.clone();
    let pool_conn = pool.get().unwrap();
    children.push(thread::spawn(move || {
        let conn = pool_conn.raw();
        let stmt = Statement::with_parent(&conn).unwrap();
        if let Data(mut stmt) = stmt.exec_direct("select * from dbtest.GLWTACT").unwrap() {
            while let Some(mut cursor) = stmt.fetch().unwrap() {
                if let Some(val) = cursor.get_data::<&str>(1).unwrap() {
                    println!("THREAD {} {}", i, val);
                }
            }
        }
    }));
}

for child in children {
    let _ = child.join();
}

}

This is a working code. Hope this helps.

[RKochenderfer]

The error I'm getting with your code which was similar to what I was getting first was this

error[E0308]: mismatched types
  --> src\main.rs:20:47
   |
20 |             let stmt = Statement::with_parent(&conn).unwrap();
   |                                               ^^^^^ expected struct `odbc::Connection`, found reference
   |
   = note: expected reference `&odbc::Connection<'_, _>`
              found reference `&&odbc::connection::Connection<'_, odbc::odbc_safe::AutocommitOn>`

Removing the reference gives the same error I posted above

[RKochenderfer]

Also when I attempt to use the dependencies you suggested I'm getting this

error: failed to select a version for the requirement `r2d2_odbc = "^0.4.1"`
candidate versions found which didn't match: 0.5.0, 0.4.0, 0.3.0, ...
location searched: crates.io index

[AWADHAMBIKA]

I am trying to figure out but in the meanwhile, you can do one thing, just clone the r2d2-odbc code from "https://github.com/Koka/r2d2-odbc" and then put my code in examples section as "connect.rs" and run. This will run properly. Possibly because there is a library file and it clarifies the import resolution and also @Koka's point that not to use r2d2-odbc directly.

[RKochenderfer]

I managed to fix it by creating a r2d2_odbc file directly into my project and copying the lib.rs file and pasting it there. I'm not sure if this means that the current version has some breaking changes to it or not but for now this was a short term fix. Thank you for the help!

[creeefs]

I hit the same type error as @RKochenderfer. I opened a PR https://github.com/Koka/r2d2-odbc/pull/122 to bump the odbc dependency, but in the meantime I also just copied a r2d2_odbc file in my project.