SQL_BIGINT parameter: once a NULL has been bound with a character C type (SQL_C_DEFAULT or SQL_C_CHAR), later SQL_C_SBIGINT rebinds on that parameter write NULL

[singhpratech]

Summary

Observed: a prepared INSERT ... VALUES (?, ?) whose second parameter is SQL_BIGINT, executed once per row with SQLBindParameter called before each execute. When one row's NULL is bound with a character C type — SQL_C_DEFAULT (whose default for SQL_BIGINT is SQL_C_CHAR per the ODBC specification) or SQL_C_CHAR explicitly — with indicator SQL_NULL_DATA, every following row on that parameter is written as NULL, although each of those rows is rebound with SQL_C_SBIGINT and a real value, and every call returns SQL_SUCCESS. If the NULL row comes first, the whole column is NULL.

Binding the NULL row with SQL_C_SBIGINT gives the correct result, and the same pattern on an INTEGER parameter (SQL_C_DEFAULT NULL, then SQL_C_SLONG values) is also correct — so it is specific to a character-typed bind on a SQL_BIGINT parameter, and the later rebinds to SQL_C_SBIGINT are not taking effect. I have not looked at the driver source for the cause.

Environment

• Driver: firebird-odbc-driver v3.5.0-rc1, Linux x64 release tarball (libOdbcFb.so, 2026-04-11); SQL_DRIVER_NAME = OdbcFb, SQL_DRIVER_VER = 00.00.000. Same result with v3.0.1 (linux_libs.zip, Release_x86_64/libOdbcFb.so).
• Driver manager: unixODBC 2.3.12 (Ubuntu), narrow (ANSI) entry points
• Server: Firebird 5.0.4 (firebirdsql/firebird:5 container), database character set UTF8, client libfbclient.so.5.0.4 from the same image
• Client: the C programs below, built with gcc repro.c -lodbc; connection string Driver=/path/libOdbcFb.so;DBNAME=inet://host:3050//path/db.fdb;UID=...;PWD=...;CHARSET=UTF8;

Reproduction

INSERT INTO t (id INTEGER, v BIGINT) VALUES (?, ?) prepared once, executed four times, parameter 2 rebound before each execute. Output of the two programs below (verbatim, v3.5.0-rc1; v3.0.1 prints the same):

A: NULL row bound SQL_C_DEFAULT : expected v = 1, NULL, 3, 4; got v = 1 NULL NULL NULL
B: NULL row bound SQL_C_SBIGINT: expected v = 1, NULL, 3, 4; got v = 1 NULL 3 4

BIGINT, NULL@2 as SQL_C_DEFAULT, values SQL_C_SBIGINT (1,NULL,3,4) got v = 1 NULL NULL NULL
BIGINT, NULL@1 as SQL_C_DEFAULT, values SQL_C_SBIGINT (NULL,2,3,4) got v = NULL NULL NULL NULL
BIGINT, NULL@2 as SQL_C_CHAR explicitly, values SQL_C_SBIGINT       got v = 1 NULL NULL NULL
BIGINT, no NULL at all, values SQL_C_SBIGINT (control)              got v = 1 2 3 4
INTEGER, NULL@2 as SQL_C_DEFAULT, values SQL_C_SLONG (control)      got v = 1 NULL 3 4

Expected

1, NULL, 3, 4 (and NULL, 2, 3, 4) in every variant: a NULL indicator carries no data whatever C type is named, and the next SQLBindParameter names SQL_C_SBIGINT explicitly.

Workaround for applications

Bind NULLs for SQL_BIGINT parameters with SQL_C_SBIGINT rather than SQL_C_DEFAULT.

Program

repro_bigint_default_null.c (variants A/B)

// A SQL_BIGINT parameter bound with SQL_C_DEFAULT: after the first NULL, later values arrive as NULL.
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
static void diag(SQLSMALLINT ht, SQLHANDLE h, const char* where) {
  SQLCHAR st[6], msg[1024]; SQLINTEGER ne; SQLSMALLINT len; SQLSMALLINT i = 1;
  while (SQLGetDiagRec(ht, h, i++, st, &ne, msg, sizeof msg, &len) == SQL_SUCCESS)
    printf("  [%s] %s (%d) at %s\n", st, msg, (int)ne, where);
}
#define CHECK(ht, h, call) do { SQLRETURN _r = (call); if (!SQL_SUCCEEDED(_r)) { printf("FAILED rc=%d: %s\n", (int)_r, #call); diag(ht, h, #call); exit(1);} } while (0)
static SQLHENV env; static SQLHDBC dbc;
static void connect_db(void) {
  const char* cs = getenv("FB_CONN");
  if (!cs) { fprintf(stderr, "set FB_CONN=Driver=...;DBNAME=...;UID=...;PWD=...;CHARSET=UTF8;\n"); exit(2); }
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
  CHECK(SQL_HANDLE_DBC, dbc, SQLDriverConnect(dbc, NULL, (SQLCHAR*)cs, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT));
  SQLCHAR name[64], ver[64]; SQLSMALLINT l;
  SQLGetInfo(dbc, SQL_DRIVER_NAME, name, sizeof name, &l); SQLGetInfo(dbc, SQL_DRIVER_VER, ver, sizeof ver, &l);
  printf("driver %s %s, ", name, ver);
  SQLGetInfo(dbc, SQL_DBMS_NAME, name, sizeof name, &l); SQLGetInfo(dbc, SQL_DBMS_VER, ver, sizeof ver, &l);
  printf("server %s %s\n", name, ver);
}
static void exec_ignore(const char* sql) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); SQLExecDirect(s, (SQLCHAR*)sql, SQL_NTS); SQLFreeHandle(SQL_HANDLE_STMT, s); }
static void exec_ok(const char* sql) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); CHECK(SQL_HANDLE_STMT, s, SQLExecDirect(s, (SQLCHAR*)sql, SQL_NTS)); SQLFreeHandle(SQL_HANDLE_STMT, s); }
static void run(const char* label, int rebind_with_sbigint) {
  exec_ignore("DROP TABLE t_bigint_null"); exec_ok("CREATE TABLE t_bigint_null (id INTEGER, v BIGINT)");
  SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s);
  CHECK(SQL_HANDLE_STMT, s, SQLPrepare(s, (SQLCHAR*)"INSERT INTO t_bigint_null (id, v) VALUES (?, ?)", SQL_NTS));
  SQLINTEGER id; SQLBIGINT v; SQLLEN id_ind = 0, v_ind = 0;
  const long long vals[] = {1, -1 /*NULL*/, 3, 4};
  for (int i = 0; i < 4; i++) {
    id = i + 1; int is_null = (vals[i] == -1); v = vals[i]; v_ind = is_null ? SQL_NULL_DATA : 0;
    CHECK(SQL_HANDLE_STMT, s, SQLBindParameter(s, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &id, 0, &id_ind));
    // Both variants: the value rows use SQL_C_SBIGINT; the NULL row uses SQL_C_DEFAULT (variant A) or SQL_C_SBIGINT (variant B).
    SQLSMALLINT ctype = (is_null && !rebind_with_sbigint) ? SQL_C_DEFAULT : SQL_C_SBIGINT;
    CHECK(SQL_HANDLE_STMT, s, SQLBindParameter(s, 2, SQL_PARAM_INPUT, ctype, SQL_BIGINT, 0, 0, &v, 0, &v_ind));
    CHECK(SQL_HANDLE_STMT, s, SQLExecute(s));
  }
  SQLFreeHandle(SQL_HANDLE_STMT, s);
  SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s);
  CHECK(SQL_HANDLE_STMT, s, SQLExecDirect(s, (SQLCHAR*)"SELECT id, v FROM t_bigint_null ORDER BY id", SQL_NTS));
  printf("%s: expected v = 1, NULL, 3, 4; got v =", label);
  while (SQLFetch(s) == SQL_SUCCESS) { SQLLEN ind; SQLBIGINT got; SQLGetData(s, 2, SQL_C_SBIGINT, &got, 0, &ind); if (ind == SQL_NULL_DATA) printf(" NULL"); else printf(" %lld", (long long)got); }
  printf("\n"); SQLFreeHandle(SQL_HANDLE_STMT, s);
}
int main(void) { connect_db(); SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
  run("A: NULL row bound SQL_C_DEFAULT ", 0);
  run("B: NULL row bound SQL_C_SBIGINT", 1);
  return 0; }

repro_bigint_null_variants.c (NULL first, explicit SQL_C_CHAR, INTEGER control)

// Variants of bigint_null.c: NULL first; INTEGER column with SQL_C_DEFAULT NULL (control); BIGINT with NULL bound SQL_C_CHAR explicitly.
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
static void diag(SQLSMALLINT ht, SQLHANDLE h, const char* where) {
  SQLCHAR st[6], msg[1024]; SQLINTEGER ne; SQLSMALLINT len; SQLSMALLINT i = 1;
  while (SQLGetDiagRec(ht, h, i++, st, &ne, msg, sizeof msg, &len) == SQL_SUCCESS)
    printf("  [%s] %s (%d) at %s\n", st, msg, (int)ne, where);
}
#define CHECK(ht, h, call) do { SQLRETURN _r = (call); if (!SQL_SUCCEEDED(_r)) { printf("FAILED rc=%d: %s\n", (int)_r, #call); diag(ht, h, #call); exit(1);} } while (0)
static SQLHENV env; static SQLHDBC dbc;
static void connect_db(void) {
  const char* cs = getenv("FB_CONN");
  if (!cs) { fprintf(stderr, "set FB_CONN=Driver=...;DBNAME=...;UID=...;PWD=...;CHARSET=UTF8;\n"); exit(2); }
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
  CHECK(SQL_HANDLE_DBC, dbc, SQLDriverConnect(dbc, NULL, (SQLCHAR*)cs, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT));
  SQLCHAR name[64], ver[64]; SQLSMALLINT l;
  SQLGetInfo(dbc, SQL_DRIVER_NAME, name, sizeof name, &l); SQLGetInfo(dbc, SQL_DRIVER_VER, ver, sizeof ver, &l);
  printf("driver %s %s, ", name, ver);
  SQLGetInfo(dbc, SQL_DBMS_NAME, name, sizeof name, &l); SQLGetInfo(dbc, SQL_DBMS_VER, ver, sizeof ver, &l);
  printf("server %s %s\n", name, ver);
}
static void exec_ignore(const char* sql) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); SQLExecDirect(s, (SQLCHAR*)sql, SQL_NTS); SQLFreeHandle(SQL_HANDLE_STMT, s); }
static void exec_ok(const char* sql) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); CHECK(SQL_HANDLE_STMT, s, SQLExecDirect(s, (SQLCHAR*)sql, SQL_NTS)); SQLFreeHandle(SQL_HANDLE_STMT, s); }
static void run(const char* label, const char* coltype, SQLSMALLINT sqltype, SQLSMALLINT valctype, SQLSMALLINT nullctype, const long long* vals, int n) {
  exec_ignore("DROP TABLE t_bn2"); char sql[128]; snprintf(sql, sizeof sql, "CREATE TABLE t_bn2 (id INTEGER, v %s)", coltype); exec_ok(sql);
  SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s);
  CHECK(SQL_HANDLE_STMT, s, SQLPrepare(s, (SQLCHAR*)"INSERT INTO t_bn2 (id, v) VALUES (?, ?)", SQL_NTS));
  SQLINTEGER id; SQLBIGINT v8; SQLINTEGER v4; SQLLEN id_ind = 0, v_ind;
  for (int i = 0; i < n; i++) { id = i + 1; int is_null = vals[i] == -1; v8 = vals[i]; v4 = (SQLINTEGER)vals[i]; v_ind = is_null ? SQL_NULL_DATA : 0;
    CHECK(SQL_HANDLE_STMT, s, SQLBindParameter(s, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &id, 0, &id_ind));
    CHECK(SQL_HANDLE_STMT, s, SQLBindParameter(s, 2, SQL_PARAM_INPUT, is_null ? nullctype : valctype, sqltype, 0, 0, valctype == SQL_C_SBIGINT ? (void*)&v8 : (void*)&v4, 0, &v_ind));
    CHECK(SQL_HANDLE_STMT, s, SQLExecute(s)); }
  SQLFreeHandle(SQL_HANDLE_STMT, s); SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s);
  CHECK(SQL_HANDLE_STMT, s, SQLExecDirect(s, (SQLCHAR*)"SELECT v FROM t_bn2 ORDER BY id", SQL_NTS));
  printf("%-58s got v =", label);
  while (SQLFetch(s) == SQL_SUCCESS) { SQLLEN ind; SQLBIGINT g; SQLGetData(s, 1, SQL_C_SBIGINT, &g, 0, &ind); if (ind == SQL_NULL_DATA) printf(" NULL"); else printf(" %lld", (long long)g); }
  printf("\n"); SQLFreeHandle(SQL_HANDLE_STMT, s);
}
int main(void) { connect_db(); SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
  const long long a[] = {1, -1, 3, 4}, b[] = {-1, 2, 3, 4}, c[] = {1, 2, 3, 4};
  run("BIGINT, NULL@2 as SQL_C_DEFAULT, values SQL_C_SBIGINT (1,NULL,3,4)", "BIGINT", SQL_BIGINT, SQL_C_SBIGINT, SQL_C_DEFAULT, a, 4);
  run("BIGINT, NULL@1 as SQL_C_DEFAULT, values SQL_C_SBIGINT (NULL,2,3,4)", "BIGINT", SQL_BIGINT, SQL_C_SBIGINT, SQL_C_DEFAULT, b, 4);
  run("BIGINT, NULL@2 as SQL_C_CHAR explicitly, values SQL_C_SBIGINT", "BIGINT", SQL_BIGINT, SQL_C_SBIGINT, SQL_C_CHAR, a, 4);
  run("BIGINT, no NULL at all, values SQL_C_SBIGINT (control)", "BIGINT", SQL_BIGINT, SQL_C_SBIGINT, SQL_C_SBIGINT, c, 4);
  run("INTEGER, NULL@2 as SQL_C_DEFAULT, values SQL_C_SLONG (control)", "INTEGER", SQL_INTEGER, SQL_C_SLONG, SQL_C_DEFAULT, a, 4);
  return 0; }

---

Found while running Firebird through adbcBridge (an ADBC-over-ODBC driver; its Firebird entry and the workaround it uses are in docs/COMPATIBILITY.md). The reproduction above is pure ODBC and does not involve it. Happy to test a fix branch against the same setup.