Stored Procedure in H2 Database

Question

I am new to database and recently started writing test cases for H2 database. I want to know how to test a stored procedure in Eclipse. I have seen the following:

http://www.h2database.com/html/features.html#user_defined_functions

How to CREATE PROCEDURE in H2

The sample code given in the h2database link,

"CREATE ALIAS NEXT_PRIME AS $$
String nextPrime(String value) {
    return new BigInteger(value).nextProbablePrime().toString();
}
$$;
"

Where should this be declared?and how to run it?

PS - I have the H2 JAR file and am testing it.

If someone can tell me how to write a simple stored procedure in Java for H2, it would be of great help.

Also is there any equivalent of the following in H2?

"begin dbms_output" ?

Thanks.

user_2685581 · Answer

Below is the way we used to implemented in our project. It might be helpful :)

package com.procedures;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CRITICAL_ACTIONS {

    public static final int SAVE_ACTION(Connection connection) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            return statement.executeUpdate("INSERT INTO SCHEMA1.CRITICAL_ACTIONS(COLLEAGUE_ID,JOURNEY_ID,TYPE,PRODUCT,DESCRIPTION,META_DATA,STATUS) values('12345',11111111,'ABC','Lloyds','hellow','hello','START')");
        } finally {
            //connection.close();
        }
    }

    public static final ResultSet FETCH_ACTION(Connection connection) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            return statement.executeQuery("SELECT * FROM SCHEMA1.CRITICAL_ACTIONS");
        }finally {
            connection.close();
        }
    }


}

Calling H2 Java Stored-procedure in Java :-

    jdbcTemplate.update("CREATE ALIAS SAVE_ACTION FOR \"com.procedures.CRITICAL_ACTIONS.SAVE_ACTION\"");
    jdbcTemplate.update("CREATE ALIAS FETCH_ACTION FOR \"com.procedures.CRITICAL_ACTIONS.FETCH_ACTION\"");

    jdbcTemplate.getDataSource().getConnection().createStatement().execute("call SAVE_ACTION()");

user_15556496 · Answer

H2 DB is not supporting for storedprocs. In place of stored proc we have to create function which returns some output jst like storedproc as we did using registerInOut params. For example,

if ur QueryConst is like this:
public static final String INSERT_EMPLOYEE = "{call INSERT_EMPLOYEE(?,?,?)}";

then,

In our schema.sql(which executes @Test before)
DROP ALIAS IF EXISTS INSERT_EMPLOYEE;
CREATE ALIAS INSERT_EMPLOYEE FOR "com.test.EmployeeDaoImplTest.updateEmpStoredproc";


package com.test;
@ContextConfiguration(locations = { "classpath:configxmltest.xml" })
@RunWith(SpringJUnit4ClassRunner.class)
@Sql(scripts = { "classpath:schema.sql" }, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
public class EmployeeDaoImplTest {
    
    public static final String INSERT_EMPLOYEE = "{call INSERT_EMPLOYEE(?,?,?)}";


    @Autowired
    EmployeeDaoImpl employeeDaoTest;
    
    and other dependencies....(if any)

    @Test
    public void testUpdateEmployee() {
        ..ur logic if any input data settings
        assertEquals("Inserted Successfully", employeeDaoTest.updateEmployee(input, INSERT_EMPLOYEE));
    }

    public static ResultSet updateEmpStoredproc(String name, String w, Integer i) throws SQLException {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("input", Types.VARCHAR, 255, 0);
        rs.addColumn("error", Types.VARCHAR, 255, 0);
        rs.addColumn("count", Types.INTEGER, 10, 0);
        rs.addRow(0, "Inserted Successfully");
        rs.addRow(1, 10);
        return rs;
    }
  }

Stored Procedure in H2 Database

5 Answers