CODEWITHSUNDEEP
mysqlcursor
Jake
Jake

Reputation: 913

MySQL Fetch Not Returning Values

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);

Upvotes: 0

Views: 755

Answers (1)

Drew
Drew

Reputation: 24949

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

Upvotes: 1

Related Questions