Bind by position in SQL Server using Node JS (MSSQL)

Question

Bind by position in SQL Server using Node JS (MSSQL)

Navigation

#1 by (1 votes)

4

I would like to know if you can bind the parameters by position instead of the name using mssql in NodeJS.

Ex:

//Por nome
return pool.request()
.input('input_parameter1', sql.Int, 10)
.input('input_parameter2', sql.Int, 20)
.query('select * from mytable where id = @input_parameter1 and valor = @input_parameter2')

What I need would be something like:

//Por posição
return pool.request()
.query('select * from mytable where id = ? and valor = ?', [10, 20], 
function(err, result){
console.log("Resultado: " + result);
});

javascript sql-server node.js

asked by anonymous 01.11.2017 / 13:55

1 answer

Problem using $ _SESSION in PHP 7.1.10 Problems assigning values to variables in an object

score 1 · Accepted Answer

You can use it this way with mysql (link) . The idea is how to show in the code, use% cos_query markers in the query and then pass an array with the positions whose value should be used instead of the marker.

According to the documentation example:

connection.query('UPDATE users SET foo=?, bar=?, baz=? WHERE id=?', ['a', 'b', 'c', userId], (error, results, fields) => {
  if (error) throw error;
  // ...
});

To implement this in mssql you could do your own wrapper:

const superQuery = (conn, query, values) => {
  let req = pool.request();
  let value = values.shift();
  let counter = 1;

  while (typeof value !== 'undefined') {
    let sep = 'input_parameter' + (counter++);
    req = req.input(sep, value);
    query = query.replace('?', '@' + sep);
    value = values.shift()
  }
  return req.query(query);
}

And then use it like this:

superQuery(
    connection, 
    'UPDATE users SET foo=?, bar=?, baz=? WHERE id=?', 
    ['a', 'b', 'c', userId]
).then(res => {
    ...etc
});

Note: ? has a concept of prototypes as the second optional argument of mssql . It would be simple to embed this in my wrapper so as not to lose that level of security. But the past array instead of simple would be an object with the prototype as well.