在Sails JS中检索多个结果集 [英] Retrieve multiple result sets in sails js
问题描述
我正在将Sails js与它的 sails-mssqlserver 适配器一起使用.问题在于,如果我的存储过程返回了多个结果集,那么我只会收到一个最新的结果集. 相同的存储过程可以在Java上正常工作,我可以遍历相关的结果集.
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.
我需要知道是否有一些特定的方法可以访问sails-mssqlserver中的所有结果集吗?
I need to know if there is some specific way to access all result sets in sails-mssqlserver?
推荐答案
sails-mssqlserver适配器是Node.js官方Microsoft SQL Server客户端的包装,该客户端可用
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.
选项1: 根据MsSQL软件包的官方文档,您可以使用request.multiple = true命令在查询中启用多个记录集.
Option 1: As per this official documentation of the MsSQL package, you can enable multiple recordsets in queries with the request.multiple = true command.
要在sails-mssqlserver适配器中启用多个查询/记录集,一个小小的解决方法是打开sails-mssqlserver/lib/adapter.js并编辑原始查询功能.在var request = new mssql.Request(mssqlConnect)下添加request.multiple = true.如下例所示.
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.
选项2: 对于运行返回多个记录集的存储过程的用例,一种更可持续的选择是对Node.js使用最新版本的官方Microsoft SQL Server客户端.有关运行存储过程的信息,请此处
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
在您要运行存储过程的代码中,添加与mssql数据库的连接:
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()
在sails- *数据库适配器未包含您需要的所有功能的情况下.我发现最好创建一个包含附加功能的sails Service.这是一个非常干净的解决方案.
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.
这篇关于在Sails JS中检索多个结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!