Javascript nodejs 乏味的mssql 有没有办法获取json？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29419842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Javascript nodejs tedious mssql is there a way to get json?
提问by Vince Banzon
I'm using nodejs and tedious connector to get data from mssql server. In documentation, I only see this one way to retrieve data
我正在使用 nodejs 和乏味的连接器从 mssql 服务器获取数据。在文档中，我只看到了这种检索数据的方法
var request = new Request("select Name, Value, Article_Id from [tableone] where Id = '1'", function (err, rowCount, rows) {
if (err) {
console.log(err);
} else {
console.log(rowCount + ' rows');
}
});
request.on('row', function (rows) {
...
bigArrat.push(JSON.stringify(rows));
});
But in my example I want all rows, not only one property but more. Currently, it return in separate row one cell eg. rows[0].value will return Name, rows[1].value Value ... for me it is rubbish.
但在我的示例中，我想要所有行，不仅是一个属性，而且还有更多。目前，它在单独的第一行单元格中返回，例如。rows[0].value 将返回 Name, rows[1].value Value ... 对我来说这是垃圾。
I want to get all information in json array of object not all metadata or one property. There is a way to do this or there is a better connector for nodejs and sqlserver ?
我想获取 json 对象数组中的所有信息，而不是所有元数据或一个属性。有没有办法做到这一点，或者有更好的 nodejs 和 sqlserver 连接器？
回答by chrisbajorin
The rows value sent to your initial callback is the array of rows being sent back:
发送到初始回调的行值是发回的行数组：
var request = new Request("select Name, Value, Article_Id from [tableone] where Id = '1'", function (err, rowCount, rows) {
if (err) {
console.log(err);
} else {
console.log(rowCount + ' rows');
}
console.log(rows) // this is the full array of row objects
// it just needs some manipulating
jsonArray = []
rows.forEach(function (columns) {
var rowObject ={};
columns.forEach(function(column) {
rowObject[column.metadata.colName] = column.value;
});
jsonArray.push(rowObject)
});
return callback(null, rowCount, jsonArray);
});
回答by Jovan MSFT
In Sql Server 2016 you can format query results as JSON text using FOR JSON option, see https://msdn.microsoft.com/en-us/library/dn921882.aspx
在 Sql Server 2016 中，您可以使用 FOR JSON 选项将查询结果格式化为 JSON 文本，请参阅https://msdn.microsoft.com/en-us/library/dn921882.aspx
You just need to read JSON fragments returned by query.
您只需要读取查询返回的 JSON 片段。
回答by Vince Banzon
Add this to your config.
将此添加到您的配置中。
rowCollectionOnRequestCompletion: true
rowCollectionOnRequestCompletion: 真
var config = {
userName: '', // update me
password: '', // update me
server: '', // update me
options: {
database: '', // update me
encrypt: true,
rowCollectionOnRequestCompletion: true
}
}
Then on your query you can now get the data of rows.
然后在您的查询中，您现在可以获取rows的数据。
var executeQuery = (res,query) => {
request = new Request(query, (err, rowCount, rows) => {
console.log("Rows: ", rows);
res.send(rows);
});
connection.execSql(request);
}
I learned it from: http://tediousjs.github.io/tedious/api-request.html
我是从这里学到的：http: //tediousjs.github.io/tedious/api-request.html
EDIT
编辑
Update not to have metadata:
更新为没有元数据：
var data = []
request = new Request(query, (err, rowCount, rows) => {
if(err) {
console.log(err)
res.send({ status: 500, data: null, message: "internal server error."})
} else {
console.log(rowCount+' row(s) returned')
res.send({ status: 200, data: data, message: "OK"})
}
})
request.on('row', function(row){
data.push({
last_name: row[0].value,
first_name: row[1].value
})
})
connection.execSql(request)
回答by Parth Shah
I tried that way but it did not work for me perhaps my knowledge of js and callbacks is not good enough. So, here is my solution. I had to add things to my config of connection to make rows of request work. You would also have to do this. Go to: at the end of new Request section, and to the rows. hereSecond thing, I did is pretty simple.
我尝试过这种方式，但它对我不起作用，也许我对 js 和回调的了解还不够好。所以，这是我的解决方案。我必须在我的连接配置中添加一些东西才能使请求的行工作。你也必须这样做。转到：在新请求部分的末尾，然后转到行。 这里第二件事，我做的很简单。
var jsonArray = [];
var rowObject= {};
var request = new Request("SELECT TOP 5 * FROM tableName",function(err,rowCounts,rows)
{
if (err)
{
console.log(err);
}
else
{
console.log(rowCounts + " rows returned");
}
//Now parse the data from each of the row and populate the array.
for(var i=0; i < rowCounts; i++)
{
var singleRowData = rows[i];
//console.log(singleRowData.length);
for(var j =0; j < singleRowData.length; j++)
{
var tempColName = singleRowData[j].metadata.colName;
var tempColData = singleRowData[j].value;
rowObject[tempColName] = tempColData;
}
jsonArray.push(rowObject);
}
//This line will print the array of JSON object.
console.log(jsonArray);
and to show you how my connection.config looks like:
并向您展示我的 connection.config 的样子：
static config: any =
{
userName: 'username',
password: 'password',
server: 'something.some.some.com',
options: { encrypt: false, database: 'databaseName' ,
rowCollectionOnRequestCompletion: true }
};//End: config
and this is how I am passing it to connection.
这就是我将它传递给连接的方式。
static connection = new Connection(Server.config);
回答by Christiano Kiss
Complementing the answer from @Jovan MSFT:
补充@Jovan MSFT 的回答：
var request = new Request('select person_id, name from person for json path', function(err) {
if (err) {
console.log(err);
}
connection.close();
});
And, finally, in the rowevent:
最后，在这种情况row下：
request.on('row', function(columns) {
var obj = JSON.parse(columns[0].value);
console.log(obj[0].name);
});
P.S.: the code above does not iterate over columnsparameter because for json pathreturns a single array of objects in a single row and column.
PS：上面的代码不会迭代columns参数，因为for json path在单个行和列中返回单个对象数组。
回答by R_FF92
If you are using express on server side I can recommend using express4-tedious (see https://www.npmjs.com/package/express4-tedious). It allows to easily write apis for SQL connections with small code and streams json result to response.
如果您在服务器端使用 express，我可以推荐使用 express4-tedious（请参阅https://www.npmjs.com/package/express4-tedious）。它允许使用小代码轻松编写用于 SQL 连接的 API，并将 json 结果流式传输到响应。
Connection:
联系：
var express = require('express');
var tediousExpress = require('express4-tedious');
var app = express();
app.use(function (req, res, next) {
req.sql = tediousExpress(req, {connection object});
next();
});
Example Api:
示例 API：
/* GET from tableone, streams json result into response */
router.get('/', function (req, res) {
req.sql("select Name, Value, Article_Id from [tableone] where Id = '1' for json path")
.into(res);
});
You can then call these apis e.g. from frontend.
然后您可以从前端调用这些 api。
