MQTT to SQLite help please

Dashboard
1

So I am trying to store MQTT data into a SQLite, this data will not be RTD, it will only be stored ON VALUE CHANGE

Node red
Node red1300×629 36.5 KB

This is what I have so far. I am using the Inject to create a data base
Next I am Joining 4 MQTT In nodes in a Join node as an Array, and I'm getting my values
Then I am going to a function Node

Function
Function1309×881 17 KB

And then going into my SQLite node

I keep getting the following Syntax error.

It says "ERROR: SQLITE_ERROR: unrecognized token: "{""

Not sure where my Problem is

2

Firstly, please post code as code image it makes it a lot easier to read and to respond to.

Secondly, you haven't shown an example of the actual data you are getting back from MQTT. Most likely it is not quite what you think which is throwing out your insert.

Thirdly, you have multiple fields that you are updating but only one input.

My guess is that the msg.payload you are getting is an object and you need to pick it apart.

3

Using join node in array mode is dangerous as MQTT values can arrive in any order. Use key/Val mode to ensure values are named properties.

Also, you can't just append an array like that anyway.

4

Here is the data I am getting back after I switched to a key value Object

data

5

You need to change the MQTT nodes to parse JSON. Then look at what comes out of the MQTT and JOIN nodes & use the .value properties in your SQL query.

6

This is what is coming out of the join node now after switching to JSON

json values

Does this look correct

7

Only you know what is correct.

Expand all the items. Do you see the values you want to put in the database?

8

I'm still very new to node red, I am a controls engineer by trade, usually just program ladder logic, g-code etc. but yes it is right, did not know you could expand them. lol

not sure where i do this

So if i want to get this data into a SQLite table, I guess what is the next step, ive been working on this for a few shifts now and am not getting anywhere.

9

You are almost there.

Spend the next 5 minutes on this. Pay particular attention to the bit about the "copy path" button that appears under your mouse cursor when you hover over a value in the debug sidebar.

Then use that to help you build a SQL query.

e.g...

msg.topic = `INSERT INTO myTable (col1, col2, col3, col4) VALUES (${msg.payload.sometinng.value}, ${msg.payload.somethingElse.value}, ${msg.payload.another.value}, ${msg.payload.i_dont_know.value});`
10

I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

11

I appreciate all the help!!

12

Note where your image says "Object" - that indicates to you that you have some structured data. Typically SQL doesn't like that and your insert statement wants simple data - a string, number, date, etc.

So you need to pick out the actual data values from those objects, that's what you need in your SQL statement.

13

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