MQTT + Sqlite? , no luck

General
1

I am trying to send MQTT sensor data to an SQL db. It works when I use the inject node but nothing happens when I use it with function node that takes data from the MQTT In node. I use the following code for the function, that take the current time and the sensor msg.payload.

var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch

msg.topic = "INSERT INTO heatindex (time, value) VALUES ('" + sqliteTimeStamp + "', '" + msg.payload + "');";

The flow:

[
    {
        "id": "7dfb81cf2d764495",
        "type": "mqtt in",
        "z": "8154c9b075d69e4f",
        "name": "",
        "topic": "upperFloor/realTemp",
        "qos": "2",
        "datatype": "auto",
        "broker": "15ad3e48f1d54338",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 610,
        "y": 260,
        "wires": [
            [
                "a0c67f9b30120712"
            ]
        ]
    },
    {
        "id": "a0c67f9b30120712",
        "type": "function",
        "z": "8154c9b075d69e4f",
        "name": "",
        "func": "var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch\nmsg.topic = \"INSERT INTO heatindex (time, value) VALUES ('\" + sqliteTimeStamp + \"', '\" + msg.payload + \"');\";",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 240,
        "wires": [
            [
                "e93f7022226fc3f2",
                "aa5c3967ce2a95c5"
            ]
        ]
    },
    {
        "id": "aa5c3967ce2a95c5",
        "type": "debug",
        "z": "8154c9b075d69e4f",
        "name": "function debug",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1200,
        "y": 320,
        "wires": []
    },
    {
        "id": "e93f7022226fc3f2",
        "type": "sqlite",
        "z": "8154c9b075d69e4f",
        "mydb": "5c4597463672354d",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "",
        "x": 1430,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "15ad3e48f1d54338",
        "type": "mqtt-broker",
        "name": "",
        "broker": "localhost",
        "port": "1883",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "5",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "sessionExpiry": ""
    },
    {
        "id": "5c4597463672354d",
        "type": "sqlitedb",
        "db": "/home/pi/Documents/NodeRedLogs/upperFloor.db",
        "mode": "RWC"
    }
]

Any ideas?

2

So how are you passing the msg on from the function node?
(hint: Writing Functions : Node-RED)

3

Add a debug node showing what is generated by the debug node and another showing what is generated by the function. They must be different. If you cannot see it then post the debug output from both here.

4

Yep. Funtion get the data from the mqtt in, then passes it as a msg.topic to sqlite node. Ill give more info later on as I am away from my pc righr now.

5

I added one. No output to the debug node. Ill provide more details later, when I'll be next to my pc.

6

Does it? @zenofmud gave you a massive hint. You did not pick it up, so here I will say it clearly. You Have not returned your function.
eg

[{"id":"36d09e7e.f733da","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":800,"wires":[["86c0438e.f8b3f8","dbca1102.4df588"]]},{"id":"86c0438e.f8b3f8","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = \"test\"","outputs":1,"noerr":0,"initialize":"","finalize":"","x":340,"y":820,"wires":[["314634da.6b51fc"]]},{"id":"dbca1102.4df588","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = \"test2\"\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":880,"wires":[["aa72e434.9d2a8"]]},{"id":"314634da.6b51fc","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":540,"y":820,"wires":[]},{"id":"aa72e434.9d2a8","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":880,"wires":[]}]

One function passes on it's message, the other does not.

7

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