0

I'm here because I don'0t understand how to wait for an SQL response, do some computation, and then send it back to the client using the http POST method. In particular, I want to query a report week by week, aggregate the response in an array and, after it finishes, send the aggregated response to the client using the HTTP post method. Here is the code example:

app.post("/getDataByWeek", (req, res) => {
  async function queryDB(){
    let dataArray = [];
    var today = new Date();
    for(let i=0;i<=req.body.prev_week;i++){
        let currWeek = today;
        currWeek.setDate(currWeek.getDate() - i*7);
        dataArray.push(currWeek.toLocaleString().split(" ")[0].replace(",",""))
    }
    let scarti = [];
    let prod = [];
    let dateArray = [];
    let pool = await sql.connect(configSQL);
    let data =  pool.request()
    for(var i=dataArray.length - 1;i>0;i--){
      let startDate = normData(dataArray[i]);
      let endDate = normData(dataArray[i-1]);
      let query_string = "SELECT  SUM(qty_good) as PRODOTTE, SUM(qty_reject) as SCARTO FROM [MESDB].[dbo].[vw_oee_data] where ent_name = 'Scatolatrice' and cast(hour_end_local as time) BETWEEN '06:00:00.000' AND '21:00:00.000' and cast(hour_end_local as date) BETWEEN '" + startDate +"' AND '" + endDate + "'";
      
      await data.query(query_string,  function (err, recordset) { 
          prod.push(recordset.recordset[0].PRODOTTE)
          scarti.push(recordset.recordset[0].SCARTO)
          dateArray.push(endDate)             
      })
    }
    return prod,scarti,dateArray; 
  }
  queryDB().then((prod,scarti,dateArray)=>console.log(prod))
 

});

At the end of the code I simply used console.log() to print the results, but they are empty. Could you please help me to understand how I have to wait for SQL response before continuing the code execution?

I have tried the code above, but the result (for example the prod array) is empty.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Mattia DELLI PRISCOLI
  • 11
  • 1
  • What are `sql.connect`, `pool.request` and `data.query`, what library are you using? – Bergi Jun 01 '23 at 09:29
  • 2
    A function that accepts a callback does typically not return a promise and cannot be `await`ed – Bergi Jun 01 '23 at 09:30
  • 1
    I think you're return might be cancelling out. What happens when you wrap it all in brackets? return { prod,scarti,dateArray } – Mark Barton Jun 01 '23 at 09:33
  • I'm using: const sql = require('mssql'); I have tried to await all the parts of the code that I think should end before continuing execution. If you have some code examples, please post me. I cannot figure out how to use await/async. The idea is to execute more than one query, push the results in an array and print it at the end of the function(that is send back the result). Now the problem is that the arrays are all empty, because it is not waiting for query execution – Mattia DELLI PRISCOLI Jun 01 '23 at 09:46
  • @MarkBarton I tried it. The arrays are still empty. I know that I'm not an expert in Javascript and maybe my question could not be clear. If you could address me to solve this problem (maybe indicating to me where to find some good explanations about await/async), I appreciate it – Mattia DELLI PRISCOLI Jun 01 '23 at 10:08
  • Did you check https://www.npmjs.com/package/mssql#asyncawait? – Bergi Jun 01 '23 at 12:33

0 Answers0