node-mssql won't execute stored procedure giving unhandlePromiseRejectionWarning

Question

The following is the output from my server.js file.
I am printing out the query string parameters that I'm passing into the stored procedure.

ID : 10################_
Email : [email protected]
Name : testname
FamilyName : testtesttest

Below this is the error I'm getting:

(node:8084) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 1): TypeError: Cannot read property 'type' of undefined

(node:8084) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Below is my call to the stored procedure. I keep getting the above error and the stored procedure is not being executed. I've looked at the following link that appeared to have the same problem, but I'm still getting the same error.

This is my 1st attempt to call a stored procedure using this node module. So any assistance is appreciated.

app.get('/addRegistration', function(req,res){
    try 
    {
        const pool5 = new sql2.ConnectionPool(config, err => 
            {
                // Stored Procedure
                pool5.request()
                .input('FBID', sql2.varchar, 250, req.query.id)
                .input('FBEmail', sql2.varchar, 250, req.query.email)
                .input('FBName', sql2.varchar, 250, req.query.memberName)
                .input('FamilyName', sql2.varchar, 250, req.query.familyName)
                .execute('sp_registerUser', (err, result) => 
                {
                    console.log("Success"); 
                }).catch(function(err) {
                    console.log(err);
                });
            })
         pool5.on('error', err => {
            console.log("Error");
         })
    } catch (err) {
    // ... error checks
        console.log("addRegistration: " + err)
    }       
});

Answer 1

I found the issue. It was my original code.

            .input('FBID', sql2.varchar, 250, req.query.id)
            .input('FBEmail', sql2.varchar, 250, req.query.email)
            .input('FBName', sql2.varchar, 250, req.query.memberName)
            .input('FamilyName', sql2.varchar, 250, req.query.familyName)

Needed to be changed to:

            .input('FBID', sql2.VarChar(250), req.query.id)
            .input('FBEmail', sql2.VarChar(250), req.query.email)
            .input('FBName', sql2.VarChar(250), req.query.memberName)
            .input('FamilyName', sql2.VarChar(250), req.query.familyName)

Ultimately I had the size as part of the value parameter. It was all wrong. Once I did it correctly, then the stored procedure executed as expected.

Answer 2

Apologies if this sounds glib, but I can’t see in your code where you have defined request - so my best answer would be that it is undefined as the error says...

Answer 3

First create a connection file for the sql server.

import mysql from 'mysql';
import config from 'config-yml';
import logger from '../logger';

/**
 * @description Creating pool to have simultanious connection to SQL.
 */
const pool = mysql.createPool({
  connectionLimit: config.db.max_limit,
  host: config.db.host,
  user: config.db.user,
  port: config.db.port,
  password: config.db.password,
  database: config.db.name,
  debug: process.env.DB_DEBUG || false, // Set to true if you want to debug the database.
});

/**
 * @description Creates the database connection.
 */
pool.getConnection((err, connection) => {
  if (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      logger.error('Database connection was closed.');
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
      logger.error('Database has too many connections.');
    }
    if (err.code === 'ECONNREFUSED') {
      logger.error('Database connection was refused.');
    }
  }
  if (connection) connection.release();
});

module.exports = pool;

Then create a export function to execute the query code follows.

import pool from '../index';

const execQuery =  async (query, fn) => {
  pool.query(query, async (err, result, fields) => { // eslint-disable-line
    try {
      console.log(result[0][0]);
      fn(result[0][0]);
      return result;
    } catch (err) {
      return err;
    }
  });
};

export default execQuery;

After this write procedure in Workbench and call sql Procedure function

call SO_CALLED_PROCEDURE(PASS_ARGUMENT);

it will work.