Linked Questions

Popular Questions

How to have H2 Database with Schema.package.function

Asked by At

I have created a Spring boot application. I'm trying to create a test database using H2 database(Oracle in production).

However I've got a problem with translating oracle construction: MYSCHEMA.MYPACKAGE.MYFUNCTION

CREATE SCHEMA IF NOT EXISTS MYSCHEMA;
CREATE ALIAS MYSCHEMA.MYPACKAGE FOR "somepackage.hello";

  public static ResultSet hello(String name)
  {
      SimpleResultSet result = new SimpleResultSet();
      result.addColumn("GREETING", Types.VARCHAR, 255, 0);
      result.addRow("Hello, " + name + "!");
      return result;
  }

My Test Class

  public void test() throws Exception
  {
      Connection connection = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");
      Statement statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery("CALL MYSCHEMA.MYPACKAGE('Thiaga')");
      if(resultSet.next()) {
        System.out.println("HelloWorld:"+resultSet.getString("GREETING"));  
      }         
  }

The above program is working fine with MYSCHEMA.MYPACKAGE('Thiaga'). But when I include function name it is failing

I want this to work

MYSCHEMA.MYPACKAGE.MYFUNCTION('Thiaga')

I tried something like this

CREATE ALIAS MYSCHEMA.MYPACKAGE.MYFUNCTION FOR "somepackage.hello";

But I am getting the following exception

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE ALIAS MYSCHEMA.MYPACKAGE.[*]MYFUNCTION FOR ""somepackage.hello"" "; expected "DETERMINISTIC, NOBUFFER, AS, FOR"; SQL statement:
CREATE ALIAS MYSCHEMA.MYPACKAGE.MYFUNCTION  FOR "somepackage.hello" [42001-196]

Could you please help me on this

Related Questions