Stored procedures in HSQLDB

Posted by & filed under Uncategorized.

The only way to implement stored procedures in older versions of HSQLDB involved calling static methods in Java code.  Starting in version 2.2, HSQLDB supports stored-procedures as predefined SQL calls.  Documentation and examples of how to do this are not ideal or abundant.  Here is an example.

DROP SCHEMA IF EXISTS example cascade
CREATE SCHEMA example AUTHORIZATION DBA
 --People Table
CREATE TABLE example.PEOPLE ( 
    ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    FIRST_NAME VARCHAR(100), 
    MIDDLE_NAME VARCHAR(100), 
    LAST_NAME VARCHAR(100), 
    SSN VARCHAR(48), 
    STREET_ADDRESS1 VARCHAR(100), 
    STREET_ADDRESS2 VARCHAR(100), 
    CITY VARCHAR(100), 
    POSTAL_CODE VARCHAR(100), 
    STATE VARCHAR(100), 
    COUNTRY_ID CHAR(2),
    DEFAULT_LOCALE_ID VARCHAR(5), 
    LAST_UPDATED TIMESTAMP)
--Read stored procedure returning a result set
CREATE PROCEDURE example.readPeople(IN LIMIT INT)
    READS SQL DATA DYNAMIC RESULT SETS 1
    BEGIN ATOMIC
        DECLARE result SCROLL CURSOR WITH HOLD WITH RETURN FOR SELECT * FROM example.PEOPLE;
        OPEN result;
    END
--Update Stored Procedure  
CREATE PROCEDURE example.updatePeopleTimestamp(IN ID_IN INT)
    MODIFIES SQL DATA
    BEGIN ATOMIC
        UPDATE example.PEOPLE set LAST_UPDATED = CURRENT_TIMESTAMP WHERE ID = ID_IN;
    END