The ODBC backend never populates its statement cache

[81reap]

Bug Description

The ODBC backend never populates its statement cache, so every query executes through SQLExecDirect.

Depending on the driver used the user impact is ::

1. statement_cache_capacity (default 100) and Execute::persistent() are silently no-ops on ODBC.
2. SQLBindParameter followed by SQLExecDirect is valid ODBC, but a driver that only collects bindings on the SQLPrepare/SQLExecute path will send the statement with no parameter values at all.

Minimal Reproduction

(1) I used PostgreSQL as it doesn't have the param pass through issue.

Cargo.toml:

[package]
name = "repro"
version = "0.0.0"
edition = "2021"

[workspace]

[dependencies]
sqlx = { package = "sqlx-oldapi", version = "0.6.56", default-features = false, features = ["runtime-tokio-rustls", "odbc"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

src/main.rs:

use sqlx::Connection;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL");
    let mut conn = sqlx::OdbcConnection::connect(&url).await?;

    for _ in 0..10 {
        let row: (i32,) = sqlx::query_as("SELECT CAST(? AS INTEGER)")
            .bind(42)
            .fetch_one(&mut conn)
            .await?;
        assert_eq!(row.0, 42);
    }

    println!("cached_statements_size = {}", conn.cached_statements_size());
    Ok(())
}

SOP

$ docker run --rm -d --name pg -e POSTGRES_PASSWORD='Password123!' -e POSTGRES_USER=root -e POSTGRES_DB=test -p 5432:5432 postgres:16

# ODBC backend
$ DATABASE_URL='Driver=/nix/store/1fyjnz2b5h28pdcy9wakcvcq8ywz166g-psqlodbc-18.00.0002/lib/psqlodbcw.so;Server=127.0.0.1;Port=5432;Database=test;UID=root;PWD=Password123!' cargo run
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.09s
     Running `target/debug/repro`
cached_statements_size = 0    # expected :: 1

(2) Is reproducible in SQLPage with SQLPage#1474.

In "index.sql": The following error occurred while executing an SQL statement:
error returned from database: ODBC emitted an error calling 'SQLExecDirect':
State: HY000, Native error: 100, Message: [GizmoData][GizmoSQL] (100) An execution error has occurred: Invalid Input Error: Values were not provided for the following prepared statement parameters: 1, 2. gRPC client debug context: UNKNOWN:Error received from peer ipv6:%5B::1%5D:31337 {grpc_message:"An execution error has occurred: Invalid Input Error: Values were not provided for the following prepared statement parameters: 1, 2", grpc_status:2, created_time:"2026-09-21T16:29:56.6640056+00:00"}. Client context: OK

Info

• SQLx version: 0.6.56 (sqlx-oldapi, main at f8b378d1)
• SQLx features enabled: runtime-tokio-rustls, odbc, postgres, any
• Database server and version: PostgreSQL 16 via psqlODBC 18.00.0002 (also reproduced against GizmoSQL 1.39.0 via gizmosql-odbc 1.1.2, and DuckDB via duckdb-odbc 1.5.5), unixODBC 2.3.14
• Operating system: NixOS 26.11, Linux 6.18.52 x86_64
• rustc --version: rustc 1.98.1 (48a229cea 2026-09-01)

[bluelightspirit]

Does 931e646 have anything to do with this? There is no current AnyKind::Odbc => Ok("ODBC"), in prepare.rs at https://github.com/sqlpage/sqlx-oldapi/blob/main/sqlx-cli/src/prepare.rs - there are more changes than this that were closed on July 30, 2026 that may fix this issue? #55

[bluelightspirit]

Do any changes in jbaker0's fork of odbc-api by pacman-82 fix this problem? https://github.com/facilitysolutionsgroup/odbc-api
Or straight from obdc-api? https://github.com/pacman82/odbc-api
Also lovasoa's pull request never got merged about a year ago into odbc-sys is that matters at pacman82/odbc-sys#78

Also, since obdc-sys and obdc-api by lovasoa are forked, no issues or discussions can be presented there it seems. https://github.com/sqlpage/odbc-api and https://github.com/sqlpage/odbc-sys

Both obdc-sys and obdc-api are about a year outdated when compared to pacman82, though. I think the issue is more likely with odbc-sys than odbc-api though since FFIs go beyond just SQLite/MariaDB/MS SQL Server/PostgreSQL from my understanding.

Additionally, the prolbem may be because the GizmoSQL README says "Statement Queueing" is part of Enterprise instead of Core at https://github.com/gizmodata/gizmosql - possibly relevant to the "ODBC backend never populates its statement cache" bug.

[bluelightspirit]

For the PostgreSQL side for queuing, PgBouncer, beanstalkd, ZeroMQ, ActiveMQ, Celery, and more may be relevant. Though this Stack Overflow thread is from February 2014... https://stackoverflow.com/questions/21657807/postgresql-database-queries-queue-mechanizm-php

Also, native PostgreSQL appears to be able to set up queues as of August 2022 per https://chbussler.medium.com/implementing-queues-in-postgresql-3f6e9ab724fa as I solely see SQL statements as code. Another solution may be Que per the Git Gist at https://gist.github.com/chanks/7585810 - however, this was made before or on January 28, 2016. A late 2024 blog seems to go more in-depth into PostgreSQL's queuing system at https://www.danieleteti.it/post/building-a-simple-yet-robust-job-queue-system-using-postgresql/ - though it is uncertain if PostgreSQL's queuing mechanism is exactly the problem rather than the ODBC - just a possibility. Furthermore, I do see PostgreSQL JDBC Driver statement caching at https://vladmihalcea.com/postgresql-jdbc-statement-caching/ - but we want ODBC, not JDBC!

Going down the GitHub Issues rabbit hole, pgjdbc/r2dbc-postgresql#382 shows 2 recent mentions this year - July 7, 2026, and August 6, 2026 for 1 issue and 1 pull request at pgjdbc/r2dbc-postgresql#726 and prisma/orm#29907 and there appears to be no auto retry functionality for pgx at jackc/pgx#841 (comment) (not sure if this is even relevant anymore). OOOOO SQLX cache issue with PostgreSQL reported in March 2020 at transact-rs#149 and fixed that same month allegedly!

GizmoSQL is a quite new server and not that popular versus these PostgreSQL issues/pull requests I found. I can't really find anything useful at a glance currently... there are cache mentions in this merged pull request at gizmodata/gizmosql#191 but not specifically about statement caches as far as I'm aware. I see queue pressure mechanisms implemented 2 weeks ago in GizmoSQL at gizmodata/gizmosql#188 and statement queuing commits done around June 2026 at gizmodata/gizmosql#172 as well as some brief mentions of "statement queu" at gizmodata/gizmosql#185 - though I am not 100% sure if we're even allowed to use any statement queue mentions if GizmoSQL Enterprise "requires a commercial license" exactly...

[81reap]

not sure of all of the things you linked, but it would be much more helpful if you are able to understand the output your agent is saying and come up with your own words and preferred ways to queue. if your having trouble picking between software then I would recommend building out MVPs and opening issues about specific things you run into, or just pick the one that fits your needs.

In my opinion, I would recommend you directly use DuckDB while we work on fixing this issue. I think DuckDB only has the first issue not the second.

---

@lovasoa I have changes locally that fix the issue, but I will have to build and pull this change into SQLPage to test if the upstream issue is also resolved.

$ export DATABASE_URL='Driver=/nix/store/1fyjnz2b5h28pdcy9wakcvcq8ywz166g-psqlodbc-18.00.0002/lib/psqlodbcw.so;Server=127.0.0.1;Port=5432;Database=test;UID=root;PWD=Password123!'

cargo run --config 'patch.crates-io.sqlx-oldapi.path="/home/reap/Desktop/sqlx-oldapi"'
   Compiling sqlx-core-oldapi v0.6.56 (/home/reap/Desktop/sqlx-oldapi/sqlx-core)
   Compiling sqlx-oldapi v0.6.56 (/home/reap/Desktop/sqlx-oldapi)
   Compiling repro v0.0.0 (/home/reap/Desktop/sqlx-oldapi/tmp)
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 1.37s
     Running `target/debug/repro`
cached_statements_size = 1

as for why its happening I think that this is the flow that's breaking.

sequenceDiagram
    participant Caller as caller (async task)
    participant Conn as OdbcConnection (&mut self)
    participant Cache as stmt_cache
    participant Worker as blocking thread (spawn_blocking)
    participant Driver as ODBC driver

    Caller->>Conn: fetch_many(query)
    Conn->>Cache: get_mut(sql)
    Cache-->>Conn: None, every time
    Note right of Conn: Only OdbcConnection::prepare inserts into the cache. fetch_many never pulls from the cache.
    Conn->>Worker: spawn_blocking(move closure)
    Note right of Conn: The closure must be 'static, so it cannot borrow &mut self to write to the stmt_cache.
    Conn-->>Caller: Receiver, returned immediately
    Worker->>Driver: SQLBindParameter
    Worker->>Driver: SQLExecDirect
    Driver-->>Worker: rows
    Worker-->>Caller: rows over channel

Loading

I think it would also be worth adding tests for GizmoSQL if that is an OBDC driver we want to support outright, but I think that would be better off being upstreamed and rebase. not exactly sure how that would work here as the branches have diverged quite a bit. I also don't use GizmoSQL so I would only be able to contribute basic tests and use cases, or permutations on existing tests.

[81reap]

I'm starting to think that the two bugs are not related. To verify my assumptions I added additional tests to run in the CI sqlpage/SQLPage#1501

statement_cache_capacity (default 100) and Execute::persistent() are silently no-ops on ODBC.

this is a real issue in SQLx that was discovered as a result of investigating the codebase and is unrelated to the GizmoSQL issue reported upstream. Looking at the CI result, we can see that test_parameterized_pages_leave_a_prepared_statement_in_the_cache is the only new test that fails.

SQLBindParameter followed by SQLExecDirect is valid ODBC, but a driver that only collects bindings on the SQLPrepare/SQLExecute path will send the statement with no parameter values at all.

I think this may actually be a bug in the GizmoSQL OBDC driver, not SQLx. If it was an issue with SQLx, I would expect DuckDB ODBC to also be failing test_a_parameter_in_a_projection_reaches_the_database.

[81reap]

I have raised #61 to address the statement cache issue

[lovasoa]

thanks !