Hello! I have a problem with my dashboard, im trying to extract from Oracle sql a table based on what i insert in the insert field but i can't get it done. v3.0.2, Windows, docker


[
    {
        "id": "1f8e6db45193bc52",
        "type": "ui_text_input",
        "z": "42f19ce051f21038",
        "name": "",
        "label": "Insert ID",
        "tooltip": "",
        "group": "45fca58b7714ea8d",
        "order": 1,
        "width": 6,
        "height": 1,
        "passthru": true,
        "mode": "text",
        "delay": "0",
        "topic": "topic",
        "sendOnBlur": false,
        "className": "insert-text",
        "topicType": "str",
        "x": 280,
        "y": 360,
        "wires": [
            [
                "c2e5246ddda832c5"
            ]
        ]
    },
    {
        "id": "c2e5246ddda832c5",
        "type": "function",
        "z": "42f19ce051f21038",
        "name": "function 7",
        "func": "\nvar topic = msg.topic;\nmsg.topic = `select * from cristi_teste_flow where klt_id = 'SSIB5230BE21D'`\nreturn msg;\n\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 540,
        "y": 360,
        "wires": [
            [
                "1003cb18009c04b1",
                "6e32373eb060306f"
            ]
        ]
    },
    {
        "id": "1003cb18009c04b1",
        "type": "mysql-r2",
        "z": "42f19ce051f21038",
        "name": "Sql",
        "host": "sdf",
        "database": "",
        "username": "fghj",
        "password": "hgj",
        "sql": "",
        "port": "546",
        "pooling": false,
        "waitForConnections": true,
        "connectionLimit": "10",
        "queueTimeout": "10000",
        "x": 750,
        "y": 360,
        "wires": [
            [
                "5eab8cab6dc92f90",
                "8e9e242d4cee0806"
            ]
        ]
    },
    {
        "id": "8e9e242d4cee0806",
        "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": 1190,
        "y": 360,
        "wires": [
            []
        ]
    },
    {
        "id": "e06287285ddaa001",
        "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": 760,
        "y": 500,
        "wires": [
            [
                "114e54c85f5e34e5"
            ]
        ]
    },
    {
        "id": "114e54c85f5e34e5",
        "type": "ui_ui_control",
        "z": "42f19ce051f21038",
        "name": "",
        "events": "all",
        "x": 1020,
        "y": 500,
        "wires": [
            [
                "8e9e242d4cee0806"
            ]
        ]
    },
    {
        "id": "4bc575ec800ff3bf",
        "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": 400,
        "y": 500,
        "wires": [
            []
        ]
    },
    {
        "id": "ccb88914b9b7883d",
        "type": "inject",
        "z": "42f19ce051f21038",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 300,
        "y": 260,
        "wires": [
            [
                "c2e5246ddda832c5"
            ]
        ]
    },
    {
        "id": "6e32373eb060306f",
        "type": "debug",
        "z": "42f19ce051f21038",
        "name": "debug 1",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 700,
        "y": 260,
        "wires": []
    },
    {
        "id": "5eab8cab6dc92f90",
        "type": "debug",
        "z": "42f19ce051f21038",
        "name": "debug 2",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 960,
        "y": 260,
        "wires": []
    },
    {
        "id": "45fca58b7714ea8d",
        "type": "ui_group",
        "z": "42f19ce051f21038",
        "name": "Insert KLT",
        "tab": "abdf2904c6495f75",
        "order": 1,
        "disp": true,
        "width": "7",
        "collapse": false,
        "className": "insertKLT"
    },
    {
        "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
    }
]

Hi @JanIwnl.

1. The Database

I'm trying to extract from Oracle sql

You seem to be using node-red-mysql-r2 which states

Prerequisites:
A MySQL database instance.

Can this node connect to an Oracle database?
Or maybe you have an SQL Server database, as your other post implies.
What kind of database is it?

2. The Query
You define an SQL query in your function node:

msg.topic = `select * from cristi_teste_flow where klt_id = 'SSIB5230BE21D'`

According to the documentation for node-red-mysql-r2 it expects

msg.sql: The SQL query to execute.

I'm using the node-red-mysql-r2 because is the only one where i could connect the sql server and for the query i made the changes you provide to me.

I repeat, what kind of database is it - multiple choice

A Microsoft SQL Server
B MySQL
C MariaDB
D Oracle
E Postgres
F InfluxDB
G SQLite
H Other (please specify)

I understand now, it is an Oracle database

Thanks.

I have never used Node-red with Oracle so I cannot tell if you are using the right node to interface with it.

Maybe there are some Oracle users here who can help?

1 Like

I will be surprised if that node works with an Oracle database, it says it is for MySQL databases.

Did you check to see if any of these nodes do what you want?