Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sqlite SQLITE_MISUSE error in node.js script

When I launch code with this query, sometimes I can see all the tables listed, sometimes only one and I get always this error:

Query Error: Error: SQLITE_MISUSE: unknown error

I've read that SQLITE_MISUSE occurs when SQLITE API is used unproperly. Could you help me, because I can't find whats wrong in this code.

EDIT. I've made changes to the code to get rid of the race issue.

The message with SQLITE_MISUSE wrror still occurs, however the vanishing tables issue is gone. Race in my queries was the case.

Here is the code.

var sqlite3 = require("node-sqlite3");
var fs = require('fs');

var query_count;
var init = function (response) {
    var db = new sqlite3.Database("test.db", function() {

        fs.readFile('./assets/sql/initDB.sql', function(err,data){
            if(err) {
                console.error("Could not open file: %s", err);
                return;
            }

            var query = data.toString('utf8');
            queries = query.split(";");

            db.serialize(function() {
                query_count = queries.length;
                for(var i=0; i<queries.length; i++) {
                    queries[i] = queries[i].replace("\r\n","");
                    db.run(queries[i], function(error) {
                        if(error) {
                            console.log("Query Error: "+error);
                        }

                        query_count--;

                        if( query_count <= 0 ) {
                            db.close();
                            listAllTables(response);
                        }
                    });
                }
            });
        });
    });
};

function listAllTables(response) {
    var db = new sqlite3.Database("./assets/sql/test.db", function () {
        db.all("SELECT name FROM sqlite_master WHERE type = 'table'", function (error, records) {
            for(var record in records) {
                response.write(record+": "+records[record]+"\n");
                for(var prop in records[record]) {
                    response.write("\t"+prop+": "+records[record][prop]+"\n");
                }
            }

            response.end();
            db.close();
        });
    });
}

exports.load_customers = function(response) {
    init(response);
};

The query file initDB.sql is like this:

CREATE TABLE IF NOT EXISTS TemporaryAuthTokens (
  authToken TEXT PRIMARY KEY NOT NULL UNIQUE,
  expireDate NUMERIC NOT NULL);

CREATE  TABLE IF NOT EXISTS User (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE  ,
  login TEXT NOT NULL ,
  pass TEXT NOT NULL ,
  creationDate NUMERIC NOT NULL ,
  authToken TEXT NULL REFERENCES TemporaryAuthTokens(authToken)
  );
like image 681
Mark Richwood Avatar asked Aug 27 '12 09:08

Mark Richwood


People also ask

Can you use SQLite with node js?

The two technologies work well together because developers can easily embed SQLite databases within a Node. js application. Therefore, it is easy to develop applications using data from an SQLite database without connecting to a separate database server.

When can you get an Sqlite_schema error?

An SQLITE_SCHEMA error is returned when a prepared SQL statement is no longer valid and cannot be executed. When this occurs, the statement must be recompiled from SQL using the sqlite3_prepare() API. An SQLITE_SCHEMA error can only occur when using the sqlite3_prepare(), and sqlite3_step() interfaces to run SQL.


2 Answers

You've got a race condition; it's possible that your last query (whose callback closes the connection) will finish before one of the earlier queries did, and that will, needless to say, hose the earlier query. You need to rework your code so that the last query to finish, rather than the last query to start, closes the connection (e.g set a counter to the number of queries and have each query decrement it when it finishes. The one that decrements it to zero closes the connection).

You might also want to look at the serialize method that's available on database objects. Right now your initialization queries are all independent of each other, but if you started using foreign-key constraints you'd have trouble if the referenced table hadn't been created yet, so you'd need to force the order of execution.

like image 165
ebohlman Avatar answered Sep 20 '22 11:09

ebohlman


Use Promises.

Here I get data from one table and use this data to create a statement for inserting in another table.

serialize() do run 1-by-1 but I wanted the response from one query to be used in another. If I put 2nd query in callback of 1st then it gives the SQLITE_MISUSE error

db.serialize(()=>{

    // QUERY 1 (SELECT) - Get data 
    let promiseGetFoo = new Promise((resolve, reject) => {
      db.all("SELECT * FROM foo", (err, rows) => {
        if (err) {
          console.log(err);
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });

    // QUERY 2 (INSERT) - Use data from QUERY 1
    promiseGetFoo.then((res) => {
      let stmt = (res) => { 
        // code to create INSERT statement 
      }      
      db.run(stmt, (err) => {
        if(err) console.log(err);
        else console.log(">>> Insert DONE");
        closeDb();
      });
    });

});

let closeDb = () => {
    db.close() ;
}
like image 29
umesh kadam Avatar answered Sep 21 '22 11:09

umesh kadam