I have to update sqlite3 query with Door status and only on that column which is coming from successfully database entry
what is your question exactly and please provide some information that people can work with.
hello Sir,
I am taking fob id from gateway side and checking that with my sqlite3 database ,it fob id is present CR_SUCCESS is showing and if not CR_FAIL is showing.
Now my query is I want to change the door status like on/off and whatever the status is it should be updated in my sqlite3 back with the successful FOB id.
please sir help in this.
UPDATE NCS_DATABASE SET Status='DR1_OFF' WHERE Person_ID='undefined' : msg.payload : undefined
undefined
this undefined i am getting???
UPDATE NCS_DATABASE SET Status='DR1_OFF' WHERE Person_ID=' 0 4 2 2 2 4 3 6 ' : msg.payload : array[0]
[ empty ]
now this much i reached but empty it is showing why??
Shipra .. because i happen to know from your previous topic regarding the sqlite error. array[0]
This happens, and you don't get an update in sqlite because your db has the Person_ID without spaces. you are trying to update with spaces in id 0 4 2 2 2 4 3 6
Did you read the function we used for the SELECT queries ? Its named "id no space"
The same applies for UPDATE queries which i guess you are trying to do now.
ok sir i will do this and update you.Thank you
The Update query looks ok.
Are you sure the record wasnt updated ?
does Person_ID='04222436' actually exist so it can be updated ? 
what you have in function before the sqlite3 node ?
use 
let newMsg = {};
newMsg.topic = `UPDATE NCS_DATABASE SET Status='${msg.payload}' WHERE Person_ID='${msg.id}'`;
return newMsg;
If it helps you can install a utility called DB Browser for SQLITE so you can visually check your database.
shipra@shipra-HP-15-Notebook-PC:~/Documents/NCS_Projects/IOT/NCS_API$ sqlite3 NCS.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .tables
NCS_DATABASE
sqlite> SELECT * FROM NCS_DATABASE;
1| | | |04222436| |2813| | |
2| | | |04222437| |2814| | |
sqlite> SELECT * FROM NCS_DATABASE;
1| | | |04222436| |2813| | |
2| | | |04222437| |2814| | |
sqlite>
Sir 04222436 exist in DB thats y CR_SUCCESS was coming then only i have added further flow.
Is there a command to show the Columns of the NCS_DATABASE table ?
is Status a column in that table?
sqlite> .schema NCS_DATABASE
CREATE TABLE NCS_DATABASE(SrNo INT NOT NULL,Date_and_Time TEXT NOT NULL,Event_Type TEXT NOT NULL,Event_Description TEXT NOT NULL,Person_ID TEXT NOT NULL,Person_Name_Description TEXT NOT NULL,Fob_Tag_ID TEXT NOT NULL,Door_ID TEXT NOT NULL,Door_Description TEXT NOT NULL,Status TEXT NOT NULL);
After logging into sql prompt mode, execute the following commands...
.header on
.mode column
...then queries should be returned with headers.
@shipra Take a look at what your query is, then take a look at the data you dumped from the database is and tell us what is different.
1| | | |04222436| |2813| | |"DR1_ON"
2| | | |04222437| |2814| | |
Sir its coming Thank you litlle bit " " this quotes was missing
