asked on

MySQL compound statement problem

I am a first time user for MySQL compound statements.

I figured out that this shoud be the one I need

BEGIN
DECLARE iDone INT DEFAULT 0;
DECLARE sUsr VARCHAR(50);
DECLARE iMax INT(10);
DECLARE c_Upd CURSOR FOR SELECT szUser, MAX(TotalPages) FROM test GROUP BY szUser;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET iDone = 1;

OPEN c_Upd;

REPEAT
FETCH c_Upd INTO sUSR, iMax;
IF NOT done THEN
UPDATE Test SET MaxPages = iMax WHERE szUser = sUSR;
END IF;
UNTIL iDone END REPEAT;

CLOSE c_Upd;
END

but whatever I try to change on de DECLARE line I always get

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 'DECLA
RE iDone INT DEFAULT 0;
at line 2

I have tried several things even the simplest one

BEGIN
DECLARE iDone INT;
END

will not work.

I am using Server version: 5.1.30-community MySQL Community Server (GPL).

What do I do wrong?

hernst42

Where do you run that sql? from the mysqlclient? Then try:
delimiter //
<your strored procedure code>
delimiter;

Marc_Engrie

ASKER

Tried that as well but still get the same error :-(

mysql> DELIMITER //
mysql> BEGIN
-> DECLARE iDone INT DEFAULT 0;
-> 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 'DECLA
RE iDone INT DEFAULT 0;
END' at line 2
mysql>

ASKER CERTIFIED SOLUTION

hernst42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

khufford19

I'm just curious, and I know it's kinda a stupid question, but are you trying to setup a stored procedure, or are you just trying to execute this code as a standard query. If it's the former, then you may need to start with the CREATE statement (see below), as the procedure probably hasn't yet been defined. You can then use the CALL statement to actually execute the operation.

If you want to get fancy with it and return some values from or pass some values to the operation, you can definitely do that, but I'd suggest taking another look at the details available in the MySQL Reference Manual
http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

mysql> delimiter //
mysql> CREATE PROCEDURE UserPageCount
    -> BEGIN
    ->     DECLARE iDone INT DEFAULT 0;
    ->     DECLARE sUsr VARCHAR(50);
    ->     DECLARE iMax INT(10);
    ->     DECLARE c_Upd CURSOR FOR SELECT szUser, MAX(TotalPages) FROM test GROUP BY szUser;
    ->     DECLARE CONTINUE HANDLER FOR NOT FOUND SET iDone = 1;
    ->     OPEN c_Upd;
    ->     REPEAT
    ->         FETCH c_Upd INTO sUSR, iMax;
    ->         IF NOT done THEN
    ->             UPDATE Test SET MaxPages = iMax WHERE szUser = sUSR;
    ->         END IF;
    ->     UNTIL iDone END REPEAT;
    ->     CLOSE c_Upd;
    -> END;
    -> //
 
mysql> delimeter ;
 
mysql> CALL UserPageCount

Open in new window

racek

you don't need CURSOR !!! and UPDATE you table faster with one statment :-)


UPDATE Test, (SELECT szUser sUSR, MAX(TotalPages) iMax  FROM test GROUP BY szUser ) t2
 SET MaxPages = t2.iMax
 WHERE szUser = t2.sUSR;

Open in new window

Marc_Engrie

ASKER

Hello,

in the meanwhile I found out that using MySQL Administrator, one can create stored procedures there. The exact same statements (inluding spaces etc) do work here. I have no clue why but, hey, it's working and that's all it takes for me right now.

Of course it would be nice to know why the stuff does not work in de command line client but that for later rightnow. No time to waste.

BTW: thx for the enhanced UPDATE statement but that was not the real problem/question.

SOLUTION

racek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marc_Engrie

ASKER

Query works!