Help with multiple requests using Tedious

Currently I am using Tedious to connect to MSSQL database. I create a request and store the information needed in an object. I have that all working. Now I need a follow request for each item in my object that has a value greater than 0 in one of the fields. This request will return one or more items that I then want to update my object with.I know I cannot make more than one request at a time to a connection.Do I need more than one connection to do this?If I only need one connection, then is there an event I listen for to fire the second request? "requestCompleted" does not seem to work and "done" does not seem to fire.I am using Node.js 8.12, Tedious, and Express.The code below is a rough work in progress, nowhere near production. I am just confused how to make a second request.​var Connection = require('tedious').Connection; var Request = require('tedious').Request; var config = { userName: 'redacted', password: 'redacted', server: 'redacted', options: {encrypt: true, database: 'redacted', useColumnNames: true, 'rowCollectionOnRequestCompletion': true} }; var connection = new Connection(config); connection.on('connect', function(err) { // If no error, then good to go... }); var cors = require('cors') var express = require('express'); var app = express(); app.use(cors()); app.get('/', function (req, res) { var testData = {}; // Build request request = new Request("SELECT aColumnID,tTabName,tGroupName,tDisplayName,tblColumnMain.tBaseTable,tDBColumnName, tblObject.tName, tblCustomField.nConstraintID FROM tblColumnMain INNER JOIN tblCustomField ON tblColumnMain.aColumnID = tblCustomField.nColumnID INNER JOIN tblFieldGroup ON tblCustomField.nGroupID = tblFieldGroup.aGroupID INNER JOIN tblTab ON tblFieldGroup.nTabID = tblTab.aTabID INNER JOIN tblObject ON tblTab.nObjectType = tblObject.aObjectType ORDER BY tTabName, tGroupName, tDisplayName", function(err, rowCount) { if (err) { console.log(err); } else { console.log(rowCount + ' rows'); } }); // For each row of data request.on('row', function(columns) { const id = columns.aColumnID.value; testData[id] = {}; for (var col in columns) { testData[id][col] = columns[col].value; } }); request.on('requestCompleted', function () { res.setHeader('content-type', 'application/json'); for (var row in testData) { if (testData[row].nConstraintID > 0) { console.log('Fetching Constraint Values'); requestContraints = new Request("SELECT tblColumnMain.tBasetable, tblColumnMain.tDBColumnName, tblColumnMain.tDisplayName, tblCustomField.nConstraintID, tblEnum.nIndex, tblEnum.tDisplayName FROM tblColumnMain JOIN tblCustomField ON tblColumnMain.aColumnId = tblCustomField.nColumnId JOIN tblFieldGroup ON tblCustomField.nGroupId = tblFieldGroup.aGroupId JOIN tblTab tblTab ON tblTab.aTabId = tblFieldGroup.nTabId JOIN tblObject ON tblObject.aObjectType = tblTab.nObjectType JOIN tblEnum ON tblCustomField.nConstraintID = tblEnum.nConstraintIDWHERE tblColumnMain.aColumnID=" + testData[row].aColumnID, function(err) { if (err) { console.log(err); } }); requestContraints.on('row', function(columnsConstraints) { for (var colContraints in columnsConstraints) { testData[id][nConstraintID] += columnsConstraints["tDisplpayName"].value; } }); connection.execSql(requestContraints); } } res.json(testData); }); // Execute statment connection.execSql(request); }); var server = app.listen(5000, function () { console.log('Server is running..'); });

Submitted October 22, 2018 at 05:53PM by webb_world