MySQL Fetch Not Returning Values

Question

I'm attempting to use MySQL cursors for the first time and hitting a wall. Below code snippet should be returning actual (non-midnight) time values for tStart and tEnd, however all I'm getting is '00:00:00'. StartTime and EndTime fields in table are type TIME. Running just the select statement returns the expected values. Num_rows is indicating 1 row returned, which is also expected. Can anyone help with what I'm doing wrong?

DECLARE tStart TIME;
DECLARE tEnd TIME;
DECLARE num_rows INT DEFAULT 0;

DECLARE curs_1 CURSOR FOR SELECT MIN(asd.StartTime), MAX(asd.EndTime) FROM database.table1 asd WHERE (((asd.varid)='1006') AND ((asd.neededdate)='2016-06-22') AND ((asd.neededint)=3));

OPEN curs_1;
SELECT FOUND_ROWS() INTO num_rows;
FETCH curs_1 INTO tStart, tEnd;

Below code can generate test data:

CREATE TABLE `table1` (`StartTime` TIME DEFAULT NULL,`EndTime` TIME DEFAULT NULL, `varid` VARCHAR(10) DEFAULT NULL, `neededdate` DATE DEFAULT NULL, `neededint` INT(11) DEFAULT NULL) ENGINE=INNODB;

INSERT INTO `table1` (`StartTime`,`EndTime`,`varid`,`neededdate`,`neededint`) VALUES ('09:00:00','18:00:00','1006','2016-06-22',3);
INSERT INTO `table1` (`StartTime`,`EndTime`,`varid`,`neededdate`,`neededint`) VALUES ('09:00:00','18:00:00','1007','2016-06-21',3);
INSERT INTO `table1` (`StartTime`,`EndTime`,`varid`,`neededdate`,`neededint`) VALUES ('09:00:00','18:00:00','1008','2016-06-20',3);
INSERT INTO `table1` (`StartTime`,`EndTime`,`varid`,`neededdate`,`neededint`) VALUES ('08:00:00','17:00:00','1006','2016-06-21',2);
INSERT INTO `table1` (`StartTime`,`EndTime`,`varid`,`neededdate`,`neededint`) VALUES ('11:00:00','20:00:00','1006','2016-06-22',1);

Do you have minimum sample data to test your query ?

Ivan Cachicatari
– Ivan Cachicatari

2016-06-23 15:19:23 +00:00
Commented Jun 23, 2016 at 15:19 — Ivan Cachicatari
– Ivan Cachicatari, Commented Jun 23, 2016 at 15:19

Drew · Accepted Answer · 2016-06-24 12:03:58Z

1

Your cursor is indeed housing a single row.

You are using FOUND_ROWS() which can work fine without the use of SQL_CALC_FOUND_ROWS but typically uses it. But in your case you will only ever get back 1 row due to your aggregate (sum). So it almost makes no sense. There is nothing to "cursor" over like a tricky set of data.

I see no sense in using a CURSOR. In fact I recommend never ever using a cursor. Performance is awful for a highly tuned SQL Server that has relations and indexes and performance strategies to deliver results. Cursors as like throwing low-performance mechanisms into an otherwise deliberate stream-lines robust engine. Like opening the carburetor on a Corvette and throwing in a handful of sand. They are something new developers to SQL think of as they procedurally try to wrap their heads around their problem. I recommend you never use it.

The following is the Manual Page for Cursors. You need a LOOP construct to use them because you are row by row on your own terms moving through the data in that cursor. It takes quite a bit of patience to ever get them right. Try to use it sparingly, like maybe 1 time per app at most and with the trickiest of code until you solve it otherwise. Performance is really dreadful, trust me.

What you could do is simply the following to get those values into variables.

SELECT MIN(asd.StartTime) , MAX(asd.EndTime) into @myMin,@myMax
FROM table1 asd 
WHERE (((asd.varid)='1006') AND ((asd.neededdate)='2016-06-22') AND ((asd.neededint)=3));

select @myMin,@myMax; -- display them if you want

Good luck, and did I mention yet not to use Cursors? :p

edited Jun 24, 2016 at 12:03

answered Jun 24, 2016 at 11:56

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jake Over a year ago

Thank you. Right now I'm actually just converting a VBA script to the stored procedure, which is why I'm using the cursors (quickly replace recordsets). Step 2, once it's functional, is to go back and streamline (eliminating the cursors where possible) as you've suggested.

Jake Over a year ago

Interestingly enough, even using the select statement I run into the same issue of the variable not updating if it is a local (DECLARE) variable - it only seems to work for the session (@) variable.

Drew Over a year ago

I avoid local variables as much as possible, that is why I used user variables. See stackoverflow.com/a/37904084 ... not that that answer is stellar by the way. I would die to read a 1 pager somewhere discussing the pros and cons of each. They make programming ridiculously over complex for new developers.

Collectives™ on Stack Overflow

MySQL Fetch Not Returning Values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related