How do I define a java defined stored proc to return multiple result sets?

Question

I'm trying to define a java (written in Kotlin) method that returns multiple result sets when called as a stored procedure. Code is below. the Hsqldb website;s features page indicates that this should be possible, what am I missing? I currently get an array out of bounds error on index 1:

val createProcedure = """
 CREATE PROCEDURE GET_CACHED(dir VARCHAR(100), hashCode INT)
   MODIFIES SQL DATA 
   LANGUAGE JAVA
   DYNAMIC RESULT SETS 9
   EXTERNAL NAME 'CLASSPATH:integration.FileCache.getResultSets'
"""
@JvmStatic
@Throws(SQLException::class)
public fun getResultSets(conn: Connection, dir: String, hashCode: Int, result: Array<ResultSet?>) {
    val file = getFile(dir, hashCode, "sql")
    //A list of cached sql statements
    val sqlList = BufferedReader(InputStreamReader(file.inputStream())).readLines()
    val stmt = conn.createStatement()
    for(i in sqlList.indices) {
        result[i] =  stmt.executeQuery(sqlList[i])
    }
}

Given that I can set a breakpoint and reach the inside of the function, I don't think I need to add any more of my code, but if that is wrong let me know.

I'm in a quest to find an in-memory database that can handle multiple result sets for testing purposes (We're modernizing an application, setting up tests first, containerized testing is currently out of reach). I've tried H2, sqlite, and now hsqldb, if there's a better solution I'm open to it.

score 1 · Accepted Answer · answered Jul 30 '20 at 23:50

HSQLDB supports multiple result test, but the Guide states :HyperSQL support this method of returning single or multiple result sets from SQL/PSM procedures only via the JDBC CallableStatement interface. Note the reference to SQL/PSM, rather than SQL/JRT. Currently there is no Java mechanism to return multiple result sets from a Java language PROCEDURE.

For test purposes, you could use a text template consisting of an SQL/PSM CREATE PROCEDURE statement written in SQL, with placeholders for the actual SQL statements that you want to execute. Process the template with your test SQL statements from your file and execute the resulting CREATE PROCEDURE statement.

How do I define a java defined stored proc to return multiple result sets?

1 Answers1