Immediately grab auto-generated field in mySql for use

Question

During this fetch, it creates a new record, and auto increments a field called "employee_id" in mySql table. That part works fine.

But now what I want to add to this is, to immediately, within this same function if possible, setState with that auto generated value. Is this possible?

This is my function in it's current state. The line that says "this.setState({selectedEmployee..." breaks it currently. What I want to do is set the selectedEmployee state, so that I can then use it in that 'createQuestions' function.

createInterview() {
    fetch(API_URL + `/interview/create`, {
      method: "PUT",
      body: JSON.stringify({
        employee: this.state.employee,
        employment_level: this.state.employment_level,
        audit_id: this.props.auditId,
      }),
      headers: { "Content-Type": "application/json" },
    })
      .then((res) => {
        if (!res.ok) {
          throw new Error();
        }
        return res.json();
      })
      .then((data) => console.log(data))
      .then((data) => this.setState({ selectedEmployeeId: data.employee_id }))
      .catch((err) => console.log(err))
      .then(() => this.createQuestions())
      .then(() => this.getAllInterviews());

    this.setState({ showHide: false });
  }

This is my API call:

app.put("/interview/create", function (req, res) {
    console.log("It is getting to the route");
    const employee = req.body.employee;
    const employment_level = req.body.employment_level;
    const audit_id = req.body.audit_id;
    console.log(`employee: ${employee}`);
    console.log(`employment_level: ${employment_level}`);
    console.log(`audit_id: ${ audit_id }`);
    connection.getConnection(function (err, connection) {
        connection.query(
        `INSERT INTO audit_interview (employee, employment_level, audit_id)
            VALUES (?, ?, ?)`,
            [
                employee,
                employment_level,
                audit_id
            ],
        function (error, results, fields) {
            if (error) throw error;
            res.json(results);
            console.log(`Interview has been created`);
        }
    );
    connection.release();
  });
});

score 0 · Accepted Answer · answered Sep 24 '20 at 01:12

Yes, you can get this value of LAST_INSERT_ID() back in your nodejs program. In your completion callback, add a reference to results.insertId, maybe something like this. It's an attribute of the results item passed to that callback.

        function (error, results, fields) {
            if (error) throw error;
            const insertId = results.insertId;
            res.json(results);
            console.log(`Interview has been created`);
        }

And, unlike a query like this,

SELECT MAX(id) FROM table; /* don't do this, it's wrong! */

using that results.insertId value is race-condition-proof even with large scale concurrency.

Immediately grab auto-generated field in mySql for use

1 Answers1