CHAR columns increase in length when re-using prepared statement

[tboothman]

If you re-use a prepared statement on a table with a character column the column length increases by 1 with each execution with ' ' making up the rest of the string. The problem does not occur if you create a new prepared statement for each query.

Here's some sample code:

create table IF NOT EXISTS t (
  a char(1) default 'A' not null
);
INSERT INTO t (a) VALUES ('A');

import * as Pool from "informix/lib/pool";
import {Informix} from "informix";
const query = `SELECT a FROM t LIMIT 1`;

let pool = new Pool({
    database: config.informix.database,
    username: config.informix.username,
    password: config.informix.password,
    max: 1
});

// Character length increases on each query
async function go() {
    let conn = await pool.acquire();
    pool.release(conn);
    let stmt = await conn.prepare(query, {reusable: true});
    while (true) {
        let cursor = await stmt.exec();
        let result = await cursor.fetchAll({close: true});
        console.log(result[0][0].length);
    }
}

// Character length remains 1 (new prepared statement per query)
async function go2() {
    let conn = await pool.acquire();
    pool.release(conn);
    while (true) {
        let stmt = await conn.prepare(query, {reusable: false});
        let cursor = await stmt.exec();
        let result = await cursor.fetchAll({close: true});
        console.log(result[0][0].length);
    }
}

// Same as 1 but using informix class
async function go3() {
    let ifx = new Informix({
        database: config.informix.database,
        username: config.informix.username,
        password: config.informix.password,
    });

    const stmt = await ifx.prepare(query);
    while (true) {
        let cursor = await stmt.exec();
        let result = await cursor.fetchAll({close: true});
        console.log(result[0][0].length);
    }
}

go3();

The result is something like:

1
2
3

and if you logged the actual string it'd be:

'A'
'A '
'A  '

I suspect it's something to do with https://github.com/nukedzn/node-informix/blob/master/src/ifx/ifx.cpp#L389 but I don't know how to compile the C to check

[tboothman]

Figured out how to compile it and I've tried changing the C to not modify sqlvar->sqllen but that didn't help .. I don't think I've quite understood what this is all doing.

[uatuko]

Fixed in v0.5.8.

[tboothman]

Thanks for looking at this, but now CHAR columns are all clipped to 1 character in length.

[uatuko]

Bummer, I knew there was something I missed. I'll take another look.

[uatuko]

Released v0.5.9 and this should be fixed now (hopefully 😬)

[jberry92]

If you have a column length of 5 and you insert some data with a length of 3 data chars it gets padded with spaces to match the length of the column. For example if you inserted the word how into a column with a length of 5 the code returns "how ".

[uatuko]

@jberry92, yes that is correct, this is because of how ESQL/C behaves (Informix CSDK v4.10 - The char data type).

I'd suggest trimming it within your code if you are sure you won't have any trailing spaces.

[tboothman]

That's great, thanks @uditha-atukorala - It looks to be all working now