Stored Procedure Output in NodeJS

Question

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

slaakso · Accepted Answer · 2020-07-09 16:04:32Z

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.

Collectives™ on Stack Overflow

Stored Procedure Output in NodeJS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related