Has anyone been able to create SPs in MySQL in the free hosting service. I am pasting the code here. It's working fine in my local machine.
drop procedure if exists BuildRoutes;
delimiter |
create procedure BuildRoutes()
begin
declare rows int default 0;
truncate routes;
insert into routes
select null,depart,arrive,1,id,distance,time from edges;
set rows=row_count();
while (rows>0) do
create or replace view nextroutes as
select routes.depart,edges.arrive,routes.hops+1 as hops,(routes.distance+edges.distance) as distance, concat(routes.route,',',edges.id) as route,min(routes.time+edges.time) as time from routes inner join edges on routes.arrive=edges.depart and locate(edges.arrive,routes.route)=0 group by depart,arrive;
insert into routes
select null,nextroutes.depart,nextroutes.arrive,nextroutes.hops,nextroutes.route,nextroutes.distance,nextroutes.time from nextroutes left join routes on nextroutes.depart=routes.depart and nextroutes.arrive=routes.arrive where routes.depart is null and routes.arrive is null;
set rows=row_count();
update routes,nextroutes set routes.hops=nextroutes.hops,routes.route=nextroutes.route,routes.distance=nextroutes.distance,routes.time=nextroutes.time where routes.arrive=nextroutes.arrive and routes.depart=nextroutes.depart and nextroutes.time<routes.time;
set rows=rows+row_count();
end while;
end;
|
Help me out please.
drop procedure if exists BuildRoutes;
delimiter |
create procedure BuildRoutes()
begin
declare rows int default 0;
truncate routes;
insert into routes
select null,depart,arrive,1,id,distance,time from edges;
set rows=row_count();
while (rows>0) do
create or replace view nextroutes as
select routes.depart,edges.arrive,routes.hops+1 as hops,(routes.distance+edges.distance) as distance, concat(routes.route,',',edges.id) as route,min(routes.time+edges.time) as time from routes inner join edges on routes.arrive=edges.depart and locate(edges.arrive,routes.route)=0 group by depart,arrive;
insert into routes
select null,nextroutes.depart,nextroutes.arrive,nextroutes.hops,nextroutes.route,nextroutes.distance,nextroutes.time from nextroutes left join routes on nextroutes.depart=routes.depart and nextroutes.arrive=routes.arrive where routes.depart is null and routes.arrive is null;
set rows=row_count();
update routes,nextroutes set routes.hops=nextroutes.hops,routes.route=nextroutes.route,routes.distance=nextroutes.distance,routes.time=nextroutes.time where routes.arrive=nextroutes.arrive and routes.depart=nextroutes.depart and nextroutes.time<routes.time;
set rows=rows+row_count();
end while;
end;
|
Help me out please.