2 API calls, second to run always uses wrong database connection

[sll1966]

UnExpected behaviour: -->

Promise fails due to DBErrorTestRack: Invalid object name 'testrack.dbo.TestData'. Which ever API is called first works fine. Randomly 2nd called API always fails. It seems to be pointing to the other API database connection which doesn't have the above table.

Return result set from 2nd API call from the other 2nd database

Actual behaviour:

the above unexpected behaviour

Query (simplified below) which runs fine if i past into MS SSMS:

SET NOCOUNT ON
SELECT *
FROM [testrack].[dbo].TestData 

PS D:\MASTER\Dashboards> node app
Dashboard server listening on port 8080
pls page requested for BHGR Line 1
/api/24HourTrend called for BHGR1
/api/24ProductionSchedule called for BHGR1 @ Fri Jun 03 2022 15:34:46 GMT-0400 (Eastern Daylight Time)
DBErrorBHB: Invalid object name 'BHBrowser.dbo.ProdJobStsGR'.

Configuration:

1 of the 2 very similar API calls is ...

        const req24 = new XMLHttpRequest()
        req24.addEventListener("load", reqListener24);
        req24.open("GET", '../../api/24HourTrend/{{{ParmLocation}}}/{{{ParmLine}}}');
        req24.send();

        function reqListener24 () {
            const result = JSON.parse(this.responseText);

The API's are:

module.exports = function(app){
    const db = require('../public/Javascript/db');
    app.get('/api/test/:ParmLocation/:ParmLine', (req, res) => {
        var ParmLocation = req.params.ParmLocation;
        var ParmLine = req.params.ParmLine;
        console.log('API test called');
        console.log('API parms: ' + ParmLocation + ParmLine );
        res.send('API test parms: ' + ParmLocation + ParmLine );
    })

    app.get('/api/ProductionSchedule/:ParmLocation/:ParmLine', (req, res) => {
        var ParmLocation = req.params.ParmLocation;
        var ParmLine = req.params.ParmLine;
        const dateTime = new Date();
        console.log('/api/24ProductionSchedule called for ' + ParmLocation + ParmLine + ' @ '+ dateTime);
        {
        var query = "..."
        }

        var PromiseBHBrowserJobs = db.DBQueryBHBrowser(query);
        PromiseBHBrowserJobs.then(function(result) {
            const dateTime = new Date();
            console.log('/api/24ProductionSchedule completed ' + dateTime);
            res.send(result.recordset);
        }).catch(function () {
            console.log("Promise Rejected for api/ProductionSchedule");
        });
    })

    app.get('/api/24HourTrend/:ParmLocation/:ParmLine', (req, res) => {
        var ParmLocation = req.params.ParmLocation;
        var ParmLine = req.params.ParmLine;
        console.log('/api/24HourTrend called for ' + ParmLocation + ParmLine);
        //console.log('API parms: ' + ParmLocation + ParmLine );
        const dateTime = new Date();
        const StartOfPeriod = new Intl.DateTimeFormat('en-US').format(dateTime.setDate(dateTime.getDate() - 1));
        const EndOfPeriod= new Intl.DateTimeFormat('en-US').format(dateTime.setDate(dateTime.getDate() + 2));
        //console.log('24hours: ' + StartOfPeriod + '-' + EndOfPeriod)
        // 'If ParmLocatn = "BHEL" Then
        // '    StartOfShift = StartOfShift.AddHours(-1)
        // '    EndOfShift = EndOfShift.AddHours(-1)
        // 'End If
        // 'MsgBox("STOP")

        // ' Show last 24 hours, but exclude all leading 0 test hours
        // ' Add in the 0 tests in the middle of shifts from temp table
        // ' Add HourRate from ... as a goal line based on first order item worked on that hour
        {
        var query = "..."
        }
        var TestRackPromise = db.DBQueryTestRack(query);
        TestRackPromise.then(function(result) {
            console.log("Promise received");
            res.send(result.recordset);
        }).catch(function () {
            console.log("Promise Rejected for api/24hourTrend");
        });
    })
}

The DB calls are

// This module is included in pages via var db = require('./db');
// Then use db.DBQuery(config, sql)

const delay = ms => new Promise(res => setTimeout(res, ms));
// function used to interact with databases
async function DBQueryTestRack(sqlquery)  {
    try {
      if (debugMode) {
        console.log('SQL:' + sqlquery);
      }
      const sqlTestRack = require('mssql')
      sqlTestRack.on('error', err => {console.log(err.message)})
      const sqlConfigTestData = {
        user: 'Dashboard', password: 'removed', database: 'testrack', server: 'S5005002\\TESTRACK',
        //pool: {max: 10, min: 0, idleTimeoutMillis: 5000 },
        options: {trustServerCertificate: true  } //for self-signed certs
      };
      // Retrieve data
      //create a connection pool
      //const poolT = await sqlTestRack.connect(sqlConfigTestData);
      await sqlTestRack.connect(sqlConfigTestData);
      //console.log(pool);
      //const result = await poolT.request().query(sqlquery);
      const result = await sqlTestRack.query(sqlquery);
      if (debugMode) {
        console.log('TD:');S
        console.log(result.recordset);
      }
      //poolT.close();
      sqlTestRack.close();
      //await delay(2000);
      return result; 
    } catch (error) {
        // ... error checks
        console.log('DBErrorTestRack: ' + error.message + '\n Query:' + sqlquery);
        sqlTestRack.close();
        return
    }
};

async function DBQueryBHBrowser(sqlquery)  {
  try {
    if (debugMode) {
      console.log('SQL:' + sqlquery);
    }
    const sqlBHBrowser = require('mssql')
    sqlBHBrowser.on('error', err => {console.log(err.message)})
    const sqlConfigBHBrowser = {
      user: 'Dashboard', password: 'removed', database: 'BHBrowser', server: 'S5005002\\common',
      //pool: {max: 10, min: 0, idleTimeoutMillis: 5000 },
      options: {trustServerCertificate: true  } //for self-signed certs
    };
    // Retrieve data
    //create a connection pool
    //const poolBB = await sqlBHBrowser.connect(sqlConfigBHBrowser);
    await sqlBHBrowser.connect(sqlConfigBHBrowser);
    //console.log(pool);
    //const result = await poolBB.request().query(sqlquery);
    const result = await sqlBHBrowser.query(sqlquery);
    if (debugMode) {
      console.log('BHB:');
      console.log(result.recordset);
    }
    //poolBB.close();
    sqlBHBrowser.close();
    //await delay(2000);
    return result; 
  } catch (error) {
      console.log('DBErrorBHB: ' + error.message + '\n Query:' + sqlquery);
      sqlBHBrowser.close();
      return
  }
};

module.exports.DBQueryTestRack  = DBQueryTestRack;
module.exports.DBQueryBHBrowser = DBQueryBHBrowser;
// or below if is package.json type:module, but then require in app.js don't work 
// export default {
//   DBQueryTestData,DBQueryBHBrowser
// };

Software versions

• NodeJS: 14.15.4
• node-mssql: 8.1
• SQL Server: 2013

[dhensby]

This is because you are using the global pool to connect to multiple dbs which is not supported. See docs https://github.com/tediousjs/node-mssql#advanced-pool-management

see also #1376 and other related issues.

[sll1966]

Thank you for your assistance. That was very helpful and i have it working correctly now....