Sqlite INSERT command syntax

General
1

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

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
Flow1195Ă—360 30.5 KB

This the function " Insert values in hvac3 "

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.

2

What is the table definition ?

3

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.

4

Hi there !

Here it's what you asking for:

Outcome from Function node```

/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.

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

Thank you.

5

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

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

I think it is easier to get right using the syntax

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.

6

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

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

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.

8

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

9

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

10

Thank you both of you @Colin @zenofmud

I solved my issue.