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)
);
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.
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.
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.
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() ;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With