Re: test script

This is not dependent on SqlTool, but the statements can be executed using SqlTool.

 You can use a HyperSQL local SQL session variable as the argument to the procedure, then retrieve it.

An SQL session variable can be passed as a function or procedure argument. The variable is visible within the declaring session and lasts until the session is closed. Its value can be modified with the SET statement directly, or when it is passed to a procedure as OUT or INOUT parameter.

Declare the variable with the DECLARE statement. Pass it to the procedure, which sets it with the SET statement. Finally retrieve it with a CALL. 

-- 
DECLARE outvar BIGINT

CALL Library.Book_insert(outvar, 'a long string')

CALL outvar

The last call returns the value of newid.

Fred Toussi

On Sat, Nov 30, 2019, at 22:43, Jorge Garcia de Alba wrote:
> Hello,
> 
> Here is my specific example. I need to reuse that newid in another procedure.
> 
> sql> \i test_script.sql
> Nov 30, 2019 4:36:22 PM org.hsqldb.cmdline.SqlFile processSpecial
> SEVERE: SQL Error at 'file:test_script.sql' line 2:
> "call Library.Book_Insert(newid,'My New Book')"
> dynamic parameter or variable required as INOUT or OUT argument
> 
> -----
> DROP SCHEMA IF EXISTS Library CASCADE;
> /
> 
> CREATE SCHEMA Library;
> /
> 
> CREATE TABLE Library.Book
> (
> Id IDENTITY,
> Name VARCHAR(8000),
> CONSTRAINT Book_primaryKey PRIMARY KEY (Id)
> );
> /
> 
> CREATE PROCEDURE Library.Book_Insert (
> OUT newid INTEGER,
> IN v_Name VARCHAR(8000)
> )
> MODIFIES SQL DATA BEGIN ATOMIC
> INSERT INTO Library.Book (
> Id,
> Name
> ) VALUES (
> DEFAULT,
> v_Name
> );
> SET newid = IDENTITY();
> END;
> /
> -----
> 
> 
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>