CLI0111E on fetch of ordinary DECFLOAT values: bound-column buffer sized from precision+3, not display size

[bjmacdonald]

Title: CLI0111E on fetch of ordinary DECFLOAT values — bound-column buffer is sized from column precision (+3), not display size

• Operating System Name: Windows 11 Pro x64 (the code path is not platform-specific; also observed from a Linux x86_64 container against the same server)
• db2level output from clidriver if in use: bundled clidriver, ibm_db.client_info().DRIVER_VER = 12.01.0000
• Target Db2 Server Version: Db2 LUW 12.1.5 (server_info().DBMS_VER = 12.01.0500)
• Python Version: 3.14
• ibm_db version: 3.2.9 (wheel). The same expressions are present in v3.3.0 / current master — see Analysis.
• IBM_DB_HOME / PATH / LIB: not set (wheel-bundled clidriver)

Summary

fetch_tuple / fetch_assoc / fetch_both / fetchall (every bound-column fetch path, and therefore everything ibm_db_dbi and SQLAlchemy use) fail with

Exception: Fetch Failure: [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003 SQLCODE=-99999

for perfectly valid DECFLOAT(16) values whose character rendering is longer than 18 characters. The value is in range; the driver's SQLBindCol buffer is too small. ibm_db.result() returns the same value correctly because it sizes its buffer differently.

Test script to reproduce

No table needed — a VALUES statement is enough. Run against any Db2 LUW database:

import ibm_db
conn = ibm_db.connect("DATABASE=...;HOSTNAME=...;PORT=...;PROTOCOL=TCPIP;UID=...;PWD=...;", "", "")
for lit in ("0.005202572", "0.00520257200000000", "-0.001234567890123456"):
    sql = f"values cast('{lit}' as decfloat(16))"
    stmt = ibm_db.exec_immediate(conn, sql)
    try:
        print(f"{lit!r:26} len={len(lit):2} fetch_tuple -> {ibm_db.fetch_tuple(stmt)[0]!r}")
    except Exception as e:
        print(f"{lit!r:26} len={len(lit):2} fetch_tuple -> {e}")
    stmt = ibm_db.exec_immediate(conn, sql)
    ibm_db.fetch_row(stmt)
    print(f"{'':26}        result()     -> {ibm_db.result(stmt, 0)!r}")

Output (ibm_db 3.2.9, Python 3.14, Windows 11, Db2 12.1.5):

'0.005202572'              len=11 fetch_tuple -> '0.005202572'
                                  result()     -> '0.005202572'
'0.00520257200000000'      len=19 fetch_tuple -> Fetch Failure: [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003 SQLCODE=-99999
                                  result()     -> '0.00520257200000000'
'-0.001234567890123456'    len=21 fetch_tuple -> Fetch Failure: [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003 SQLCODE=-99999
                                  result()     -> '-0.001234567890123456'

Steps to Reproduce:

1. Run the script above with a valid connection string.
2. Observe that any DECFLOAT(16) value rendering to more than 18 characters fails the bound-column fetch but is returned correctly by ibm_db.result().
3. The Db2 CLP (db2 "values cast('-0.001234567890123456' as decfloat(16))") returns the value.

The failing renderings are ordinary data: a stored value with trailing zeros (0.00520257200000000, 19 chars), or a normalized 16-significant-digit value with a small magnitude (-0.001234567890123456, 21 chars). Both are produced by everyday arithmetic (qty * price). For DECFLOAT(34) the same defect leaves a 36-character limit against a 42-character display size.

Analysis

In ibm_db.c the bound-column buffer for a SQL_DECFLOAT column is sized from SQLDescribeCol's column size — which for DECFLOAT is the precision (16 or 34) — plus 3:

_python_ibm_db_bind_column_helper (master, ~line 1922):

        case SQL_BIGINT:
        case SQL_DECFLOAT:
            ...
            in_length = stmt_res->column_info[i].size + 3;
            row_data->str_val = (SQLCHAR *)ALLOC_N(char, in_length);
            ...
            rc = SQLBindCol((SQLHSTMT)stmt_res->hstmt, (SQLUSMALLINT)(i + 1),
                            SQL_C_CHAR, row_data->str_val, in_length,
                            (SQLINTEGER *)(&stmt_res->row_data[i].out_length));

and the same expression in the new SQLFetchScroll rowset path added in v3.3.0, _python_ibm_db_bind_rowset_columns (master, ~line 2378):

        case SQL_BIGINT:
        case SQL_DECFLOAT:
            in_length = stmt_res->column_info[i].size + 3;
            bufs[i].elem_size = in_length; bufs[i].ctype = SQL_C_CHAR;

So DECFLOAT(16) gets a 19-byte buffer = 18 characters of value (the NUL terminator takes one), and DECFLOAT(34) gets 37 bytes = 36 characters. The CLI display size for DECFLOAT (SQL_DESC_DISPLAY_SIZE, which db2cli uses for this) is precision + 8 — 24 and 42 — to allow for the sign, the decimal point and an exponent. Any value rendering longer than the buffer fails the fetch with CLI0111E.

ibm_db.result() (_python_ibm_db_result, ~line 14488) already gets this right:

            if (column_type == SQL_DECFLOAT)
            {
                in_length = MAX_DECFLOAT_LENGTH;   /* 44 */
            }

which is why the same value fetches through result() and not through fetch_tuple.

History: #720 reported the same CLI0111E on a DECFLOAT(34) PERCENT_RANK() column rendering to 36 characters; the follow-up commit changed the sizing to size + 3, which is exactly enough for that one case (37 bytes ≥ 36 + NUL) and leaves the general defect in place at both precisions. #795 was the result() side on Windows, separately fixed.

Suggested fix

Either of:

1. Size the bound buffer from SQL_DESC_DISPLAY_SIZE (+1 for the terminator), as db2cli does, or simply use MAX_DECFLOAT_LENGTH for SQL_DECFLOAT in both bind sites, matching ibm_db.result().
2. Bind SQL_DECFLOAT columns as SQL_C_DECIMAL64 / SQL_C_DECIMAL128 (8/16 bytes, declared in the clidriver headers the wheel ships) and convert to text/Decimal in the driver — no character round-trip and no width guess at all.

Option 1 is a two-line change and is consistent with the existing result() code.

Workarounds in use

• On the write side, store normalize_decfloat(cast(? as decfloat(16))) so renderings stay short (does not help values that are genuinely 16 significant digits with a small exponent).
• On the read side, select varchar(col) instead of col, so the server renders the digits and the driver binds a character column.

Both are needed on every decfloat column in every query, which is why a driver fix would be welcome.

[Earammak]

@bjmacdonald , Thank you so much for the detailed info. I will work on this on this and provide a fix.

Thank you