CODEWITHSUNDEEP
mysqlnode.jsstored-procedures
Glen Pierce
Glen Pierce

Reputation: 4821

How does one create a stored procedure in Node.js using the mysql library?

How does one create a stored procedure in Node.js using the mysql library? Does the npm package mysql support creating stored procedures or is there an error in this code? This is the npm library I'm using: https://www.npmjs.com/package/mysql

I am trying to automate setting up the database for an open source project to help new contributors get their development environments up and running (and get us all on the same database schema).

To accomplish this, I've written a script that is supposed to send all of the commands to a mysql instance to setup the database. Creating tables is working, but creating new stored procedures is always resulting in an error. When I run the command in MySQL Workbench, the creation script does work, so the problem seems to be limited to the script.

var emails =
        "CREATE TABLE emails (" +
            "`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
            "`email` VARCHAR(2048) CHARACTER SET utf8" +
        ");";

var addEmail =
        "DELIMITER //\n" +
        "CREATE PROCEDURE addEmail(IN email VARCHAR(2048))\n" +
        "BEGIN\n" +
        "INSERT INTO emails (email) VALUES (email);\n" +
        "END //\n" +
        "DELIMITER ;";

var query = [];
query.push(emails);
query.push(addEmail);

for(var i = 0; i < query.length; i++) {

    console.log(query[i]);
    connection.query(query[i], function (err, rows, fields) {
        if (!err) {
            console.log("success"); //emails succeeds, as do other create table commands
        } else {
            console.log('Error while performing Query.'); //any queries that create stored procedures fail
        }
    });

}

Taking the excellent advice from the accepted answer, the corrected code looks like this:

var addEmail =
        "CREATE PROCEDURE addEmail(IN email VARCHAR(2048))\n" +
        "BEGIN\n" +
        "INSERT INTO emails (email) VALUES (email);\n" +
        "END";

Upvotes: 4

Views: 3266

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

  1. You don't need to use DELIMITER when you're running SQL programmatically. The DELIMITER is needed only when using the mysql client for executing a stream of SQL statements, either manually or from a script file. When you run one query at a time, you don't have to resolve any ambiguity about whether the ; inside the procedure definition is the end of the statement.

  2. It's good that you check for error status from your query, but you also should develop the habit of checking the type of error and the error message. That will help you to be able to troubleshoot many types of problems—without needing to post to Stack Overflow and hoping someone answers your question promptly! 

    connection.query(query[i], function (err, rows, fields) {
    if (!err) {
        console.log("success");
    } else {
        console.log('Error while performing Query.');
        console.log(err.code);
        console.log(err.message);
    }
});

Upvotes: 8

Related Questions