CODEWITHSUNDEEP
node.jssqlite
randy
randy

Reputation: 1877

Getting BUSY errors using nodejs and sqlite

I am looking for some insight on how the best way to write this piece of code.

I want all these commands to run synchronous so i am trying to use the db.serialize function.

I need to do some other DB stuff based on the results of the query (BTW i am still a node newbie)

I first tried this

var db = new sqlite3.Database(file);
var stmt = "SELECT image_id FROM image WHERE file_downloaded = 1 ORDER BY image_id DESC LIMIT 1";
db.serialize(function() {
    db.all(stmt, function(err, rows) {
        if (err){
            if (err) { logger.error('Error %j',  err); throw err; }
        }
        if ( rows.length > 0 ){
                db.run("DELETE FROM image_status");
                db.run("INSERT INTO image_status ( next_new_id, next_type , restart_new  ) VALUES ("+rows[0].image_id+",'old',"+restart_new+")");
                db.run("UPDATE image_status SET next_old_id = (SELECT image_id FROM image WHERE file_downloaded = 1 ORDER BY image_id ASC LIMIT 1)");
                db.all("SELECT next_old_id FROM image_status LIMIT 1", function(err, rows) {
                    if (err) { logger.error('connection %j',  err); throw err; }
                    if ( rows.length > 0 ){
                        next_old_id = rows[0].next_old_id;
                    }
                    mycallback(next_new_id, next_old_id,'old');
                })
        }
    })
});
db.close();

But of course by the time i got to the DELETE part the db is already closed So next i tried where i moved the db.serialize inside the callback for the query and then managed closing the DB in the callback. This seems not to be the best solution ( i get the occasional DB is busy from other events) . I am looking for the correct way to do this.

Thanks for any help

var db = new sqlite3.Database(file);
var stmt = "SELECT image_id FROM image WHERE file_downloaded = 1 ORDER BY image_id DESC LIMIT 1";
db.all(stmt, function(err, rows) {
    if (err){
        db.close();
        if (err) { logger.error('Error %j',  err); throw err; }
    }
    if ( rows.length > 0 ){
        db.serialize(function() {
            db.run("DELETE FROM image_status");
            db.run("INSERT INTO image_status ( next_new_id, next_type , restart_new  ) VALUES ("+rows[0].image_id+",'old',"+restart_new+")");
            db.run("UPDATE image_status SET next_old_id = (SELECT image_id FROM image WHERE file_downloaded = 1 ORDER BY image_id ASC LIMIT 1)");
            db.all("SELECT next_old_id FROM image_status LIMIT 1", function(err, rows) {
                if (err) { logger.error('connection %j',  err); throw err; }
                if ( rows.length > 0 ){
                    next_old_id = rows[0].next_old_id;
                }
                mycallback(next_new_id, next_old_id,'old');
            })
            db.close();
        });
    }else{
        db.close();
    }
})

Upvotes: 2

Views: 575

Answers (2)

randy
randy

Reputation: 1877

refactored to below. The other part that was giving me problems was the db.all. After i refactored, the db.all was not seeing the insert. Changing the db.each fixed that part: 

db.serialize(function() {
    db.run("DELETE FROM image_status");
    var stmt = db.prepare("INSERT INTO image_status ( restart_new , next_type ,  next_new_id,  next_old_id ) "
                          +"VALUES (?,'old'," +
                            "(SELECT image_id FROM image WHERE file_downloaded = 1 ORDER BY image_id DESC LIMIT 1)," +
                            "(SELECT image_id FROM image WHERE file_downloaded = 1 ORDER BY image_id ASC LIMIT 1))"
                          );
    stmt.run(restart_new);
    stmt.finalize();

    db.each("SELECT next_new_id, next_old_id FROM image_status LIMIT 1", function(err, row) {
        if (err) { logger.error('connection %j',  err); throw err; }
        mycallback(row.next_new_id, row.next_old_id,'old');
    });
});
db.close();

Upvotes: 0

hbillings
hbillings

Reputation: 174

If you aren't going to implement promises then this is the best way to handle closing the database.

Bluebird promises has a .finally option where you can put the db.close() and it will run after all your db call promises are fulfilled.

Upvotes: 1

Related Questions