Bug #16305 Create procedure

• Submitted: 9 Jan 2006 16:35
• Modified: 9 Jan 2006 20:30
• Reporter: Diego Burbano
• Status: Can't repeat
• Category: MySQL Query Browser
• Severity: S1 (Critical)
• Version: 1.1.18
• OS: Windows (Windows xp)
• CPU Architecture: Any

[9 Jan 2006 16:35] Diego Burbano

Description:
Friends:
I try to ejecute a store procedure in Mysql Query Browser but it return :
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
declare ch1 character(30) at line 1
unknown variable i... and other errors.
I ejecute the same store procedure in EMS sql manager 2005 for mysql trial version and the script is correct.
Please help me.
Diego Burbano

How to repeat:
CREATE PROCEDURE `spCreaCliente`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT 'sp crea datos en la tabla cleintes'
BEGIN
DECLARE i integer;
DECLARE ch1 CHARACTER(30);
DECLARE ch2 CHARACTER(30);

delete from salud.clientes;
set i = 1;
WHILE i <= 5000 DO
set ch1 = "[e-mail]";
set ch2 = "2439481";
insert into salud.clientes values (i,"sierra","av 1 de diciembre",ch2,ch1,"T","0501890255");
SET i = i + 1;
END WHILE;
select * from salud.clientes;
END;

Suggested fix:
I dont know

[9 Jan 2006 17:17] MySQL Verification Team

I got an error on QB running the procedure created as below using the mysql
client:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database salud;
Query OK, 1 row affected (0.00 sec)

mysql> use salud;
Database changed

mysql> create table clientes (col1 int,
    -> col2 char(25), col3 char(50),
    -> col4 char(30), col5 char(30), col6 char(1),
    -> col7 char(20));
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE `spCreaCliente`()
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT 'sp crea datos en la tabla cleintes'
    -> BEGIN
    -> DECLARE i integer;
    -> DECLARE ch1 CHARACTER(30);
    -> DECLARE ch2 CHARACTER(30);
    ->
    -> delete from salud.clientes;
    -> set i = 1;
    -> WHILE i <= 10 DO
    -> set ch1 = "[e-mail]";
    -> set ch2 = "2439481";
    -> insert into salud.clientes values (i,"sierra","av 1 de diciembre",ch2,ch1,"T","0501890255");
    -> SET i = i + 1;
    -> END WHILE;
    -> select * from salud.clientes;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call spCreaCliente();
+------+--------+-------------------+---------+-----------------------+------+------------+
| col1 | col2   | col3              | col4    | col5                  | col6 | col7       |
+------+--------+-------------------+---------+-----------------------+------+------------+
|    1 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    2 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    3 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    4 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    5 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    6 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    7 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    8 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|    9 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
|   10 | sierra | av 1 de diciembre | 2439481 | [e-mail] | T    | 0501890255 |
+------+--------+-------------------+---------+-----------------------+------+------------+
10 rows in set (0.03 sec)

[9 Jan 2006 17:32] MySQL Verification Team

Sorry in my last post I had not noticed I did a wrong syntax with the
procedure name, using the correct name I am able to run the procedure
like on mysql client.

[9 Jan 2006 20:30] Diego Burbano

importat: introduce delimiter ....
correct store procedure:
delimiter //
CREATE PROCEDURE spCreaCliente()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT 'sp crea datos en la tabla cleintes'
BEGIN
DECLARE i intEGER;
DECLARE ch1 CHARACTER(30);
DECLARE ch2 CHARACTER(30);
delete from salud.clientes;
set i = 1;
WHILE i <= 500 DO
set ch1 = "[e-mail]";
set ch2 = "2439481";
insert into salud.clientes values (i,"sierra","av 1 de diciembre",ch2,ch1,"T","0501890255");
SET i = i + 1;
END WHILE;
select * from salud.clientes;
END//