0

i want to create in mysql an event schedule every day that check if the current date is greater than a date stored in the database table and then call some store procedures.

Reading my WRONG mysql code you will understand that i want to do:

delimiter $
set global event_scheduler = on$
create event if not exists `end_qualifications`
    on schedule
    every day
    do
    begin
    if curdate() >= (select `end_date` from `round` where `nome` = 
    "qualifications")
    then
    /* call myprocedure(params); */
    end if;     
    end $
delimiter ;

I found here If-statement in the MySQL stored procedure for selecting data something similar, but there is not way my code work as i want.

I'm a beginner with mysql so it's possible that what i want to do can't be done.

Anyone knows how to make my code work?

I'm using MySQL client version: 5.7.25

EDITED: this is the error i get when i try to run the query

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MariaDB server version for the right 
syntax to use near 'do                                            
     begin
     if curdate() >= (select `end_date` from `round` where `nome` = 
     "q' at line 5

I don't know if it matters, but the database is empty for now.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
fabianod
  • 501
  • 4
  • 17
  • this sounds like a cronjob, not something mysql is supposed to do – Philipp Sander Jul 24 '19 at 12:25
  • Every must have a THEN – P.Salmon Jul 24 '19 at 12:27
  • Where are params set up? – P.Salmon Jul 24 '19 at 12:28
  • The select should be enclosed in brackets ,column name and table name should be enclosed in bacticks (not single quotes). I suggest you fix the logic by building a stored procedure before attempting to create the event. Carry out these fixes and let us know if you are still having problems. – P.Salmon Jul 24 '19 at 12:32
  • https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql/11321508#11321508 – P.Salmon Jul 24 '19 at 12:34
  • Since you have changes the delimiter then line `set global event_scheduler = on;` should be terminated as `set global event_scheduler = on$` – James Jul 24 '19 at 12:40
  • I tried what you said, but I still get the error I reported above – fabianod Jul 24 '19 at 15:32
  • You need to specify the interval eg every 1 day and you need to do something in the if..then..end if please review https://dev.mysql.com/doc/refman/5.7/en/create-event.html – P.Salmon Jul 24 '19 at 15:45
  • BTW if you want to get someones attention include their user name prefixed with an @ in comments. – P.Salmon Jul 24 '19 at 15:47
  • @P.Salmon oh, i didn't know that. Thank you, it works! Thank you all! – fabianod Jul 24 '19 at 16:20

0 Answers0