I'm not very experienced with MySQL or HeidiSQL so this might be pretty obvious but the problem I'm having is this:
I'm using the create new -> stored routine in HeidiSQL to create a new stored routine. This works fine. However, I was expecting it to be possible to copy the code shown in the "CREATE code" tab in the routine and paste it in an SQL file or into an empty query tab and run it from there. But when I do that I start getting syntax errors.
This is the code exactly copied from the CREATE code tab:
CREATE DEFINER=`user`@`%` PROCEDURE `add_rev`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN -- Rollback on error DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION; CREATE TABLE IF NOT EXISTS db_rev(revision INT NOT NULL UNIQUE); INSERT INTO db_rev VALUES (1); -- Add commands here COMMIT; END
The complaining starts at DECLARE EXIT HANDLER ... and if I remove that it jumps somewhere else.
To me, it seems like all of the code isn't read as one entity and that's what messes things up. But I don't know how to make HeidiSQL understand what I want it to do.