Calling stored procedure from node js

Question

I am using express and I am trying to call LoginMember stored procedure from Azure portal. This is my form from, taken from ejs:

<form method="post" action="/available-copies">
      <input type="text" placeholder="SSN" name="userssn">
      <input type="password" placeholder="Password" name="userpassword">
      <input type="submit" value="Login">
</form>

This is my post request

router.post('/available-copies', function (req, res) {
    var ssn = req.body.userssn;
    var password = req.body.userpassword;

    sql.connect(sqlConfig, function (err) {
        if (err) console.log(err);
        // create Request object
        var request = new sql.Request();
        // query to the database and execute procedure
        request.query("exec LoginMember @SSN='"+ssn+"', @PASSWORD='"+password+"';", function (err, recordset) {
            if (err) console.log(err);

            res.send(recordset);
        });
    });
    sql.close()
});

SqlConfig comes from another file and this part is fine. Just in case, this is my stored procedure creation code:

CREATE PROCEDURE LoginMember @SSN varchar(11), @PASSWORD char(64)
AS
BEGIN
   Select * from Member
   WHERE Member.ssn = @SSN AND Member.password = @PASSWORD
END

What happens- When I submit my form, page keeps loading for 3-5mins, after that I get

This page isn’t working
localhost didn’t send any data.

Console doesn't return any additional errors. I know this might be not the best solution I can be using, but this is my approach and I want to make it work.

DHIRAJ KATEKAR · Accepted Answer · 2018-06-05 13:07:57Z

7

Make sure you are closing sql connection after query execution or failure.What i found in your code is that you are closing sql connection outside sql.connect which is incorrect because node js is asynchronous in nature which is killing your connection before completion.Please find the update code below:

router.post('/available-copies', function (req, res) {
var ssn = req.body.userssn;
var password = req.body.userpassword;

sql.connect(sqlConfig, function (err) {
    if (err) console.log(err);
    // create Request object
    var request = new sql.Request();
    // query to the database and execute procedure 
    let query = "exec LoginMember @SSN='" + ssn + "', @PASSWORD='" + password + "';";
    console.log(query)
    request.query(query, function (err, recordset) {
        if (err) {
            console.log(err);
            sql.close();
        }
        sql.close();
        res.send(recordset);

    });
  });
});

I hope it helps.

edited Jun 5, 2018 at 13:07

answered Jun 5, 2018 at 10:02

DHIRAJ KATEKAR

2381 silver badge8 bronze badges

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

4 Comments

Kelb56 Over a year ago

Thank you, this seems to work, so I guess my issue was that I was closing my connection in incorrect place?

DHIRAJ KATEKAR Over a year ago

yes, alway close or release connection inside the execution function after it's success or failure

Kelb56 Over a year ago

One more fast question, is there a way to console.log() my whole query that I am passing? To see what actually gets send to the sql server?

DHIRAJ KATEKAR Over a year ago

just set you query string which you are passing in request.query to a variable and log that variable and pass it to request.query for example: let query = "exec LoginMember @SSN='" + ssn + "', @PASSWORD='" + password + "';"; console.log(query); request.query(query, function (err, recordset) {

ASHUTOSH PAWAR · Accepted Answer · 2023-03-29 11:07:07Z

In this example, I've used request.execute() method insted of using request.query() to call the procedure.

Just remember that while calling the procedure we have to add cloumn_name it's data_type and the key_value which we are using to compare with.

For that we have to use request.input() method, here I am calling a procedure called GetLedgerBalance as an example.

CREATE PROCEDURE GetLedgerBalance(
    @c_id VARCHAR(MAX) 
)
AS
BEGIN
    SELECT ledgerBalance 
    FROM clients
    WHERE c_id = @c_id
END

In this procedure now we are declaring a variable for key_value which is user_id

let user_id = "C00008";

//calling stored procedure   
sql.connect(config).then(() => {
    const request = new sql.Request();

    request.input("c_id", sql.VarChar, user_id);

    request.execute("GetLedgerBalance").then((result) => { 
        const ledgerBalance = result.recordset[0].ledger_balance;
        console.log(`Ledger Balance is: ₹ ${ledgerBalance}/-`);
    }).catch((err) => {
        console.log(`Error executing sp ${err}`);
    });
}).catch((err) => {
    console.log(err);
});

Please consider it for mssql module maybe there should be some differences in another modules

Collectives™ on Stack Overflow

Calling stored procedure from node js

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related