How to Write a Function in the H2 Database Without Using Java

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

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.

Create Function in h2 from Oracle

I can't reproduce the problem. How exactly did you run the statement? Possibly you run the statement with a tool that only sent part of the query? Or possibly there is a 'special character' (some kind of unicode space) just before the '$$'? I tried this:

create schema hts;
CREATE ALIAS HTS.TEST AS $$
String nextPrime(String value){
    return null;
}
$$;

Could you try this again in the H2 Console?

H2 database User defined java function Class not found

Make sure that:

• The class is public
• The method is public and static

• The class must be available in the classpath of the database engine

  From H2 Docs:

  When referencing a method, the class must already be compiled and included in the classpath where the database is running. Only static Java methods are supported; both the class and the method must be public

H2 database and functions in separate schemas

H2 doesn't support packages. What you could do is create the function using a different name, for example: PERMISSION_TOOLS_GET_ACCESS_LEVEL. The disadvantage is that you need to change the query as well. Or, you create a schema PERMISSION_TOOLS and the method there:

create schema PERMISSION_TOOLS;
CREATE ALIAS PERMISSION_TOOLS.GET_ACCESS_LEVEL as $$
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
$$;
select permission_tools.get_access_level(1) from dual;

Please not this will not yet work in H2 version 1.2.131 (which is the version you are using according to the error message code you got). The reason is that 'functions in schemas' was just recently implemented (in version 1.2.135). Actually I suggest to upgrade to version 1.2.138, because there was a bug fixed related to this feature in earlier versions. The disadvantage of creating the method is a special schema is: if you do create such functions in schemas other than PUBLIC, then the database can not be opened with older versions of H2.

JDBC with H2 and MySQL mode: create procedure fails

The "MySQL Compatibility Mode" doesn't make H2 100% compatible with MySQL. It just changes a few things. The documentation lists them:

• Creating indexes in the CREATE TABLE statement is allowed using INDEX(..) or KEY(..). Example: create table test(id int primary key, name varchar(255), key idx_name(name));
• When converting a floating point number to an integer, the fractional digits are not truncated, but the value is rounded.
• ON DUPLICATE KEY UPDATE is supported in INSERT statements, due to this feature VALUES has special non-standard meaning is some contexts.
• INSERT IGNORE is partially supported and may be used to skip rows with duplicate keys if ON DUPLICATE KEY UPDATE is not specified.
• REPLACE INTO is partially supported.
• Spaces are trimmed from the right side of CHAR values.
• REGEXP_REPLACE() uses \ for back-references.
• Datetime value functions return the same value within a command.
• 0x literals are parsed as binary string literals.
• Unrelated expressions in ORDER BY clause of DISTINCT queries are allowed.
• Some MySQL-specific ALTER TABLE commands are partially supported.
• TRUNCATE TABLE restarts next values of generated columns.
• If value of an identity column was manually specified, its sequence is updated to generate values after inserted.
• NULL value works like DEFAULT value is assignments to identity columns.
• Referential constraints don't require an existing primary key or unique constraint on referenced columns and create a unique constraint automatically if such constraint doesn't exist.
• LIMIT / OFFSET clauses are supported.
• AUTO_INCREMENT clause can be used.
• YEAR data type is treated like SMALLINT data type.
• GROUP BY clause can contain 1-based positions of expressions from the SELECT list.
• Unsafe comparison operators between numeric and boolean values are allowed.

That's all. There is nothing about procedures. As @jccampanero pointed out in the other answer, you must use the syntax specific to H2 if you want to create stored procedures.

---

Related Topics