[Fixed] Connection to SQL Server using Express.js

Issue

I’m writing code to connect to SQL Server from express.js. Everything is fine but I want to return error at the end of function. I declared global variable and if error I’m assigning error to that variable and returning at the end. But still that variable value I’m getting undefined. Please someone help me on this.

Here is the code:

 async function mysqlConnection(userParam) {
  var config = {
    user: userParam.connectionUsername,
    password: userParam.connectionPassword,
    server: userParam.hostname,
    database: userParam.databaseAccount,
  };

  var msg
  let connection=sql.connect(config, function (err) {
    if (err) {
      console.log(err.message);
      msg=err.message // return err;
    } else {
      console.log("Done");
    }
    return msg
  });

  console.log(msg)
  return msg
}

Tried this too…

  async function mysqlConnection(userParam) {
  var config = {
    user: userParam.connectionUsername,
    password: userParam.connectionPassword,
    server: userParam.hostname,
    database: userParam.databaseAccount,
  };

 var msg
  try {

    let connection=await sql.connect(config);
    return msg; // undefined
  } catch (err) {
     console.log(err.message);
     msg = err.message;
     return msg;
  }

}

Here msg is a global variable and and I’m assigning value inside if condition and I’m returning that variable before function end but I’m getting undefined if connection fails instead of error message, but inside if condition I’m getting error message. Please can someone suggest a solution for this?

Thank you

Solution

You are using callback function to connect to SQL Server. These 2 lines :

  console.log(msg)
  return msg

are executed before the code in callback function

  if (err) {
      console.log(err.message);
      msg=err.message // return err;
    } else {
      console.log("Done");
    }
    return msg

And the variable msg is undefined as consequence.

My suggestion: try to avoid callback if you can, searching for the same function but using Promise or async/await style instead of callback. Then you can rewrite your code to something like :

 var msg
try {

  let connection=await sql.connectAsync(config);
  return msg; // undefined
} catch (err) {
   console.log(err.message);
   msg = err.message;
   return msg;
}

These articles may help you understand better:

• https://codeburst.io/node-js-mysql-and-promises-4c3be599909b
• https://codeburst.io/node-js-mysql-and-async-await-6fb25b01b628

— EDIT —

To debug, you can add some console.log in try and catch block.

var msg;
try {

  let connection=await sql.connect(config);
  console.log("Connect to database success");
// the msg variable is undefined here because we haven't assigned any value to it
// so it's normal
 return "Connect to database success"; 
} catch (err) {
   console.log("Error when connecting to database");
   console.log(err); // not sure if err.message is defined.
                    // You should look into the err variable to check that

   return "Error"; // we can return our custom message, or `err.message` if it present in err object
}

In the end, you don’t even need the msg variable