# Saving Data in DB at SQLite Function - Help!

**URL:** <https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789>\
**Category:** General\
**Created:** [12 November 2018 10:23 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789 "2018-11-12T10:23:15Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [13 November 2018 12:40 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/21 "2018-11-13T12:40:46Z")

</div>

> [@zenofmud](#):
>
> Did you even try Nick's advice?

Yes I tried but didnt work unfortunately.

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/0f1013a279f528eaeb8dc4a9bc9f7e9a0671c58a.png)

> [@knolleary](#):
>
> I gave you the one line change to make in my reply. Please at least try it for yourself.

After I tried I asked you, what I making wrong I dont know.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [13 November 2018 12:41 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/22 "2018-11-13T12:41:44Z")

</div>

> [@wtrk](#):
>
> After I tried I asked you, what I making wrong I dont know.

But you didn't say you had tried it and you didn't provide any information about what happened. I'm sorry, but you have to help us to help you.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [13 November 2018 12:43 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/23 "2018-11-13T12:43:13Z")

</div>

You can see the error coming back from the SQLITE node when you pass it those queries.

> no such table: Status

I hope you can guess what that means.... it's hard to be much clearer. You need to create the database table in sqlite before you can insert data into it.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [13 November 2018 13:02 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/24 "2018-11-13T13:02:55Z")

</div>

> [@knolleary](#):
>
> > [@wtrk](#):
> >
> > After I tried I asked you, what I making wrong I dont know.
> 
> But you didn't say you had tried it and you didn't provide any information about what happened. I'm sorry, but you have to help us to help you.

Sorry.

Eventually I did it with replacing code. I used this code for values sending db.

```auto
var date = Date.now();
var newMsg = {
    "topic": "INSERT INTO Status VALUES ( " + date + ", " +
    msg.payload + ", " + msg.humidity + ")"
}
return newMsg;

```

I almost completed my project first part saving data.  
But in phpmyadmin some records saving many times in like picture. Any solution you advice me ?

Also Device name should be static, like Laser 1 - Laser 2 .... like this. How can I make this ?

 ![Untitled_1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/19917db11ca2de696fcbd9a6e55ce0e599d99926.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [13 November 2018 13:09 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/25 "2018-11-13T13:09:00Z")

</div>

You see how you split the flow at the begining to go to the teo function nodes? Now you have two msg's going to the next flow and it creates two outputs for every input

so 1 reading from the dht11 becomes two msgs that become 4 sql statements. You sure that is what you want to do?

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [13 November 2018 13:31 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/26 "2018-11-13T13:31:54Z")

</div>

> [@zenofmud](#):
>
> You see how you split the flow at the begining to go to the teo function nodes? Now you have two msg's going to the next flow and it creates two outputs for every input

Dear @zenofmud my english is not enough good. Just want try to what this sentence mean.

I just want to 1 record, one of them temperature other one is humidity. Just make this.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [13 November 2018 13:59 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/27 "2018-11-13T13:59:46Z")

</div>

since the original msg has both the temperature (msg.payload) and the humidity (msg.humidity) you can get rid of the 'temperature' node since it really doesn't do anything. Next remove the line from the humidity function node to the `Insert Statement` node. and you should be all set.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [13 November 2018 14:08 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/28 "2018-11-13T14:08:33Z")

</div>

> [@zenofmud](#):
>
> since the original msg has both the temperature (msg.payload) and the humidity (msg.humidity) you can get rid of the 'temperature' node since it really doesn't do anything. Next remove the line from the humidity function node to the `Insert Statement` node. and you should be all set.

Ok I will try and share results, thanks.  
But I used 2 function node one of them temp and the other humid graph and gauge. Is one function enough for showing both ?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [13 November 2018 14:46 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/29 "2018-11-13T14:46:05Z")

</div>

The only reason you NEED the humidity function is to move the msg.humidity to msg.payload (you could have used a `change` node) so you can pass the data to the graph in msg.payload

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [14 November 2018 07:10 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/30 "2018-11-14T07:10:41Z")

</div>

Other problem is solved and now started new one, I want to send dsm status to DB but after deployment I take an **"SyntaxError: Unexpected token )"**

Not saving values and date and other to db, any advice ?

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/b77d515baa075ac00fde777626cefcdd54b8f99f.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 November 2018 07:30 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/31 "2018-11-14T07:30:11Z")

</div>

Please export your flow and paste it into a reply and make sure to add the back tic’s

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [14 November 2018 07:32 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/32 "2018-11-14T07:32:16Z")

</div>

> [@zenofmud](#):
>
> Please export your flow and paste it into a reply and make sure to add the back tic’s

Here is my flow.

```auto
[{"id":"48b91207.ef6bec","type":"rpi-gpio in","z":"3acbc945.b2c336","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":410,"y":220,"wires":[["544b54b5.c1860c"]]},{"id":"544b54b5.c1860c","type":"dsm","z":"3acbc945.b2c336","name":"state right","sm_config":"{\n \"triggerInput\": \"payload\",\n \"currentState\": \"running\",\n \"states\": {\n \"stopped\": {\n \"0\": \"running\"\n },\n \"running\": {\n \"1\": \"stopped\"\n }\n },\n \"methods\": {\n \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 1 Working';\",\n \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1 Error!!!';\"\n }\n}","x":596,"y":218,"wires":[["361c506e.fba1b","5c067af9.126b44","c68ffb5b.cde4e8"]]},{"id":"361c506e.fba1b","type":"ui_button","z":"3acbc945.b2c336","name":"Laser 1","group":"5e2b93d7.7465fc","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":760,"y":140,"wires":[[]]},{"id":"5c067af9.126b44","type":"debug","z":"3acbc945.b2c336","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":750,"y":180,"wires":[]},{"id":"c68ffb5b.cde4e8","type":"function","z":"3acbc945.b2c336","name":"insert Statement","func":"var date = Date.now();\nvar topic = ();\nvar label = ();\n\nvar newMsg = {\n \"topic\": \"INSERT INTO cube VALUES ( \"+ date +\" , \" +\n msg.topic + \",\" + msg.label + \",\" + msg.payload + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"x":800,"y":220,"wires":[["9e9baecc.2325f"]]},{"id":"9e9baecc.2325f","type":"sqlite","z":"3acbc945.b2c336","mydb":"44d3ca4a.01a264","sqlquery":"msg.topic","sql":"","name":"Record","x":960,"y":220,"wires":[[]]},{"id":"ef8b2b30.2a6698","type":"inject","z":"3acbc945.b2c336","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":420,"y":180,"wires":[["544b54b5.c1860c"]]},{"id":"5e2b93d7.7465fc","type":"ui_group","z":"","name":"Assembly Lines","tab":"3e7f6350.5b23cc","disp":true,"width":"6","collapse":false},{"id":"44d3ca4a.01a264","type":"sqlitedb","z":"","db":"/var/www/html/database/cube","mode":"RWC"},{"id":"3e7f6350.5b23cc","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"}]

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 November 2018 08:15 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/33 "2018-11-14T08:15:08Z")

</div>

add a debug node to the output of your 'Insert Statement' function node and set it to display complete message and what does it show?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 November 2018 08:19 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/34 "2018-11-14T08:19:43Z")

</div>

also in your DSM node you have  
`"onBeforeTransition": "msg.pyload = msg.payload.toString();",`  
Do you really want to create msg.pyload?

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [14 November 2018 08:43 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/35 "2018-11-14T08:43:34Z")

</div>

> [@zenofmud](#):
>
> add a debug node to the output of your 'Insert Statement' function node and set it to display complete message and what does it show?

you can see in picture..  
It looks good but, also want to see +stopped and running text not ( 1 or 0 ) + date and time in Db , how can I add this ?

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/24aac74ec6cb930f6af0e82b8d276503d2278c14.png)

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [14 November 2018 08:52 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/36 "2018-11-14T08:52:16Z")

</div>

@wtrk modify your query so you're inserting the information you want. Your query currently inserts `msg.payload` in the query. If you want the text, then appears to be in `msg.topic` instead.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 November 2018 08:53 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/37 "2018-11-14T08:53:11Z")

</div>

You have changed the function node. Why? All I asked you to do was to add the debug node in.

If you are going to change things between your posts, it makes it hard to help you.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [14 November 2018 09:02 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/38 "2018-11-14T09:02:13Z")

</div>

> [@zenofmud](#):
>
> also in your DSM node you have  
> ` "onBeforeTransition": "msg.pyload = msg.payload.toString();",`  
> Do you really want to create msg.pyload?

I dont know what this code is doing and if not necessary, I dont want it

> [@zenofmud](#):
>
> You have changed the function node. Why? All I asked you to do was to add the debug node in.
> 
> If you are going to change things between your posts, it makes it hard to help you.

Just deleted  
var topic = ();  
var label = ();

Because, when I deploy the flow it starts with error, you can see in picture.

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e5ee0e5e472ca75debff8a7146d6e756bbb091f6.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 November 2018 09:22 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/39 "2018-11-14T09:22:12Z")

</div>

ok, this is the key - you show two insert statements that are different but one works and the other doesn't. The two columns you are adding are defined as `text` in the DB so they need to have quote marks around them in the final sql statement.

ThIs can be tricky because you have to escape the quotes to use them inside quote. thy this in your function

```auto
var newMsg = {
    "topic": "INSERT INTO cube VALUES ( "+ date +" , \"" +
    msg.topic + "\",\"" + 
    msg.label + "\"," + msg.payload + ")"
}

```

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [14 November 2018 09:23 UTC](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789/40 "2018-11-14T09:23:10Z")

</div>

I wrote not needed message, sorry

[Previous page](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789.md?page=1)

[Next page](https://discourse.nodered.org/t/saving-data-in-db-at-sqlite-function-help/4789.md?page=3)