5

I am attempting to create a stored procedure with out parameters in a JUnit test that is configured with Spring (non-boot). I understand from documentation and examples that I must alias a static method to respond to the stored procedure call. However, I can find no mention of how this works with out parameters. What goes in the method signature of the static method?

package com.example;

import static java.sql.Types.INTEGER;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.h2.tools.SimpleResultSet;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { H2tests.TestConfiguration.class })
public class H2tests {

    @Autowired
    DataSource dataSource;

    public static ResultSet executeMyProc(int a) {

        return new SimpleResultSet();
    }

    // what should the type of the second parameter be?
    public static ResultSet executeMyProc(int a, int b) {

        return new SimpleResultSet();
    }

    @Test // passes
    public void worksWithInParam() throws SQLException {

        final CallableStatement stmt = dataSource.getConnection().prepareCall("CALL my_proc(?)");
        stmt.setInt(1, 44);
        stmt.executeQuery();
    }

    @Test // fails
    public void worksWithOutParam() throws SQLException {

        final CallableStatement stmt = dataSource.getConnection().prepareCall("CALL my_proc(?, ?)");
        stmt.setInt(1, 999);
        stmt.registerOutParameter(2, INTEGER);
        stmt.executeQuery();
    }

    public static class TestConfiguration {

        @Bean
        public DataSource dataSource() throws SQLException {

            final EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
            final EmbeddedDatabase db = builder //@formatter:off
                    .setType(EmbeddedDatabaseType.H2)
                    .setName("MODE=MySQL")
                    .build(); //@formatter:on
            db.getConnection().prepareStatement("CREATE ALIAS my_proc FOR \"com.example.H2tests.executeMyProc\"")
                    .execute();
            return db;
        }
    }
}
ds390s
  • 587
  • 1
  • 6
  • 19
  • 1
    My response might be a bit off topic, but in our project we ditched H2 in favor of TestContainers library (https://github.com/testcontainers/testcontainers-java). The library runs actual database (MySQL in you case) in Docker container and you have access to all features that you require and don't have to find work arounds for H2 limitations. So if there's any chance for you to use Docker I would definitely recommend you to take a look at the TestContainers. – Igor Nikolaev Jan 05 '18 at 22:33
  • Thanks for the input. Unfortunately, I don't have enough influence on the build process to make this happen. I need to use H2, or something like it, to keep the mocked database inside the Java process. – ds390s Jan 08 '18 at 16:58
  • Now that I’m thinking about it - what stops you from defining stored procedure with SQL instead? – Igor Nikolaev Jan 09 '18 at 18:09
  • 1
    Igor, I don't think that such a feature exists in H2 either. Implementing them in Java appears to be the intended way to have stored procedures in H2. – ds390s Jan 10 '18 at 21:55
  • See [Issue 2990](https://github.com/h2database/h2database/issues/2990): H2 Database stored procedure with multiple out params (currently open, opend Jan 2, 2021). – Sascha Doerdelmann Mar 16 '23 at 16:46

1 Answers1

2

I don't think H2 supports out parameters. You will have to return values through the result set:

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

@Test
public void test() throws Exception
{
    Connection connection = DriverManager.getConnection("jdbc:h2:mem:");
    Statement statement = connection.createStatement();
    statement.execute("CREATE ALIAS hello FOR \"DatabaseTest.hello\"");
    ResultSet resultSet = statement.executeQuery("CALL hello('Bart')");
    assertThat(resultSet.next(), is(true));
    assertThat(resultSet.getString("GREETING"), is("Hello, Bart!"));
}
Per Huss
  • 4,755
  • 12
  • 29
  • I'm afraid the result set is already being used to return vital info. The db team is pretty insistent that we use an out param for this. Would it be possible for you to provide a source for your conclusion that the feature is not supported? – ds390s Jan 10 '18 at 21:57
  • 3
    They have "Support OUT parameters in user-defined procedures" in their [roadmap](http://www.h2database.com/html/roadmap.html)... – Per Huss Jan 11 '18 at 08:10