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

Unless the OP wants to [change the delimiter](https://stackoverflow.com/questions/1346637/what-does-delimiter-do-in-a-trigger). — showdev, Jun 06 '19 at 09:53

Toolbox · Accepted Answer · 2019-06-06T10:14:19.703

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.

edited Jun 06 '19 at 10:14

answered Jun 06 '19 at 09:47

Toolbox

2,333
12
26

Mysql Workbench is telling me that the last line (DELIMITER;) is not valid at that position. – Leonardo Viegas Jun 06 '19 at 09:53
The last line only reset the delimiter to the default [;]. – Toolbox Jun 06 '19 at 09:54
I updated the answer based on your question. Try to do something minimum that should update your db, like insert a row. Secure that you use [call] to execute the PROCEDURE. – Toolbox Jun 06 '19 at 10:04
It worked! Thank you. Is there a way to use a cycle without having to declare the procedure and call it? I just want to use the cycle and that's it... – Leonardo Viegas Jun 06 '19 at 10:06
Of course, you should see the STORED PROCEDURE as a way to store structures (such as as mysql queries) directly in the db. The benifit is that if you construct a webfront you will in the code add your STORED PROCEDURE which create the similarity of an API between your scripts and your db. But, to send in your query you do not need to use STORED PROCEDURES, just add your db query in a *.sql file and call it from your php file. – Toolbox Jun 06 '19 at 10:10
I suggest you take the last code in your comment and create a separate question, for better visibility of code structure. – Toolbox Jun 06 '19 at 10:33
Ok. Here is the question: https://stackoverflow.com/questions/56476502/mysql-create-a-while-loop-without-creating-a-procedure – Leonardo Viegas Jun 06 '19 at 11:43

Mysql while loop is not working (Returning a 1064)

1 Answers1