HSQLDB Stored Procedure Error: attempt to assign to non-updatable column

Question

I am using HSQLDB 2.3.2 and am getting a bizarre error when trying to create a stored procedure.

My addresses table:

CREATE TABLE IF NOT EXISTS addresses (
    address_id                  INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL PRIMARY KEY,
    address_line_1              NVARCHAR(500) NOT NULL,
    address_line_2              NVARCHAR(500),
    address_city                NVARCHAR(100) NOT NULL,
    address_postal_code         NVARCHAR(25) NOT NULL,

    CONSTRAINT uc_addresses UNIQUE (address_line_1, address_city)
)

An insert to populate it:

INSERT INTO addresses (
    address_line_1,
    address_city,
    address_postal_code
) VALUES (
    '123 Test Blvd.', 'Testville', '11111'
)

And my proc:

CREATE PROCEDURE sp_get_address_by_id(
        IN address_id INTEGER,
        OUT address_id  INTEGER,
        OUT address_line_1 NVARCHAR(500),
        OUT address_line_2 NVARCHAR(500),
        OUT address_city NVARCHAR(100),
        OUT address_postal_code NVARCHAR(25))
    READS SQL DATA
    BEGIN ATOMIC
        SELECT
            a.address_id,
            a.address_line_1,
            a.address_line_2,
            a.address_city,
            a.address_postal_code
        INTO
            address_id,
            address_line_1,
            address_line_2,
            address_city,
            address_postal_code
        FROM
            addresses a 
        WHERE
            a.address_id = address_id;
    END

When I run this I get:

Error: attempt to assign to non-updatable column
SQLState:  0U000
ErrorCode: -2500

Questions:

1. What is wrong with my proc (what is producing this error)?
2. I'm actually looking for a CREATE IF NOT EXISTS-type declaration, so I can run this script over and over again and the procedure will only get created one time if it doesn't already exist. Will this happen or do I need to change the syntax to accomplish IF NOT EXISTS?

Answer 1

Try the syntax below, according to the hsqldb documentation http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_assignment

The SET statement is used for assignment. It can be used flexibly with rows or single values.

Also change the address_id parameter to type INOUT and remove the duplicate address_id parameter lines.

CREATE PROCEDURE sp_get_address_by_id(
    INOUT address_id INTEGER,
    OUT address_line_1 NVARCHAR(500),
    OUT address_line_2 NVARCHAR(500),
    OUT address_city NVARCHAR(100),
    OUT address_postal_code NVARCHAR(25))
READS SQL DATA
BEGIN ATOMIC
    SET (address_id,
        address_line_1,
        address_line_2,
        address_city,
        address_postal_code)
    = 
    (
    SELECT
        a.address_id,
        a.address_line_1,
        a.address_line_2,
        a.address_city,
        a.address_postal_code
    FROM
        addresses a 
    WHERE
        a.address_id = address_id
    );
END

You can try adding this as the first statement in your script if you want to drop the procedure if it already exists, so that you can re-run the script many times. You can search the documentation for <specific routine designator> for more info.

DROP SPECIFIC PROCEDURE sp_get_address_by_id IF EXISTS;