insertId returning 0 after posting with mysqljs and stored procedure

Question

Using mysqljs to query mySQL database via a stored procedure for a webAPI endpoint in express.js. I am required to return the inserted object. for this, I tried to acess the insrtedId based on the documentation from mysqljs. but the insertedid always returns zero.

I tried to include the output parameter in the stored procedure and set it to LAST_INSERT_ID(). Still insertedId is 0

router.post("/", (req, res) => {
  name = req.body.name;
  apiconnection.query(
    `CALL userAdd ('${name}', @_LID)`,
    (error, rows, fields) => {
      if (error) {
        res.json({ message: `cant be saved to the database` });
      } else {
        const id = rows.insertId;
        router.get("/", (req, res) => {
          apiconnection.query(
            `select * from tbl1 where id = ${id}`,
            (error, rows, fields) => {
              if (!error) {
                res.json(rows);
              } else {
                res.json(error);
              }
            }
          );
        });
       }
    }
  );
});

here is the stored procedure 

```CREATE DEFINER=`root`@`localhost` PROCEDURE `userAdd`(IN _name varchar(250), OUT _LID int)
BEGIN
  insert into tbl1(name) values (_name);
  set _LID = LAST_INSERT_ID();
END```

note that the id is set to auto increment

what does `console.log(rows)` from your first Stored Proc call look like? — dusthaines, Jun 11 '19 at 01:50
I am getting the values I want from the database with console.log(rows) — Firealem Erko, Jun 11 '19 at 02:26

score 2 · Accepted Answer · answered Jun 11 '19 at 19:57

As I am requrired to work with only stored procedures, I selected the added record in the insert stored procedure. This makes that record available when the POST method is called.

CREATE DEFINER=`root`@`localhost` PROCEDURE `userAdd`(IN _name varchar(250), OUT _LID int)
BEGIN
  insert into tbl1(name) values (_name);
  set _LID = LAST_INSERT_ID();
  select * from tbl1 where id = _LID;
END

then in the POST method, the added recored can be accessed as an object from the rows as 'rows[0][0]' . no need to make a get call to the database

   router.post("/", (req, res) => {
  name = req.body.name;
  apiconnection.query(
    `CALL userAdd ('${name}', @_LID)`,
    (error, rows, fields) => {
      if (error) {
        res.json({ message: `cant be saved to the database` });
      } else {
        res.json(rows[0][0]);
      }
    }
  );
});

happy to help, firealone. looks like you have a good path forward and this is actually a relevant example of where a stored proc. is a beneficial strategy since you want to create a record, get the resulting id, then query for a full record set against that id. this will provide some efficiencies from a time and connections perspective - and clearly saves you lines of code as well. best of luck and let me know if you have additional questions. — dusthaines, Jun 12 '19 at 01:57

score 0 · Answer 2 · answered Jun 11 '19 at 11:58

Because you're using a Stored Proc. the insertId feature of the mysqljs module is not available. By the design of your query you have essentially offloaded that task to your stored procedure which is why you're seeing the result you expect in the returned rows element with no additional scope needed.

While there's nothing wrong with this approach, using a stored proc for such a simple query might be over complicating things and it limits what module features are available to you as compared to a straight INSERT query.

Consider this alternative where the insertId feature would be available to you:

apiconnection.query('INSERT INTO tbl1 SET ?', {name: name}, function (error, results, fields) {
  if (error) throw error;
  console.log(results.insertId);
});

This would also give you access to other elements of the results object such as affected rows or changed rows:

https://github.com/mysqljs/mysql#getting-the-number-of-affected-rows

On an unrelated note, proceed with caution using const id = … the way you are where it is defined inside a function that producers ever changing results. Variables created by const are immutable. In this situation you might consider using let = or var = depending on where you need access to that data. It seems like you only need it in the next query so I would recommend let. You can read further on the topic here:

http://2ality.com/2015/02/es6-scoping.html

thank you dusthaines, the problem is I am only allowed to use stored procedures. the raw sql in the second call will also be replaced. The actual stored procedure is different, I am doing an end to end test before moving to the actual table and stored procedures. Considering these rules, I modified the POST call and the stored procedure. Posted below. — Firealem Erko, Jun 11 '19 at 19:46

insertId returning 0 after posting with mysqljs and stored procedure

2 Answers2