node.js express router to render data from executed SQL statement

69
April 30, 2019, at 3:00 PM

I am trying to build node.js + express application which consumes the data from SQL server database. But I have a problem in getting the response for my executeStatement() which is within app.get() router function. I expect my code to render an array object which I could simply use in my ejs template or view. As shown in the code below column.value is the main array of object that I want to use on the frontend.

PS: I am fairly new to the world of programming.Thank you for your help!

var express = require('express');
var tediousExpress = require('express4-tedious');
var app = express();
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
app.set('view engine', 'ejs')
app.set('views', './views')
var config = { 'contains my credentials'  }
var connection = new Connection(config);
app.use(function (req, res, next) {
    req.sql = tediousExpress(connection);
    next();
});

app.get('/products', function (req, res ) {
    /* I want to get column.value object to be rendered to my frontend 
       I already used res.json() here but it returns nothing*/ 
    function executeStatement() {
        request = new Request("select count * from table where ArticleId= 
                24588 for json path", function(err, data) {
          if (err) {
            console.log(err);
          } else {
            console.log('total rows fetched ') ;
          }
          connection.close();
        });
       request.on('row', function(columns) {
          columns.forEach(function(column) {
            if (column.value === null) {
              console.log('NULL');
            } else {
                 column.value ;       
                }
          });
        });
      connection.execSql(request) ; 
       }  
});
Answer 1

You are not rendering any data to be passed to the front-end, also you aren't returning the array from the function.

To neaten up your logic, you should have something like this:

app.get('/products', function(err, data) {
  res.render('products', { arrData: executeStatement() }, function(err, data) {
    // ...
  })
})
executeStatement() { // ... }

Then in executeStatement() change column.value to return column.value

In your front-end template you can then iterate the data and output it as you wish:

<% arrData.forEach(value => { %>
  // do something with each value
<% }) %>
Answer 2

You need to render within your callback to row event:

app.get('/products', function (req, res ) {
  // assuming you get the datas in column.value
  request = new Request("select count * from table where ArticleId=24588 for json path", function(err, rowCount) {
    if (err) {
      console.log(err);
    } else {
      console.log('total rows fetched ') ;
    }
    connection.close();
  }
  request.on('row', function(columns) {
    const datas = []; // <-- array to hold values
    columns.forEach(function(column) {
      if (column.value === null) {
        console.log('NULL');
      } else {
        datas.push(column.value);
      }
    });
    res.render('myView', { datas })
  });
  connection.execSql(request);
}

In your view, you can access the datas like:

<% datas.forEach(value => { %>
  // do something like
  // <p><%= value %></p>
<% }) %>
READ ALSO
Lambda Response size automatically exceeds more than 6MB in nodejs

Lambda Response size automatically exceeds more than 6MB in nodejs

I have a lambda function,I get buffer from external api which is approximate 25 MB size but when I return same buffer in JSON object it becomes more than 7

70
How to count rating on group aggregation?

How to count rating on group aggregation?

I was stuck on mongodb aggregation to get count of ratingi have already trying to make myself pipeline

68
How can I load data on JSON instead of MySQL? (converting code)

How can I load data on JSON instead of MySQL? (converting code)

My server loads data from MySQL which is unnecessarilyI want to load data from local JSON files instead

81
How to do a selection sort with linked list

How to do a selection sort with linked list

I think I have the basic idea down for the selection sort, but for some reason it's not working and I don't know why? Anyone have an idea what is wrong here?

96