Retrieve multiple result sets in sails js

Question

I am using sails js with it sails-mssqlserver adapter. The problem with it is that if my stored procedure returns multiple result sets then I only receive one result set which is the latest of all. The same stored procedure is working fine with Java and I get to iterate over the relevant result sets.

I need to know if there is some specific way to access all result sets in sails-mssqlserver?

Answer 1

The sails-mssqlserver adapter is a wrapper of the official Microsoft SQL Server client for Node.js available here its dependecy however is not on the latest release.

Option 1: As per this official documentation of the MsSQL package, you can enable multiple recordsets in queries with the request.multiple = true command.

To enable multiple queries/recordsets in the sails-mssqlserver adapter, a hackish workaround is to open sails-mssqlserver/lib/adapter.js and edit the raw query function. Adding request.multiple = true below var request = new mssql.Request(mssqlConnect). As shown in the example below.

// Raw Query Interface
query: function (connection, collection, query, data, cb) {
  if (_.isFunction(data)) {
    if (debugging) {
      console.log('Data is function. A cb was passed back')
    }
    cb = data
    data = null
  }

  adapter.connectConnection(connection, function __FIND__ (err, uniqId) {
    if (err) {
      console.error('Error inside query __FIND__', err)
      return cb(err)
    }

    uniqId = uniqId || false
    var mssqlConnect
    if (!uniqId) {
      mssqlConnect = connections[connection].mssqlConnection
    } else {
      mssqlConnect = connections[connection].mssqlConnection[uniqId]
    }

    var request = new mssql.Request(mssqlConnect)

    // Add it here
    request.multiple = true

    request.query(query, function (err, recordset) {
      if (err) return cb(err)
      if (connections[connection] && !connections[connection].persistent) {
        mssqlConnect && mssqlConnect.close()
      }
      cb(null, recordset)
    })
  })
},

Now the returned recordset should contain multiple results.

Option 2: A more sustainable option for use cases where running a stored procedure which returns multiple recordsets, is to use the latest version of the official Microsoft SQL Server client for Node.js. Information on running stored procedures is available here

First install the latest package:

npm install mssql --save

In your code where you would like to run the stored procedure add a connection to the mssql database:

// require the mssql package
const sql = require('mssql')

// make a connection, you can use the values you have already stored in your adapter
const pool = new sql.ConnectionPool({
    user: sails.config.connections.<yourMsSQLConnection>.user,
    password: sails.config.connections.<yourMsSQLConnection>.password,
    server: sails.config.connections.<yourMsSQLConnection>.server,
    database: sails.config.connections.<yourMsSQLConnection>.database
})

// connect the pool and test for error 
pool.connect(err => {
    // ...
})

// run the stored procedure using request
const request = new sql.Request()
request.execute('procedure_name', (err, result) => {
    // ... error checks 
    console.log(result.recordsets.length) // count of recordsets returned by the procedure
    console.log(result.recordsets[0].length) // count of rows contained in first recordset
    console.log(result.recordset) // first recordset from result.recordsets
    console.log(result.returnValue) // procedure return value
    console.log(result.output) // key/value collection of output values
    console.log(result.rowsAffected) // array of numbers, each number represents the number of rows affected by executed statemens 
    // ...
})

// you can close the pool using
pool.close()

In cases, where the sails-* database adapter doesn't include all the functionality you require. I find it best to create a sails Service that wraps the additional functionality. It is a really clean solution.