CODEWITHSUNDEEP
node.jssqlite
Siwei
Siwei

Reputation: 21597

in sqlite3 (node package), how to run multiple queries by sequence?

I am using this pakcage: sqlite3 to manipulate database queries, code like this:

single query is quit simple:

 var sqlite3 = require('sqlite3').verbose();
 let db = new sqlite3.Database('database.sqlite3');
 let entity_1_name = ''
 db.serialize(function(){
   db.get('select * from table1 where id = 1', function(error, row){
      entity_1_name = row.name
   })
 })
 db.close()

But how to execute multiple queries and in sequence? (e.g. I want to run query1, then query2, then query3 )
just like:

 var sqlite3 = require('sqlite3').verbose();
 let db = new sqlite3.Database('database.sqlite3');
 let entity_1_name = ''
 let entity_2_name = ''
 db.serialize(function(){

   // this is query1
   db.get('select * from table1 where id = 1', [], function(error, row){
      entity_1_name = row.name
   })

   // this is query2
   db.get('select * from table2 where dependency_column_name = ' + entity_1_name, function(error,row){
      // this sql returns null value, because when this query was executed, the variable "entity_1_name" was not determined by query1.
   })

 })


 db.close()

Upvotes: 1

Views: 4629

Answers (2)

Siwei
Siwei

Reputation: 21597

OK, at last, I used promise + async + await to make it done.

Step1. define a function return new Promise()

      do_query_1(material_name){
        // 1. return this new Promise() 
        return new Promise( (resolve, reject) => {
          let db = this.$database_tool.get_db()
          let that = this
          db.serialize(function(){
            db.get(`select * from table1 where id = 1`, [], function(error, row){
              // 2. this is the point, put anything you want to result to resolve()
              resolve(row.id)
            })
          })
          db.close();

        })
      },

Step2. define a async function, to call the method defined in Step1.


      async do_query_2(){
        let that = this
        // other lines was omitted.
        // HERE , use await to get the result from STEP1. 
        let entity_1_name = await that.do_query_1(row.material_name)

        db.serialize(function(){
          let sql = `'select * from table2 where dependency_column_name = ' + entity_1_name`

          db.run(sql)
        })
        db.close();
      },

Step3. call do_query_2() in normal way:

do_query_2()

That's it!

Upvotes: 2

Rexy
Rexy

Reputation: 121

https://stackoverflow.com/a/40903302/16140221 May help you out

Their solution was to place each query within a function, then simply calling them. (This is directly the code taken from the answer, it is not mine.)

function db_query1(your_param,...., callback){
　　// database operation
   db.run( sql , [param,...] , function(err,rows){
      if(err) // return
      else{
         // get rows with callback
         callback(null, rows);
      }
   });
}

function db_query2(your_param,...., callback){
　　// database operation
   db.run( sql , [param,...] , function(err,rows){
      if(err) // return
      else{
         // get rows with callback
         callback(null, rows);
      }
   });
}

Then calling the functions

db_query1(....,function(err,result1){
   if(err) ...// return 
   // do the things with result1
   // And then call query2
   db_query2(....,function(err,result2){
      if(err) ...// return 
      // do the things with result1
   });
});

Upvotes: 1

Related Questions