Mysql while loop is not working (Returning a 1064)

Question

I'm trying to do multiple inserts into the database. I need to use a cycle due to the fact that, in Node.js, I'm defining how many rows I should add. Therefore, I can't use the Values(...), (...) syntax on the Insert Into.

When I use this cycle however, I get an error 1064. What can I do?

DELIMITER //
DROP PROCEDURE IF EXISTS addReservations;
CREATE PROCEDURE addReservations()

BEGIN
  DECLARE v1 INT DEFAULT 0;
  WHILE v1 < 5 DO
    INSERT INTO `Reservations` (`Timeslot_idTimeslot`, `Exam_type_idExam_type`, `Temp_Student_idStudent`, `Lock_expiration_date`)
    VALUES (1, 1, 1, '2019-07-02 00:00:00');
    SET v1 = v1 + 1;
  END WHILE;
END;
DELIMITER;

The error I'm receiving is:

Error Code: 1064 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 'DELIMITER' at line 1

Answer 1

Try adjusting to delimiter [;;] as below. If it does not work, try delimiter [$$].

DROP PROCEDURE IF EXISTS addReservations;
DELIMITER ;;
CREATE PROCEDURE addReservations()
BEGIN
  DECLARE v1 INT DEFAULT 0;
  WHILE v1 < 5 DO
    INSERT INTO `Reservations` (`Timeslot_idTimeslot`, `Exam_type_idExam_type`,    `Temp_Student_idStudent`, `Lock_expiration_date`)
    VALUES (1, 1, 1, '2019-07-02 00:00:00');
    SET v1 = v1 + 1;
  END WHILE;
END;;
DELIMITER;

If it still does not work , I suggest you strip down your PROCEDURE to a minimum of code in order to secure that it is in fact the delimiter that is the root cause of your problem, e.g.:

DROP PROCEDURE IF EXISTS addReservations;
DELIMITER ;;
CREATE PROCEDURE addReservations()
BEGIN
  SELECT * FROM ["comment: add your table name here"];
END
;;
DELIMITER ;

Further on, if you expect your stored procedure to actually execute, you need to call it, by using the command [CALL]. Note that the call should be done outside of the PROCEDURE. Only then you will see the query to be executed, thus changes to be visible in your db.