HsqlException: General Error when calling a stored procedure

Question

I'm trying to write an integration test for a DAO that calls a stored procedure. I've created an embedded HSQL database from a test file that simply creates a table, populates it, and a simple stored procedure that returns the results of the table:

CREATE PROCEDURE usp_GetAvailableServices()
READS SQL DATA
BEGIN ATOMIC
    DECLARE result CURSOR WITH RETURN FOR SELECT FeatureType FROM PUBLIC.service_areas ;
    OPEN result;
END/;

The code to create the database is as follows. The database is created and populated properly.

        db = new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.HSQL)
                .addScript("db/sql/getAvailableServicesStoredProcedure.sql")
                .setSeparator("/;")
                .build();

My test fails when trying to call the stored procedure with General error; nested exception is java.sql.SQLException: General error which is ultimately caused by

Caused by: org.hsqldb.HsqlException: General error
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    ...

I got the HSQLDB console running and confirmed that my stored procedure was indeed created (it is listed under information_schema.routines), however when I try calling the stored procedure directly in the console with call usp_GetAvailableServices(), I am still presented with General error / Error Code: -458 / State: S1000

This leads me to believe that I have an error in my create procedure syntax, (seems correct per http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_cursors) or that I don't know how to properly call a routine in HSQLDB.

The code that calls the live stored procedure using SqlServer works properly. It's only HSQLDB that doesn't work.

fredt · Answer 1 · 2019-10-25T20:21:31.997

Your procedure is fine. Because it uses OPEN cursorname CURSOR, you cannot call it with DatabaseManager or SqlTool. You need to call it this way with JDBC using execute():

    CallableStatement cs = conn.prepareCall(
        "call usp_GetAvailableServices()");
    boolean isResult = cs.execute();

    assertFalse(isResult);

    isResult = cs.getMoreResults();

    ResultSet rs = cs.getResultSet();

    rs.next();
    // do something with the rs row
    rs.close();

Note the need for the getMoreResults() call.

Alternatively, use executeQuery() to avoid the extra call.

    CallableStatement cs = conn.prepareCall(
        "call usp_GetAvailableServices()");

    ResultSet rs = cs.executeQuery();

    rs.next();
    // do something with the rs row
    rs.close();

The next version, currently available as snapshot jar from http://hsqldb.org/downlaod, supports cs.execute() and cs.getResultSet() without cs.getMoreResults().

    CallableStatement cs = conn.prepareCall(
        "call usp_GetAvailableServices()");
    boolean isResult = cs.execute();

    ResultSet rs = cs.getResultSet();

    rs.next();
    // do something with the rs row
    rs.close();

Thank you, this helps me understand the problem. The solution as prescribed won't work for me because it requires me to change the code I am testing. Is it possible to return the result set of the query in the stored procedure without a cursor? My dataset will always be very small. — kjl, Oct 18 '19 at 14:55
Please add the Java code that works with SqlServer to your question. — fredt, Oct 18 '19 at 18:26
I was able to answer my question using the information provided here. The issue was indeed the cursor. HSQLDB is not able to return a ResultSet without a cursor, however a Function can return a table that can mimic the behavior of the actual stored procedure without using a cursor. — kjl, Oct 18 '19 at 20:39

HsqlException: General Error when calling a stored procedure

1 Answers1