MYSQL Cursor Implementation Question

Question

I am using following Stored procedure in which the following cursor code is written ( i am using PHPmyadmin to execute the code

create procedure cursorproc(OUT p_out DECIMAL(10,2))
begin

   declare l_salary, l_total DECIMAL(10,2);

   declare cur_1 cursor for select line_distance from elements;

   open cur_1;

   set l_total = 0;

   loop

      fetch cur_1 into l_salary;


         set l_total = l_total + l_salary;

  end loop;

   close cur_1;

   set p_out = l_total;

end;

And the table schema:

CREATE TABLE IF NOT EXISTS `elements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `distance` int(11) NOT NULL,
  `repeat` int(2) NOT NULL,
  `interval` varchar(11) NOT NULL,
  `eta` varchar(11) NOT NULL,
  `best` varchar(11) NOT NULL,
  `line_distance` int(5) NOT NULL,
  `line_time` varchar(11) NOT NULL,
  `intensity` varchar(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `best_time_event` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=215 ;

I am getting the following error:

ERROR -  #1329 - No data - zero rows fetched, selected, or processed

What can be the problem ? please help

Frank Schmitt · Accepted Answer · 2011-08-09 06:28:50Z

1

I do not know much about MySQL, but the error message is pretty clear - you never check whether your fetch actually returns data / reaches EOF, so you try to fetch even if your query returns no more data. Search the MySQL documentation for info about "CONTINUE HANDLER".

The more important question is: Why do you use a stored procedure at all for this? And why - if you have to - do you use an explicit loop where a simple

select sum(line_distance) from elements

should suffice?

answered Aug 9, 2011 at 6:28

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

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

3 Comments

TSCAmerica.com Over a year ago

Using stored procedure its because i dont know how to call a cursor from a PHP page ? Stored procedure will atleast return some data thats why i have used stored procedure.

Devart Over a year ago

@user580950 - Have you tried suggested query without opening cursor? Just run it.

TSCAmerica.com Over a year ago

Without cursor - normal store procedure runs - With cursor it throws error

Kevin Burton · Accepted Answer · 2011-08-09 07:56:09Z

0

you could catch the error DECLARE CONTINUE HANDLER FOR NOT FOUND

see: http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html

or you can count the number of rows the sql will select and choose not to read them if there are none

answered Aug 9, 2011 at 7:56

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Collectives™ on Stack Overflow

MYSQL Cursor Implementation Question

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related