MySQL - HeidiSQL

Good day,

I am really frustrated with HeidiSQL.
I create a stored procedure in HeidiSQL and it is working perfeck.

But when I copy the Create Code for the procedure in HeidiSQL and then paste it in a query window (renaming the stored procedure obviously) it wont run the query.

I have noticed many times that MySQL query errors points you to the line number indicating where  the offending code is but this "line number" hardly seem to be accurate.

In my instance - my sql error message points me to line 4 near '' but not on the line before or after line 4 do I have any quotes etc....

I need to test my query to see if it will create the store procedure as I want to run it on my phpMyAdmin which does not have interface to created stored procedures (or I can't see that so please advise on this).

What is wrong with my query?
I am attaching screenshot of my error and will attach code as well.

Kind regards :)
CREATE PROCEDURE usp_Explode_Images2 (IN droptable INT, IN seperator VARCHAR(50))
body:
BEGIN
DECLARE guesthouseid_val BIGINT;
DECLARE title_val VARCHAR(50);
DECLARE desc_val VARCHAR(255);

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;

DECLARE images_cur CURSOR FOR select guesthouseid, title, description from tblguesthouseimages where title <> ''; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

IF droptable = 1 THEN DROP TABLE IF EXISTS tblwords;
END IF;
CREATE TABLE IF NOT EXISTS tblwords(
`wordid` int unsigned NOT NULL auto_increment,
`guesthouseid` BIGINT NOT NULL,
`word` VARCHAR(255) NOT NULL,
PRIMARY KEY  (`wordid`),
UNIQUE INDEX `idx_word` (`word`)
) ENGINE=MyISAM;

IF seperator IS NULL THEN LEAVE body; END IF;

OPEN images_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP FETCH images_cur INTO guesthouseid_val, title_val, desc_val;
IF no_more_rows THEN
CLOSE images_cur;
LEAVE the_loop;
END IF;

# Title
SET @id = guesthouseid_val;
SET @title_text = title_val;
SET @desc_text = desc_val;
SET @iSeparLen = LENGTH( seperator );

create_layers:
WHILE @title_text != '' DO
# Get the next value
SET @sHead = SUBSTRING_INDEX(@title_text, seperator, 1);
SET @title_text = SUBSTRING( @title_text, LENGTH(@sHead) + 1 + @iSeparLen );
INSERT IGNORE INTO tblwords SET word = @sHead, guesthouseid = @id;
END WHILE;

#Description
SET @desc_text = desc_val;

create_layers:
WHILE @desc_text != '' DO
# Get the next value
SET @sHead = SUBSTRING_INDEX(@desc_text, seperator, 1);
SET @desc_text = SUBSTRING( @desc_text, LENGTH(@sHead) + 1 + @iSeparLen );
INSERT IGNORE INTO tblwords SET word = @sHead, guesthouseid = @id;
END WHILE;
END LOOP the_loop;
delete from tblwords where word in (select word from tblwords_ignore);
END;

Open in new window

mysql-stored-procedure.png
Marius0188Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hi.

I am not sure of all the pains you are seeing with HeidiSQL as I do not use it; however, I will defend it a bit as a guess to what is going on. Development / management tools like HeidiSQL usually handle some of the dirty work for you. In this case, specifically in MySQL when defining procedures that have within them the ; (delimits the end of statement), you have to change the delimiter so that SQL knows not to stop processing when it hits a ; because that is meant for the final code and not an immediate processing instruction.

In other words, when creating this procedure, I would expect to do this:
DELIMITER //

CREATE PROCEDURE usp_Explode_Images2 (IN droptable INT, IN seperator VARCHAR(50))
BEGIN
DECLARE guesthouseid_val BIGINT;
DECLARE title_val VARCHAR(50);
DECLARE desc_val VARCHAR(255);
-- ...
END //

DELIMITER ; 

Open in new window


See if that is the culprit here. *smile* If so, maybe you don't have to hate HeidiSQL as much. *laughing*
Marius0188Author Commented:
Thanks for the reply.
I added the line:
DELIMITER //

at the beginning only and then it worked.
Won't work with the DELIMITER ; at the end.

What is this DELIMITER?
Why is it needed?

Thanks again!
:)
Kevin CrossChief Technology OfficerCommented:
"In this case, specifically in MySQL when defining procedures that have within them the ; (delimits the end of statement), you have to change the delimiter so that SQL knows not to stop processing when it hits a ; because that is meant for the final code and not an immediate processing instruction." < DELIMITER is the keyword you use to change the delimiter temporarily as eluded to in the highlighted sentence. IT is probably more obvious if you were typing this at the command prompt. When in the mysql prompt, the semi-colon tells the shell you are done typing your SQL command and so it tries to run it.

So without changing the delimiter, you are trying to execute:
CREATE PROCEDURE usp_Explode_Images2 (IN droptable INT, IN seperator VARCHAR(50))
BEGIN
DECLARE guesthouseid_val BIGINT;

Open in new window


If you change the delimiter to not be semi-colon (;), then when you type line 3, the shell does not think you are done. It thus allows you to complete the statement to make it a valid procedure body and the new delimiter -- // in this case -- is what tells the mysql shell to process the previous syntax and your procedure gets created successfully. The second DELIMITER instruction is supposed to change it back to semi-colon. The DELIMITER change is limited to session scope if I am not mistaken, so the second failing should not hurt as the next session should start back out with the correct delimiter. If it does not, just try running DELIMITER ; again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marius0188Author Commented:
Ok actually plain simple - or maybe you just a good teacher :)
Thanks for the help!

You earned the points....
Keep well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.