Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create stored procedure using H2 database?

Has anyone tried to create stored procedures using the H2 database?

like image 899
user351687 Avatar asked Jun 23 '10 04:06

user351687


People also ask

Can we create stored procedure in H2 database?

There is no stored procedure and sql userdefined function in H2 database instead of that we use java methods and create a alias to refer that. We can call that methods using alias.

Does H2 database support functions?

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.


1 Answers

To access the database within a Java function, you do need a connection. For H2, there are two ways to get such a connection:

Solution 1: If the first parameter of the Java function is a java.sql.Connection, then the database provides the connection. For SQL, this is a 'hidden' parameter, meaning you can't and don't need to set it explicitly. This is documented: User-Defined Functions and Stored Procedures, "Functions That Require a Connection". Example:

CREATE ALIAS QUERY AS $$
ResultSet query(Connection conn, String sql) throws SQLException {
    return conn.createStatement().executeQuery(sql);
} $$;
CALL QUERY('SELECT * FROM DUAL');

Solution 2: For compatibility with Apache Derby and Oracle, You can open a new connection within the Java function using DriverManager.getConnection("jdbc:default:connection"). This feature is available in H2 version 1.3.151 and newer, and it it disabled by default. To enable it, append ;DEFAULT_CONNECTION=TRUE to the database URL. It's a problematic feature because the Oracle JDBC driver will try to resolve this database URL if it is loaded before the H2 driver. So basically you can't use the feature if the Oracle driver is loaded (I consider this a bug in the Oracle driver).

like image 74
Thomas Mueller Avatar answered Oct 07 '22 06:10

Thomas Mueller