Populating table from SQL Server with Node.js
Populating table from SQL Server with Node.js
mmcardle
Posts: 2Questions: 1Answers: 0
Hi everyone,
I'm new to DataTables and want to get the basics working first before I move on with it. I'm using a local install of SQL Server Express 2019 and the AdventureWorks database.
And this is where I have got stuck after several days of researching and trial and error. I "think" I'm stuck with my JSON formatting, but it looks like it should work to me.
Below is my app.js, table.js(model) and main.ejs and then more detail about the problem I'm getting. App.js uses a model for the DB connection. Not included that as it works fine.
Note: Yes, there are irrelevant things in the app.js and main.ejs files, but wanted to leave them in as it's working as it is and I'll need them later.
app.js
var express = require("express"),
app = express(),
bodyParser = require("body-parser"),
{pool, poolConnect} = require("./models/dbConn");
app.use(bodyParser.urlencoded({extended: true}));
app.set("view engine", "ejs");
app.use(express.static(__dirname + "/public"));
app.get("/", function(req, res){
res.redirect("/salespeople");
});
app.get("/salespeople", function(req, res){
let qry = "SELECT TOP 3 [FirstName], [LastName], [EmailAddress] FROM [Sales].[vSalesPerson] FOR JSON AUTO, ROOT('peopleData')";
const request = pool.request(); // or: new sql.Request(pool1)
request.query(qry, function(err, data){
if(err){
console.log("Query had an issue: ", err);
} else {
console.log(data.recordset);
res.render("main", {data: data.recordset});
}
});
});
table.js
$(document).ready(function() {
$('#tabletest').DataTable( {
"processing": true,
"serverSide": true,
"ajax": {
"url": "/salespeople",
"dataSrc": "peopleData"
},
"columns": [
{ "data" : "FirstName" },
{ "data" : "LastName" },
{ "data" : "EmailAddress" },
]
});
});
main.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Table</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css">
<script src="https://code.jquery.com/jquery-3.5.0.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.js"></script>
</head>
<body>
<table id="tabletest" class="display" style="width:100%">
<thead>
<tr>
<th>First Name</th>
<th>Surname</th>
<th>Email</th>
</tr>
</thead></table>
<script type="text/javascript" src="/js/table.js"></script>
</body>
</html>
So, localhost:5000 starts to render the table on the web page, but then I get an alert saying:
localhost:5000 says
DataTables warning: table id=tabletest - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
So, I have followed that link, but it hasn't helped at all. I'm using FOR JSON in the TSQL statement, along with a root.
The console.log line in app.js is showing this:
[
{
'JSON_F52E2B61-18A1-11d1-B105-00805F49916B': '{"peopleData":[{"FirstName":"Syed","LastName":"Abbas","EmailAddress":"syed0@adventure-works.com"},{"FirstName":"David","LastName":"Campbell","EmailAddress":"david8@adventure-works.com"},{"FirstName":"Garrett","LastName":"Vargas","EmailAddress":"garrett1@adventure-works.com"}]}'
}
]
I think that should be working, but I'd be very grateful if someone is able to point out where I'm going wrong.
Regards,
Mark
Answers
You set
ajax.dataSrcto bepeopleData, but there's an ID in front of that -JSON_F52E2B61-18A1-11d1-B105-00805F49916B. Also, the main part is an array, rather that an object.It would be worth looking at the example here, as that is doing pretty much what you want.
Colin
Hi Colin and thank you for answering.
I did consult that example as well as a helpful post on StackOverflow to get to where I am now!
On the JSON ID thing returned by SQL Server, which is actually the column header, shoud I consider a function in the dataSrc option to deal with that or is there a better way?
Also, I didn't really understand your comment about array rather than object?
Regards,
Mark
Ideally, it would be better if you could do it on the server, as that'll involve less processing on the client, but if that's not possible, then yep, a function is a good way to go.
If you look at this page, you'll notice the JSON is
so the
datais within an object. Your main container is an array. That's not a problem, if you use that function to return the expected data.Colin