porting tigase from derby to hsqldb ... how to call stored Java procedure and throw away (ignore) the result?

Question

Trying to configure tigase to use hsqldb (hsqldb-1.8.0.9-1jpp.2) instead of derby (don't ask why, that's not the point) and everything works fine, except for setting some properties in the end. In Derby I had

CREATE procedure TigAddUserPlainPw(userId varchar(2049), userPw varchar(255)) 
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.db.derby.StoredProcedures.tigAddUserPlainPw';

and

call TigAddUserPlainPw('db-properties', NULL);

When I try to replices this with hsqldb by

CREATE ALIAS TigAddUserPlainPw
    FOR "tigase.db.derby.StoredProcedures.tigAddUserPlainPw";

and

CALL TigAddUserPlainPw('db-properties', NULL);

I get this error message

[root@tikanga scripts]# ./hsqldb-db-create.sh /var/lib/tigase/db/tigase
SQL Error at '/etc/tigase/database/hsqldb-schema-4-props.sql' line 1:
"CALL TigAddUserPlainPw('db-properties', NULL)"
Wrong data type: [Ljava.sql.ResultSet; in statement [CALL TigAddUserPlainPw(]

Any idea, what I am doing wrong?

Answer 1

You cannot use the Java static methods as they are. The Result[] parameters are not acceptable to HSQLDB 1.8.x.

It would be easier to convert to HSQLDB 2.0, as its stored procedure support has improved over version 1.8.

Your example shows we need to make some more improvements to HSQLDB to support these procedure declarations.

Answer 2

As far as I know, HSQLDB supports Java functions that return a ResultSet (Fred will correct me if I'm wrong): http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#N12835

You would need a new method:

public static ResultSet tigAddUserPlainPw(
    String userId, String userPw) throws SQLException;