pg.ddx.io  GitHub: postgresql-interfaces/psqlodbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
From: Joungsoo-Yoon (@Joungsoo-Yoon) <noreply+Joungsoo-Yoon@github.com>
To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com>
Subject: [postgresql-interfaces/psqlodbc] issue #228: SQLColumns returns CHAR_OCTET_LENGTH -1/-4 instead of NULL; since 18.00.0003 MSDASQL column schema rowsets fail with 22003
Date: Thu, 29 Oct 2026 07:37:00 +0000
Message-ID: <gh-postgresql-interfaces-psqlodbc-228@github.com> (raw)

**What happens**

Since 18.00.0003, reading column metadata through the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) fails for every table that has a non-character column. ADO `OpenSchema(adSchemaColumns)` fails with "Object or provider is not capable of performing requested operation.", and an ATL `CColumns` rowset (`IDBSchemaRowset`, `DBSCHEMA_COLUMNS`) fails with E_FAIL and the error record `22003 Numeric value out of range`. Tables with only character columns work, and 18.00.0002 and earlier work.

It is the same mechanism as #215, in SQLColumns instead of SQLGetTypeInfo: MSDASQL binds column 16 (CHAR_OCTET_LENGTH) as SQL_C_ULONG, the driver puts a negative value there, and the range check from #210 now rejects it.

**Where it comes from**

The ODBC reference for SQLColumns says CHAR_OCTET_LENGTH is "The maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL."

`PGAPI_Columns` in info.c sets it with

```c
set_tuplefield_int4(&tuple[COLUMNS_CHAR_OCTET_LENGTH], PGTYPE_ATTR_TRANSFER_OCTET_LENGTH(conn, field_type, mod_length));
```

`pgtype_attr_transfer_octet_length()` in pgtypes.c ends with `return -1;` for every type outside its switch (integer, numeric, timestamp and so on). For bytea it returns the column size, which is SQL_NO_TOTAL (-4). Before 18.00.0003 these values wrapped to 4294967295 and 4294967292 when read as SQL_C_ULONG, so the wrong value is older than #210; #210 turned it into an error.

With BoolsAsChar=1, boolean is reported as SQL_VARCHAR and also gets CHAR_OCTET_LENGTH = -1.

**Environment**

- Fails: psqlODBC 18.00.0003, 18.00.0004 and 18.00.0005 x64, Unicode (podbc35w.dll); the 18.00.0005 ANSI driver (podbc30a.dll) fails the same way
- Works: 18.00.0000, 18.00.0001, 18.00.0002 and 17.00.0010
- Windows 11, Windows ODBC Driver Manager, MSDASQL
- PostgreSQL 16.3
- Default DSN settings. UseDeclareFetch=1, Parse=1, UnknownSizes=2, TextAsLongVarchar=0, LFConversion=0 and BI=1 don't change the result.

**Standalone reproduction**

```sql
CREATE TABLE public.t_octet (i integer, v varchar(10), b bytea);
```

```c
/* Shows CHAR_OCTET_LENGTH from SQLColumns for each column of one table:
 * pass 1 reads it with SQLGetData(SQL_C_SLONG) and prints the value and the
 * indicator; pass 2 binds it as SQL_C_ULONG, the way MSDASQL does, and
 * prints the SQLFetch result.
 *   octet_repro.exe "<connection string>" <schema> <table>
 */
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

static void diag(SQLHSTMT stmt)
{
    SQLCHAR state[6], msg[512];
    SQLINTEGER native;
    SQLSMALLINT len;
    SQLSMALLINT i;
    for (i = 1; SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, state, &native, msg, sizeof(msg), &len) == SQL_SUCCESS; i++)
        printf("    SQLSTATE=%s native=%ld %s\n", state, (long)native, msg);
}

int main(int argc, char **argv)
{
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN rc;
    SQLCHAR name[129];
    SQLLEN nameInd, ind;
    SQLINTEGER slong;
    SQLUINTEGER ulong;

    if (argc < 4) {
        printf("usage: octet_repro \"<connection string>\" <schema> <table>\n");
        return 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);
    rc = SQLDriverConnect(dbc, NULL, (SQLCHAR *)argv[1], SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    if (!SQL_SUCCEEDED(rc)) {
        printf("connect failed\n");
        return 1;
    }
    {
        SQLCHAR ver[64];
        SQLSMALLINT len;
        SQLGetInfo(dbc, SQL_DRIVER_VER, ver, sizeof(ver), &len);
        printf("driver %s\n", ver);
    }

    printf("pass 1: SQLGetData(16, SQL_C_SLONG)\n");
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    SQLColumns(stmt, NULL, 0, (SQLCHAR *)argv[2], SQL_NTS, (SQLCHAR *)argv[3], SQL_NTS, NULL, 0);
    while (SQL_SUCCEEDED(SQLFetch(stmt))) {
        SQLSMALLINT type;
        SQLGetData(stmt, 4, SQL_C_CHAR, name, sizeof(name), &nameInd);
        SQLGetData(stmt, 5, SQL_C_SSHORT, &type, 0, NULL);
        rc = SQLGetData(stmt, 16, SQL_C_SLONG, &slong, 0, &ind);
        if (!SQL_SUCCEEDED(rc)) {
            printf("  %-6s DATA_TYPE=%4d SQLGetData failed rc=%d\n", name, type, rc);
            diag(stmt);
        } else if (ind == SQL_NULL_DATA)
            printf("  %-6s DATA_TYPE=%4d CHAR_OCTET_LENGTH=NULL\n", name, type);
        else
            printf("  %-6s DATA_TYPE=%4d CHAR_OCTET_LENGTH=%ld\n", name, type, (long)slong);
    }
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    printf("pass 2: SQLBindCol(16, SQL_C_ULONG) + SQLFetch\n");
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    SQLColumns(stmt, NULL, 0, (SQLCHAR *)argv[2], SQL_NTS, (SQLCHAR *)argv[3], SQL_NTS, NULL, 0);
    SQLBindCol(stmt, 4, SQL_C_CHAR, name, sizeof(name), &nameInd);
    SQLBindCol(stmt, 16, SQL_C_ULONG, &ulong, 0, &ind);
    for (;;) {
        rc = SQLFetch(stmt);
        if (rc == SQL_NO_DATA)
            break;
        if (!SQL_SUCCEEDED(rc)) {
            printf("  %-6s SQLFetch failed rc=%d\n", name, rc);
            diag(stmt);
            continue;
        }
        if (ind == SQL_NULL_DATA)
            printf("  %-6s CHAR_OCTET_LENGTH=NULL\n", name);
        else
            printf("  %-6s CHAR_OCTET_LENGTH=%lu\n", name, (unsigned long)ulong);
    }
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
    return 0;
}
```

Build in an x64 Native Tools prompt and run:

```
cl /W3 octet_repro.c /link odbc32.lib
octet_repro.exe "DSN=<psqlODBC DSN>;UID=<user>;PWD=<password>" public t_octet
```

Output with 18.00.0005:

```
driver 18.00.0005
pass 1: SQLGetData(16, SQL_C_SLONG)
  i      DATA_TYPE=   4 CHAR_OCTET_LENGTH=-1
  v      DATA_TYPE=  12 CHAR_OCTET_LENGTH=20
  b      DATA_TYPE=  -4 CHAR_OCTET_LENGTH=-4
pass 2: SQLBindCol(16, SQL_C_ULONG) + SQLFetch
  i      SQLFetch failed rc=-1
    SQLSTATE=22003 native=23 Numeric value out of range
  v      CHAR_OCTET_LENGTH=20
  b      SQLFetch failed rc=-1
    SQLSTATE=22003 native=23 Numeric value out of range
```

Output with 18.00.0002:

```
driver 18.00.0002
pass 1: SQLGetData(16, SQL_C_SLONG)
  i      DATA_TYPE=   4 CHAR_OCTET_LENGTH=-1
  v      DATA_TYPE=  12 CHAR_OCTET_LENGTH=20
  b      DATA_TYPE=  -4 CHAR_OCTET_LENGTH=-4
pass 2: SQLBindCol(16, SQL_C_ULONG) + SQLFetch
  i      CHAR_OCTET_LENGTH=4294967295
  v      CHAR_OCTET_LENGTH=20
  b      CHAR_OCTET_LENGTH=4294967292
```

The same table through ADO and MSDASQL, in 64-bit PowerShell:

```powershell
$c = New-Object -ComObject ADODB.Connection
$c.Open("Provider=MSDASQL;DSN=<psqlODBC DSN>;UID=<user>;PWD=<password>")
$rs = $c.OpenSchema(4, [object[]]@($null, "public", "t_octet"))   # 4 = adSchemaColumns
```

With 18.00.0005, `OpenSchema` throws "Object or provider is not capable of performing requested operation."; with 18.00.0002 it returns the three columns.

**Suggested fix**

Return NULL for the types the driver has no octet length for, and INT_MAX for SQL_NO_TOTAL, as #218 does for COLUMN_SIZE:

```c
Int4 octet_length = PGTYPE_ATTR_TRANSFER_OCTET_LENGTH(conn, field_type, mod_length);

if (SQL_NO_TOTAL == octet_length)
    octet_length = INT_MAX;
set_nullfield_int4(&tuple[COLUMNS_CHAR_OCTET_LENGTH], octet_length);
```

Unlike COLUMN_SIZE in #218, NULL works for this column through MSDASQL: it binds column 16 with an indicator (a non-null pcb in the mylog). With RowVersioning=1, the xmin row that `add_tuple_for_oid_or_xmin()` adds already has a NULL CHAR_OCTET_LENGTH, and on 18.00.0005 that row passes through MSDASQL and ADO without an error.


Message-ID: <gh-postgresql-interfaces-psqlodbc-228@github.com>
Permalink:  ../gh-postgresql-interfaces-psqlodbc-228@github.com/

 · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox