@JanIwnl welcome to the forum.
At this point, you have not provided enough information for someone to help you. No one but you knows:
- what platform you are using
- what version of Node-RED or node.js you are using (you can get this from the startup log)
- are you running Docker and/or Home Assistane
what the data being sent to theui-tablenode looks like - how you have configured the
ui-tablenode. - what you have in the template node.
My suggestion is to add a debug node (set to display the complete msg object) to the output of the sql node. Then put that data into an inject node and connect the inject node to the ui-table node and then select and export the inject node, ui-table node and template node and paste them to a reply and answer items 1, 2 and 3 above and that will give people a chance of helping you
In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)
```
code goes here
```
You can edit and correct your post by clicking the pencil 
icon.
See this post for more details - How to share code or flow json
.
Additionally,
- What SQL node are you using (provide a link to it from the catalog)
- You stated SQL Server which implies Microsoft SQL Server - but that node looks like MySQL (or other)
- What do you get out of the SQL node (hint: add a debug node AFTER the SQL node)
Thank you for you response, here is the flow
[
{
"id": "c3de653ef65210eb",
"type": "inject",
"z": "f3462e3d13b09243",
"name": "Start",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 270,
"y": 260,
"wires": [
[
"d648110d80c74935"
]
]
},
{
"id": "d648110d80c74935",
"type": "function",
"z": "f3462e3d13b09243",
"name": "Send SQL query",
"func": "msg.payload = `select * from cristi_teste_flow where klt_id = 'SSIB5230BE21D'`\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 460,
"y": 260,
"wires": [
[
"5b075331d3da1fd3",
"ee84cc2ad236c487"
]
]
},
{
"id": "3eabe0434a9e4a65",
"type": "debug",
"z": "f3462e3d13b09243",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 830,
"y": 300,
"wires": []
},
{
"id": "56778bea0b4d9324",
"type": "function",
"z": "f3462e3d13b09243",
"name": "Extract KLT ID",
"func": "if (msg.payload != \"\") {\n msg.KLT_ID = msg.payload;\n}\nelse {\n msg.KLT_ID = \"\";\n}\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 960,
"y": 240,
"wires": [
[
"8ebec30b127d2d46"
]
]
},
{
"id": "8ebec30b127d2d46",
"type": "ui_table",
"z": "f3462e3d13b09243",
"group": "6681fc5131a55311",
"name": "",
"order": 1,
"width": 8,
"height": 4,
"columns": [
{
"field": "id",
"title": "id",
"width": "3px",
"align": "left",
"formatter": "rownum",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "From Where",
"title": "From Where",
"width": "7px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ID_TYPE Piesa",
"title": "ID_TYPE Piesa",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_ID",
"title": "KLT_ID",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_ID_TYPE",
"title": "KLT_ID_TYPE",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_DATA_CREATED",
"title": "KLT_DATA_CREATED",
"width": "11px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ORDER Tehnic",
"title": "ORDER Tehnic",
"width": "17px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ORDER Logistic",
"title": "ORDER Logistic",
"width": "17px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "TERMINATED",
"title": "TERMINATED",
"width": "8px",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "LOCKED_COLUMN",
"title": "LOCKED_COLUMN",
"width": "10px",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
}
],
"outputs": 1,
"cts": true,
"x": 850,
"y": 460,
"wires": [
[]
]
},
{
"id": "739602dc6ea9385c",
"type": "template",
"z": "f3462e3d13b09243",
"name": "",
"field": "payload",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "msg.ui_control={\n \"tabulator\": {\n \"layout\": \"fitData\"\n }\n}\nreturn msg;\n",
"output": "str",
"x": 360,
"y": 460,
"wires": [
[
"1bb7c0fa7f666596"
]
]
},
{
"id": "1bb7c0fa7f666596",
"type": "ui_ui_control",
"z": "f3462e3d13b09243",
"name": "",
"events": "all",
"x": 620,
"y": 460,
"wires": [
[
"8ebec30b127d2d46"
]
]
},
{
"id": "23463cd878dbe50d",
"type": "ui_text_input",
"z": "f3462e3d13b09243",
"name": "",
"label": "Insert ID",
"tooltip": "",
"group": "887177f20dcb6406",
"order": 1,
"width": 6,
"height": 1,
"passthru": true,
"mode": "text",
"delay": "0",
"topic": "topic",
"sendOnBlur": true,
"className": "",
"topicType": "str",
"x": 280,
"y": 360,
"wires": [
[
"d648110d80c74935"
]
]
},
{
"id": "5b075331d3da1fd3",
"type": "mysql-r2",
"z": "f3462e3d13b09243",
"name": "Sql",
"host": "sdgsg",
"database": "APEDE",
"username": "OEMRH",
"password": "IEMES_APOCRYPHA",
"sql": "",
"port": "4356",
"pooling": false,
"waitForConnections": true,
"connectionLimit": "10",
"queueTimeout": "10000",
"x": 630,
"y": 260,
"wires": [
[
"56778bea0b4d9324",
"3eabe0434a9e4a65"
]
]
},
{
"id": "ee84cc2ad236c487",
"type": "debug",
"z": "f3462e3d13b09243",
"name": "debug 4",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 640,
"y": 140,
"wires": []
},
{
"id": "6681fc5131a55311",
"type": "ui_group",
"z": "f3462e3d13b09243",
"name": "KLT Table",
"tab": "abdf2904c6495f75",
"order": 2,
"disp": true,
"width": "8",
"collapse": false
},
{
"id": "887177f20dcb6406",
"type": "ui_group",
"z": "f3462e3d13b09243",
"name": "Insert KLT",
"tab": "abdf2904c6495f75",
"order": 1,
"disp": true,
"width": "7",
"collapse": false
},
{
"id": "abdf2904c6495f75",
"type": "ui_tab",
"name": "KLT Dashboard",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
i couldnt provide the real database[confidential]. What i actually want from this flow is when i will insert for example an id in the insert field to retrive me the data from the database into the table.
You did not provide a small example flow.
You do not have to provide the actual data, you can make it up but what I want to see is an inject node (with the example data) connected to the ui-table node and the ui-template node. That way I can see what you are doing.
When you want help you want to make it as easy as possible for others (who are volunteering their time) to help you solve your problem.
1 Like
Sorry for missunderstanding it, here is the flow
[
{
"id": "f0d87afef697cfba",
"type": "inject",
"z": "42f19ce051f21038",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 260,
"y": 720,
"wires": [
[
"b145487466d7167e"
]
]
},
{
"id": "b145487466d7167e",
"type": "mysql-r2",
"z": "42f19ce051f21038",
"name": "Sql",
"host": "rewe",
"database": "rtutru",
"username": "earryeryr",
"password": "yyfr",
"sql": "",
"port": "5473",
"pooling": false,
"waitForConnections": true,
"connectionLimit": "10",
"queueTimeout": "10000",
"x": 450,
"y": 720,
"wires": [
[
"dcff7fbfe70c3417",
"b3c8d59aab9190bb"
]
]
},
{
"id": "b3c8d59aab9190bb",
"type": "ui_table",
"z": "42f19ce051f21038",
"group": "b3a85a5f0312fd16",
"name": "",
"order": 1,
"width": 8,
"height": 4,
"columns": [
{
"field": "id",
"title": "id",
"width": "3px",
"align": "left",
"formatter": "rownum",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "From Where",
"title": "From Where",
"width": "7px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ID_TYPE Piesa",
"title": "ID_TYPE Piesa",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_ID",
"title": "KLT_ID",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_ID_TYPE",
"title": "KLT_ID_TYPE",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_DATA_CREATED",
"title": "KLT_DATA_CREATED",
"width": "11px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ORDER Tehnic",
"title": "ORDER Tehnic",
"width": "17px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ORDER Logistic",
"title": "ORDER Logistic",
"width": "17px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "TERMINATED",
"title": "TERMINATED",
"width": "8px",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "LOCKED_COLUMN",
"title": "LOCKED_COLUMN",
"width": "10px",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
}
],
"outputs": 1,
"cts": true,
"x": 770,
"y": 720,
"wires": [
[]
]
},
{
"id": "db236851ac441298",
"type": "template",
"z": "42f19ce051f21038",
"name": "",
"field": "payload",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "msg.ui_control={\n \"tabulator\": {\n \"layout\": \"fitData\"\n }\n}\nreturn msg;\n",
"output": "str",
"x": 460,
"y": 800,
"wires": [
[
"b3c8d59aab9190bb"
]
]
},
{
"id": "dcff7fbfe70c3417",
"type": "debug",
"z": "42f19ce051f21038",
"name": "debug 4",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 600,
"y": 680,
"wires": []
},
{
"id": "f95732d7fd3602d7",
"type": "ui_template",
"z": "42f19ce051f21038",
"group": "45fca58b7714ea8d",
"name": "",
"order": 3,
"width": 0,
"height": 0,
"format": "<style>\n .insert-button>.md-button {\n width: 100px;\n height: 40px;\n left:10px;\n top:40px;\n }\n\n .tabulator-headers{\n position:relative;\n margin-left:50px;\n }\n\n.nr-dashboard-cardpanel{\nmargin-left:50px\n}\n\n.nr-dashboard-textinput{\nmargin-left: auto;\nmargin-right: auto;\nwidth: 40px;\n}\n.insertKLT{\nposition:relative;\nmargin-left: 200px}\n\n.insert-text> md-input-container{\nfont-size:20px;\nmargin-left:-20px;\nheight:60px;\n\n}\n<style>\n",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": true,
"templateScope": "local",
"className": "",
"x": 280,
"y": 780,
"wires": [
[]
]
},
{
"id": "b3a85a5f0312fd16",
"type": "ui_group",
"z": "42f19ce051f21038",
"name": "KLT Table",
"tab": "abdf2904c6495f75",
"order": 2,
"disp": true,
"width": "8",
"collapse": false,
"className": ""
},
{
"id": "45fca58b7714ea8d",
"type": "ui_group",
"z": "42f19ce051f21038",
"name": "Insert KLT",
"tab": "abdf2904c6495f75",
"order": 1,
"disp": true,
"width": "7",
"collapse": false,
"className": "insertKLT"
},
{
"id": "abdf2904c6495f75",
"type": "ui_tab",
"name": "KLT Dashboard",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
This is the third time I have asked this:
If you don't provide a flow with with an inject node (with test data) connected to the ui-template node I will be dropping off this thread.
[{"id":"db7089494f3eb831","type":"ui_table","z":"a0da04ce2ca2ea94","group":"7d53e5625b8837b0","name":"","order":15,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":700,"y":390,"wires":[]},{"id":"7784ee5d61fc21b8","type":"inject","z":"a0da04ce2ca2ea94","name":"some data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"rdate\":\"2023-04-30\",\"totalcases\":1000,\"totalcl\":10,\"caseperman\":100,\"costpercase\":23.1,\"cpm\":47.7,\"cpc\":11},{\"rdate\":\"2023-05-01\",\"totalcases\":10000,\"totalcl\":70,\"caseperman\":143,\"costpercase\":23.5,\"cpm\":47.7,\"cpc\":10}]","payloadType":"json","x":490,"y":390,"wires":[["db7089494f3eb831"]]},{"id":"274eccb4d0c5a62b","type":"inject","z":"a0da04ce2ca2ea94","name":"Clear","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[]","payloadType":"json","x":490,"y":450,"wires":[["db7089494f3eb831"]]},{"id":"7d53e5625b8837b0","type":"ui_group","name":"SELECT","tab":"49ce3da32b7171c0","order":3,"disp":false,"width":"30","collapse":false,"className":""},{"id":"49ce3da32b7171c0","type":"ui_tab","name":"APEX","icon":"dashboard","order":3,"disabled":false,"hidden":false}]
@JanIwnl try providing some data like this...
in your template node, you are using msg.payload = select * from cristi_teste_flow where klt_id = 'SSIB5230BE21D'
i think it should be msg.topic not msg.payload for a query.
i may be wrong, since i dont use the mysql-r2 node but use mysql node (node-red-node-mysql)
Edit: the doc for mysql-r2 says
Input
- msg.sql: The SQL query to execute.
I hope this is what you want
[
{
"id": "44896e517aa8f384",
"type": "inject",
"z": "42f19ce051f21038",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "select * from cristi_teste_flow where klt_id='SSIB5230BE21D'",
"payloadType": "str",
"x": 330,
"y": 740,
"wires": [
[
"117c88cefc32b538"
]
]
},
{
"id": "117c88cefc32b538",
"type": "ui_table",
"z": "42f19ce051f21038",
"group": "b3a85a5f0312fd16",
"name": "",
"order": 1,
"width": 8,
"height": 4,
"columns": [
{
"field": "id",
"title": "id",
"width": "3px",
"align": "left",
"formatter": "rownum",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "From Where",
"title": "From Where",
"width": "7px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ID_TYPE Piesa",
"title": "ID_TYPE Piesa",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_ID",
"title": "KLT_ID",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_ID_TYPE",
"title": "KLT_ID_TYPE",
"width": "9px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "KLT_DATA_CREATED",
"title": "KLT_DATA_CREATED",
"width": "11px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ORDER Tehnic",
"title": "ORDER Tehnic",
"width": "17px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "ORDER Logistic",
"title": "ORDER Logistic",
"width": "17px",
"align": "left",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "TERMINATED",
"title": "TERMINATED",
"width": "8px",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "LOCKED_COLUMN",
"title": "LOCKED_COLUMN",
"width": "10px",
"align": "center",
"formatter": "plaintext",
"formatterParams": {
"target": "_blank"
}
}
],
"outputs": 1,
"cts": true,
"x": 690,
"y": 740,
"wires": [
[]
]
},
{
"id": "e8019b7b3ee4b07d",
"type": "template",
"z": "42f19ce051f21038",
"name": "",
"field": "payload",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "msg.ui_control={\n \"tabulator\": {\n \"layout\": \"fitData\"\n }\n}\nreturn msg;\n",
"output": "str",
"x": 340,
"y": 840,
"wires": [
[
"082e823246a6a83a"
]
]
},
{
"id": "082e823246a6a83a",
"type": "ui_ui_control",
"z": "42f19ce051f21038",
"name": "",
"events": "all",
"x": 600,
"y": 840,
"wires": [
[
"117c88cefc32b538"
]
]
},
{
"id": "b3a85a5f0312fd16",
"type": "ui_group",
"z": "42f19ce051f21038",
"name": "KLT Table",
"tab": "abdf2904c6495f75",
"order": 2,
"disp": true,
"width": "8",
"collapse": false,
"className": ""
},
{
"id": "abdf2904c6495f75",
"type": "ui_tab",
"name": "KLT Dashboard",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
No it is not.
Your inject node contains only an sql select statement. It is useless since no one but you has the database and data. I've asked (multiple times) for some test data and you have not provided it.
Good luck with your issue, I'm done trying to help you.
did you try to copy this ?
[{"id":"db7089494f3eb831","type":"ui_table","z":"a0da04ce2ca2ea94","group":"7d53e5625b8837b0","name":"","order":15,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":700,"y":390,"wires":[]},{"id":"7784ee5d61fc21b8","type":"inject","z":"a0da04ce2ca2ea94","name":"some data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"rdate\":\"2023-04-30\",\"totalcases\":1000,\"totalcl\":10,\"caseperman\":100,\"costpercase\":23.1,\"cpm\":47.7,\"cpc\":11},{\"rdate\":\"2023-05-01\",\"totalcases\":10000,\"totalcl\":70,\"caseperman\":143,\"costpercase\":23.5,\"cpm\":47.7,\"cpc\":10}]","payloadType":"json","x":490,"y":390,"wires":[["db7089494f3eb831"]]},{"id":"274eccb4d0c5a62b","type":"inject","z":"a0da04ce2ca2ea94","name":"Clear","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[]","payloadType":"json","x":490,"y":450,"wires":[["db7089494f3eb831"]]},{"id":"7d53e5625b8837b0","type":"ui_group","name":"SELECT","tab":"49ce3da32b7171c0","order":3,"disp":false,"width":"30","collapse":false,"className":""},{"id":"49ce3da32b7171c0","type":"ui_tab","name":"APEX","icon":"dashboard","order":3,"disabled":false,"hidden":false}]
That is not the flow you posted last time.
sorry, i was asking @JanIwnl
Oh yes, it was me getting confused. Sorry.
How do I stop recieving mails for this topic, my mail-administrator is giving warning continuously due to long subject line.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
