在nodejs上是否有支持存储过程的mysql驱动程序？

I am looking for a mySQL driver for nodejs that supports stored procedures. http://nodejsdb.org/db-mysql/ that I have been using gives the error

我正在寻找支持存储过程的nodejs的mySQL驱动程序。我一直在使用的http://nodejsdb.org/db-mysql/给出了错误

PROCEDURE can't return a result set in the given context

PROCEDURE无法在给定的上下文中返回结果集

4 个解决方案

#1

it works in nodejs-mysql-native

它适用于nodejs-mysql-native

stored procedure:

存储过程：

DELIMITER //
CREATE PROCEDURE test1p1()
  BEGIN
  SELECT 1+1;
  END //
DELIMITER ;

node.js script:

node.js脚本：

mysql = require('mysql-native');
var db = mysql.createTCPClient();
    db.auth('test', 'tester', ''); // db, user, password

db.query('call test.test1p1;').on('row', function(r) {
    console.log(r);
}).on('end', function() {
    console.log('OK!');
});

output:

输出：

{ '1+1': 2 }
OK!

#2

Felix Geisendörfer's node-mysql supports stored procedures, but you need to end your stored procedure by SELECTing a success/failure flag, then query it as you would a SELECT query. Here's how the stored procedure might look:

FelixGeisendörfer的node-mysql支持存储过程，但您需要通过选择成功/失败标志来结束存储过程，然后像查询SELECT一样查询它。以下是存储过程的外观：

DELIMITER //
DROP PROCEDURE IF EXISTS MyProcedure //
CREATE PROCEDURE MyProcedure(IN param1 VARCHAR/*, My, Parameters, ... */)
BEGIN

    DECLARE EXIT HANDLER FOR NOT FOUND, SQLWARNING, SQLEXCEPTION SELECT 0 AS res;
    # My Queries etc. ...

    SELECT 1 AS res;

END //
DELIMITER ;

Your Node code would look something like this:

您的节点代码看起来像这样：

var mysql = require('mysql');

var client = mysql.createConnection({
    host    : '127.0.0.1',
    user    : 'username',
    password: 'password'
});
client.query('USE mydatabase');

var myParams = "'param1', 'param2', ... ";
client.query("CALL MyProcedure(" + myParams + ")", function(err, results, fields) {
    if (err || results[0].res === 0) {
        throw new Error("My Error ... ");
    } else {
        // My Callback Stuff ...

    }
});

#3

node-mysql driver work with stored procedure and its very simple just call your stored procedure with parameter.

node-mysql驱动程序使用存储过程，它非常简单，只需使用参数调用存储过程即可。

CREATE PROCEDURE GetAllStudent(id int)
BEGIN
SELECT * FROM student where userid = id ;
END;

and in node just call

并在节点中调用

app.get('/sp', function (req, res, next) {
    connection.connect();
    connection.query('CALL GetAllStudent(?)',[req.body.id],function (err, rows, fields) {
        if (err) {
            res.status(400).send(err);
        }
        res.status(200).send(rows);
    });

    connection.end();
});

this way no need to worry of sql injection.

这种方式无需担心sql注入。

here is good tutorial on nodejs and mysql

这是关于nodejs和mysql的好教程

#4

Putting multiple solutions together for completeness

将多个解决方案放在一起以实现完整性

Stored Procedure:

存储过程：

CREATE PROCEDURE GetStudent(id int)
BEGIN
SELECT * FROM student where userid = id ;
END;

Node.js and Express code:

Node.js和Express代码：

var express = require('express');
var mysql = require("mysql");
var

app = express();

var pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'demo'
});

app.get('/pool', function (req, res) {

    var studentId = req.body.id;

    pool.getConnection(function (err, connection) {
        // connected! (unless `err` is set)
        if (err) {
            res.status(400).send(err);
        }
        connection.query('CALL GetStudent(?)',[studentId], function (err, rows, fields) {
             connection.release();
             if (err) {
                res.status(400).send(err);
            }
            res.status(200).send(rows);
        });     
    });
});

app.listen(4000, function () {
    console.log('Server is running.. on Port 4000');
});

(Source credits: Pushker Yadav and "http://www.javascriptpoint.com/nodejs-mysql-tutorial-example/")

（来源：Pushker Yadav和“http://www.javascriptpoint.com/nodejs-mysql-tutorial-example/”）