HSQLDB stored procedure error: attempt to assign non-updatable column

Question

I use HSQLDB 2.3.2 and get a fancy error when trying to create a stored procedure.

My table addresses :

 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) ) 

Insert for filling:

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

And my good:

 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:

• What is wrong with my proc (what causes this error)?
• I'm really looking for a CREATE IF NOT EXISTS -type declaration, so I can run this script over and over and the procedure will only be created once if it does not already exist. Will this happen or do I need to change the syntax to execute 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 strings or single values.

Also change the address_id parameter to type INOUT and delete the duplicate address_id 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 abandon the procedure if it already exists, so you can re-run the script many times. You can search the documentation for <specific routine designator> for more information.

 DROP SPECIFIC PROCEDURE sp_get_address_by_id IF EXISTS; 

Answer 2

In your procedure, you have two parameters with the same name:

 IN address_id INTEGER, OUT address_id INTEGER, 

This can cause a problem when you refer to address_id in the body of the procedure.
You should use:

 INOUT address_id INTEGER, 

instead of these two lines.

Answering your second question:
Why do you want to run this script over and over again without rebuilding the procedure? Running this script again makes sense when something has changed in it.

• If you do not plan to change this procedure, but want to change other things in your script, then perhaps you should just move this procedure to another script and run it only once
• If you plan to change the body of the procedure, it should be rebuilt every time. Therefore you should use CREATE OR REPLACE PROCEDURE

Answer 3

For followers, the same error message is issued if you do this:

  PreparedStatement selectStmt = conn.prepareStatement(query); ResultSet rs = selectStmt.executeQuery()) { rs.next(); rs.updateLong("column", value); // boom 

one fix is ​​to use this style:

  PreparedStatement selectStmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);