NodeJS - SQL Server connection closed

Question

I'm trying to call a register stored procedure from SQL Server. I'm using NodeJS with the mssql package. I got most of the code from the mssql Npm documentation. But it still doesn't work.

The code basically tries to initiate a ConnectionPool as a read the last update renamed connection to ConnectionPool, create a new request, introduce the parameters and the execute the stored procedure. I have it this way because I need to change the connection variable to another database every once in a while.

The error I get is:

ConnectionError: Connection is closed.

My code:

const sql = require('mssql');

const config = require('../config/dbPoly');

module.exports.registerUser = function(pUserName, pPassword, pNombre, pApellidos, pFK_TipoUsuario, callback) {

  var connection = new sql.ConnectionPool(config);
  var request = new sql.Request(connection);
  request.input('input_parameter', sql.VARCHAR(25), pUserName);
  request.input('input_parameter', sql.VARCHAR(16), pPassword);
  request.input('input_parameter', sql.VARCHAR(25), pNombre);
  request.input('input_parameter', sql.VARCHAR(50), pApellidos);
  request.input('input_parameter', sql.Int, pFK_TipoUsuario);

  request.execute('RegistrarUsuario', (err, result)=>{
    if(err){
      console.log(err);
    }
    else{
      console.log(result);
    }
  });
};

Prabodh M · Accepted Answer · 2017-07-05 05:48:53Z

2

There is this async version of implementation. Check if this serves your purpose

const sql = require('mssql');

const config = require('../config/dbPoly');

module.exports.registerUser = async function(pUserName, pPassword, pNombre, pApellidos, pFK_TipoUsuario) {

    try {

        let connection = await sql.connect(config)

        let result = await connection.request()
            .input('input_parameter', sql.VARCHAR(25), pUserName);
            .input('input_parameter', sql.VARCHAR(16), pPassword);
            .input('input_parameter', sql.VARCHAR(25), pNombre);
            .input('input_parameter', sql.VARCHAR(50), pApellidos);
            .input('input_parameter', sql.Int, pFK_TipoUsuario);
            .execute('RegistrarUsuario');

        return result;
    } catch (err) {
        console.log(err); // ... error checks 
    }

};

sql.on('error', err => {
    // ... error handler 
})

answered Jul 5, 2017 at 5:48

Prabodh M

2,2822 gold badges18 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Prabodh M Over a year ago

Remove the port config, as it is default 1443. Don't use if using named instance

Prabodh M Over a year ago

So your problem solved ? Upvote if found helpful, you are welcome!

Luis Over a year ago

Actually as that code is, node send the following error: Request error :RequestError: Procedure or function 'RegistrarUsuario' expects parameter '@pNombreUsuario', which was not supplied. Any ideas.

Prabodh M Over a year ago

What is exact the stored procedure syntax of mssql you are calling ? You might be missing input or output parameter there to pass from node.

Luis Over a year ago

The parameters are supposed to be added to the request by calling request.input() like I did in the corrected code in this thread. But it still gives me the same error. stackoverflow.com/questions/44930584/…

|

Luis · Accepted Answer · 2017-07-05 14:39:23Z

0

If someone else faces this problem, Prabodh's answer works perfectly.

I then faced another problem, it said the localhost was undefined in 1500ms. The reason was because my server field in the json config file had the following format: 'localhost\Server-Name'. I changed it just 'localhost' and problem fixed.

Also make sure your server has SQL Authentication enabled and not just Windows Authentication.

edited Jul 5, 2017 at 14:39

answered Jul 5, 2017 at 14:23

Luis

1301 gold badge5 silver badges21 bronze badges

1 Comment

Prabodh M Over a year ago

Also if you are using named instance, avoid adding port in config as per the documentation of the module. npmjs.com/package/mssql#general-same-for-all-drivers

Collectives™ on Stack Overflow

NodeJS - SQL Server connection closed

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related