jeudi 10 décembre 2015

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

Aucun commentaire:

Enregistrer un commentaire