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!
