Categories
MySQL

Restrict running of a MySQL Stored Procedure by time

I am creating an Excel Report that queries a MySQL database using an ODBC connection. The data is the returned result of a Stored Procedure which contains some pretty slow, resource intensive queries on some massive tables belonging to a VICIDIAL installation.

I need the data to be as fresh as possible — but not at the expense of locking the database. So I need to be able to prevent the Stored Procedure from running during business hours.

So my first attempt involved some simple IF logic:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_restrict`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT 'first iteration'

BEGIN
    DECLARE currentTime TIME;
    DECLARE startWorkTime TIME;
    DECLARE endWorkTime TIME;
    SET currentTime = CURTIME();
    SET startWorkTime = CAST('08:00:00' AS TIME);
    SET endWorkTime = CAST('17:00:00' AS TIME);
    IF(startWorkTime > currentTime > endWorkTime) THEN
        SELECT * FROM table;
    END IF;
END

My second attempt used LEAVE which just seems a bit neater:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_restrict`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT 'second iteration'

FoxSP:BEGIN
    DECLARE currentTime TIME;
    DECLARE startWorkTime TIME;
    DECLARE endWorkTime TIME;
    SET currentTime = CURTIME();
    SET startWorkTime = CAST('08:00:00' AS TIME);
    SET endWorkTime = CAST('17:00:00' AS TIME);

    IF(startWorkTime < currentTime < endWorkTime) THEN
        #EXIT - IT IS NOW DURING WORKING HOURS
        LEAVE FoxSP;
    END IF;

    SELECT * FROM table;
END

A final solution would be to accommodate non-working days (weekends as well as public holidays).

By foxbeefly

PHP / MySQL Developer

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.