StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL query browser procedure error code -1
text
Body
copied!<p>I'm having a rather strange problem with MySQL. Trying to create a procedure to update some fields in the database (the code is below).</p> <p>The problem is with the line that is currently commented. It seems that if no SELECT statements get executed during the procedure MySQL query browser will return an error code of "-1, error executing SQL query".</p> <p>I tried the same thing in HeidiSQL and the error was "cannot return result set". So I suppose the question is do I always have to select something in the procedure, or is there some other thing I missed.</p> <p>The query works fine when the comment is removed.</p> <pre><code>DELIMITER / DROP PROCEDURE IF EXISTS updateFavourites / CREATE PROCEDURE updateFavourites(quota INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE artist_id,releases INT; DECLARE c_artist Cursor FOR SELECT Artist.id_number,COUNT(Artist.id_number) FROM Artist JOIN CD ON CD.is_fronted_by = Artist.id_number GROUP BY Artist.id_number; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; IF quota > 0 THEN OPEN c_artist; REPEAT FETCH c_artist INTO artist_id,releases; IF NOT done THEN IF releases >= quota THEN UPDATE CD SET CD.rating='favourite' WHERE CD.is_fronted_by = artist_id; END IF; END IF; UNTIL done END REPEAT; CLOSE c_artist; -- SELECT 'Great success'; ELSE SELECT CONCAT('\'quota\' must be greater than 0.',' Got (',quota,')'); END IF; END / DELIMITER ; </code></pre> <p>Here's the sql to create the tables and some data:</p> <pre><code>DROP TABLE IF EXISTS CD; DROP TABLE IF EXISTS Artist; CREATE TABLE Artist ( id_number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), ); CREATE TABLE CD ( catalog_no INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, is_fronted_by INT UNSIGNED, rating ENUM ('favourite','top draw','good','so-so','poor','rubbish'), CONSTRAINT fk_CD_Artist FOREIGN KEY (is_fronted_by) REFERENCES Artist(id_number) ON UPDATE CASCADE ); INSERT INTO Artist VALUES(11,'Artist 1'); INSERT INTO Artist VALUES(10,'Artist 2'); INSERT INTO CD VALUES (7,11, 'top draw'); INSERT INTO CD VALUES (650,11,'good'); INSERT INTO CD VALUES (651,11,'good'); INSERT INTO CD VALUES (11,10,'favourite'); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload