Sqlite error - no such table

General
1

Hey,
i am doing my first attempts to work with node red. Actual i am in trouble with sqlite,

[{"id":"46bcec5b.fd09a4","type":"tab","label":"Flow 5","disabled":false,"info":""},{"id":"ac016156.86dc2","type":"sqlite","z":"46bcec5b.fd09a4","mydb":"a9d461c1.968aa","sqlquery":"msg.topic","sql":"","name":"DB node-red","x":430,"y":160,"wires":[["24cdeb3c.fdd404"]]},{"id":"16dd87f9.aa2178","type":"inject","z":"46bcec5b.fd09a4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"INSERT INTO indexdata (jobs, projekte, freelancer) VALUES (111, 111, 111)","payload":"","payloadType":"date","x":180,"y":160,"wires":[["ac016156.86dc2"]]},{"id":"24cdeb3c.fdd404","type":"debug","z":"46bcec5b.fd09a4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"topic","statusType":"msg","x":660,"y":180,"wires":[]},{"id":"a9d461c1.968aa","type":"sqlitedb","z":"","db":"d:\\\\node-red","mode":"RWC"}]

I tested the sql statement on my database and it works. But as message I get alwas the result "Error: SQLITE_ERROR: no such table: indexdata". So whats going wrong or where i make a mistake?

Thanks in advance
Branko

2

I don't have all the nodes you've got installed, but I'm willing to guess. When you're running the sql yourself, you probably have the correct database open. Whereas when running under NR, you're probably in the default database. So you could try qualifying the table (ie. select * from mydatabase.mytable).

3

Hi,

as Michael pointed out you have to confirm if that indexdata table exists.
if not and only in the case that its a brand new database you need to create the indexdata table
I'm sending a flow with this example that you run only once to create table :

[{"id":"7da6c76d.f9f6f8","type":"sqlite","z":"3bf432d6.467d5e","mydb":"e133c6a4.857d","sqlquery":"msg.topic","sql":"","name":"DB node-red","x":590,"y":460,"wires":[["85a9a9b0.038bb8"]]},{"id":"4f35abac.28f2cc","type":"inject","z":"3bf432d6.467d5e","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":460,"wires":[["ed078520.fa3478"]]},{"id":"85a9a9b0.038bb8","type":"debug","z":"3bf432d6.467d5e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"topic","statusType":"msg","x":760,"y":460,"wires":[]},{"id":"7f3ee9d9.a69ac8","type":"inject","z":"3bf432d6.467d5e","name":"trigger","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":270,"y":140,"wires":[["152d6169.5e26d7"]]},{"id":"800f453b.b7adb","type":"comment","z":"3bf432d6.467d5e","name":"create initial sqlite DB table","info":"","x":300,"y":80,"wires":[]},{"id":"152d6169.5e26d7","type":"function","z":"3bf432d6.467d5e","name":"CREATE TABLE","func":"msg.topic = `CREATE TABLE indexdata (\nid INTEGER PRIMARY KEY AUTOINCREMENT, \njobs NUMERIC, \nprojekte NUMERIC, \nfreelancer DATETIME)`\nreturn msg;\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":320,"y":200,"wires":[["488c7296.29527c"]]},{"id":"488c7296.29527c","type":"sqlite","z":"3bf432d6.467d5e","mydb":"e133c6a4.857d","sqlquery":"msg.topic","sql":"","name":"DB node-red","x":410,"y":280,"wires":[[]]},{"id":"ed078520.fa3478","type":"function","z":"3bf432d6.467d5e","name":"INSERT VALUES","func":"msg.topic = `INSERT INTO indexdata (jobs, projekte, freelancer) VALUES ('111', '222', '333')`;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":390,"y":460,"wires":[["7da6c76d.f9f6f8"]]},{"id":"e133c6a4.857d","type":"sqlitedb","z":"","db":"d:\\node-red.db","mode":"RWC"}]

Also its good practice to add an extension to your file node-red.db
and another thing i noticed when you insert values with sql query you need to have them in single quotes

INSERT INTO indexdata (jobs, projekte, freelancer) VALUES ('111', '222', '333')
4

If you look at the help option in the sidebar while configuring the node you will see this

sqlitedb

The default directory for the database file is the user's home directory through which the NR process was started. You can specify absolute path to change it.

If you only put in a dbname, it will look in your home folder for the database. (on the pi it would be /home/pi)

For example, say you are running on a Pi and in CLI you create a database in your /home folder called 'mysqlite.db'. It is /home/mysqlite.db and you create some tables and add data to it. Now you go to use it in NR and configure the DB as mysqlite.db, Node-RED will look for the database at /home/pi/mysqlite.db

5

Actually I don't think that is correct. I believe it looks in the Current Working Directory for the node-red process. So, for example, if node red was installed using the Pi/Debian script that will be the home directory of the user that installed node-red. So on a pi the default would be /home/pi. I believe that generally the safe way to use the node is to specify the full path to the database.

I note that the error message 'no such table' is confusing as even if the database does not exist that is the error that is shown.

6

I was correcting my post while you wrote your post :smile:.

I agree the safe way is the give the full path! :+1:

7

In fact it seems I was wrong, it doesn't look in the Working Directory that node-red is run under (at least when run as a service), it does look in the users home directory as the help says.

8

Another interesting item is that on a Pi, when you define a DB in the configuration, as soon as you deploy the flow, the database will be created at the location you specify

On a Mac, the deploy after configuring the node does nothing. If you issue a create table statement, you will get a
message: "Error: SQLITE_IOERR: disk I/O error"
message. If you try any other sql statement you will get a
message: "Error: SQLITE_ERROR: no such table: hvac4"
message.

9

Thanks to all of you.
With your advice i found a solution.

[{"id":"ac016156.86dc2","type":"sqlite","z":"46bcec5b.fd09a4","mydb":"a9d461c1.968aa","sqlquery":"fixed","sql":"INSERT INTO indexdata (jobs, projekte, freelancer) VALUES (112, 111, 111)","name":"DB node-red","x":430,"y":160,"wires":[["24cdeb3c.fdd404"]]},{"id":"a9d461c1.968aa","type":"sqlitedb","z":"","db":"D:\\sql-node-red.db","mode":"RWC"}]

Two steps were important. The only problem was the tool with which i generated the database. The second thing was to use the filetype on win10 to open the database - in my case *.db,

10

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.