H2 procedure will throw "Parameter not set" after a few calls
<p>I'm coming across a weird behavior when calling procedures on an H2 database : the first few calls (1 to 3 so far) will successfully return, then the next calls will consistently throw a <code>org.h2.jdbc.JdbcSQLException: Parameter "#1" is not set;</code>, where the first parameter is supposedly the OUT parameter.</p>

<p>I've also noticed that this failure pattern is relative to the <code>DataSource</code> : multiple <code>DataSource</code>s interfacing the same H2 database will each be able to call the procedure a few times before consistently throwing the error.</p>

<p>It also looks like waiting long enough will enable the <code>Datasource</code> to call the procedure again. Although I'm not sure exactly how long the wait needs to be, it's at least 10 minutes.</p>

<p>The procedure is created with the following query :</p>

<pre><code>CREATE OR REPLACE ALIAS ps_sleep as$$
String ps_sleep(int seconds) throws InterruptedException {
    Thread.sleep(seconds * 1000);
    return "DB Procedure : " + seconds + " seconds waited" ;
}
$$;
</code></pre>

<p>which is executed by the following Java code :</p>

<pre><code>((DataSource)getJNDIResource(datasourceJndiUri)).getConnection().prepareStatement(PS_SLEEP_QUERY).execute();
</code></pre>

<p>The procedure is then called by the following Java code :</p>

<pre><code>InitialContext context = new InitialContext();
DataSource ds = (DataSource) context.lookup(datasourceJndiUri);
Connection con = ds.getConnection();
CallableStatement cs = con.prepareCall("{?=call PS_SLEEP(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setInt(2, input_proc_timer);
cs.execute();
</code></pre>

<p>I've made sure the value of <code>input_proc_timer</code> always is an <code>int</code>.</p>

<p>The stacktrace is the following :</p>

<pre><code>org.h2.jdbc.JdbcSQLException: Parameter "#1" is not set; SQL statement:
 ?=call PS_SLEEP(?)  [90012-173]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
    at org.h2.message.DbException.get(DbException.java:171)
    at org.h2.message.DbException.get(DbException.java:148)
    at org.h2.expression.Parameter.checkSet(Parameter.java:80)
    at org.h2.command.Prepared.checkParameters(Prepared.java:163)
    at org.h2.command.CommandContainer.query(CommandContainer.java:90)
    at org.h2.command.Command.executeQuery(Command.java:195)
    at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:189)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
    at my.application.SomeServlet.doGet(SomeServlet.java:49)
    [...]
</code></pre>

<p>I'm running this under wildfly 8.1.0 and its packaged H2 database, which I believe is version 1.3 (the h2 module contains a h2-1.3.173.jar). I'm using the <code>standalone-full.xml</code> configuration file, which I haven't particularly modified.</p>

<p>I welcome any way to fix this, and would be particularly delighted if someone could explain how this behavior occurs.</p>