Question from a noob: Connecting to sqlite 3


#1

Hi!!
I'm new using node - red.

My project is basically this one

So here my question:

  1. How can I get the values from ehall and gas in order to send to my DB?
  2. how can I get the 10 higher values from the bd? or can i do it there?

I will apreciate it any help


#2

the values will come in at different times, so your function 'Grabar a BD' will not have the data from both when it runs. If you need the data from both, you will have to feed the two change nodes into a join node and feed it's output into the function node.

Read up about the join node to see how it works and/or do some google searches to learn about it.


#3

Hi!! ty for your answer..

I try to do your advice:

[{"id":"30d6141b.37ce6c","type":"function","z":"751ee55e.e3ec3c","name":"Grabar a BD","func":"\nvar outp = [];\noutp[0] = {payload:lines[0]};\noutp[1] = {payload:lines[1]};\n\nvar sql= \"\";\nvar d = new Date();\nvar epoch = d.getTime();\nvar device=\"ESP8266\";\nvar outputs = [];\nsq1 = \"INSERT INTO dhtreadings (eHall,gas,fecha,hora,device)\" + \n \"VALUES (\"+outp[1]+\",\"+outp[0]+\",\"+d+\",\"+epoch+\",\"+device\")\";\noutputs.push({topic:sq1});\n\n//Actualizar status con el tiempo \nvar now=new Date();\nvar yyyy = now.getFullYear();\nvar mm=now.getMonth()<9? \"0\" + (now.getMonth()+1): (now.getMonth()+1); //getMonth()\nvar dd=now.getDate()<10? \"0\" + now.getDate() : now.getDate();\nvar hh = now.getMinutes() < 10 ? \"0\" +now.getMinutes() : now.getMinutes();\nvar ss = now.getSeconds()< 10 ? \"0\" + now.getSeconds(): now.getSeconds();\nnode.status({fill:\"blue\",shape:\"ring\",text:\"Última actualización: \"+dd+\".\"+mm+\".\"+yyyy+\" \"+hh+\":\"+ss});\n\n/*var dia=dd+\"/\"+mm+\"/\"+yyyy;\nvar hora=hh+\":\"+ss;\nvar fecha=dia.toString();\nvar hora_s=hora.toString();\n\nsq1 = \"INSERT INTO lecturasensor (eHall,gas,fecha,hora,device)\" + \n \"VALUES (\"+outp[1]+\",\"+outp[0]+\",\"+fecha+\",\"+hora_s+\",\"+device\")\";\noutputs.push({topic:sq1});*/\n\nreturn [ outputs ];","outputs":1,"noerr":0,"x":950,"y":620,"wires":[["af8ffcd9.fba2e","79c65c31.aa0304"]]}]


#4

Okay I think I got the problem:
I try to send 2 obj type, but then,how can I get the number on this?

var lines = msg.payload.split("\n");
var outp = [];
outp[0] = {payload:lines[0]};
outp[1] = {payload:lines[1]};

var sql= "";
var d = new Date();
var epoch = d.getTime();
var device="ESP8266";
var outputs = [];
var eHall=outp[0].toInt();
var gas=outp[1].toInt();
/*sq1 = "INSERT INTO dhtreadings (eHall,gas,fecha,hora,device)" + 
        "VALUES ("+outp[1]+","+outp[0]+","+d+","+epoch+","+device+")";
outputs.push({topic:sq1});*/
var fecha=d.toString();
var hora=epoch.toString();
sq1 = "INSERT INTO lecturasensor (eHall,gas,fecha,hora,device)" + 
        "VALUES ("+eHall+","+gas+","+fecha+","+hora+","+device+")";
outputs.push({topic:sq1});    indent preformatted text by 4 spaces

I get this from union
image

What I intent to do, is get the first value and put it on eHall, and the second value on gas. Then send it to my sqlite db.


#5

Can you show what is coming out of the debug node connected to the join. Also please read the post in the FAQ which explains how to add your flow to a post, then edit and fix your posted flow


#6

Okay I solve it!!
I put the join like an array and then I can get both values!! YAY

Now I got two problems: I wanted to send the day and the hour on string Format, but when I do that, it's appears a msg that said: Unexpected String.

var value =[];
var value=msg.payload;
var eHall=parseInt(value[1]);
var gas=parseInt(value[0]);
var sql= "";
var d = new Date();
var epoch = d.getTime();
var dispositivo="ESP8266";
var outputs = [];

/*sq1 = "INSERT INTO lecturasensor (eHall,gas,fecha,device)" + 
        "VALUES ("+eHall+","+gas+","+tiempo+","+dispositivo")";
outputs.push({topic:sq1});*/

sq1 = "INSERT INTO dhtreadings (eHall,gas)" + 
        "VALUES ("+eHall+","+gas+")";
outputs.push({topic:sq1});

//Actualizar status con el tiempo 
var now=new Date();
var yyyy = now.getFullYear();
var mm=now.getMonth()<9? "0" + (now.getMonth()+1): (now.getMonth()+1); //getMonth()
var dd=now.getDate()<10? "0" + now.getDate() : now.getDate();
var hh = now.getMinutes() < 10 ? "0" +now.getMinutes() : now.getMinutes();
var ss = now.getSeconds()< 10 ? "0" + now.getSeconds(): now.getSeconds();
node.status({fill:"blue",shape:"ring",text:"Última actualización: "+dd+"."+mm+"."+yyyy+""+hh+":"+ss});

return [outputs];

#7

Show us the full message.


#8

Also, please show the code yu used to create the sqlite table