Struggling with sqlite3

I need to drop a table and recreate it, after which I need to insert 30k records.
Sometimes dropping works - but then does not recreate the table.
Nothing in database. Database complains - 'database is locked'.
When I recreate the table directly on cli, it works.

The 30k records should be a begin/commit transaction (?) for speed?

Currently I use a template node and prepare all statements and put that in msg.topic and set the sqlite node to batch without response. But whatever step I take separately it does not appear to function properly.

Is there a "best approach" tactic for working with sqlite because I am losing my patience with the inconsistent behaviour because I don't understand what is happening.

It would be helpful if you gave some background like

  1. device running NR, the OS and amount of memory
  2. version of NR and node.js
  3. a copy of the flow

Also, how are you trying to drop the table? I would concentrate on that problem intially to try and work out what is going on. Are you sure it has completed any existing activity when you try to drop it?

1 Like

I gave up and went with mysql instead and then i gave up on that as well and just used arrays with json objects instead :crazy_face: in the end it performs faster.

If you only have a small amount of data then that can be a good solution.