Nodejs query in SQL Server

Question

Hello, guys I need to make a query filtered for days in SQL in a Nodejs application, but during the POST that’s when I filter the Nodejs accuses me of a value conversion error. When running get I can recover the values of Data through the Datebook, but the function Lerdatagames accuses me error when searching in the database

router.post('/', function(req, res, next) {
 lerData(function(d){
   lerJogosData(function(j){
     res.render('jogos', { title: 'Tabela de Jogos', datas: d, jogos: j});
     }, req.body.escolher_data_jogo);
   }); 
 });
}
function lerJogosData(callback, data){
console.log(data);
var conexao = new sql.ConnectionPool(dbconfig, function(err) {
    if (err) throw err;
    var request = new sql.Request(conexao);
    var statement = "SELECT team1.codigo, team1.nome as teamA, team2.codigo, team2.nome as teamB, "+
    "CONVERT(varchar(10), data_jogo, 103) AS dia FROM jogos, team AS team1, team AS team2 WHERE "+
    "jogos.codTeamA = team1.codigo and jogos.codTeamB = team2.codigo AND data_jogo = '"+data+"'";
    request.query(statement, function (err, result) {
        if (err) throw err;
        conexao.close();
       callback(result.recordset);
    });
   });
 }
 function lerData(callback){
   var conexao = new sql.ConnectionPool(dbconfig, function(err){
     if (err) throw err;
       var request = new sql.Request(conexao);
       var statement = "SELECT DISTINCT CONVERT(VARCHAR(10), data_jogo, 103) AS dia FROM jogos ORDER BY dia DESC";
       request.query(statement, function (err, result) {
         if (err) throw err;
           conexao.close();
    callback(result.recordset);
  });
});

}

That’s the stracktrace of the error

RequestError: Operand type clash: date is incompatible with int base.js:1530
 at handleError (c:\Users\Caio\Documents\Development\node\LabBD\av01\node_modules\mssql\lib\tedious.js:546:15)
at emitOne (events.js:116:13)
at Connection.emit (events.js:211:7)
at Parser.<anonymous> (c:\Users\Caio\Documents\Development\node\LabBD\av01\node_modules\tedious\lib\connection.js:611:16)
at emitOne (events.js:116:13)
at Parser.emit (events.js:211:7)
at Parser.<anonymous> (c:\Users\Caio\Documents\Development\node\LabBD\av01\node_modules\tedious\lib\token\token-stream-parser.js:54:15)
at emitOne (events.js:116:13)
at Parser.emit (events.js:211:7)
at addChunk (c:\Users\Caio\Documents\Development\node\LabBD\av01\node_modules\readable-stream\lib\_stream_readable.js:291:12)

This is the structure of the consultation I need to do

TABLE jogos(
numJogo INT UNIQUE IDENTITY, --USADO PARA CONSULTAS INTERNAS DO SISTEMAS NÃO PRECISO DELE NO FRONT
codTeamA INT NOT NULL, --CÓDIGO DO TIME A PARA GERAR O JOGO
codTeamB INT NOT NULL, --CÓDIGO DO TIME B PARA GERAR O JOGO
data_jogo DATE, --DATA QUE O JOGO VAI ACONTECER
FOREIGN KEY(codTeamA) REFERENCES team(codigo),
FOREIGN KEY(codTeamB) REFERENCES team(codigo),
PRIMARY KEY(codTeamA, codTeamB),
CHECK(codTeamA != codTeamB),

)

Answer 1

Rewriting your code and applying best practices defined in airbnb guide I got the following code:

// REQUIRES

const lerData = async (pool) => {
  const resultado = await pool
    .request()
    .query(`SELECT DISTINCT CONVERT(VARCHAR(10), j.data_jogo, 103) AS dia
              FROM jogos j
             ORDER BY j.dia DESC`);

  return resultado;
}

const lerJogosData = async (pool, dia) => {
  const resultado = await pool
    .request()
    // Determina o tipo do parâmetro "dia"
    .input('dia', sql.DateTime, dia)
    .query(`SELECT t1.codigo,
                   t1.nome AS teamA,
                   t2.codigo,
                   t2.nome AS teamB,
                   CONVERT(varchar(10), data_jogo, 103) AS dia
              FROM jogos AS j
             INNER JOIN team AS t1 ON j.codteama = t1.codigo
             INNER JOIN team AS t2 ON j.codteamb = t2.codigo
             WHERE data_jogo = @dia`);

  return resultado;
}

const resolver = async ({ body: { escolher_data_jogo } }, res) => {
  try {
    // Cria apenas uma conexão para ser usada nas duas consultas
    const pool = await sql.ConnectionPool(config).connect();

    // Executa as duas funções em paralelo
    const [datas, jogos] = await Promise.all([
      lerData(pool),
      lerJogosData(pool, escolher_data_jogo),
    ]);

    res.render('jogos', { datas, jogos, title: 'Tabela de Jogos' });
  } catch(e) {
    console.error(e);
    res.status(500).send('Erro Interno.');
  }
}

// Rotas
router.post('/', resolver);

Note that I used async/await to simplify the code and avoid chained functions. I performed the parameter passage as indicated in the module documentation mssql (which, despite not being informed, is what is apparently being used).