Asynchronously connecting to a database

[pacman82]

Queries can already be executed asynchronously, but opening the connection is still blocking.

[genusistimelord]

ahh this is nice that your adding this. However can you add a few examples of how to use it?

Currently I am doing something like the below in my async code.

blocking! ( let query = format!(
                r#"SELECT DISTINCT trim(Client) as "Client"
                FROM DMS_Recordkeys
                where {}
                order by client
                OPTION (FORCE ORDER)"#,
                get_type(&dis)
            );

            if let Some(mut cursor) = connection.execute(&query, ()).ok().flatten() {
                let mut buffers = TextRowSet::for_cursor(5000, &mut cursor, Some(4096)).unwrap();
                let mut row_set_cursor = cursor.bind_buffer(&mut buffers).unwrap();

                while let Some(batch) = row_set_cursor.fetch().unwrap_or(None) {
                    for rid in 0..batch.num_rows() {
                        let client = batch.get_string(0, rid);
                        data.push(client);
                    }
                }
            };

            data.sort();
            data
        );

get_string is a custom trait implement which just does

fn get_string(&self, cid: usize, rid: usize) -> String {
        self.at_as_str(cid, rid)
            .unwrap_or(None)
            .unwrap_or_default()
            .to_owned()
            .replace(',', ";")
            .replace('(', "{")
            .replace(')', "}")
    }

any idea on how this could be converter to use the async code or will i need to wait for a few more implementations before I can?

[pacman82]

Most of that you can do already. For asynchronously executing something there is already a code example here: https://docs.rs/odbc-api/latest/odbc_api/struct.Connection.html#method.execute_polling

Of course you don't execute an insert statement, but a query with a result set. You'll find that CursorPolling works much like CursorImpl, but the fetch method is also async.

The Connection you cannot open asynchronously yet. This is what this issue is about. While connecting is not asynchronous, you can of course connect in asynchronous code.

Does this answer your question?

Cheers, Markus

[genusistimelord]

ahh I was having issue trying to figure out how to pass the cursor to TextRowSet::for_cursor(5000, &mut cursor, Some(4096)) so that way i could use the cursor the way I am currently using it.

[pacman82]

Nope, you are right. Oversight on my part. You currently have no (good) way to get metadata on the result set using an asynchronous cursor. I'll open an issue for this.

[genusistimelord]

Thank you. Yeah I was attempting to switch my Axum lib and depreciate my Block method in place of this and noticed there was no way that I could see to use it.

[pacman82]

Yeah, my mistake. Maybe announced it a tad to early 😅

[BratSinot]

Nope, you are right. Oversight on my part. You currently have no (good) way to get metadata on the result set using an asynchronous cursor. I'll open an issue for this.

So, now there is no way to fetch data from DB asynchronously? =(

[pacman82]

@BratSinot

Fetching data asynchronously works with ODBC if:

1. You know the schema in advance (currently you can not query the metadata (i.e. the schema information) of the result set asynchronously. This is a limitation of this crate, which can be addressed in the future.
2. While this crate always supports the async syntax, please make sure that your ODBC driver actually is not blocking any system threads. I know for a fact that Microsoft SQL Server is "truly" async, but I do not know how far async support on the driver level is spread in other drivers. (If async is not supported by your driver natively, there is little I can do within this crate)

You may want to look into https://docs.rs/odbc-api/latest/odbc_api/struct.Connection.html#method.execute_polling for a code example of how to execute a statement asynchronously.

If it would turn out, that async is not supported well enough for you out of the box, you can of course always spawn a system thread, to make it async in a do it yourself fashion.

Maybe I'll be able to help better, if you tell me a bit more about your usecase

Best, Markus