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

Well.. it is what it is :smile:

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:

image

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 :joy:)

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.

@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:

image

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

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 .

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

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?

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.

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

1 Like

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

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.

image

image

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

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)

image

:thinking: :thinking:

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

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

This is working, theres data in the table :partying_face:

But at least just one page :thinking:

1 Like

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

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)

I think the last_page represents the last number you can click in the table. If I set last_page to 15 I can navigate up to 15.

So as you said earlier

I have to get the total rows from the database, do the calculation and write it to last_page

you can chain the nodes, first do a query like select count(*) from yourtable, and use a change node to set to some total property and use that for calculations and from there create the new query

1 Like

It's finally working. Thanks for all your effort! (And very sorry for the little dispute)

A little summary for everybody who is interested:

The flow

Every node from left to right (except database):

image

image

And the code of the table(inside template node):

<script type="text/javascript" src="/tabulator-tables/dist/js/tabulator.min.js"></script>
<link rel="stylesheet" href="/tabulator-tables/dist/css/tabulator_semanticui.min.css" />
<div id="table-messungen"></div>
<script>
    var tableM = new Tabulator("#table-messungen", {
        height:"1000px",
        placeholder:"Keine Daten verfügbar!",
        layout:"fitColumns",
        pagination:true,
        paginationMode:"remote",
        //groupBy:"Zeitstempel",
        //groupToggleElement:"header",
        ajaxURL:"http://10.207.48.36:1880/fetchmessungen",
        paginationSize:1000,
    /*
        initialSort:[
            {column:"Zeitstempel", dir:"asc"},
        ],
    */
        columns:[
            {title:"Register", field:"Register", width:150},
            {title:"Druck (Pascal)", field:"Druck"},
            {title:"Zeitstempel", field:"Zeitstempel"},
        ],
       
    });
</script>

Nice :smile:

Now just for a hint, you can now also create your own webpage, by adding a normal template node (non-UI) with the html/javascript/css code -> response node and then opening the url in a new tab :slight_smile:

I use this setup a lot to create custom API interfaces / pages.

1 Like

Thats cool! Thanks a lot!

You were right, your solution was the better one :wink:

1 Like

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.