CODEWITHSUNDEEP
mysqlnode.js
user11586200
user11586200

Reputation:

Stored Procedure Output in NodeJS

Can someone tell me what is wrong with this code? I am getting syntax error near Select category_ID; I am using latest version is mysql in nodejs

Note - If i remove the output params, Input code is properly working.

Node Server Code -

app.post('/api/createcategory', function (req, res) {
     name = req.body.categoryName, icon = req.body.categoryIcon;
     let createcategory = `CALL spAddCategory(?, ?, @category_id); SELECT @category_id;`
     db.query(createcategory, [name, icon], (err, result) => {
          if(err) {throw err};
          console.log(result);

     })
     res.send('Category Created')
})

SQL Query -

CREATE PROCEDURE spAddCategory ( IN category_name varchar(255), IN category_icon varchar(255), OUT category_id int )
     BEGIN
          INSERT INTO categories ( categoryName, categoryIcon ) 
          VALUES ( category_name, category_icon );

          SELECT categoryID INTO category_id FROM categories
          WHERE categoryName = category_name;
     END

Upvotes: 0

Views: 894

Answers (1)

slaakso
slaakso

Reputation: 9080

Instead of OUT-variables (which are useful mainly between procedures), consider handling the output as normal result set:

app.post('/api/createcategory', function (req, res) {
     name = req.body.categoryName, icon = req.body.categoryIcon;
     let createcategory = `CALL spAddCategory(?, ?);`
     db.query(createcategory, [name, icon], (err, result) => {
          if(err) {throw err};
          console.log(result[0]);

     })
     res.send('Category Created')
})

And the procedure, returns the result set which contains the last inserted id (assuming the categoryID is an AUTO_INCREMENT id):

CREATE PROCEDURE spAddCategory (
category_name varchar(255),
category_icon varchar(255)
)
BEGIN

INSERT INTO categories ( categoryName, categoryIcon ) 
VALUES ( category_name, category_icon );

SELECT last_insert_id();

END

Note that you may end up with more than one category with same name.

Upvotes: 0

Related Questions