@bigmadgaga posted a new topic, subject "Sqlite error - no such table". It read:

   Hey,  
    i am doing my first attempts to work with node red. Actual i am in trouble with sqlite,
    [code] 
        [{"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"}]
        
    [/code]
    
    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

After which, @michaelblight (Michael) added

    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).

So @UnborN (Andy) replied

    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 :
    [code] 
        [{"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"}]
        
    [/code]
    
    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
    [code] 
        INSERT INTO indexdata (jobs, projekte, freelancer) VALUES ('111', '222', '333')
        
    [/code]

So @zenofmud (Paul) replied

    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`

After which, @Colin added

    zenofmud:
    
    > If you only put in a dbname, it will look in your .node-red folder for the database.
    
    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.

After which, @zenofmud (Paul) added

    I was correcting my post while you wrote your post .
    
    I agree the safe way is the give the full path!

@Colin replied

    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.

Then @zenofmud (Paul) said

    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.

After which, @bigmadgaga added

    Thanks to all of you.  
    With your advice i found a solution.
    [code] 
        [{"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"}]
        
    [/code]
    
    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,

To which @system (system) replied

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


{'source': 'discourse', 'url': 'https://discourse.nodered.org/t/sqlite-error-no-such-table/32065', 'title': 'Sqlite error - no such table', 'views': 2535, 'has_accepted_answer': False, 'word_count': 1111, 'like_count': 0, 'reply_count': 5, 'participant_count': 5, 'posts': 10, 'tags': '', 'author': '@bigmadgaga', 'participants': '@bigmadgaga, @michaelblight (Michael), @UnborN (Andy), @zenofmud (Paul), @Colin, @system (system)', 'image_url': [None], 'created_at': '2020-08-26T22:32:24.088Z', 'last_posted_at': '2020-10-26T16:36:00.004Z'}