英文:
How do I define a java defined stored proc to return multiple result sets?
问题
我正在尝试定义一个Java方法（用Kotlin编写），当作为存储过程调用时返回多个结果集。以下是代码。Hsqldb网站的功能页面指示应该是可能的，我漏掉了什么？我目前在索引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")
//缓存的SQL语句列表
val sqlList = BufferedReader(InputStreamReader(file.inputStream())).readLines()
val stmt = conn.createStatement()
for(i in sqlList.indices) {
result[i] = stmt.executeQuery(sqlList[i])
}
}
考虑到我可以设置断点并进入函数内部，我认为我不需要添加更多我的代码，但如果有错误，请告诉我。
我正在寻找一个能够处理多个结果集以供测试目的的内存数据库（我们正在现代化应用程序，首先设置测试，容器化测试目前无法实现）。我已经尝试了H2、sqlite和hsqldb，如果有更好的解决方案，我愿意尝试。
英文:
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.
答案1
得分: 1
HSQLDB支持多结果测试，但指南说明：HyperSQL仅通过JDBC CallableStatement接口支持从SQL/PSM过程返回单个或多个结果集。 请注意引用的是SQL/PSM，而不是SQL/JRT。目前，没有Java机制可以从Java语言PROCEDURE返回多个结果集。
为了测试目的，您可以使用一个SQL/PSM CREATE PROCEDURE语句的文本模板，其中包含用于执行的实际SQL语句的占位符。使用您的测试SQL语句处理模板，然后执行生成的CREATE PROCEDURE语句。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论