I'm trying to call stored procedure in SQL Server database using tedious-connection-pool

74 Views Asked by At

I get the following error:

Error: Validation failed for parameter 'strSearchKeywords'. No collation was set by the server for the current connection.

var ConnectionPool = require('tedious-connection-pool');

var poolConfig = {
  min: 2,
  max: 4,
  log: true
};

var connectionConfig = {
  userName: '***',
  password: '**',
  server: '*\***.com',
  database: '**',
  options: {
    cryptoCredentialsDetails: {
      minVersion: 'TLSv1'
    },
    encrypt: false,
    rowCollectionOnRequestCompletion: true,
  }
};

var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.on('error', function(err) {
  console.error(err);
});

app.post('/api/search', (req, res, next) => {
  pool.acquire((err, connection) => {
    if (err) {
      console.error(err);
      return;
    }
    console.log(connection);
    let storedProcedure = '[dbo].[procedure_name]';

    // SQL Server request
    const request = new Request(storedProcedure, function(err, rowCount, rows) {
      if (err) {
        throw err;
      } else {
        res.json(rows);
      }
    });

    request.addParameter('siPageIndex', TYPES.SmallInt, req.body.StartIndex);
    request.addParameter('siPageSize', TYPES.SmallInt, 10);
    request.addParameter('strSearchKeywords', TYPES.VarChar, req.body.Keywords);
    request.addParameter('btSearchAnyKeywords', TYPES.Bit, req.body.AllOrAnyKeywords);
    request.addParameter('searchedBy', TYPES.Int, req.body.userId);
    request.addParameter('country', TYPES.VarChar, "0");

    // SQL Server procedure call
    connection.callProcedure(request);
  });
});

This works perfectly fine when use tedious.Connection() to call the procedure but throws error when i try to use connection pool

0

There are 0 best solutions below