[BUG] Prepared statement with cursor does not return result in second call with new bind.

[ErikJansenIRefact]

Describe your system

• odbc Package Version: 2.4.7
• ODBC Driver: Microsoft ODBC Driver V18
• Database Name: SQLServer
• Database Version: 2019
• Database OS: Windows
• Node.js Version: 16.15.1
• Node.js OS: Mac Ventura 13.3

Describe the bug A prepared statement is created (a query) with a single parameter together with a bind for it's value. The prepared statement is executed as a cursor and an expected result is returned after which the cursor is closed.

A new bind value is passed to the statement and again the statement is executed as a cursor. In the second execute a result was expected but nothing was returned.

Expected behavior

1. A query result in the first call.
2. A query result in the second call.

To Reproduce

1. create a simple test table and populate with 2 rows: CREATE TABLE test (id int); insert into test values (1), (2);
2. Execute the code as given below

Code

const odbc = require('odbc');

async function connectToDatabase() {
    const connectionConfig = {
        connectionString: 'DSN=MSSQL2019;uid=sa;pwd=iRefact2017;MARS_Connection=no;trustServerCertificate=yes',
        connectionTimeout: 10,
        loginTimeout: 10
    };

    const connection = await odbc.connect(connectionConfig);
    const statement = await connection.createStatement();
    await statement.prepare(`
    select * from IR_TSTGIN.dbo.test where id = ?;
    `);
    await statement.bind([1]);

    let cursor = await statement.execute({
        fetchSize: 1
    });
    while (!cursor.noData) {
        const result = await cursor.fetch();
        console.dir(result, {
            depth: null
        });
    }
    await cursor.close();

    await statement.bind([2]);

    cursor = await statement.execute({
        fetchSize: 1
    });
    while (!cursor.noData) {
        const result = await cursor.fetch();
        console.dir(result, {
            depth: null
        });
    }
    await cursor.close();

}

connectToDatabase()
    .catch(error => {
        console.dir(error, {
            depth: null
        });
    });

Additional context

The logging of the result returned reveals something interesting: the value of the parameters property in the second execution reports a value [1] where a value 2 was bound. Nevertheless the query does not report a result (if the parameter would have had value 1 it still should have returned a record).

This is the console.log of the result object for the first and second statement:

First statement:

[
  { id: 1 },
  statement: '\n    select * from IR_TSTGIN.dbo.test where id = ?;\n    ',
  parameters: [ 1 ],
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'id',
      dataType: 4,
      columnSize: 10,
      decimalDigits: 0,
      nullable: true
    }
  ]
]

Second statement:

[
  statement: '\n    select * from IR_TSTGIN.dbo.test where id = ?;\n    ',
  parameters: [ 1 ],
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'id',
      dataType: 4,
      columnSize: 10,
      decimalDigits: 0,
      nullable: true
    }
  ]
]

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[ErikJansenIRefact]

Please do not close.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[ErikJansenIRefact]

Do not close.

[ErikJansenIRefact]

Please re-open.

[FredoMartini]

Hello,

I encountered the same problem. A temporary solution would be to ignore noData and check the return of fetch().

So, this code :

    while (!cursor.noData) {
        const result = await cursor.fetch();

        // use result here
    }

can be written like this :

    while(true) {
        const result = await cursor.fetch();
        if (result.length === 0) {
            break;
        }
        // use result here
    }