pretty new to Node-RED. Installed the node-red-node-sqlite and was able to connect to a database.
The Database-file is exported every hour to the same location and contains updated records.
This file is set up as the Database in the sqlite node.
Both if the SQL-Statement is taken from the msg.payload from an inject or is put in as SQL-Query as a Fixed statement it is not using the current sqlite DB-file.
How can it be ensured that the current DB file is used when querying the Database?
Dont know what additional information's are required.
Do you mean that the database file is replaced with a completely new file while node-red already has the database open? If so then node-red will continue to use the original file as it already has it open. That is the way the file system works. It is nothing to do with node-red. You will have to restart node-red to get it to pick up the new file. The better alternative is to get the export operation to update the database contents rather than replacing the complete file.
Thank you for your reply. Changed this. Got the result of the DB query in the debug window. But it is still querying an old version of the DB. Another user pointed out that a node-red restart is required to accomplish what I am intending.
Many thanks for your reply. Agree on the findings. Nevertheless cant get my head around the solution outlined.
The sqlite file "db.sql" ist copied over on file lever every hour. This contains the table that is being queried. The table has new rows every time the db-file with the same name is exported again. This filename is "static" as sql-source file in node-red. Where should the export happening? Outside of node-red to a csv-file that is than picked up by node-red?
If time allows can you provide some more insight? That would be highly appreciated. Thank you in advance.
Starting point is a Mi Smart Band 5. This device was paired with gadgetbridge, an Android app that allows to use the band without the vendors app. Gadgetbridge can export the data collected from the Mi Band to a sqlite DB. The exported db file located on an Android device is copied over periodically to a raspberry pi that runs node-red using an app called folder sync.
The node red flow is pretty simple. Inject node having the SQL statement in the topic --> SQL lite node Database --> tabel UI node. The flow itself is running but as outlined before the DB is not updated even so a new version exists on file level.
Can gadgetbridge communicate using a more useful protocol such as MQTT?
Can it build a csv file that could be transferred to node-red?
As explained, the original database file probably is being updated, but you can't see it because you have added another database file with the same name. Because node-red already has the original file open it will continue to access the same file. The only way to work around that problem is to restart node-red.
SQL Query now in a file called steps_q.sql. The following command running as a cronjob will export the table to csv every hour:
sqlite3 -header -csv /mnt/1TBTOSHIBA/files/transfer/miband/db < /mnt/1TBTOSHIBA/files/transfer/miband/steps_q.sql > /mnt/1TBTOSHIBA/files/transfer/miband/steps.csv
In Node-RED flow changed to Inject (every hour) --> file in node (with the csv file as Filename) --> csv node --> talbe UI node.
Hope this will work and the updated csv file is read every time the flow runs.
Thank you all for the very helpful and friendly support!
I am having a similar problem and wanted to check if there are any new answers.
I have a DB with a table full of configuration settings for a process. I have it plugged into my Linux hardware running Node-red 3.0.2 and Nodejs14. If I have to update the configuration table, I will eject the SD card, carry it to my PC, use SQLite DB Browser to add or update a few rows, then plug it back into my Node-red server. This allows me to keep many systems in sync and it keeps the Node-red code from writing changes.
Up until I eject the card, everything works
After I eject the card, the DB still runs and returns data from queries - I presume it has the table cached
When I reinsert the card, it never sees the new data.
I've tried setting the configuration table as an attached DB, and detaching, reattaching that table and no luck.
Is there a way to have the SQLite node release the database and reconnect? - So I don't have to shut down/reboot node-red? I thought about just exporting to CSV, but once I eject the SD card, the DB won't read any new data. The only approach I can think of is to write a web page to allow a user to upload a csv file to the Node-Red server over http - then import that data. That sounds like a lot more work than reloading the DB.
It seems drastic to physically transfer a filesystem from one computer to another.
Why can't you pass your configuration updates to the Linux machine as SQL, and apply them to the db without dismounting the card?
Its as much a matter of convenience as anything. The equipment is on the plant floor and if I don't have to run network cables from the office out back, it will save me a bunch of time and money. I can try wifi, but I don't fully trust the stability. Alternatively, I am writing to SD because it has 32gb vs my hardware which has 2gb, and since I can just occasionally eject the card and plug it into my PC, that is the easy option. Except, I have to cycle power to get the DB to remount when I plug the SD card back in.
If you are considering that, you must have network access between the two servers.
Personally I'm uncomfortable with HTTP, and CSV files. I would try MQTT, if necessary using the "guaranteed delivery" scheme that has been discussed fairly recently in the forum.
