MariaDB while loops at the CLI

Development Code
1

I feel kinda silly here. I realize that I’ve never made a while loop in MySQL or MariaDB. Now that I’m actually trying to do it, it’s proving to be quite the task. My first set of problems that I ran into were actually a result of running HeidiSQL in WINE. So much for, “It runs in WINE, so yeah it’s got Linux support.” I should see if I can make a proper Linux port of HeidiSQL sometime, but that’s another project.

So, I cannot use HeidiSQL for this, as it is now untrustworthy. So I’m down at the CLI client. Pretty simple. Just open up the editor and…

SET @myvar = 1
WHILE @myvar <= 10 DO
INSERT INTO mytable (list, a, few, columns)
VALUES (“values”, “for”, “said”, “columns”);
SET @myvar = @myvar + 1;
END WHILE

There is nice spacing and such there. The problem I run into is that that MariaDB is treating the WHILE line, the INSERT, and the VALUES line as one line. Normally, I’d expect this out of SQL, as it stops reading a line at the semicolon. But both MySQL and MariaDB official docs swear up and down that I don’t need to put anything at the end of the WHILE line except DO. And yet I get the error below.

ERROR 1064 (42000): 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 ‘WHILE @myvar <= 10 DO
INSERT INTO mytable (list, a, fe’ at line 1

I feel like I’m missing something really silly here.

2

I asked over on Ars Technica. Someone found this little nugget.

https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html

Which tells us that such statements need to be in a stored procedure. Suddenly, the fact that MariaDB’s documentation has their while loop example in a stored procedure makes sense. I thought they were just being fancy with it.

3

Semicolon?

1 Like
4

Ha! Yeah, that too. I must have missed that when typing up the example. :slight_smile:

5

My best guess:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE myvar INT DEFAULT 1;
  WHILE myvar <= 10 DO
    INSERT INTO mytable (list, a, few, columns)
      VALUES (“values”, “for”, “said”, “columns”);
    SET myvar = myvar + 1;
  END WHILE;
END
4 Likes
6

I use my semicolons in SQL always. People poke fun at me but it helps. When I fixing their stuff and it only took one of two semicolons, they immediately go silent. If you code in any standard languages, it is a good habit to have unless you write python of course.

1 Like