0

I am trying to replicate this raiserle sample code

But I am having this error:

{
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCK TABLES `individual` WRITE INSERT INTO individual (email, last_name, firs...' at line 1",   
  sql: "BEGIN LOCK TABLES `individual` WRITE INSERT INTO individual (email, last_name, first_name, phone_number, auth_string, suppress_email_sending, is_participant, org_id, suborgs, roles, initiation_date, created_at, modified_at, created_by, modified_by) VALUES ('king23garoo@sample.com', 'Garoo', 'King', NULL,  '111111111100000000000000000111111111111111111110000001000', 0, 1, 1, '1', '6', '2022-06-01T10:39', NOW(), NOW(), '1', '1')"
}

This is my code:

    db.query(
      "BEGIN LOCK TABLES `individual` WRITE INSERT INTO individual 
(email, last_name, first_name, phone_number, auth_string, 
suppress_email_sending, is_participant, org_id, suborgs, 
roles, initiation_date, created_at, modified_at, created_by, modified_by) 
VALUES (?, ?, ?, ?,  ?, ?, ?, ?, '?', '?', ?, NOW(), NOW(), ?, ?)",
      [
        data.email,
        data.last_name,
        data.first_name,
        data.phone_number,
        data.auth_string,
        data.suppress_email_sending,
        data.is_participant,
        data.org_id,
        data.suborgs,
        data.roles,
        data.initiation_date,
        data.created_by,
        data.modified_by,
      ],
      (err) => {
        console.log(err)
        if (err) return result(err, null)
        db.query("SET @last_ind_id = LAST_INSERT_ID()");
        "UNLOCK TABLES"
        db.query(
          "INSERT INTO ind_group 
(ind_id, program_id, iteration_id, org_id, suborg_id, 
created_at, modified_at, created_by, modified_by) 
VALUES (@last_ind_id, ?, ?, ?, ?, NOW(), NOW(), ?, ?) END",
          [
            data.indGroup.program_id,
            data.indGroup.iteration_id,
            data.org_id,
            data.suborgs,
            data.created_by,
            data.modified_by,
          ],
          (err, results) => {
            console.log(results)
            console.log(err)
            if (err) return result(err, null)
            result(null, results)
        });
      })

I have a unique constraint on the email, What I am trying to achieve is to put the LAST_INSERT_ID() from the individual table, into the ind_group table. Is this a good approach? or is there another way to get the last inserted id from table1 to be inserted in table2

  • MySQL/MariaDB does not support anonymous code blocks, BEGIN-END is allowed in compound statements only. – Akina Jun 01 '22 at 11:50

0 Answers0