pg.ddx.io  GitHub: postgresql-interfaces/psqlodbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
From: davecramer (@davecramer) <noreply+davecramer@github.com>
To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com>
Subject: [postgresql-interfaces/psqlodbc] PR #238: Don't return negative sizes from SQLColumns (#228)
Date: Thu, 05 Nov 2026 22:06:45 +0000
Message-ID: <gh-postgresql-interfaces-psqlodbc-238@github.com> (raw)

Fixes #228.

`SQLColumns` returned negative values in three size columns:

| Column | Types | Value |
| --- | --- | --- |
| `CHAR_OCTET_LENGTH` (16) | not character or binary: integer, numeric, timestamp, boolean, … | -1 |
| `COLUMN_SIZE` (7), `BUFFER_LENGTH` (8), `CHAR_OCTET_LENGTH` (16) | bytea | `SQL_NO_TOTAL` (-4) |

MSDASQL binds these columns as `SQL_C_ULONG`. Before 18.00.0003 the values wrapped to 4294967295 and 4294967292. Since the range check from #210, the fetch fails with `22003 Numeric value out of range`, so ADO `OpenSchema(adSchemaColumns)` and other MSDASQL column schema rowsets fail for any table with such a column.

## Change

`PGAPI_Columns` now sets the three columns through a new `set_columns_size()`:

- **-1 becomes NULL.** ODBC requires `CHAR_OCTET_LENGTH` to be NULL for data types other than character and binary.
- **`SQL_NO_TOTAL` becomes `INT_MAX`,** as #218 does for `COLUMN_SIZE` in `SQLGetTypeInfo`.

The reporter's suggested fix covered only `CHAR_OCTET_LENGTH`. Bytea also had -4 in `COLUMN_SIZE` and `BUFFER_LENGTH`, so tables with a bytea column could still fail if MSDASQL binds those columns unsigned too, as it did for `COLUMN_SIZE` in `SQLGetTypeInfo` (#215).

With `BoolsAsChar=1`, boolean is reported as `SQL_VARCHAR` and its `CHAR_OCTET_LENGTH` is now NULL rather than a length. That's enough for MSDASQL. Giving it a real octet length would be a separate change in `pgtype_attr_transfer_octet_length()`.

## Test

The new `columns-sizes` test binds the three columns as `SQL_C_ULONG`, the way MSDASQL does, for an integer, varchar, bytea and boolean column (with `BoolsAsChar=1`). It prints the exact sizes for integer and bytea. For varchar and boolean it prints only whether a value was returned, because those sizes depend on the platform's wide character size.

- **Without the fix:** the first fetch fails with `22003 Numeric value out of range`.
- **With the fix:**

  ```
  i: COLUMN_SIZE=10 BUFFER_LENGTH=4 CHAR_OCTET_LENGTH=NULL
  v: COLUMN_SIZE=set BUFFER_LENGTH=set CHAR_OCTET_LENGTH=set
  b: COLUMN_SIZE=INT_MAX BUFFER_LENGTH=INT_MAX CHAR_OCTET_LENGTH=INT_MAX
  f: COLUMN_SIZE=set BUFFER_LENGTH=set CHAR_OCTET_LENGTH=NULL
  ```

- **Full suite:** passes (69 tests), on macOS against PostgreSQL 18.

The ADO and MSDASQL failure itself needs Windows, so I haven't reproduced it directly. CI runs the new test on Windows.

Assisted-By: Claude


inline patch 1: info.c, test/expected/columns-sizes.out, test/src/columns-sizes-test.c and 1 more (+115/-4 lines) download inline-1.patchdiff --git a/info.c b/info.c index 6c7eb350..d5adb8a4 100644 --- a/info.c +++ b/info.c @@ -2328,6 +2328,21 @@ add_tuple_for_oid_or_xmin(TupleField *tuple, int ordinal, const char *colname, O set_tuplefield_int4(&tuple[COLUMNS_TABLE_INFO], table_info); } +/* + * Set COLUMN_SIZE, BUFFER_LENGTH or CHAR_OCTET_LENGTH in a SQLColumns row. + * These are sizes, so SQL_NO_TOTAL (a length/indicator value) is not valid + * there, and applications such as MSDASQL bind them as SQL_C_ULONG, where a + * negative value fails with 22003. -1 means the size does not apply to the + * type and becomes NULL; SQL_NO_TOTAL becomes the largest representable size. + */ +static void +set_columns_size(TupleField *field, Int4 size) +{ + if (SQL_NO_TOTAL == size) + size = INT_MAX; + set_nullfield_int4(field, size); +} + RETCODE SQL_API PGAPI_Columns(HSTMT hstmt, const SQLCHAR * szTableQualifier, /* OA X*/ @@ -2847,8 +2862,8 @@ MYLOG(0, " and the data=%s\n", attdef); if (mod_length >= 4) mod_length -= 4; } - set_tuplefield_int4(&tuple[COLUMNS_PRECISION], PGTYPE_ATTR_COLUMN_SIZE(conn, field_type, mod_length)); - set_tuplefield_int4(&tuple[COLUMNS_LENGTH], PGTYPE_ATTR_BUFFER_LENGTH(conn, field_type, mod_length)); + set_columns_size(&tuple[COLUMNS_PRECISION], PGTYPE_ATTR_COLUMN_SIZE(conn, field_type, mod_length)); + set_columns_size(&tuple[COLUMNS_LENGTH], PGTYPE_ATTR_BUFFER_LENGTH(conn, field_type, mod_length)); set_tuplefield_int4(&tuple[COLUMNS_DISPLAY_SIZE], PGTYPE_ATTR_DISPLAY_SIZE(conn, field_type, mod_length)); set_nullfield_int2(&tuple[COLUMNS_SCALE], PGTYPE_ATTR_DECIMAL_DIGITS(conn, field_type, mod_length)); @@ -2866,7 +2881,7 @@ MYLOG(0, " and the data=%s\n", attdef); set_tuplefield_string(&tuple[COLUMNS_COLUMN_DEF], attdef); set_tuplefield_int2(&tuple[COLUMNS_SQL_DATA_TYPE], concise_type); set_nullfield_int2(&tuple[COLUMNS_SQL_DATETIME_SUB], pgtype_attr_to_datetime_sub(conn, field_type, mod_length)); - set_tuplefield_int4(&tuple[COLUMNS_CHAR_OCTET_LENGTH], PGTYPE_ATTR_TRANSFER_OCTET_LENGTH(conn, field_type, mod_length)); + set_columns_size(&tuple[COLUMNS_CHAR_OCTET_LENGTH], PGTYPE_ATTR_TRANSFER_OCTET_LENGTH(conn, field_type, mod_length)); set_tuplefield_int4(&tuple[COLUMNS_ORDINAL_POSITION], ordinal); set_tuplefield_null(&tuple[COLUMNS_IS_NULLABLE]); set_tuplefield_int4(&tuple[COLUMNS_FIELD_TYPE], field_type); diff --git a/test/expected/columns-sizes.out b/test/expected/columns-sizes.out new file mode 100644 index 00000000..efadd21c --- /dev/null +++ b/test/expected/columns-sizes.out @@ -0,0 +1,6 @@ +connected +i: COLUMN_SIZE=10 BUFFER_LENGTH=4 CHAR_OCTET_LENGTH=NULL +v: COLUMN_SIZE=set BUFFER_LENGTH=set CHAR_OCTET_LENGTH=set +b: COLUMN_SIZE=INT_MAX BUFFER_LENGTH=INT_MAX CHAR_OCTET_LENGTH=INT_MAX +f: COLUMN_SIZE=set BUFFER_LENGTH=set CHAR_OCTET_LENGTH=NULL +disconnecting diff --git a/test/src/columns-sizes-test.c b/test/src/columns-sizes-test.c new file mode 100644 index 00000000..dae62185 --- /dev/null +++ b/test/src/columns-sizes-test.c @@ -0,0 +1,89 @@ +/* + * Test that SQLColumns never returns a negative COLUMN_SIZE, BUFFER_LENGTH or + * CHAR_OCTET_LENGTH. MSDASQL binds them as SQL_C_ULONG, where a negative + * value fails with 22003 (issue #228). CHAR_OCTET_LENGTH is NULL for types + * that are not character or binary, and SQL_NO_TOTAL is reported as INT_MAX. + * + * The sizes of the character columns depend on the platform and the driver, + * so for those the test only shows whether a value was returned. + */ +#include <limits.h> +#include <stdio.h> +#include <stdlib.h> +#include <string.h> + +#include "common.h" + +static void +show_size(const char *label, SQLUINTEGER value, SQLLEN ind, int exact) +{ + if (SQL_NULL_DATA == ind) + printf(" %s=NULL", label); + else if (!exact) + printf(" %s=%s", label, value > 0 ? "set" : "zero"); + else if (INT_MAX == value) + printf(" %s=INT_MAX", label); + else + printf(" %s=%lu", label, (unsigned long) value); +} + +int main(int argc, char **argv) +{ + SQLRETURN rc; + HSTMT hstmt = SQL_NULL_HSTMT; + SQLCHAR name[129]; + SQLLEN nameInd, sizeInd, lengthInd, octetInd; + SQLUINTEGER size, length, octet; + int exact; + + /* boolean is reported as a character type with BoolsAsChar=1 */ + test_connect_ext("BoolsAsChar=1"); + + rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt); + CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn); + + rc = SQLExecDirect(hstmt, (SQLCHAR *) "DROP TABLE IF EXISTS columns_sizes", SQL_NTS); + CHECK_STMT_RESULT(rc, "DROP TABLE failed", hstmt); + rc = SQLExecDirect(hstmt, (SQLCHAR *) + "CREATE TABLE columns_sizes " + "(i integer, v varchar(10), b bytea, f boolean)", SQL_NTS); + CHECK_STMT_RESULT(rc, "CREATE TABLE failed", hstmt); + SQLFreeStmt(hstmt, SQL_CLOSE); + + rc = SQLColumns(hstmt, NULL, 0, NULL, 0, + (SQLCHAR *) "columns_sizes", SQL_NTS, NULL, 0); + CHECK_STMT_RESULT(rc, "SQLColumns failed", hstmt); + + SQLBindCol(hstmt, 4, SQL_C_CHAR, name, sizeof(name), &nameInd); + SQLBindCol(hstmt, 7, SQL_C_ULONG, &size, 0, &sizeInd); + SQLBindCol(hstmt, 8, SQL_C_ULONG, &length, 0, &lengthInd); + SQLBindCol(hstmt, 16, SQL_C_ULONG, &octet, 0, &octetInd); + + while ((rc = SQLFetch(hstmt)) != SQL_NO_DATA) + { + if (!SQL_SUCCEEDED(rc)) + { + print_diag("SQLFetch failed", SQL_HANDLE_STMT, hstmt); + exit(1); + } + /* integer and bytea sizes are the same on every platform */ + exact = (0 == strcmp((char *) name, "i") || 0 == strcmp((char *) name, "b")); + printf("%s:", name); + show_size("COLUMN_SIZE", size, sizeInd, exact); + show_size("BUFFER_LENGTH", length, lengthInd, exact); + show_size("CHAR_OCTET_LENGTH", octet, octetInd, exact); + printf("\n"); + } + + SQLFreeStmt(hstmt, SQL_CLOSE); + SQLFreeStmt(hstmt, SQL_UNBIND); + rc = SQLExecDirect(hstmt, (SQLCHAR *) "DROP TABLE columns_sizes", SQL_NTS); + CHECK_STMT_RESULT(rc, "DROP TABLE failed", hstmt); + + rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); + CHECK_STMT_RESULT(rc, "SQLFreeHandle failed", hstmt); + + test_disconnect(); + + return 0; +} diff --git a/test/tests b/test/tests index fd0fcdac..f89eb541 100644 --- a/test/tests +++ b/test/tests @@ -74,4 +74,5 @@ TESTBINS = exe/connect-test \ exe/dbms-version-test \ exe/surrogate-pair-test \ exe/many-params-test \ - exe/keyset-overflow-test + exe/keyset-overflow-test \ + exe/columns-sizes-test

Message-ID: <gh-postgresql-interfaces-psqlodbc-238@github.com>
Permalink:  ../gh-postgresql-interfaces-psqlodbc-238@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