H2: How to create a stored procedure with out parameters

Question

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;
        }
    }
}

Answer 1

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!"));
}