SQLite SELECT from node-red function return nothing but in DB browser works fine

Well Hi first!
I'm a new with SQLite database, so problem is that SELECT statement in DB browser works just fine but in function node nothing is return.

DB Browser
image

Flow

[
    {
        "id": "2c85767fe0fb433d",
        "type": "sqlite",
        "z": "f4319603ec036678",
        "mydb": "0aa4467edfdf9b8e",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "DB",
        "x": 610,
        "y": 180,
        "wires": [
            []
        ]
    },
    {
        "id": "e5eecd6db43aec5f",
        "type": "function",
        "z": "f4319603ec036678",
        "name": "Get TracID from table Tracks",
        "func": "msg.topic = \"SELECT id FROM tr ORDER BY id DESC LIMIT 1\";\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 400,
        "y": 160,
        "wires": [
            [
                "2c85767fe0fb433d",
                "e0a66b35608cbedb"
            ]
        ]
    },
    {
        "id": "e0a66b35608cbedb",
        "type": "debug",
        "z": "f4319603ec036678",
        "name": "SELECT",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 620,
        "y": 140,
        "wires": []
    },
    {
        "id": "0ae7bf79ebc920eb",
        "type": "comment",
        "z": "f4319603ec036678",
        "name": "SELECT FROM problem",
        "info": "",
        "x": 290,
        "y": 100,
        "wires": []
    },
    {
        "id": "62079b96238a5a46",
        "type": "inject",
        "z": "f4319603ec036678",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 170,
        "y": 160,
        "wires": [
            [
                "e5eecd6db43aec5f"
            ]
        ]
    },
    {
        "id": "0aa4467edfdf9b8e",
        "type": "sqlitedb",
        "db": "/home/pi/dbs/boats",
        "mode": "RWC"
    }
]

Table

CREATE TABLE "tr" ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
'deviceid' TEXT,
'time' INTEGER,
'DateTime' DATETIME, 
'name' TEXT,
'latitude_start' REAL,
'longitude_start' REAL, 
'latitude_end' REAL, 
'longitude_end' REAL);

DEBUG

image

node-red status
16 Feb 20:30:38 - [info]

Welcome to Node-RED

16 Feb 20:30:38 - [info] Node-RED version: v2.2.0
16 Feb 20:30:38 - [info] Node.js version: v14.19.0
16 Feb 20:30:38 - [info] Linux 5.10.63-v7+ arm LE
16 Feb 20:30:41 - [info] Loading palette nodes
16 Feb 20:30:51 - [info] Worldmap version 2.25.0
16 Feb 20:30:51 - [info] Dashboard version 3.1.5 started at /ui
16 Feb 20:30:55 - [info] Settings file : /home/pi/.node-red/settings.js
16 Feb 20:30:55 - [info] Context store : 'default' [module=localfilesystem]
16 Feb 20:30:55 - [info] User directory : /home/pi/.node-red
16 Feb 20:30:55 - [warn] Projects disabled : editorTheme.projects.enabled=false
16 Feb 20:30:55 - [info] Flows file : /home/pi/.node-red/status
16 Feb 20:30:56 - [error] Uncaught Exception:
16 Feb 20:30:56 - [error] Error: listen EADDRINUSE: address already in use 0.0.0.0:1880
at Server.setupListenHandle [as _listen2] (net.js:1331:16)
at listenInCluster (net.js:1379:12)
at doListen (net.js:1516:7)
at processTicksAndRejections (internal/process/task_queues.js:83:21)

So I'm stuck here and need help.
Thx

Do you have a debug that we can see after the sqlite node?

Are you sure you have created the table? I don't believe you use quotes around the table name
[EDIT] I'm mistaken, you can use quotes

Are you sure you have data in it?
can you show how you are adding data to the table?
add a catch node connected to a debug node(set to display the complete msg object) and add a new row to the table.

In that debug, please switch to showing the whole msg rather than just the payload.

Yes, I can add new record.

Here is debug full object
image

I guess that is the output of the Debug node that is wired after your Function node (named SELECT)
but as @E1cid pointed out you need to wire a Debug node after the Sqlite node to see the output of your database. By importing your flow i dont see one.

2 Likes

Yep, sorry.

Appears to be working to me. It returns an array with one item id.

Yes, thats it. Thx for effort.
I'm new in node-red and sqlite.
Regards

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