get the number of affected rows ?

Hi !
I'm currently trying to integrate odbc-api as a new backend for sqlx(-oldapi).
sqlx has an api to retrieve the number of rows affected by a query (such as an UPDATE): https://docs.rs/sqlx-oldapi/latest/sqlx_oldapi/any/struct.AnyQueryResult.html#impl-AnyQueryResult
Is there a way to retrieve that information from ODBC ?

@lovasoa

Hello @lovasoa ,

Certainly. In safe code there are two ways:

Without using a prepared statement: https://docs.rs/odbc-api/latest/odbc_api/struct.Preallocated.html#method.row_count
Using a prepared statement: https://docs.rs/odbc-api/latest/odbc_api/struct.Prepared.html#method.row_count

You can not fetch row count if you use Connection::execute, because you will not have access to a statement handle this way.

Great to see an effort to make ODBC databases available via SQLX!

Hope this helps. I wish you much success on your endeavor,
Markus

Thanks ! I'll let you know when I release the next version of odbc-oldapi, with odbc support.

I implemented it and it seems to be working fine; thanks again: sqlpage/sqlx-oldapi@d82bd8e#diff-57a0cae56cfe5042b85a47d5c19e51ec066aa9a10620caeb79317f7466406b2f

I'll try to publish sqlx-oldapi v0.6.49-beta today; any testing and feedback is welcome !

@lovasoa

@lovasoa Off topic, but since you seem to be invested in it:

Using .next_row is by far the slowest way to extract data out of a database. Think up to three order of magnitudes slower than extracting data using a bulk cursor.

https://github.com/sqlpage/sqlx-oldapi/blob/d82bd8ebc6dc3ba6166460fdabd102abf6ea09f6/sqlx-core/src/odbc/connection/worker.rs#L594C38-L594C46

See also: https://docs.rs/odbc-api/latest/odbc_api/trait.Cursor.html#method.next_row

Best, Markus

Thanks! What buffer can I use when I don't know the results schema in advance?

Another thing I'm trying to do is create a prepared statement cache. I'd need a struct to own both a Connection and a Hashmap<_, Statement> but I don't think I can do that with the lifetimes of the structures exposed in this crate, without resorting to unsafe. I see I can own a connection and a single statement, but not a connection and an arbitrary number of statements.

What buffer can I use when I don't know the results schema in advance?

You can get the schema information from the cursor. You can use it to decide what columns to create on e.g. a ColumnarAnyBuffer and bind it to the statement before extracting rows.

I'd need a struct to own both a Connection and a Hashmap<_, Statement> but I don't think I can do that with the lifetimes of the structures exposed in this crate, without resorting to unsafe. I see I can own a connection and a single statement, but not a connection and an arbitrary number of statements.T

This might be possible. Call into_prepared on an Arc<Connection>. You may need to import the ConnectionTransition trait.

This might be possible. Call into_prepared on an Arc. You may need to import the ConnectionTransition trait.

Thanks! It works

You can get the schema information from the cursor. You can use it to decide what columns to create on e.g. a ColumnarAnyBuffer and bind it to the statement before extracting rows.

I see ! But since sqlx is more row-oriented, it would be nice if odbc-api exposed something like

pub enum OdbcValue {
    F32(f32),
    F64(f64),
    I8(i8),
    I16(i16),
    I32(i32),
    I64(i64),
    U8(u8),
    U16(u16),
    U32(u32),
    U64(u64),
    Date(Date),
    Numeric(Numeric),
    Time(Time),
    Timestamp(Timestamp),
}

And then I could use something like a RowVecBuffer<OdbcValue>

You can get the schema information from the cursor. You can use it to decide what columns to create on e.g. a ColumnarAnyBuffer and bind it to the statement before extracting rows.

Looking at this a little bit deeper, it looks like there is no way to use the bulk fetch API without knowing in advance the max size of textual and binary columns ? If so, then it's a dealbreaker for sqlx, since it can be used with data of arbitrary size...

Looking at this a little bit deeper, it looks like there is no way to use the bulk fetch API without knowing in advance the max size of textual and binary columns ?

Yes, if the schema information does not provide a sensible upper bound, you have to come up with one based on domain knowledge. Yet, you need to allocate the buffers before writing into them. Otherwise, you are stuck with the slow row-wise fetch.

There is a mechanism proposed for ODBC 4.0 to handle overflowing columns, but I do not know when, if ever ODBC 4.0 becomes a reality.

One way to deal with this could be to only use bulk fetch, if a sensible upper bound can be established, and fall back to row-wise otherwise.

In my testing, the SQLite driver reports VARCHAR(255) even for long strings, so I guess we could do that only when the query doesn't return any string...

sqlpage/sqlx-oldapi#40

I have a PR here, if you want to help ;)

In my testing, the SQLite driver reports VARCHAR(255) even for long strings, so I guess we could do that only when the query doesn't return any string...

Some drivers allow to configure these values in the Connection String. In my general purpose artifacts (odbc2parquet, arrow-odbc, etc.). I have done well to just respect the decisions of the driver.

I have a PR here, if you want to help ;)

Thanks for the invitation, but I already have my own artifacts and day job to worry about.

Of course ! No problem. I'll keep you posted on how this goes.

Sure, maybe the PR you linked is an even better place to discuss these questions than an already closed issue here. Feel free to ping me if something interesting is implemented, or you have a question about ODBC or odbc-api.

Navigation Menu

Repository navigation

get the number of affected rows ? #778

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

get the number of affected rows ? #778

Description

Activity

pacman82 commented on Sep 22, 2025

lovasoa commented on Sep 23, 2025

lovasoa commented on Sep 24, 2025

pacman82 commented on Sep 24, 2025

lovasoa commented on Sep 24, 2025

lovasoa commented on Sep 24, 2025

pacman82 commented on Sep 24, 2025

lovasoa commented on Sep 25, 2025

lovasoa commented on Sep 25, 2025

lovasoa commented on Sep 25, 2025

pacman82 commented on Sep 25, 2025

lovasoa commented on Sep 26, 2025

lovasoa commented on Sep 26, 2025

pacman82 commented on Sep 26, 2025

lovasoa commented on Sep 26, 2025

pacman82 commented on Sep 26, 2025

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions