admin管理员组

文章数量:1026147

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.

  • javascript
  • node.js
  • sqlite
Share Improve this question asked Jan 11, 2014 at 23:07 Bob CochranBob Cochran 1502 silver badges11 bronze badges
Add a ment  | 

1 Answer 1

Reset to default 3

It 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.

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.

  • javascript
  • node.js
  • sqlite
Share Improve this question asked Jan 11, 2014 at 23:07 Bob CochranBob Cochran 1502 silver badges11 bronze badges
Add a ment  | 

1 Answer 1

Reset to default 3

It 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.

本文标签： javascriptNodejs and sqlite3 npm module obtain all rows in databaseeachStack Overflow

版权声明：本文标题：javascript - Node.js and sqlite3 npm module: obtain all rows in database#each - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745631669a2160202.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。