CODEWITHSUNDEEP
javascriptsql-servernode.js
user2126824
user2126824

Reputation:

Javascript nodejs tedious mssql is there a way to get json?

I'm using nodejs and tedious connector to get data from mssql server. In documentation, I only see this one way to retrieve data

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.

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 ?

Upvotes: 6

Views: 19397

Answers (8)

shackleton
shackleton

Reputation: 793

This is a combination of a few responses above. This uses FOR JSON AUTO in the SELECT statement and parses the "column" as JSON. The row/column nomenclature may be a bit misleading for folks unfamiliar with this API. In this case, the first "columns" value will be an array of the rows in your table:

var request = new Request("SELECT Name, Value, Article_Id FROM [tableone] WHERE Id = '1' FOR JSON AUTO", function (err, rowCount, rows) {
  if (err) {
    console.log(err);
  } else {
    console.log(rowCount + ' rows');
  }
});
request.on('row', (columns) => {
  const json = JSON.parse(columns[0].value);
});

Upvotes: 0

Noid
Noid

Reputation: 1

Applying map-reduce function in returned rows:

rows.map(r=>{
      return r.reduce((a,k)=>{
        a[k.metadata.colName]=k.value
        return a
      }
      ,{})
    })

Upvotes: 0

Fabi_92
Fabi_92

Reputation: 581

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.

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:

/* 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.

Upvotes: 2

Christiano Kiss
Christiano Kiss

Reputation: 557

Complementing the answer from @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 row event:

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 columns parameter because for json path returns a single array of objects in a single row and column.

Upvotes: 0

Vince Banzon
Vince Banzon

Reputation: 1489

Add this to your config.

rowCollectionOnRequestCompletion: true

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. 

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

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)

Upvotes: 4

Parth Shah
Parth Shah

Reputation: 56

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. here Second thing, I did is pretty simple. 

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: 

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);

Upvotes: 1

Jovan MSFT
Jovan MSFT

Reputation: 14610

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

You just need to read JSON fragments returned by query.

Upvotes: 10

chrisbajorin
chrisbajorin

Reputation: 6153

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);
});

Upvotes: 15

Related Questions