Hi,
i have many csv. file that i want to send them to a database(i used sqlite hier). I thought that i can use function node after csv node, but i became error that i cannot slove them. I would be happy when i get some advise?
THX a lot.
Samira
you are getting [object] because you are not accessing the correct item
Put node.warn( [Data, Time, Temperature] ) in your loop - see what the values look like
slightly updated version of your code
var payload=msg.payload;
for(let i=0; i<payload.length ; i++){
var Data= payload[i]["Data"]; //correct this - get the sub property!
var Time= payload[i]["Time"]; //correct this - get the sub property!
var Temperature= payload[i]["Temperature"]; //correct this - get the sub property!
node.warn( [Data, Time, Temperature] ); //check debug sidebar
}
var newMsg = {
"topic": "INSERT INTO TOPI VALUES ( ? , ? , ?)",
"payload": [Data, Time, Temperature]
}
return newMsg;
Hi,
thank you Steve for your helpful answer. I have some Questions now. I have 3 colums in my csv. file and i want to have Index column in my table in DB. I tried to set Data(date of every row of my file) as primary key but get it "Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: TOPI.DATA" (TOPI is name of table and DATA is first column of table). As i understand right, i can select date as INT or string OR i should change my type of date?
I read SQLite UNIQUE Constraint too but it doesent work.
I know, i am wrong somewhere but i cannot find it.
Hi ,
i have solved the error "unique constraint" but now any data come to my sqlite. I think , i should change maybe my code but i am not sure.
i am thankful with advise.
tnx
Samira
What do you mean by now any data come to my sqlite?
If you think you should change your code, then change it. remember, no one on the forum knows what your needs are.
INSERT INTO table (column1,column2 ,..)
VALUES( value1, value2 ,...);
The column names are missing from your query after the name of your table
Also the script only sends one msg .. it should send multiple messages while looping throught the converted csv data.
Modified function code :
var arr = msg.payload;
arr.forEach( row => {
let Data = row.Data
let Time = row.Time
let Temperature = row.Temperature
node.send( {
"topic": "INSERT INTO TOPI (Data, Time, Temperature) VALUES (?, ?, ?)",
"payload": [Data, Time, Temperature]
})
})
return null;
ps. you may need to add a Delay node after the Function as to not send to many data to fast to the db
ps2. backup you db if you have to as the above code couldnt be tested since we dont use the same db
Hi UnborN,
thank you for your response . I build new table and database and wrote your codes, it showed 3 messages that i wanted but there is nothing in table same before.
I have many csv. file that everyone hat different rows , also i need for loop that start (0 till length of my payload).
I have another question, Time ist als primary key in my table. and i get always the "Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: STAR.TIME" .
.. there is still something wrong with the syntax ..
If you select the sqlite node and read the Help notes on the sidepanel .. it describes how to properly send the values as parameters (which is the safest way, since it sanitizes the values and protects from sql injection).
Hello zenofmud,
in last week i could not spend enough time on programm but i tried to reach the Result. I tried to reach answer with arr.forEach() but it still on some errors and i prefer to continue with topic and payload not with params. my last flow is:
[{"id":"e2c78e0e.28abc","type":"function","z":"581344b8.85f0cc","name":"insert ","func":"var payload=msg.payload;\n\n for(i=0; i<payload.length ; i++){\n var Data= payload[i][\"Data\"];\n var Time= payload[i][\"Time\"];\n var Temperature= payload[i][\"Temperature\"];\n node.warn( [Data, Time, Temperature] ); //check debug sidebar\n \nvar newMsg = {\n\n // \"topic\": \"INSERT INTO STAR VALUES ( \" + Data + \" , \" + Time + \" , \" + Temperature + \")\",\n //\"topic\": \"INSERT INTO DATEI VALUES ( ? , ? , ? )\",\n // \"topic\":\"INSERT or IGNORE into STAR VALUES (? , ? ,? )\",\n //\"payload\": [Data, Time, Temperature]\n \"topic\": \"INSERT INTO STAR (Data, Time, Temperature) VALUES (?, ?, ?)\",\n \"payload\": [Data, Time, Temperature]\n }\n}\nreturn newMsg; \n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":690,"y":360,"wires":[["dfbbaacc.149558","79159544.a872ac","30da63d.19e679c"]]}]
var payload = msg.payload;
for(i=0; i < payload.length; i++) {
var Data = payload[i]["Data"];
var Time = payload[i]["Time"];
var Temperature = payload[i]["Temperature"];
node.warn( [Data, Time, Temperature] ); //check debug sidebar
node.send({"topic": `INSERT INTO STAR ('Data', 'Time', 'Temperature') VALUES ('${Data}', '${Time}', ${Temperature})`})
}
Hi UnborN,
tnx for your answer.the problem is error of UNIQUE constraint. at first i set Time as primary key but i had this error. I tried to change my primary key and i add one column(called Numik) as primary key. but it still same error.
ok .. so you know what the problem is. You setup your db table in such a way that the primary key (that must be unique) repeats itself and that is not allowed. Setting the Date as primary key will not work since the date repeats itself several times every day for your inserted rows. And in the case of the Time as primary key .. the time will repeat itself the next day.
One solution is to set a field in the db called (lets say) Datetime a combination of Date and Time fields from your CSV file .. This surely will be unique and can be set as Primary key. Once you make those changes to your table columns, a variation of the function could be ..
var payload = msg.payload;
for(i=0; i < payload.length; i++) {
var Data = payload[i]["Data"];
var Time = payload[i]["Time"];
var Temperature = payload[i]["Temperature"];
node.warn( [Data, Time, Temperature] ); //check debug sidebar
node.send({"topic": `INSERT INTO STAR ('Datatime', 'Temperature') VALUES ('${Data} ${Time}', ${Temperature})`})
}
ps. As far as the function code goes .. you dont need to save node.send() into a variable and then returning it outside the loop. That is not how i shared the code. We used node.send() in this case instead of a return because we wanted to send many msgs in the loop.
