MySQL Syntax Error

Question

When executing:

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
     ROLLBACK;
     SELECT 1;
    END;

    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
     ROLLBACK;
     SELECT 1;
    END;

    -- delete all users in the main profile table that are in the MaineU18 by email address
    DELETE FROM ap_form_1 WHERE element_5 IN (SELECT email FROM MaineU18);

    -- delete all users from the MaineU18 table
    DELETE from MaineU18;

    COMMIT;
END;

I 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 'e1:
                DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                        ROLLBACK' at line 2

Any ideas? Thanks.

UPDATE 2:

I have tried putting the script into a PROCEDURE:

DELIMITER |
DROP PROCEDURE IF EXISTS temp_clapro|
CREATE PROCEDURE temp_clapro()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK;

    SET AUTOCOMMIT=0;

    -- delete all users in the main profile table that are in the MaineU18 by email address
    DELETE FROM ap_form_1 WHERE element_5 IN (SELECT email FROM MaineU18);

    -- delete all users from the MaineU18 table
    DELETE from MaineU18;

    COMMIT;
    SET AUTOCOMMIT=1;
END
|
DELIMITER ;
CALL temp_clapro();

I am still having issues:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (2.40 sec)

Query OK, 0 rows affected (2.40 sec)

Query OK, 0 rows affected (2.40 sec)

Query OK, 0 rows affected (2.40 sec)

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;
|
DELIMITER ;
CALL temp_clapro()' at line 1

UPDATE 3:

It seems that many of my problems are coming from the fact that I am running the script from a file using the "SOURCE" command. If I only have the DROP and CREATE commands in the file and run the DELIMITER and CALL commands outside the file, everything works without error.

Is there away to run this from a single script file?

Answer 1

You seem to be using BEGIN as the opening of a block of ad hoc statements, as one would do in Oracle.

MySQL doesn't support this. You can DECLARE only in the body of a stored procedure or stored function or trigger.

http://dev.mysql.com/doc/refman/5.1/en/declare.html:

DECLARE is allowed only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

http://dev.mysql.com/doc/refman/5.1/en/begin-end.html:

BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs.

---

Re your comments and updated question: I don't know why it's failing. I just tried it myself and it worked fine. I'm using MySQL 5.0.75 on my Macbook. What version of MySQL are you using?

Answer 2

you're using semicolons with the procedure for statement delimiters, so you have to change the delimiter in your client. see http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html