This question is reference to question "How to create stored procedure using H2 database?" (continuation of the same question).
Is there a way to write a function in H2 without using Java code? My requirement is to translate the functions written in SQL to H2 without using Java code. I found lot of examples in different portals doing the same using Java code. Your help will be greatly appreciated.
Regards Arun
Currently, H2 does only support functions written in Java or a related language (for example Groovy or Scala). PL/SQL (Oracle) and T-SQL (MS SQL Server, Sybase) are not supported.
If your primary goal is to run SQL (or SQLesque) statements within your H2 Java user-defined functions, jOOQ could be an option as a "PL/Java" implementation. Of course, this would still be a Java solution.
An example of such a function can be seen in this blog post:
http://blog.jooq.org/2011/11/04/use-jooq-inside-your-h2-database
public class Functions {
public static int countBooks(Connection connection, Integer authorId)
throws SQLException {
// Translate your T-SQL statements to jOOQ statements
return DSL.using(connection, SQLDialect.H2)
.selectCount()
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(authorId))
.fetchOne(0, int.class);
}
}
Declare the above method as an ALIAS to H2
CREATE ALIAS countBooks
FOR "org.example.Functions.countBooks";
Use the function in SQL
SELECT author.last_name, countBooks(author.id)
FROM author
A similar approach can be taken with H2's own SQL abstraction JaQu, of course. Using JaQu wouldn't add any additional dependency, I think.
