从节点js调用存储过程 [英] Calling stored procedure from node js

问题描述

我正在使用Express，并且试图从Azure门户调用LoginMember存储过程。这是我的表格，摘自ejs：

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>

这是我的发帖请求

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来自另一个文件，这部分很好。以防万一，这是我的存储过程创建代码：

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

会发生什么-当我提交表单时，页面会持续加载3-5分钟，之后我会得到

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.

推荐答案

确保在执行查询或失败后关闭sql连接。我在代码中发现的是，您正在sql.connect外部关闭sql连接，这是不正确的，因为节点js本质上是异步的，在完成之前正在终止您的连接。请在下面找到更新代码：

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);

    });
  });
});

我希望这会有所帮助。

这篇关于从节点js调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！