Recursive stored procedure in HSQLDB

Question

is it possible to create recursive stored procedure in HSQLDB ?

I wrote the following one to update a record and recursively all the parent records:

CREATE PROCEDURE updateFolderTotals(IN p_id VARCHAR(32), IN p_size BIGINT, IN p_files INT, IN p_folders INT) 
  MODIFIES SQL DATA 
  BEGIN ATOMIC  
    DECLARE l_parentid VARCHAR(32); 
    UPDATE folders  
    SET tot_files = tot_files + p_files, 
        tot_size = tot_size + p_size , 
        tot_folders = tot_folders + p_folders 
    WHERE id = p_id;  

    SELECT parentid INTO l_parentid FROM folders WHERE id = p_id; 

    IF (l_parentid IS NOT NULL) THEN 
        CALL updateFolderTotals(l_parentid,p_size,p_files,p_folders); 
    END IF; 

  END; 

but I get the following error:

user lacks privilege or object not found: UPDATEFOLDERTOTALS / Error Code: -5501 / State: 42501

In HyperSQL User Guide I've found some info (see Recursive Routines in HyperSQL User Guide) but it seems it is supported for funtions only.

Thank you in advance for support.

Answer 1

You can create recursive procedures following the same guidelines. First create the procedure with a simple body that throws an exception. You need to specify the SPECIFIC name of the procedure:

CREATE PROCEDURE updateFolderTotals(IN p_id VARCHAR(32), IN p_size BIGINT, IN p_files   INT, IN p_folders INT) 
  SPECIFIC updateFolderTotals_1 MODIFIES SQL DATA 
  SIGNAL SQLSTATE '45000'

Then alter the created procedure with the full body:

ALTER SPECIFIC ROUTINE updateFolderTotals_1
  BEGIN ATOMIC  
  DECLARE l_parentid VARCHAR(32); 
  UPDATE folders  
  SET tot_files = tot_files + p_files, 
    tot_size = tot_size + p_size , 
    tot_folders = tot_folders + p_folders 
  WHERE id = p_id;  

  SELECT parentid INTO l_parentid FROM folders WHERE id = p_id; 

  IF (l_parentid IS NOT NULL) THEN 
    CALL updateFolderTotals(l_parentid,p_size,p_files,p_folders); 
  END IF; 
END;