I am using node.js version 0.10.24 and the npm module named sqlite3 version 2.1.19. I test my scripts on an OS X 10.9.1 box, using a terminal window. The following script:
var sqlite3 = require('sqlite3').verbose()
var async = require('async')
var myList = []
var db = new sqlite3.Database('./test0.db')
async.series([
// Create a table and populate it
function (callback) {
db.run("CREATE TABLE lorem (listnumb bigint, info TEXT)", function (err) {
if (err) return callback(err)
console.log('Table created. ')
callback()
});
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
for (var i = 0; i < 10; i++) {
stmt.run(i, "Ipsum " + i)
}
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
db.each("SELECT listnumb as numb, info FROM lorem;",
function (err, row) {
if (err) return callback(err)
console.log(' numb = ' + row.numb + ' info field = ' + row.info)
},
function (err, cntx) {
if (err) return callback(err)
console.log('Number of retrieved rows is ' + cntx)
callback()
}
)
}
],
// This function gets called after the tasks above have pleted
function(err) {
if (err) return new Error(err)
console.log("\n\nLength of array after the function go runs " + myList.length)
db.close()
})
appears to work until the db.each method. The method does not actually return all the rows in the table lorem.
$ node pacheco3.js
Table created.
Table populated.
numb = 0 info field = Ipsum 0
numb = 1 info field = Ipsum 1
numb = 2 info field = Ipsum 2
numb = 3 info field = Ipsum 3
numb = 4 info field = Ipsum 4
numb = 5 info field = Ipsum 5
Number of retrieved rows is 6
When I query the database table by running the SQLite shell, I get these results:
$ sqlite3 test0.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select listnumb as numb, info from lorem;
0|Ipsum 0
1|Ipsum 1
2|Ipsum 2
3|Ipsum 3
4|Ipsum 4
5|Ipsum 5
6|Ipsum 6
7|Ipsum 7
8|Ipsum 8
9|Ipsum 9
sqlite> .exit
Am I making an amateur mistake here in my code for db.each? I'm quite new to node.js and the Node style of asynchronous (non-blocking) code.
I am using node.js version 0.10.24 and the npm module named sqlite3 version 2.1.19. I test my scripts on an OS X 10.9.1 box, using a terminal window. The following script:
var sqlite3 = require('sqlite3').verbose()
var async = require('async')
var myList = []
var db = new sqlite3.Database('./test0.db')
async.series([
// Create a table and populate it
function (callback) {
db.run("CREATE TABLE lorem (listnumb bigint, info TEXT)", function (err) {
if (err) return callback(err)
console.log('Table created. ')
callback()
});
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
for (var i = 0; i < 10; i++) {
stmt.run(i, "Ipsum " + i)
}
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
db.each("SELECT listnumb as numb, info FROM lorem;",
function (err, row) {
if (err) return callback(err)
console.log(' numb = ' + row.numb + ' info field = ' + row.info)
},
function (err, cntx) {
if (err) return callback(err)
console.log('Number of retrieved rows is ' + cntx)
callback()
}
)
}
],
// This function gets called after the tasks above have pleted
function(err) {
if (err) return new Error(err)
console.log("\n\nLength of array after the function go runs " + myList.length)
db.close()
})
appears to work until the db.each method. The method does not actually return all the rows in the table lorem.
$ node pacheco3.js
Table created.
Table populated.
numb = 0 info field = Ipsum 0
numb = 1 info field = Ipsum 1
numb = 2 info field = Ipsum 2
numb = 3 info field = Ipsum 3
numb = 4 info field = Ipsum 4
numb = 5 info field = Ipsum 5
Number of retrieved rows is 6
When I query the database table by running the SQLite shell, I get these results:
$ sqlite3 test0.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select listnumb as numb, info from lorem;
0|Ipsum 0
1|Ipsum 1
2|Ipsum 2
3|Ipsum 3
4|Ipsum 4
5|Ipsum 5
6|Ipsum 6
7|Ipsum 7
8|Ipsum 8
9|Ipsum 9
sqlite> .exit
Am I making an amateur mistake here in my code for db.each? I'm quite new to node.js and the Node style of asynchronous (non-blocking) code.
Share Improve this question asked Jan 11, 2014 at 23:07 Bob CochranBob Cochran 1502 silver badges11 bronze badges1 Answer
Reset to default 3It is because stmt.run() is asynchronous itself, so the sync function
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
for (var i = 0; i < 10; i++) {
stmt.run(i, "Ipsum " + i)
}
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
is creating 10 async stmt.run() tasks to run, an arbitrary number of these 10 tasks may run before the insert function finishes so when the select function runs there are still stmt.run tasks stacked up behind it (which run later, which is why when you query the db they all exist).
if the code is changed to
var sqlite3 = require('sqlite3').verbose()
var async = require('async')
var myList = []
var db = new sqlite3.Database('./test0.db')
async.series([
// Create a table and populate it
function (callback) {
db.run("CREATE TABLE lorem (listnumb bigint, info TEXT)", function (err) {
if (err) return callback(err)
console.log('Table created. ')
callback()
});
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(0, "Ipsum " + 0)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(1, "Ipsum " + 1)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(2, "Ipsum " + 2)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(3, "Ipsum " + 3)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(4, "Ipsum " + 4)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(5, "Ipsum " + 5)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(6, "Ipsum " + 6)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(7, "Ipsum " + 7)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(8, "Ipsum " + 8)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
stmt = db.prepare("INSERT INTO lorem VALUES (?, ?);", function (err) {
if (err) return callback(err)
stmt.run(9, "Ipsum " + 9)
stmt.finalize();
console.log('Table populated. ')
callback()
})
},
function (callback) {
db.each("SELECT listnumb as numb, info FROM lorem;",
function (err, row) {
if (err) return callback(err)
console.log(' numb = ' + row.numb + ' info field = ' + row.info)
},
function (err, cntx) {
if (err) return callback(err)
console.log('Number of retrieved rows is ' + cntx)
callback()
}
)
}
],
// This function gets called after the tasks above have pleted
function(err) {
if (err) return new Error(err)
console.log("\n\nLength of array after the function go runs " + myList.length)
db.close()
})
then the output is
Table created.
Table populated.
Table populated.
Table populated.
Table populated.
Table populated.
Table populated.
Table populated.
Table populated.
Table populated.
Table populated.
numb = 0 info field = Ipsum 0
numb = 1 info field = Ipsum 1
numb = 2 info field = Ipsum 2
numb = 3 info field = Ipsum 3
numb = 4 info field = Ipsum 4
numb = 5 info field = Ipsum 5
numb = 6 info field = Ipsum 6
numb = 7 info field = Ipsum 7
numb = 8 info field = Ipsum 8
numb = 9 info field = Ipsum 9
Number of retrieved rows is 10
Length of array after the function go runs 0
the array length is 0 because myList is not used.