If `<sql> RETURNING COL` fails for some reason (e.g., PK, FK, DEADLOCK conflict), any other query will throw an uncaught error

[mreis1]

node 16.20.1 node-firebird 1.1.8 from npm

if <sql> RETURNING COL fails for some reason (i.g Due to PK, FK , DEADLOCK, or any other error) the active transaction can no longer be used to execute other sql statements.

Note: This problem doesn't occur if we RETURNING COL is removed from the queries. Also, the example below is a easiest way to reproduce the problem. It doesn't describe a useful case that justifies the use of RETURNING COL.

Code sample:

import * as fb from 'node-firebird';

const opts: fb.Options = {
    host: '127.0.0.1',     // default
    port: 3050,            // default
    user: 'SYSDBA',        // default
    password: 'masterkey'
    database: 'YOUR_DB'
}

//     `
//     CREATE TABLE CLIENTS (
//         CLI_ID Integer NOT NULL,
//         CLI_FIRST_NAME varchar(50) NOT NULL,
//         CLI_LAST_NAME varchar(50) NOT NULL,
//     );
//     ALTER TABLE CLIENTS ADD CONSTRAINT PK_CLIENTS PRIMARY KEY (CLI_ID);
// `

fb.attach(opts as any, (err: any, att) => {
    if (err) throw err;
    console.log('successfully connected...');

    att.transaction(fb.ISOLATION_READ_COMMITTED, async (err, tx) => {
        if (err) throw err;
        console.log('Transaction ready...');

        const cols = [
            ['CLI_ID', 1],
            ['CLI_FIRST_NAME', 'TEST#_'],
            ['CLI_LAST_NAME', 'TEST#_']
        ]
        let idx = 0;
        await runQuery(); // if your db is empty, it should work
        await runQuery(); // should fail due to PK conflict
        await runQuery(); // no longer works due to uncaught error
        console.log('done');

        function runQuery() {
            return new Promise((resolve) => {
                tx.query(`INSERT INTO CLIENTS(${[...cols.map(i => i[0])]}) VALUES(${cols.map(i => '?')}) RETURNING CLI_ID`,
                    cols.map(i => i[1] ?? null),
                    (err, result) => {
                        if (err) {
                            console.log(err);
                        }
                        console.log((++idx) + '# . =========',{err, result})
                        resolve({err, result})
                    })
            })
        }
    })
})

Output:

1# . ========= { err: undefined, result: { CLI_ID: 1 } }
2# . ========= {
  err: Error: Violation of PRIMARY or UNIQUE KEY constraint "PK_CLIENTS" on table "CLIENTS", Problematic key value is ("CLI_ID" = 1)

3# is never executed due to exception: 
node_modules/node-firebird/lib/index.js:3507
        throw err;
        ^

TypeError: Cannot read properties of undefined (reading 'length')

Regarding the error:

I notice the fist error occurs in describe method.

function describe(buff, statement) {
    var br = new BlrReader(buff);
    var parameters = null;
    var type, param;

    while (br.pos < br.buffer.length) { // <--- here  - replaced with br.buffer?.length 

With the mentioned change, a second erro emerges at:

Connection.prototype.executeStatement = function(transaction, statement, params, callback, custom) {
....
if (input.length) { // <----- here - replace with if (input?.length) {

Output:

1# . ========= { err: undefined, result: { CLI_ID: 1 } }
2# . ========= {
  err: Error: Violation of PRIMARY or UNIQUE KEY constraint "PK_CLIENTS" on table "CLIENTS", Problematic key value is ("CLI_ID" = 1)
3# . ========= { err: undefined, result: undefined }.   <-- still not good. Callback is executed without err nor result

Any help would be much appreciated.