Creating HSQLDB Procedure in DbVisualizer

Question

I am trying to create the following simple HSQLDB procedure, by a DbVisualizer client.

DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure(OUT my_output INTEGER, IN my_input INTEGER)
        MODIFIES SQL DATA -- NO SQL
        BEGIN ATOMIC
                SET my_output = my_input;
        END

Error message:

[CREATE - 0 rows, 0.001 secs]  [Code: -5590, SQL State: 42590]  unexpected end of statement:  required: ; : line: 3
[END - 0 rows, 0.000 secs]  [Code: -5581, SQL State: 42581]  unexpected token: END
... 2 statement(s) executed, 0 rows affected, exec/fetch time: 0.001/0.000 sec  [0 successful, 2 errors]

It looks like there is a problem with the interpretation of ;, but I could not find anything in HSQLDB equivalent to MySQl delimiter.

How do I get around this problem?

Answer 1

In DbVisualizer, I can use the DbVisualizer-specific delimiter.

@delimiter ++;

DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure(OUT my_output INTEGER, IN my_input INTEGER)
        MODIFIES SQL DATA -- NO SQL
        BEGIN ATOMIC
                SET my_output = my_input;
        END
;
++
@delimiter ;++

From the Dbvisualizer documentation:

Using the @delimiter command

With the @delimiter command you can temporarily change the statement delimiter DbVisualizer uses to separate the statements and send them one by one to the database. Use it before the complex statement, and after the statement if the script contains additional statements. Here's an example:

@delimiter ++;
CREATE OR REPLACE FUNCTION HELLO (p1 IN VARCHAR2) RETURN VARCHAR2
AS
BEGIN
 RETURN 'Hello ' || p1;
END;
++
@delimiter ;++
@call ${returnValue||(null)||String||noshow dir=out}$ = HELLO('World');
@echo returnValue = ${returnValue}$;

The first @delimiter command sets the delimiter to ++ so that the default ; delimiter can be used within the function body in the CREATE statement. The ++ delimiter is then used to end the CREATE statement, and another @delimiter command sets the delimiter back to ; for the remaining commands in the script.