MySQL Forums
Forum List  »  Stored Procedures

Not able to find problwm with this small stored proc
Posted by: Ankur Srivastava
Date: March 13, 2015 03:39AM

Hi everyone.
I am using mysql 5.6 and new to programming in mysql. Can anyone help me find the problem with the following code.First two cursors work fine, but as soon as I add the third one, I get into trouble.

Please help.

--------------------------------------------------------
BEGIN
declare route_done boolean;
declare bus_done boolean;
declare dev_done boolean;
declare routeId int;
declare busId varchar(20);
declare timest int;
declare lat double default -1;
declare lng double default -1;
declare lnglat varchar (100) default "NA";
declare onroute boolean ;
declare startPoint int;
declare endPoint int;
declare startgeom point;
declare endgeom point;
declare currgeom point;
declare routegeom linestring;

DECLARE curRoutes CURSOR FOR select distinct assigned_route_id from bus;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET route_done = TRUE;

OPEN curRoutes;
cur_route_loop: LOOP
FETCH FROM curRoutes INTO routeId;
IF route_done THEN
CLOSE curRoutes;
LEAVE cur_route_loop;
END IF;

set startgeom = (SELECT station_point FROM station WHERE id = (select from_id from route where id = routeId));
set endgeom = (SELECT station_point FROM station WHERE id = (select to_id from route where id = routeId));
set routegeom = (Select routegeomvals from route where id = routeId);
BLOCKBus: BEGIN
DECLARE curBuses CURSOR FOR SELECT bus_id FROM bus where assigned_route_id = routeId ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bus_done = TRUE;

OPEN curBuses;
cur_bus_loop: LOOP
FETCH FROM curBuses INTO busId;
IF bus_done THEN
set bus_done = false;
CLOSE curBuses;
LEAVE cur_bus_loop;
END IF;

BLOCKDev: BEGIN
DECLARE curDevs CURSOR FOR (select ts,latitude,longitude from ent.eventdata where accountID='rc' and deviceid = busId) ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dev_done = TRUE;

OPEN curDevs;
cur_dev_loop: LOOP
FETCH FROM curDevs INTO timest,lat,lng;
IF dev_done THEN
set dev_done = false;
CLOSE curDevs;
LEAVE cur_dev_loop;
END IF;

set lnglat = CONCAT(cast(lng as char),' ',cast(lat as char));
set currgeom = GeomFromText('POINT(lnglat)');

if(!(SELECT if (ST_Intersects(routegeom, Buffer(currgeom,.001))))) then
insert into tbloffroute values (busId, routeId, from_unixtime(timest));
end if;

if (select if(ST_within(startgeom, currgeom))) then
insert into tblstartend(busId,routeId,from_unixtime(timest),'s');
end if;

if (select if(ST_within(endgeom, currgeom))) then
insert into tblstartend(busId,routeId,from_unixtime(timest),'e');
end if;

END LOOP cur_dev_loop;
END BLOCKDev;




END LOOP cur_bus_loop;
END BLOCKBus;

END LOOP cur_route_loop;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
Not able to find problwm with this small stored proc
2537
March 13, 2015 03:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.