Queueing SQL queries

Question

I have a socket that listens to events (strings) through UDP. Each time it gets a string an SQL request is generated. My problem is making it run asynchronously, so that requests queue up and not try to access the database while another query is running.

Pseudo:

socket.on('message', function (msg) {
    switch (msg) {
        case "case1":
            storeRows();
            break;
    //there's a new message every 1-2 secs
    ...
}
var rows = []; //push results of all queries here
function storeRows() {
    rows.push(getFromDB());
}

function getFromDB() {
    var sqlString = "SELECT * ..."
    var req = new Req(sqlString, function(err, rowCount) {
...
    }
var resultsArray = [];
req.on('row', function (cols) {
    //add results to resultsArray
}
return resultsArray;
}

Basically I need getFromDB() to run asynchronously, wait for the previous query to finish before running the next one. This I don't know how to do. I'm using tedious.js to access SQL Server DB.

Edit:

var config = {
    userName: "admin",
    password: "pw",
    server: "test",
    domain: "test",
    options: {
        port: '10322'
    }
}
connection = new Connection(config);
connection.on('connect') {
    isConnected = true;
}

getCoordinates(vehicleID, fromTime, toTime) { 
    var SQLString = "Select * FROM coordinates WHERE TimeStamp BETWEEN '" + fromTime + "' AND '" + toTime + "' AND vehicleID = " + vehicleID;
    var rowsToSend = [];
    var req = new Req(SQLString, function(err, rowCount) {
        if (err) console.log(err)
        else console.log(rowCount);
    }

    req.on('row', function (columns) {
        var rowArray = [];
        columns.forEach(function (column) {
            var colValue = column.value;
            switch (column.metadata.colName) {
                case "ID":
                    if (rowArray.length > 0)                   
                        rowsToSend.push(rowArray);
                        rowArray = new Array();
                        break;

                default:                     
                    rowArray.push(colValue);
                    break;
                }
         });
         rowsToSend.push(rowArray);
    });
    connection.execSql(req);

    req.on('doneProc', function () {
        return(rowsToSend);
    }  
}

//called every few seconds
function runQueries() {
    someArray.push(getCoordinates ());
}

From their documentation, it's clear that database queries using tedious.js are already asynchronous (as one would hope, in a Node.js environment). Are you using some option or some such to make them blocking instead? Could you show us the real content of getFromDB (with the SQL omitted is fine)? — T.J. Crowder
– T.J. Crowder, Commented Sep 13, 2018 at 8:14
When sending a request while another is running it returns Requests can only be made in the LoggedIn state, not the SentClientRequest state — user2979612
– user2979612, Commented Sep 13, 2018 at 8:16
We can't help you without seeing more of your code. But it sounds like the problem isn't synchronous vs. asynchronous, but some other kind of state management. Please update your question with a minimal reproducible example demonstrating the problem with simple queries (a lot of us have access to SQL Server and can quickly set up a local example to help you). — T.J. Crowder
– T.J. Crowder, Commented Sep 13, 2018 at 8:18
@T.J.Crowder What does this have to do with state management? The question seems rather straightforward. I have a message that needs to query a database every 1-2 seconds, and I want to ensure that the queries aren't being run in parallel. I've provided an example below. — Blue
– Blue, Commented Sep 13, 2018 at 8:28
Have the callback that you provide to the new Req() call respond to the current message with the data or an error. Then if there's still requests inside your request queue, have it do the same. So all the requests arriving through the socket can stay synchronous. Only the database query triggering the socket to send the response is async, since you want to wait for the prev query to finish instead of running all queries in parallel. Storing pending queries can just be as simple as pushing and popping an array. — Shilly
– Shilly, Commented Sep 13, 2018 at 8:30

Blue · Accepted Answer · 2018-09-13 08:29:42Z

You can build something like a Processor, which builds an array based queue, and only allows one query to be executed on the database at any one time. Here's an example using async/await functions with promises:

class Processor {
  constructor() {
    this.queue = [];
    this.queryRunning = false;
  }
  
  // Main query function.
  // Add the query to a queue, and try to process the queue.
  query(query) {
    return new Promise((resolver) => {
      this.queue.push({
        query,
        resolver
      });
      
      this.tryProcessNext();
    });
  }
  
  // Try to process the next element of the queue
  tryProcessNext() {
    let self = this;
    
    // If it's running, or queue is empty, simply do nothing
    if (this.queue.length === 0 || this.queryRunning)
      return;
    
    this.queryRunning = true;
    
    // Get the first added element to the queue (And shorten the array at the same time)
    let qry = this.queue.shift();
    
    // Run query, and use `qry.resolver()` to resolve the original promise.
    setTimeout(function() {
      qry.resolver(`Result: ${qry.query}`);
      self.queryRunning = false;
      // If there's anything next in the queue, try to process that next
      self.tryProcessNext();
    }, 1500);
  }
  
}

const proccessor = new Processor();

let i = 0;
// A simple function to demonstrate adding queries via a websocket.
setInterval(async function() {
  console.log(`Adding query: QUERY ${++i}`);
  var res = await proccessor.query(`QUERY ${i}`);
  
  console.log(res);
}, 1000);

Collectives™ on Stack Overflow

Queueing SQL queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related