multiple queries using mssql in node.js

Question

I have data that are being inserted into my db. It works perfectly fine but i know that there is a better way to do this.. how should I compact all these queries into single one?

data

data = [{Location: 'A', shift: '1'}, {Location: 'B', Shift: '2'}, {Location: 'C', Shift: '3'}]

node.js

  //Receive from Angular Server 
 const express = require('express');
 const bodyParser = require('body-parser');
 const app = express();
 var sql = require('mssql');

 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({ extended: true }));

 app.all("/*", function (req, res, next) {
   res.header('Access-Control-Allow-Origin', '*');
   res.header('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE,OPTIONS');
   res.header('Access-Control-Allow-Headers', 'Content-Type, Authorization, Content-Length, X-Requested-With');
   next();
 });

 app.listen(1433, function () { console.log('Example app listening on port 1433!') })

 var config = {
  server: "T21DOPD1",
  database: "ARGYLE_COMPARE",
  user: "dop_adm",
  password: "dopadm",
  port: 1433
};

 app.post('/ping', function (req, res) {
   res.send(res.body);
   var jsondata = JSON.stringify(req.body);
   var data = JSON.parse(jsondata);
   // data = [{Location: 'A', shift: '1'}, {Location: 'B', Shift: '2'}, {Location: 'C', Shift: '3'}] 


   sql.connect(config, function(err) { 
     if(err)
       console.log(err);
     else
       console.log("Connection successful");

     var request = new sql.Request();

     request
     .query('INSERT INTO testDB set ?', data, (err,results)=>{
      console.log(err, results);
  }) 
   })
 });

error

error : return callback(err, recordsets, _this.rowsAffected); TypeError: callback is not a function

shivshankar shivshankar · Accepted Answer · 2017-11-23T17:59:56

simply go through official mysqljs doc https://github.com/mysqljs/mysql and figure out this line

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

query('INSERT INTO testDB set ?', [[ 'A', '1'], [ 'B', '2'],[ 'C', '3']], (err,results)=>{
    console.log(err, results);
})

multiple queries using mssql in node.js

1 Answers