-1

i just wrote a mysql stored procedure that would take data based on certain conditions from two tables and then inserts them on a third table. I m planning to schedule this procedure to run in one of the two situations: a) when a new entry is made in EITHER of the first two tables and update the third table, OR b) just schedule the proc to run every say 5 mins and update the third table.

I m not aware of how to do the option a). I was thinking about using cron to schedule in my linux based web host, but then at the very last moment realised that they dont allow cron jobs for the basic version that i am using.

this brought me to services like onlinecronjobs.com and similar others, but i did not understand how they will help me run this stored proc.

my question is, if i give such sites the link to the stored proc - example: www.mysite.com/cron/mySP.sql then can these sites automatically call this stored proc and have the stored proc insert data into the third table? Is my assumption correct - just need to give them the path to the stored proc, is it?

thanks!

user1644208
  • 105
  • 5
  • 12

1 Answers1

3

a) Use a trigger:

CREATE TRIGGER foo AFTER INSERT ON tableA FOR EACH ROW CALL my_proc();
CREATE TRIGGER bar AFTER INSERT ON tableB FOR EACH ROW CALL my_proc();

b) Use MySQL's event scheduler:

CREATE EVENT baz ON SCHEDULE EVERY 5 MINUTE DO CALL my_proc();
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • the trigger seems to perfect solution, but unfortunately my webhosts have disabled tht also. I think only a third party cron can do now. Would my approach work - using an online cron site to call my php page which would then call the stored proc? – user1644208 Oct 14 '12 at 11:16
  • one more question.. most cron sites limit the number using coins and stuff like that. if i do not want to use a cron site, can i use a laptop and have a shell script keep calling this url every x minutes? wouldnt that be a replacement for these cron sites? i can keep the laptop ON all day, and make it hit the link every x mins. would that work? – user1644208 Oct 14 '12 at 11:25
  • 1
    @user1644208: Yes, it would work (provided that network connectivity between your laptop and the webserver is maintained). However it's quite costly in terms of network usage, electricity, etc. - surely you'd be better to simply purchase a better level of hosting? – eggyal Oct 14 '12 at 11:26
  • great! one last question. is there some specific place where i should keep the mysql SP which is being called by a php page? like /usr/bin something? i dont see any such folders in my webhost. can i just keep this SP in one of the www docs folder? will that work? thanks – user1644208 Oct 14 '12 at 11:28
  • i think they dont give the usr/bin folder access because they are not allowing Stored Proc, mysql triggers etc.. would the location of the script have any infulence on its execution? – user1644208 Oct 14 '12 at 11:29
  • 1
    @user1644208: You define stored procedures within MySQL using its [`CREATE PROCEDURE`](http://dev.mysql.com/doc/en/create-procedure.html) statement. It will then store it in its own data directory, but you you don't need to worry about that. – eggyal Oct 14 '12 at 11:31
  • ok, i understand. i believe the stored procs are located in the ROUTINE table somewhere. SO basically, if i have created a stored proc, then i dont have to bother about its location, i can just use my php page and simply do a CALL proc_name(). thats all i should think about, right? thanks a lot! – user1644208 Oct 14 '12 at 11:34
  • @user1644208: That is correct (of course, in this situation, you probably don't need an SP at all: you could implement the functionality from within the PHP page that you will be invoking). – eggyal Oct 14 '12 at 11:34
  • 1
    @user1644208: Alternatively, just ensure that you `CALL my_proc()` (or whatever) immediately after every `INSERT`? – eggyal Oct 14 '12 at 11:42
  • ok. i will check that way. thanks a lot for your suggestions. – user1644208 Oct 14 '12 at 11:48