18

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

NullUserException
  • 83,810
  • 28
  • 209
  • 234
user351687
  • 241
  • 1
  • 3
  • 6
  • My problem with using Java functions is different from [this one](http://stackoverflow.com/questions/3098905/how-to-create-stored-procedure-using-h2-database). I don't know how to access tables in such a function (creating a connection makes no sense there, does it)? – maaartinus Mar 18 '11 at 12:06
  • It always helps to look at the manual: [H2: User-Defined Functions and Stored Procedures](http://www.h2database.com/html/features.html#user_defined_functions) – NullUserException Oct 07 '10 at 03:52

1 Answers1

9

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).

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • always close your statements when your done with them...Statement s= conn.createStatement();try{return s.executeQuery(sql);}finally{s.close();} – jcalfee314 Aug 05 '13 at 19:20
  • For H2 (and this is about H2) it doesn't matter if you close the statement or not. But yes, generally the statement should be closed. – Thomas Mueller Aug 05 '13 at 19:47
  • @Thomas Mueller your documentation examples are so poor would you put a CRUD examples – Dunken Mar 29 '16 at 10:47
  • @Dunken your comment is poor would you ask a question – Thomas Mueller Mar 29 '16 at 12:08
  • 1
    @Thomas Mueller i think i did i ask you to enhance your documentation of the H2 database and put CRUD examples – Dunken Mar 29 '16 at 12:43
  • 1
    @ThomasMueller, I'm struggling to understand **where** I write the block of code you proposed as solution 1. If I paste it into my java class then it doesn't recognize `CREATE ALIAS QUERY AS $` as valid Java. I tried writing that entire code block like `ResultSet rs = st.executeQuery("CREATE ALIAS QUERY IF NOT EXISTS AS $$\n" + " ResultSet query(Connection conn, String sql) .... ` but I get a syntax error. What am I missing Thomas? Thanks for your time. – Mathomatic Jul 13 '17 at 01:15