Empty error when 0 rows updated

[luisbraganca]

First of all thank you a lot for all the support and implementation of this module.
Great work!

However I faced a bug and decided to share my thoughts about it with you.

So, the following code:

"use strict";

const odbc = require("odbc");

odbc.connect("Dsn=example;Uid=example;Pwd=example;", function (err, connection) {
    if (err) {
        return console.error(err);
    }
    connection.query("UPDATE some_entity SET value = 10000 WHERE book = (SELECT id FROM books WHERE name = 'Best book of the world')", function (error, results) {
        if (error) {
            return console.error(error);
        }
        console.log(results);
    });
});

Shows the following output on the terminal:
[Error: "errors": []]

This error is shown when I execute an UPDATE query that the WHERE criteria does not match any rows, if it matches at least 1 row, there's no error...
I don't know if this is the correct behaviour although I don't agree it should be.
Imagine the following situation: I want to execute an UPDATE, and if no rows were updated on that same query, I want to be able to execute an INSERT query instead.

Currently using version 2.0.0-4.

[markirish]

I think the issue is probably that the query is returning SQL_NO_DATA, which isn't passing the error check (which is only passing on SQL_SUCCESS and SQL_SUCCESS_WITH_INFO). I actually found this same bug this morning and I am hoping to remedy for the next release.

[luisbraganca]

I think the issue is probably that the query is returning SQL_NO_DATA, which isn't passing the error check (which is only passing on SQL_SUCCESS and SQL_SUCCESS_WITH_INFO). I actually found this same bug this morning and I am hoping to remedy for the next release.

Ok thanks a lot for the quick response and for all the support.

[markirish]

Hi @luisbraganca ,

Can you confirm that this is no longer an issue in 2.2.1? I think it should be fixed.

[jasonwardell]

I know this is a couple months old now, but I can confirm this is still an issue in 2.2.2.

[leucippus1608]

I'm running into a similar issue at 2.3.5 connecting to DB2 on the IBM i.

I was testing some code using commit() and rollback() when I noticed that both UPDATE and DELETE statements that affect 0 rows return a generic error - stack = Error: [odbc] Error executing the statement, message = [odbc] Error executing the statement - but the odbcErrors array is empty.

On the contrary, an INSERT that fails due to a primary key violation throws an error in which odbcErrors contains the code -803.

It would be nice to have the option to decide how to proceed, especially during transactions, depending on the type of SQL error. It is not always the case that finding no records to update or delete is unexpected.

[markirish]

Going to mark as closed, a lot of development since the last update and I just tried on my Db2 for i ODBC connection. For both update and delete where 0 rows were affected, the query ran fine and produced no errors.