How can I save ResultSet<'_, oracle::Row> for later access?

[krstn420]

Im writing a tauri application with your library to access a oracle database. the users will create huge result lists, so ResultSet comes in handy. with the following code I can loop over the ResultSets:

let mut rows: oracle::ResultSet<'_, oracle::Row> = stmt.query(&[]).unwrap(); 
while let Some(row_result) = &rows.next() {
// further details below (fn fetch)
}

which works great. But... I want to give the user a "preview" e.g. first 50 rows, as i am required to use the same ResultSet, therefore I have to save the ResultSet somewhere (e.g. RWLockCacheManager). After the user is starting to ask for more data of the same ResultSet again (fetch_from_cache), I have to access the same ResultSet at a later point in time. Therefore I have to save the ResultSet in a "Cache" for later access.

How can I save the ResultSet?

This is where the issues start to unveil:

// Prevents additional console window on Windows in release, DO NOT REMOVE!!
#![cfg_attr(not(debug_assertions), windows_subsystem = "windows")]

use std::{ collections::HashMap, sync::RwLock };

use oracle::{ Connection, ResultSet, Row };
use serde::Serialize;
use tauri::State;

#[derive(Default, Debug)]
pub struct CacheManager<'a> {
    cache: HashMap<String, &'a ResultSet<'a, Row>>,
}

#[derive(Default)]
pub struct RWLockCacheManager<'a>(pub RwLock<CacheManager<'a>>);

impl<'a> CacheManager<'a> {
    pub fn write_cache(&mut self, key: &str, data: &'a ResultSet<'_, Row>) {
        let key = key.to_string();
        let value = data; 
        println!("write cache for {}", key);
        self.cache.insert(key, value);
    }
    pub fn read_cache(self, key: &str) -> &'a ResultSet<'_, Row> {
        let key = key.to_string();
        println!("read cache for {}", key);
        self.cache.get(&key).unwrap()
    }
}

#[derive(Serialize, Default)]
pub struct CacheResult {
    pub data: Vec<Vec<Option<String>>>,
    pub cache_key: String,
}
/*
#[tauri::command]
fn fetch_from_cache(cache: State<'_, RWLockCacheManager>, cache_key: String) -> Result<String, String> {
    let max_elements: u32 = 50;
    let cache_binding = cache.0.read().unwrap();
    let rows = cache_binding.read_cache(&cache_key);
    let mut x= 0;
    while let Some(row_result) = rows.next() {
        if x > max_elements {
            break;
        }
        x += 1;
        println!("{:?}", row_result.as_ref().unwrap().sql_values());
    }
    Ok(cache_key)
}*/

#[tauri::command]
fn fetch(cache: State<'_, RWLockCacheManager>, cache_key: String) -> Result<String, String> {
    let max_elements = 50;

    let connection_string = format!(
        "(description= (retry_count=1)(retry_delay=1)(address=(protocol=tcps)(port={})(host={}))(connect_data=(service_name={}))(security=(ssl_server_dn_match=yes)))",
        1522,
        "adb.eu-frankfurt-1.oraclecloud.com",
        "xxx"
    );
    let user: String = "xxx".to_string();
    let pass: String = "xxx".to_string();

    match Connection::connect(user, pass, connection_string) {
        Ok(conn) => {
            let query: String = "select * from all_objects".to_string();

            let mut stmt = conn.statement(&query).fetch_array_size(max_elements).build().unwrap();
            let mut rows = stmt.query(&[]).unwrap();
            println!("print first 50 to screen:");
            let mut x = 0;
            while let Some(row_result) = &rows.next() {
                if x > max_elements {
                    break;
                }
                x += 1;
                println!("{:?}", row_result.as_ref().unwrap().sql_values());
            }
            println!("{} The rest should be put in the cache", x);

            let mut cache_binding = cache.0.write().unwrap();
            cache_binding.write_cache(&cache_key, &rows);

            println!("done");
            Ok(cache_key)
        }
        Err(err) => Err(err.to_string()),
    }
}

fn main() {
    println!("Tauri Starting...");

    tauri::Builder
        ::default()
        .manage(RWLockCacheManager(Default::default()))
        .invoke_handler(tauri::generate_handler![fetch])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

[kubo]

It is impossible at present.

I'm thinking about changing the lifetime of following methods' return types from 'a to 'static.

• Connection::query()
• Connection::query_named()
• Connection::query_as()
• Connection::query_as_named()
• Statement::into_result_set()
• Statement::into_result_set_named()

After I do it, you can put ResultSet in the following type.

#[derive(Default, Debug)]
pub struct CacheManager {
    cache: HashMap<String, ResultSet<'static, Row>>,
}

Note: The number of ResultSet in a session at once is limited by database initialization parameter OPEN_CURSORS.

[krstn420]

Hello kubo,

thank you for your reply. I have one more suggestion. Is it possible to implement a Send trait for dpiStmt?

error[E0277]: `*mut oracle::binding::binding::dpiStmt` cannot be sent between threads safely
   --> src\main_as_trex.rs:55:17
    |
55  | fn fetch(cache: State<'_, RWLockCacheManager>, cache_key: String,) -> Result<String, String> {
    |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ `*mut oracle::binding::binding::dpiStmt` cannot be sent between threads safely
    |
    = help: within `(std::string::String, ResultSet<'static, Row>)`, the trait `Send` is not implemented for `*mut oracle::binding::binding::dpiStmt`
note: required because it appears within the type `Stmt`
   --> C:\Users\xxx\.cargo\registry\src\index.crates.io-6f17d22bba15001f\oracle-0.5.7\src\statement.rs:393:19
    |
393 | pub(crate) struct Stmt {
    |                   ^^^^
note: required because it appears within the type `StmtHolder<'static>`
   --> C:\Users\xxx\.cargo\registry\src\index.crates.io-6f17d22bba15001f\oracle-0.5.7\src\row.rs:105:6
    |
105 | enum StmtHolder<'a> {
    |      ^^^^^^^^^^
note: required because it appears within the type `ResultSet<'static, Row>`
   --> C:\Users\xxx\.cargo\registry\src\index.crates.io-6f17d22bba15001f\oracle-0.5.7\src\row.rs:112:12
    |
112 | pub struct ResultSet<'a, T>
    |            ^^^^^^^^^
    = note: required because it appears within the type `(String, ResultSet<'static, Row>)`
    = note: required for `hashbrown::raw::RawTable<(std::string::String, ResultSet<'static, Row>)>` to implement `Send`
note: required because it appears within the type `HashMap<String, ResultSet<'static, Row>, RandomState>`
   --> /rust/deps\hashbrown-0.14.2\src\map.rs:190:12
note: required because it appears within the type `HashMap<String, ResultSet<'static, Row>>`
   --> C:\Users\xxx\.rustup\toolchains\stable-x86_64-pc-windows-msvc\lib/rustlib/src/rust\library\std\src\collections\hash\map.rs:216:12
    |
216 | pub struct HashMap<K, V, S = RandomState> {
    |            ^^^^^^^
note: required because it appears within the type `CacheManager`
   --> src\main_as_trex.rs:10:12
    |
10  | pub struct CacheManager {
    |            ^^^^^^^^^^^^
    = note: required for `std::sync::RwLock<CacheManager>` to implement `Send`
note: required because it appears within the type `RWLockCacheManager`
   --> src\main_as_trex.rs:15:12
    |
15  | pub struct RWLockCacheManager(pub RwLock<CacheManager>);
    |            ^^^^^^^^^^^^^^^^^^
note: required by a bound in `State`
   --> C:\Users\xxx\.cargo\registry\src\index.crates.io-6f17d22bba15001f\tauri-1.6.1\src\state.rs:14:25
    |
14  | pub struct State<'r, T: Send + Sync + 'static>(&'r T);
    |                         ^^^^ required by this bound in `State`

I get this compiler error and therefore cannot use anything from this library in a tauri state.

Thank you for the hint on the OPEN_CURSORS parameter, very valuable!

Current Version:

// Prevents additional console window on Windows in release, DO NOT REMOVE!!
#![cfg_attr(not(debug_assertions), windows_subsystem = "windows")]

use std::{collections::HashMap, sync::RwLock};

use oracle::{Connection, ResultSet, Row};
use tauri::State;

#[derive(Default, Debug)]
pub struct CacheManager {
    cache: HashMap<String, ResultSet<'static, Row>>,
}

#[derive(Default)]
pub struct RWLockCacheManager(pub RwLock<CacheManager>);

impl CacheManager {
    pub fn write_cache(&mut self, key: &str, data: ResultSet<'static, Row>) {
        let key = key.to_string();
        let value = data;
        println!("write cache for {}", key);
        self.cache.insert(key, value);
    }
    pub fn read_cache(&self, key: &str) -> &ResultSet<'static, Row> {
        let key = key.to_string();
        println!("read cache for {}", key);
        self.cache.get(&key).unwrap()
    }
}

/*
#[derive(Serialize, Default)]
pub struct CacheResult {
    pub data: Vec<Vec<Option<String>>>,
    pub cache_key: String,
}

#[tauri::command]
fn fetch_from_cache(cache: State<'_, RWLockCacheManager>, cache_key: String) -> Result<String, String> {
    let max_elements: u32 = 50;
    let cache_binding = cache.0.read().unwrap();
    let rows = cache_binding.read_cache(&cache_key);
    let mut x= 0;
    while let Some(row_result) = rows.next() {
        if x > max_elements {
            break;
        }
        x += 1;
        println!("{:?}", row_result.as_ref().unwrap().sql_values());
    }
    Ok(cache_key)
}*/

#[tauri::command]
fn fetch(cache: State<'_, RWLockCacheManager>, cache_key: String,) -> Result<String, String> {
    let max_elements = 50;

    let connection_string = format!(
        "(description= (retry_count=1)(retry_delay=1)(address=(protocol=tcps)(port={})(host={}))(connect_data=(service_name={}))(security=(ssl_server_dn_match=yes)))",
        1522,
        "adb.eu-frankfurt-1.oraclecloud.com",
        "xxx"
    );
    let user: String = "xxx".to_string();
    let pass: String = "xxx".to_string();

    match Connection::connect(user, pass, connection_string) {
        Ok(conn) => {
            let query: String = "select * from all_objects".to_string();

            let mut stmt = conn
                .statement(&query)
                .fetch_array_size(max_elements)
                .build()
                .unwrap();
            let mut rows = stmt.query(&[]).unwrap();
            println!("print first 50 to screen:");
            let mut x = 0;
            for row_result in rows.by_ref().take(max_elements) {
                println!("{:?}", row_result.as_ref().unwrap().sql_values());
            }
            println!("{} The rest should be put in the cache", x);

            //let mut cache_binding = cache.0.write().unwrap();
            //cache_binding.write_cache(&cache_key, &rows);

            println!("done");
            Ok(cache_key)
        }
        Err(err) => Err(err.to_string()),
    }
}

fn main() {
    println!("Tauri Starting...");

    tauri::Builder
        ::default()
        .manage(RWLockCacheManager(Default::default()))
        .invoke_handler(tauri::generate_handler![fetch])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

[kubo]

I'm now implementing Send trait for Row. You can make it satisfy Send + Sync trait bound by wrapping it in Mutex after that. Note that you cannot use RwLock because RwLock<T> doesn't implement Send + Sync when T doesn't implement Sync.

The version will be 0.6.0 introducing breaking changes.

[krstn420]

With 6.0.0-dev crate via git, and the following definition:

#[derive(Default, Debug)]
pub struct CacheManager {
    cache: HashMap<String, ResultSet<'static, Mutex<Row>>>,
}

i get the following error:

error[E0277]: the trait bound `std::sync::Mutex<Row>: FromSql` is not satisfied
   --> src\main_as_trex.rs:13:28
    |
13  |     cache: HashMap<String, ResultSet<'static, Mutex<Row>>>,
    |                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `FromSql` is not implemented for `std::sync::Mutex<Row>`
    |
    = help: the following other types implement trait `FromSql`:
              bool
              isize
              i8
              i16
              i32
              i64
              usize
              u8
            and 18 others
    = note: required for `std::sync::Mutex<Row>` to implement `RowValue`
note: required by a bound in `ResultSet`
   --> C:\Users\xxx\.cargo\git\checkouts\rust-oracle-858dd8ce065f6a17\e98d639\src\row.rs:134:8
    |
132 | pub struct ResultSet<'a, T>
    |            --------- required by a bound in this struct
133 | where
134 |     T: RowValue,
    |        ^^^^^^^^ required by this bound in `ResultSet`

[kubo]

I'm doing now. It will take at least a few days.

Use cache: HashMap<String, Mutex<ResultSet<'static, Row>>> after that.

[kubo]

I implemented Send for ResultSet<'static, T>. You can use it as cache: HashMap<String, Mutex<ResultSet<'static, Row>>>.

You need to use stmt.into_result_set::<Row>(&[]) in place of stmt.query(&[]). See https://www.jiubao.org/rust-oracle/oracle/struct.ResultSet.html#remarks

I'll release 0.6.0 in a few weeks. I plan to redesign Error, which causes a breaking change.