Firebird: `odbc-copy` Silent Data Loss with INTEGER→VARCHAR SP Parameters

[fdcastel]

odbc_copy silently loses data (partial or total rollback without error) when a DuckDB INTEGER/BIGINT column is bound to a Firebird stored procedure VARCHAR parameter.

The function reports success (True, correct row count) but the transaction is not committed — or only partially committed.

Environment

• DuckDB: 1.3.0 and 1.4.4 (both affected)
• odbc_scanner: commit 52e168c
• ODBC Driver: Firebird ODBC Driver 3.0.1.21
• Database: Firebird 3.0.13

Reproduction

import duckdb

odbc_conn = (
    "Driver={Firebird ODBC Driver};"
    "Database=127.0.0.1:C:/path/to/database.fdb;"
    "UID=SYSDBA;PWD=masterkey;CHARSET=UTF8"
)

# Assume this SP exists on the Firebird side:
#   CREATE PROCEDURE my_sp(p_id VARCHAR(20), p_name VARCHAR(100)) AS
#   BEGIN
#       UPDATE OR INSERT INTO my_table(id, name) VALUES(:p_id, :p_name)
#       MATCHING (id);
#   END

con = duckdb.connect()
con.execute("LOAD odbc_scanner")

# Source table has INTEGER id column
con.execute("CREATE TABLE src (id INTEGER, name VARCHAR)")
con.execute("INSERT INTO src VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')")

# This reports success but data is NOT committed
result = con.execute(
    """FROM odbc_copy($odbc_conn,
        source_query=$src,
        dest_query=$dest,
        batch_size=1,
        max_records_in_transaction=10000)""",
    {
        "odbc_conn": odbc_conn,
        "src": "SELECT * FROM src",
        "dest": "EXECUTE PROCEDURE my_sp(?, ?)",
    },
).fetchall()

print(result)  # [(True, 3, ...)]  ← reports 3 rows, looks successful
# But data is NOT in the database!

What works

dest_query	Source type	SP param type	Result
UPDATE OR INSERT ... VALUES (?, ?)	INTEGER	n/a (DML)	Committed ✓
EXECUTE PROCEDURE sp(?, ?)	INTEGER	BIGINT	Committed ✓
EXECUTE PROCEDURE sp(?, ?)	VARCHAR	VARCHAR	Committed ✓
EXECUTE PROCEDURE sp(?, ?)	INTEGER	VARCHAR	Silent loss ✗

Key observations

1. Plain DML (no SP) always commits regardless of type mismatch.
2. SP with matching types commits correctly.
3. Only SP + INTEGER→VARCHAR mismatch causes the silent rollback.
4. odbc_copy returns (True, N, ...) indicating success and N rows processed.
5. No error or exception is raised.
6. Data loss may be partial (e.g., 11 out of 500 rows) or total (0 out of N).

Root Cause Hypothesis

When odbc_copy binds parameters to an ODBC statement for EXECUTE PROCEDURE, the C-type (SQL_C_SBIGINT or similar) doesn't match the Firebird SP's expected SQL type (SQL_VARCHAR). The Firebird ODBC driver appears to:

1. Accept the SQLExecute call without error.
2. Execute the SP body, which may internally fail on type coercion.
3. Signal a rollback condition that odbc_copy doesn't detect.

The odbc_query function (non-parameterized) does NOT have this issue because it sends inline string literals that Firebird parses correctly.

Workaround

Cast INTEGER columns to VARCHAR in the DuckDB source query before passing to odbc_copy:

-- Before (broken):
SELECT id, name FROM my_table

-- After (works):
SELECT id::VARCHAR, name FROM my_table

Suggested Fix

In odbc_copy's parameter binding path, when the ODBC driver reports the expected SQL parameter type (via SQLDescribeParam), convert the DuckDB value to match. Alternatively, detect the mismatch and raise an error rather than silently losing data.

[fdcastel]

@staticlibs I can put together a PR for this, but I’d appreciate some guidance.

• Would the “suggested fix” above be acceptable or useful (e.g. for other databases, too)?
• Or should this be addressed solely within the ODBC driver instead?

[staticlibs]

@fdcastel

Hi, thanks for the report! The situation with mismatched types is the following:

• we do not support mismatching types (there is no test coverage for them): it is expected that SQL cast (like in your example) is specified by user in all places where necessary
• we do not restrict mismatching types: different drivers handle them differently, we do not want to restrict the driver from accepting (and autoconverting) mismatched types
• we rely on driver error reporting: if driver has accepted the mismatched type - we expect it was handled in some consistent manner on driver side, when driver reports an error - we report it to user (and abort the copy)

Thus I would like to not introduce neither auto-conversion (easy to do this wrong, hard to test) nor strict type checks (breaks existing queries).

I wonder if in case of the Firebird driver, we can detect this this "Signal a rollback condition"? Then it will be better to have a special check (guarded by the Firebird driver name) for that condition and abort the copy in this case.

Firebird driver complicates it more by not having the Linux driver/dev env, so if you can develop a PR for this - that would be great!

Ideally this should be fixed on driver side, but even if that happens, I would think that old driver versions are not going away and we'd rather support them in some way.

[fdcastel]

Great! Message received, loud and clear.

Thanks for sharing the detailed insights and overall vision for the project: that’s exactly what I needed.

We’re currently undertaking a major overhaul for the next version of the Firebird driver: FirebirdSQL/firebird-odbc-driver#283

This update will address several issues, including the driver’s problems on Linux. It will take some time to complete, but once the new version is stable, I’ll make sure to update the tests here as well.