Is there a driver for mysql on nodejs that supports stored procedures?

Question

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

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

Answer 1

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:

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 ...

    }
});

Answer 2

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

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.

here is good tutorial on nodejs and mysql