How to build a database connection inside dashboard-template-node

[JustinB]

Hello guys,

I want to implement a MySQL-database connection inside a dashoard-template-node because the MySQL-node doesnt quit fit my needs. The template-node, I want the connection to be established in, is also used to generate a tabulator-table. Currently I'm getting the data from the MySQL-node, which I have to change because I need to make use of progressive loading inside the table.

As I noticed ajax should be used for this. But I'm not sure if its possible to use PHP inside a node-RED-node. If not, what can I do?

Thanks in advance

Justin

[Colin]

Are you trying to access the database from within the browser, rather than from the node red server?

[JustinB]

@Colin Currently I'm accessing the database from the node red server (Because of the MySQL-node). I guess this will change when the connection is built inside the template-node. But unfortunately I think this is the only way to get progressive loading working. The tabulator-doc says that progressive loading will be disabled when handling with files or normal json input..

tabulator-documentation

[bakman2]

If you need so much data to be displayed in your table, do you actually know what to look for? but i digress.

The approach to include a database connection will not work.
Reading the tabulator documentation; an ajax request would be the easiest.

How to solve this in node-red ?

http-in -> sql query -> http response

This will create an http endpoint that can be called from fetch/ajax.

var table = new Tabulator("#example-table", {
    ajaxURL:"http://<node-red-ip>/sqldataendpoint", 
    progressiveLoad:"load",
});

Note that it requires you to supply and keep track of the paging mechanism:

{
    "last_page":15, //the total number of available pages (this value must be greater than 0)
    "data":[ // an array of row data objects
        {"id":1, "name":"bob", "age":"23"} //example row data object
    ]
}

I assume on load and when you scroll, it will perform requests to the endpoint.
Note 2: this will only work if the browser can reach the node-red-ip (ie, not internet, unless this was setup).

If you are new to http-in/response nodes, make sure to read the node documentation and the cookbook documentation on the node-red homepage.

[JustinB]

I actually found this site and got my ajax connection done. I set up the table for progressive loading but the data isnt showing up. (Because it's not retrieving an array)

The data looks like the following:

{"payload":[{"Register":1,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":2,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":3,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":4,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":5,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":6,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":7,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":8,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":9,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":10,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":11,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":12,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":13,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":14,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":15,"Druck":59,"Zeitstempel":"13:37:41:967"},{"Register":16,"Druck":59,"Zeitstempel":"13:37:41:967"}]}

I think I have to change something inside the server file..

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

But I have no clue about express or body-parser...

[bakman2]

Cool, now you can stop node-red because you completely defeated its purpose

What i explained above is exactly the same, except in the node-red way.

example;

[JustinB]

Well.. it is what it is

The data we are receiving is actually an array.. I don't understand why tabulator doesnt realize that.

I did the following (inside the tabulator-configuration):

        ajaxResponse:function(url, params, response){
            console.log(response);
            return response;
        },

and the console says:

[JustinB]

It's working fine now. The problem was inside the server function. I had to change the name of the array (payload -> data) and the request has to hand over a parameter called last_page which I just set to 1 (as long as it works )

[bakman2]

I don't get it.

You come at this forum with a "node-red" question, I am trying to help you to make this work the node-red way. "Well.. it is what it is" and ignore it and in the mean time you reinvent the wheel by using an additional express server. Node-red has no use here. Try rebooting your server and see if it still works....hint: it won't.

My hint: remove the whole nodejs+express idea and try to set it up as as shown above, I am willing to help.

[JustinB]

@bakman2 I'm not ignoring what you are telling me. I'm just sitting here since days trying to get things done.

I tried it the node-red-way and this is the result:

Am I missing something? Have you made some configurations inside the nodes, I can't see? My flow looks like this:

I mean, where do I have to configure the query, the last_page setting etc.?

[bakman2]

Where did you point the tabulator/ajaxURL to ?

should be something like http(s)://<node-red-ip>:<port>/fetchmessungen
to add the last_page you can add a change node and set msg.last_page to some value, but if you want to do it properly with actual progressive loading you should use LIMIT with an OFFSET in your query. last page would be total records/limit.

Make sure to add debug nodes everywhere to see what is actually happening when tabulator calls the url.

When tabulator calls the url, you will see something like "page=x", you can use a change node to set the query and feed it into the mysql node, if you read the sidebar documentation for the mysql node:

msg.topic must hold the query for the database, and the result is returned in msg.payload .

[JustinB]

I pointed it to http://<node-red-ip>/fetchmessungen (I know the port is missing.. which port do I have to connect to?). What do you mean with

@bakman2

last page would be total records/limit

Lets say we have a total of 900.000 records and want the limit to be 1000. So as I understood, the last page will be 900?

@bakman2

When tabulator calls the url, you will see something like "page=x", you can use a change node to set the query and feed it into the mysql node

What exactly is triggering the change node? Sorry but I always tried to work around these http nodes and dont really know how to use them yet.

[bakman2]

The port is the same as the node-red interface (default 1880)

When you add a debug node to the http-in node you can see what is being requested.

Sorry but I always tried to work around these http nodes and dont really know how to use them yet.

Understandable, but once you understand what they do, they are extremely useful, because you can create API's with them very easily (the same as you did with nodejs + express, but it is already build in).

If I "emulate" your flow (i dont have mysql/tabulator etc):

I open a browser tab to: http://10.0.0.6:1880/fetchmessungen?page=16&paginationSize=1000, I see this in my node-red flow/debug

I setup the change node like this (this is a jsonata expression):

in text:

"select * from mytable limit "& msg.payload.paginationSize &" offset "& ($number(msg.payload.page)*$number(msg.payload.paginationSize))

So it reads the paginationSize and page properties it received and uses them to create a query (and calculates the offset).

This output you can put into the mysql node, it (hopefully) returns results. The http-response node will send the output back to tabulator.

Note the paginationSize parameter comes from the tabulator documentation

[JustinB]

Very nice, I think I'm getting in love with these http nodes.

The data is coming in from the database. The only problem we have now is that the array has to be named to data. Otherwhise tabulator isn't able to import the data.

EDIT: I tried it with a function node:

var data = msg.payload;
node.send(data);
node.done();

But the http-response node doesnt like that. I guess because its not the "original" message that comes from the http in node

[bakman2]

Then you can use (another) change node, after the mysql node and use the "move" option to move msg.payload to msg.data (depending on what it tries to read, because it could be msg.data, or msg.payload.data)

[JustinB]

[bakman2]

Could you try in a function node (after mysql node):

const data = msg.payload
msg.payload = {}
msg.payload.data = data
return msg;

[JustinB]

This is working, theres data in the table

But at least just one page

[JustinB]

I think we have to set the last_page somehow.

EDIT: I can set this inside the function node before the response. But have to get the total records first

[bakman2]

What you could do is, in the same change node as the msg.topic, add another parameter:

and then in the function node

msg.payload.last_page = msg.last_page

(not sure if the mysql node strips off all properties from msg, else you could skip the first step and directly set it to msg.payload.last_page = msg.page)