Query function is actually called 3 times in express

[qlecler]

Hi there,

I've a weird issue there :

router.get("/list/", async (req, res, next) => {
  let promise = new Promise(function(resolve, reject) {
      db.query(queries.LIST, parameters, (err, rows, moreResultSets) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
    let result = await promise;
    res.status(200).send(result);
  });

The db.query function is somehow called 2 times and I don't know why exactly.

The first time it return an empty array, the second time the good one.

I'm using Express and it causes a lot of issues with res.send.

Thanks for the help 👍

[asztal]

What is the query like? What database engine?

In my experience, a query like the following will generate two result sets with SQL Server:

select @someID = id from SomeTable where someColumn = 'someValue';

select id, value, foo, bar
from SomeOtherTable
where bazID = @someID;

The first result set has no row data but it does return a "rows affected" count. The same thing occurs if you insert into a temporary table or update/delete rows from a table.

And it is solved by prefixing the query with set rowcount off;.

[qlecler]

What is the query like? What database engine?

In my experience, a query like the following will generate two result sets with SQL Server:

select @someID = id from SomeTable where someColumn = 'someValue';

select id, value, foo, bar
from SomeOtherTable
where bazID = @someID;

The first result set has no row data but it does return a "rows affected" count. The same thing occurs if you insert into a temporary table or update/delete rows from a table.

And it is solved by prefixing the query with set rowcount off;.

I'm calling a stored procedure on a Sybase Anywhere DB.

the db.query should only be called once and in express when I call the route I've got 2 different result (see the code above). Also it doesn't wait for the promise to be resolved (apparently).

The first time result is [] and the second time it got the rows.

So in Express it called the res.send with the empty array and doesn't send the one with the rows in it.

[qlecler]

Here is a full example (still using Sybase Anywhere 9 DB).

Query (queries.LIST)

SELECT *
FROM dba.xxx
WHERE id = ?

Code

router.get("/list/", (req, res, next) => {
  try {
    ...
    const parameters = [userId];
    db.query(queries.LIST, parameters, (err, rows, moreResultSets) => {
      if (err) {
        return res.status(400).json({ error: `an error occured: ${err}` });
      }
      console.log(rows);
      return res.status(200).send(rows);
    });
  } catch (e) {
    next(e);
  }
});

Result

[]
[]
 [ { lib: 'xxx', val: '111' },
   { lib: 'xxx', val: '222' } ]

It's called 3 times! I only call the route '/list' once.

It actually only res.send the first empty row and then :

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client

[wankdanker]

Check the value of moreResultSets. If it is true, then the callback function is going to be called again. My guess is that it is true two times and then on the last time it is false. If that is the case, then for some reason, your database is returning empty result sets before the actual result set you are looking for.

You could then change your code to something like:

router.get("/list/", (req, res, next) => {
  try {
    ...
    const parameters = [userId];
    db.query(queries.LIST, parameters, (err, rows, moreResultSets) => {
      if (err) {
        return res.status(400).json({ error: `an error occured: ${err}` });
      }

      console.log(rows);

      if (!moreResultSets) { 
         return res.status(200).send(rows);
      }
    });
  } catch (e) {
    next(e);
  }
});

[qlecler]

Check the value of moreResultSets. If it is true, then the callback function is going to be called again. My guess is that it is true two times and then on the last time it is false. If that is the case, then for some reason, your database is returning empty result sets before the actual result set you are looking for.

You could then change your code to something like:

router.get("/list/", (req, res, next) => {
  try {
    ...
    const parameters = [userId];
    db.query(queries.LIST, parameters, (err, rows, moreResultSets) => {
      if (err) {
        return res.status(400).json({ error: `an error occured: ${err}` });
      }

      console.log(rows);

      if (!moreResultSets) { 
         return res.status(200).send(rows);
      }
    });
  } catch (e) {
    next(e);
  }
});

Thanks, I'll try that.

Maybe the issue come from the stored procedure that's being called on the DB side (Sybase Anywhere 9).

I know I've received warnings (The result returned is non deterministic SQLCode=122) on InteractiveSQL.

Edit: Your code works with the query, thanks 👍

[qlecler]

Hi there,

Is it possible to access somehow "moreResultsSets" with querySync ?

Thanks 👍

[kolzar]

What is the query like? What database engine?

In my experience, a query like the following will generate two result sets with SQL Server:

select @someID = id from SomeTable where someColumn = 'someValue';

select id, value, foo, bar
from SomeOtherTable
where bazID = @someID;

The first result set has no row data but it does return a "rows affected" count. The same thing occurs if you insert into a temporary table or update/delete rows from a table.

And it is solved by prefixing the query with set rowcount off;.

I think I have the same error. I am using nodejs & express on sql server.
I already tried as 'set rowcount 0' as 'set nocount off' without effect.
Any idea?