How to write a function in the H2 database without using Java

Question

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

I don't think this is possible in H2. You might want to look at HSQLDB which supports procedures in its own SQL dialect and does not require Java for that. Btw: what do you mean with "*written in SQL*"? Which DBMS is you source? (SQL is **only** a *query* language it is **not** a DBMS product). — , Jun 25 '13 at 09:32
If you look [here](http://www.h2database.com/html/features.html#user_defined_functions) at the h2 Documentation it states "In addition to the [built-in functions](http://www.h2database.com/html/functions.html), this database supports user-defined Java functions". i.e. you create your procedures as java code then create aliases for them. — Java Devil, Jun 25 '13 at 09:36
Thanks for the quick response. Iam using **MsSqlDatabase** and the function is written for MsSqlDB. Then the only wayout to write procedures in H2 is through Java Code..? — arun_kk, Jun 25 '13 at 09:37
@JavaDevil : Thank you. I read it but believed that there will be a tweak for it. :( . — arun_kk, Jun 25 '13 at 09:38

score 18 · Accepted Answer · answered Jun 25 '13 at 11:18

18

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.

answered Jun 25 '13 at 11:18

Thomas Mueller

48,905
14
116
132

Does H2 still doesn't support Stored Procedure so our query doesn't have to be recompiled every time and rest of the features of SP? – Airy Sep 27 '14 at 18:28
@AbdulJabbarWebBestow sorry I don't understand your question. – Thomas Mueller Sep 29 '14 at 07:57

score 5 · Answer 2 · answered Jun 25 '13 at 11:35

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.

Just what I was looking for! – user6123723 Dec 23 '16 at 22:14 — user6123723, Dec 23 '16 at 22:14

How to write a function in the H2 database without using Java

2 Answers2

Linked