function that waits until condition to return

Question

I have barely any experience with proper SQL but I've been trying to write a database function that waits until a condition is met and then releases the thread so I can call that function whenever I want to wait until that condition is met. The condition tells me that a seperate service is not currently working on the data I want to access, but for development I created a useless entry that I just want to manually delete for testing.

Here is my code so far, but I can't get it to work. For development I tried just executing it in a DB manager like I would execute a SELECT statement:

SET @c = 1;
WHILE @c > 0 DO
    SELECT COUNT(*) INTO @c FROM pb_db_operational.application_parameter WHERE `key` = "waitingtest";

    IF @c > 0
        SELECT SLEEP(5);
    END IF;
END WHILE;

SELECT 'asdf' FROM accessory_complete_price_factor LIMIT 1

I also attempted to create a procedure since I wasn't able to use the DECLARE statement when running it outside of a stored procedure:

CREATE FUNCTION `WaitUntilDataReady`()
RETURNS TINYINT
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE v_count INTEGER DEFAULT 1

WHILE v_count > 0 DO
    set v_count = (SELECT COUNT(*) FROM pb_db_operational.application_parameter WHERE `key` = "waitingtest")

    IF v_count > 0
        SELECT SLEEP(5)
    END IF
END WHILE

RETURN 1;

END

I wasn't able to get either of my examples running, so I'm happy about each and any piece of info you can tell me, even if it's just basic syntax.

Thanks in advance :)

It is better to run this sort of logic outside of the database, instead of locking the query thread for an indeterminate amount of time. You can use _triggers_ to detect conditions in real time but the question is what is the operation that you want to _trigger_ with this type of logic? — Chris Schaller, Mar 03 '22 at 10:42
@ChrisSchaller I'm trying to use this for a daily event that saves a report to a table, so I think triggers won't work for us. I also think having a service that is scheduled by eg. windows to check the data integrity and then start an event would be a lot more work. What does locking the query thread mean exactly? can no other connections execute queries until it is unlocked? — lenny, Mar 03 '22 at 11:11
Its more about the calling context, it's not likely to hang around that long connected to the database. It will timeout or some other environmental issue could easily sever the connection. If this is a daily event, then use polling it will be much more efficient and require a lot less management code. For high frequency events, or real-time, triggers are great. — Chris Schaller, Mar 03 '22 at 12:45
@ChrisSchaller well, the calling context is the database itself. It's an event on the database that just does an insert with data from a select statement. I just want that to wait until a certain condition is met. — lenny, Mar 04 '22 at 07:38
Don't use sleep in this way, setup a proper job rather than blocking in this way. — Chris Schaller, Mar 04 '22 at 16:09

Thierry Gerardin · Accepted Answer · 2022-03-04T05:37:23.797

you should try a stored procedure, using the following example :

    DROP PROCEDURE IF EXISTS pb_db_operational.WaitUntilDataReady ;
    
    DELIMITER //
    CREATE PROCEDURE pb_db_operational.WaitUntilDataReady(
      IN p_db_name VARCHAR(100),
      IN p_tab_name VARCHAR(100),
      IN p_col_name VARCHAR(100),  
      IN p_key VARCHAR(255),
      INOUT p_return_value INT
    )
    SQL SECURITY DEFINER
    BEGIN
      SET @v_count := 1 ;
      SET @req_sql := CONCAT("SELECT COUNT(*) INTO @v_count FROM ", p_db_name, ".", p_tab_name, " WHERE ", p_col_name, " = '", p_key, "' ; ") ;
      WHILE @v_count > 0 DO
        EXECUTE IMMEDIATE @req_sql ;
        IF @v_count > 0 THEN
          SELECT SLEEP(5) ;
        END IF ;
      END WHILE ;
      SET p_return_value := 1 ;
    END//
    DELIMITER ;
    
    
    SET @a:=0 ;
    CALL pb_db_operational.WaitUntilDataReady('pb_db_operational', 'application_parameter', 'v_key', 'waiting_test', @a) ;
    SELECT @a ;

Stored procedure allows to use dynamic SQL. As you can see above, it allows to call the procedure with parameters, changing the database name, table name and column name as needed.

The procedure return value, will be stored in @a variable.

Advice: avoid to use reserved words as object name (variable, database, table, column, etc.), such as "key"

Hope this will help.

function that waits until condition to return

1 Answers1