# Sqlite INSERT command syntax

**URL:** <https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471>\
**Category:** General\
**Created:** [10 February 2020 01:32 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471 "2020-02-10T01:32:28Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [10 February 2020 01:32 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/1 "2020-02-10T01:32:28Z")

</div>

I'm founding difficulties to apply the correct syntax in regarding the INSERT command  
that can be seen in the following function code.

```auto
msg = "(UTC, DateTime, hp,lp) values (" + msg.payload + " , " + " datetime " + ", " + hp + ","+ lp + ")"; 

```

As it can be seen below , **datetime** is the local date and time I'm intending to insert into the db and it's part of the INSERT command into sqlite.  
I do not succeed in choosing the correct syntax for **datetime** that I understand is a string.  
The flow also shows the CREATE TABLE node, where is shown that **DateTime** is a column header.

_This the flow:_

 ![Flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/9/4920de8579fed3fcb9d2fac66734ef5df3e47e38.png)

_This the function " Insert values in hvac3 "_

```auto
var now = new Date();
var yyyy = now.getFullYear();
var mm = now.getMonth() < 9 ? "0" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based
var dd = now.getDate() < 10 ? "0" + now.getDate() : now.getDate();
var hh = now.getHours() < 10 ? "0" + now.getHours() : now.getHours();
var mmm = now.getMinutes() < 10 ? "0" + now.getMinutes() : now.getMinutes();
var ss = now.getSeconds() < 10 ? "0" + now.getSeconds() : now.getSeconds();
var datetime = yyyy +"/"+ mm + "/"+ dd +"T" + hh + ":" + mm + ":"+ ss;

var hp;
var lp;

hp=flow.get('hp') || 0; // retrive saved varaibles from the flow
lp=flow.get('lp') || 0;

// msg.payload is the UTC
msg = "(UTC, DateTime, hp,lp) values (" + msg.payload + " , " + " datetime " + ", " + hp + ","+ lp + ")"; 
var topic="INSERT INTO hvac3" + msg;
var msg1={};
msg1.topic=topic;
return msg1;

```

Thanks for some help with that.

---

<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:** [10 February 2020 08:02 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/2 "2020-02-10T08:02:45Z")

</div>

What is the table definition ?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 February 2020 09:16 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/3 "2020-02-10T09:16:09Z")

</div>

Also feed the output of the function node into a debug node set to Show Complete Message and copy/paste the output here. Also paste the error message that you should see on the output of the db node.

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [10 February 2020 16:26 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/4 "2020-02-10T16:26:04Z")

</div>

Hi there !

Here it's what you asking for:

_ **Outcome from Function node```** _

```auto
/10/2020, 8:15:28 AMnode: d1e43b2a.41c358
INSERT INTO hvac3(UTC, DateTime, hp,lp) values (1581351328634 , datetime , 100.3,92.8) : msg : Object
object
topic: "INSERT INTO hvac3(UTC, DateTime, hp,lp) values (1581351328634 , datetime , 100.3,92.8)"
_msgid: "38ecdc12.a9d7b4"

*Outcome from Sqlite node*

2/10/2020, 8:15:28 AMnode: Sqlite
msg : error
"Error: SQLITE_ERROR: no such column: datetime"

```

**This the table definition so far.**

```auto
 CREATE TABLE hvac3 ( id INTEGER PRIMARY KEY AUTOINCREMENT, UTC NUMERIC, DateTime TEXT , hp NUMERIC, lp NUMERIC)  

```

Thank you.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 February 2020 16:46 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/5 "2020-02-10T16:46:30Z")

</div>

> [@NODE777](#):
>
> msg = "(UTC, DateTime, hp,lp) values (" + msg.payload + " , " + " datetime " + ", " + hp + ","+ lp + ")";

You have datetime in quotes so it has put that in as a string. You want something like

```auto
msg = "(UTC, DateTime, hp,lp) values (" + msg.payload + " , /"" + datetime + "/", " + hp + ","+ lp + ")";

```

I think it is easier to get right using the syntax

```auto
msg = `(UTC, DateTime, hp,lp) values (${msg.payload} , "${datetime}", ${hp}, ${lp})`;

```

Don't use msg as a variable name unless it is a message, you will get confused at some point if you do that. At least I would.  
Also it is good practice not to make a new message when returning, but return the original message after setting up the properties it needs.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 February 2020 16:53 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/6 "2020-02-10T16:53:54Z")

</div>

Even better, if you want the current time in an sqlite db you can use the macro CURRENT\_TIMESTAMP so you can say

```auto
`(UTC, DateTime, hp,lp) values (${msg.payload} , CURRENT_TIMESTAMP, ${hp}, ${lp})`;

```

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [10 February 2020 17:02 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/7 "2020-02-10T17:02:44Z")

</div>

I'll give a try to those options.!!!  
Can you tell me where get info about command syntax for sqlite clauses using node  
red. There're many tutorial on line about sqlite clauses and they give you examples.  
But I need the general syntax to follow with node-red.

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [10 February 2020 17:07 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/8 "2020-02-10T17:07:19Z")

</div>

The query is passed directly to sqlite so there is no difference in the query syntax.

---

<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:** [10 February 2020 19:37 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/9 "2020-02-10T19:37:32Z")

</div>

There are multiple ways to create a sqlite (or any sql) query but all must end up putting the query in msg.topic.

- you could create the query entirely in an `inject` node - but you would have to hard code any values ![Screen Shot 2020-02-10 at 2.17.46 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/5/e56df3a5d60d25aba5d1bd8c108c4da0b9774608.png)  
or you could use a `function` node and add the values there:  
 ![Screen Shot 2020-02-10 at 2.34.08 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/5/c505e71fceede5e940892dffc1da12c992de3907.png)  
but my favorite way is to use a template node:  
 ![Screen Shot 2020-02-10 at 2.36.49 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/c/fcee0f04eeb08d96ffd5a94986be94181ec1ce33.png)

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [13 February 2020 03:21 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/10 "2020-02-13T03:21:57Z")

</div>

Thank you both of you @Colin@zenofmud

I solved my issue.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [27 February 2020 03:22 UTC](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/11 "2020-02-27T03:22:01Z")

</div>

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