• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3324
  • Last Modified:

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
0
Marius0188
Asked:
Marius0188
  • 2
  • 2
1 Solution
 
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*
0
 
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!
:)
0
 
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.
0
 
Marius0188Author Commented:
Ok actually plain simple - or maybe you just a good teacher :)
Thanks for the help!

You earned the points....
Keep well.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now