[BUG] null value bind to number value results in nulls for numeric values for subsequent calls even when afterwards a non null value is bound

[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 to insert values into a table with 3 parameters. In a loop 3 rows will be inserted:

• For the first row all parameters will be bound to values.
• In the second row parameters 2 and 3 are bound to null values.
• in the third row parameters 2 and 3 are bound to values.

The bound value for the numeric value for row 3 is not inserted in the database. The column value is inserted as a null value. It seems that after binding a null value to a numeric afterwards non null values are inserted as null values in the database.

Expected behavior Proper insert of values after bind.

To Reproduce Execute the provided script under code.

Code

const odbc = require('odbc');

run().then(
    () => process.exit(),
    error => {
        console.log(error);
        process.exit();
    },
);

async function run() {
    // connect to the database
    const driver = 'MSSQL2019';
    const server = 'localhost';
    const uid = 'sa';
    const password = 'iRefact2017';
    const connectionString = `DSN=${driver};Uid=${uid};Pwd=${password};TrustServerCertificate=yes;`;
    const connection = await odbc.connect(connectionString);
    // initialize the connection
    await connection.setIsolationLevel(odbc.SQL_TXN_READ_COMMITTED);
    await connection.beginTransaction();
    await connection.query('set nocount on');
    // create a table to test the reuse of parameters
    await connection.query('create table dbo.test_parameters (id int, textValue varchar(100), numValue int);');
    // create a statement to insert values into the table using parameters
    const statement = await connection.createStatement();
    await statement.prepare('insert into dbo.test_parameters values (?, ?, ?);');
    // insert 3 rows:
    // row 1 with values for all parameters
    // row 2 with null values for parameters 2 and 3
    // row 3 with values for all parameters
    const textValues = ['row 1', null, 'row 3'];
    const numValues = [1, null, 3];
    for (let i = 0; i < textValues.length; i++) {
        await statement.bind([i + 1, textValues[i], numValues[i]]);
        await statement.execute();
    }
    // get the inserted rows
    const rows = await connection.query('select * from dbo.test_parameters order by id');
    console.dir([...rows]);
    // The result of row 3 has a null value for numValue. Expected the value to be 3!!!.
    // close the connection
    await connection.rollback();
    await connection.close();
}

Additional context

This is the result of retrieving the inserting rows from the table:

[ { id: 1, textValue: 'row 1', numValue: 1 }, { id: 2, textValue: null, numValue: null }, { id: 3, textValue: 'row 3', numValue: null } ]

[ErikJansenIRefact]

Does anyone has a clue what goes wrong in above approach? This is what we can see what happens in de database by profiling all statements:

1. exec sp_describe_undeclared_parameters N'insert into dbo.test_parameters values (@P1, @P2, @P3);'
2. declare @p1 int set @p1=NULL exec sp_prepexec @p1 output,N'@P1 int,@P2 varchar(100),@P3 int',N'insert into dbo.test_parameters values (@P1, @P2, @P3);',1,'row 1',1 select @p1
3. exec sp_execute 1,2,NULL,NULL
4. exec sp_execute 1,3,'row 3',NULL

In step 2 the first row is prepared and inserted with value: 1, 'row 1', 1. In step 3 the second row is inserted with values: 2, NULL, NULL. In step 4 the third row is inserted with values: 2, 'row 3', NULL.

As you can see in step 4 the value for column "numValue" is missing. So somewhere in the execution flow the bind to numValue parameter to value 3 is lost.

[ErikJansenIRefact]

odbc.log

[markirish]

@ErikJansenIRefact taking a peek now

[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 re-open.