MySQL script not working

[ayu]

I have been trying for about an hour now to fill a database with LOADS of information for an experiment of mine.
The experiment was supposed to take about 5 minutes so wasn't really expecting this ^^

Anyway, I installed mysql server on my desktop, tried to create a procedure that bombs a table with rows.

Code: Select all

CREATE PROCEDURE dowhile()
BEGIN
  SET @v1 = 5000000;
  WHILE v1 > 0 DO
    INSERT INTO test values(10000, 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest');
    SET v1 = v1 - 1;
  END WHILE;
END;

I receive this though

Code: Select all

mysql> BEGIN
    ->   WHILE 1 > 0 DO
    ->     INSERT INTO test values(10000, 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest');
ERROR 1064 (42000): 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 'WHILE 1 > 0 DO
    INSERT INTO test values(10000, 'thisisatest', 'thisisatest', ' at line 2
mysql>   END WHILE;
ERROR 1064 (42000): 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 'END WHILE' at line 1
mysql> END;
ERROR 1064 (42000): 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 'END' at line 1
mysql> 

This is where the example was stolen from

Code: Select all

http://dev.mysql.com/doc/refman/5.5/en/while-statement.html

Now, I admit that I haven't written procedures in MySQL since MySQL 5.0, but my thinking was "I couldn't have gotten that bad, right?", but apparently I have.

Can anyone enlighten me?

[maboroshi]

This line maybe

Code: Select all

INSERT INTO test values(10000, 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest');

should read

Code: Select all

INSERT INTO test (col1, col2, col3, col4, etc) values(10000, 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest');

Not entirely sure been a while since I used MySQL and even then wasn't very good at it

[pseudo_opcode]

Mab: what you are saying is just another way to do it, when you have to input values only in certain columns, so without mentioning the column names, it would not match column count and fail... but that's not the case here.


Catz, yout put

Code: Select all

->   WHILE 1 > 0 DO

didnt you mean v1? it seems like an infinite loop

also where did you declare variable v1? is it global?

How exactly are you trying to run it? typing it on command line, line by line?

[ayu]

Mab: what you are saying is just another way to do it, when you have to input values only in certain columns, so without mentioning the column names, it would not match column count and fail... but that's not the case here.


Catz, yout put

Code: Select all

->   WHILE 1 > 0 DO

didnt you mean v1? it seems like an infinite loop

also where did you declare variable v1? is it global?

How exactly are you trying to run it? typing it on command line, line by line?

Yeah sorry I posted two different versions of the code.

But I solved it with just writing it in PHP instead

Code: Select all

<?php
$con = mysql_connect("localhost","root","");

if (!$con)
        die('Could not connect: ' . mysql_error());

mysql_select_db("test", $con);

for($i = 0; $i < 500000; ++$i)
{
        mysql_query("INSERT INTO test values(10000, 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest', 'thisisatest')");
        echo "$i\n";
}

mysql_close($con);
?>

Still interested in why this doesn't work though.

Yes I write it line by line on the command line.
The first code example is the correct one, ignore the second one where I am trying to run it.

[pseudo_opcode]

Yes I write it line by line on the command line.

There's your problem,

on mysql shell, after you put ';' at the end of the line, mysql thinks that the command is complete and it tries to interpret it, it fails and that is why it gives you this error.

You need to change delimeter first

Code: Select all

mysql > delimeter !!
mysql > CREATE PROCEDURE dowhile()
        -> .....
        -> .....
        ->END!!
mysql > delimeter ;
mysql > CALL dowhile();

[ayu]

There's your problem,

on mysql shell, after you put ';' at the end of the line, mysql thinks that the command is complete and it tries to interpret it, it fails and that is why it gives you this error.

aaah!

Yes I think you are right
I will try this when I get back from work today ^^

Thanks!