CODEWITHSUNDEEP
javascriptnode.jssqlite
Mark Richwood
Mark Richwood

Reputation: 165

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

Upvotes: 10

Views: 17308

Answers (2)

umesh kadam
umesh kadam

Reputation: 1181

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() ;
}

Upvotes: 2

ebohlman
ebohlman

Reputation: 15003

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.

Upvotes: 13

Related Questions