"code": "ER_PARSE_ERROR", "errno": 1064 on an SQL Valid Query in NodeJs

Question

"code": "ER_PARSE_ERROR", "errno": 1064, "sqlMessage": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @last_id_in_users = LAST_INSERT_ID(); INSERT INTO sale_targets (user_id, mon' at line 1", "sqlState": "42000", "index": 0,

NodeJs throw me this error on a valid query. It is valid for sure, because in phpmyadmin it works successfully. Please help me.

Or alternative solutions to use multiple inserts one after another, using LAST_INSERT_ID() like this.

BEGIN;
INSERT INTO users (email, first_name, last_name, password, role_id) values ("test@tester.com", "test", "test", "$2b$10$dGZ0NArlvpE7M3J4SEuW4eXrDLR4/w218mdftB0eNuJInV6BS7V2.", 3); 
SET @last_id_in_users = LAST_INSERT_ID(); 
INSERT INTO sale_targets (user_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES(LAST_INSERT_ID(), '0', '0','0','0','0','0','0'); 
INSERT INTO close_ratio (user_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES(@last_id_in_users,'0', '0','0','0','0','0','0'); 
INSERT INTO lead_targets (user_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES(@last_id_in_users,'0', '0','0','0','0','0','0'); 
COMMIT;

My node query connection:

    const q = `BEGIN; INSERT INTO users (email, first_name, last_name, password, role_id) 
values ("${rBody.email}", "${rBody.firstName}", "${rBody.lastName}", "${hash}", ${roleId}); 
SET @last_id_in_users = LAST_INSERT_ID(); 
INSERT INTO sale_targets (user_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday); 
VALUES(LAST_INSERT_ID(), '0', '0','0','0','0','0','0'); 
INSERT INTO close_ratio (user_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday) 
VALUES(@last_id_in_users,'0', '0','0','0','0','0','0'); 
INSERT INTO lead_targets (user_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES(@last_id_in_users,'0','0','0','0','0','0','0'); 
COMMIT`;
                    connection.query(q, (err, resp) => {
                        if (err) {
                            // Error inserting into users table
                            resolve({
                                success: false,
                                error: err
                            })
                        } else {
                            // User succesfully created
                            resolve({
                                success: true,
                                message: 'User succesfully created',
                                id: resp.insertId
                            })
                            console.log(resp)
                        }

                    })

you can check answer of [this](https://stackoverflow.com/questions/23266854/node-mysql-multiple-statements-in-one-query) post — Rajeev Bhat, Sep 12 '21 at 11:47

score 0 · Answer 1 · answered Mar 19 '20 at 07:06

0

SET @last_id_in_users = SELECT LAST_INSERT_ID();

answered Mar 19 '20 at 07:06

Siva Koteswara Rao

129
1
6

"code": "ER_PARSE_ERROR", "errno": 1064 on an SQL Valid Query in NodeJs

1 Answers1