Solved

MySQL compound statement problem

Posted on 2009-04-06
8
1,001 Views
Last Modified: 2012-05-06
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?


0
Comment
Question by:Marc_Engrie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 48

Expert Comment

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

Author Comment

by:Marc_Engrie
ID: 24076168
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>


0
 
LVL 48

Accepted Solution

by:
hernst42 earned 125 total points
ID: 24076282
You need to create a stored procedure or function first and then call that function. AFAIK you can't define standalone code like that and execute it.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Expert Comment

by:khufford19
ID: 24082789
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

0
 
LVL 14

Expert Comment

by:racek
ID: 24084387
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

0
 

Author Comment

by:Marc_Engrie
ID: 24084437
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.
0
 
LVL 14

Assisted Solution

by:racek
racek earned 125 total points
ID: 24084506
have you test my query - withou stored procedure and without CURSOR ???

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

Author Comment

by:Marc_Engrie
ID: 24085595
Query works!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question