NodeJs query in SQL Server

Question

NodeJs query in SQL Server

Navigation

#1 by (1 votes)

2

Hello everyone, I need to do a filtered query for days in SQL in a NodejS application, but during the POST that is when I make the NodeJs filter I am accused of a value conversion error. When I run get get myself to retrieve the values of the Data through readData, but the ReadDataGames function accuses me of error when querying 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);
  });
});

}

This is the error stracktrace

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 query 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),

)

sql sql-server query node.js

asked by anonymous 21.09.2018 / 17:17

1 answer

Subtitles ggplot2 How to break lines according to a value

score 1 · Answer 1

Rewriting your code and applying good practices defined in the AirBNB guide , I obtained 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 code and avoid chained functions. I made the parameter passing in the way that is indicated in the documentation of the mssql module (which, although it was not informed, is what is apparently being used).