getting values as strings from database-CodePudding

I'm trying to get a row of data (which has two columns) with a SQL query SELECT * FROM btc WHERE date = '...' as a string. the SQL returns all the data from a row where the date is the one I'm looking for, and it is correct (I am getting the wanted line in DB Browser) but for some reason I'm getting this output:{} any Idea what's the problem? Here's my code:


  const sqlite3 = require('sqlite3').verbose()

  const db = new sqlite3.Database("./server/coin.db", sqlite3.OPEN_READWRITE, (err) =>{
    if (err) return console.error(err.message);

    console.log("connection successfull")
  });

  console.log("entered bitcoin query!");
  
  let msg = "";
  for (let m = 1; m<= 12; m  )
  {
    if (m==1 || m==3 || m==5 || m == 7 || m == 8 || m == 10 || m == 12)
    {
      for (let d = 1; d<= 31; d  )
      {
        if(d<10 && m<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-0'   m   "-0"   d   '"';
          console.log(sql);
        }
        else if (m<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-0'   m   "-"   d   '"';
        }
        else if (d<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-'   m   "-0"   d   '"';
        }
        else
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-'   m   "-"   d   '"';
        }
        msg = msg   JSON.stringify(db.run(sql, (err) =>{

          if (err) return console.error(err.message);
        }));
      }
    }
    if(m==4 || m==6 || m==9 || m==11)
    {
      for (let d = 1; d<= 30; d  )
      {
        if(d<10 && m<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-0'   m   "-0"   d   '"';
        }
        else if (m<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-0'   m   "-"   d   '"';
        }
        else if (d<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-'   m   "-0"   d   '"';
        }
        else
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-'   m   "-"   d   '"';
        }
        msg = msg   JSON.stringify(db.run(sql, (err) =>{

          if (err) return console.error(err.message);
        }));
      }
    }
    if (m == 2)
    {
      for (let d = 1; d<= 28; d  )
      {
        if (d<10)
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-0'   m   "-0"   d   '"';
        }
        else
        {
          sql = 'SELECT * FROM btc WHERE date = "2021-0'   m   "-"   d   '"';
        }
        msg = msg   JSON.stringify(db.run(sql, (err) =>{

          if (err) return console.error(err.message);
        }));
      }
    }
    console.log(msg);
  }
  res.json({ message: msg });

  db.close((err) =>{  
    if (err) return console.error(err.message);
  });
});

CodePudding user response：

First, db.run does not return any data from the database, you must use db.get instead.

Second, these statements are executed asynchronously and a callback function is executed after they has finished. But you implement only the error handling in the callback function (err) =>{...}, not the success handling.

Third, additional complexity comes from the fact that you want to execute 365 such statements. I suggest that you use util.promisify to convert the callback flavor into a promise flavor and then Promise.all to collect the results:

let getPromise = util.promisify(db.get.bind(db));
let queries = [];
for (let m = 1; m <= 12; m  ) {
  let dmax = /* number of days of that month */
  for (let d = 1; d <= dmax; d  ) {
    let sql;
    /* Let sql = SELECT statement string depending on m and d. */
    queries.push(getPromise(sql));
  }
}
Promise.all(queries).then(
  function(results) {
    res.json({message: results});
  }, function(err) {
    console.error(err.message);
  });

Lastly, it would probably be much quicker if you retrieved the values for all days of the year in one SQL request:

SELECT * FROM btc WHERE date LIKE "2021-%"