[hibernate-dev] Statements leaks when using JPA StoredProcedureQuery API

[Robert Marcano]

On 10/13/2017 03:11 PM, Steve Ebersole wrote:
> The conceptualization of a Query for both Hibernate and JPA is that you 
> execute it and get back the result of that execution: a List, a single 
> result, a ScrollableResult, a Stream, etc.
> 
> Yet then JPA throws in StoredProcedureQuery which is completely 
> different conceptually.  Here you execute and then (statefully!!!) 
> access the individual pieces of that execution's results.
> 
> Sadly the JPA StoredProcedureQuery API is just messed up.  In my opinion 
> (yes, of course I'm biased) our ProcedureCall + ProcedureOutputs model 
> is MUCH better. It is more in line with that conceptualization of Query 
> -> Result as opposed to StoredProcedureQuery's Query -> Query-as-Result.
> 
> All that said...
> 
> What you are asking for here is to implicitly close the JDBC 
> CallableStatement when we recognize <some condition> that signifies the 
> exhaustion of the execution results.  What is that condition?  Can't be 
> just when there are no more ResultSets in the CallableStatement's 
> pipeline.  Consider calling StoredProcedureQuery#getOutputParameterValue 
> (which would require accessing the CallableStatement) *after* we have 
> implicitly closed the CallableStatement.

No, I know implicitly closing the JDBC statement is not possible with 
only the current CallableStatement API. There is no way to know if the 
client code needs or read all possible outputs from the stored 
procedure. My previous email mentioned making CallableStatement an 
AutoCloseable but that will make the API too different from the other 
kind of queries, that you explained better than me, are stateless.

Maybe exposing Hibernate ProcedureOutputs to JPA or StoredProcedureQuery 
implementing some kind of prepareCall() method that return a stateful 
object that implement AutoCloseable.

> 
> So what is that condition?  That's the rub...
> 
> 
> On Wed, Oct 11, 2017 at 1:07 PM Robert Marcano <robert at marcanoonline.com 
> <mailto:robert at marcanoonline.com>> wrote:
> 
>     Migrating code from the Hibernate API to JPA, I found a stored procedure
>     being called on a loop that was generating DB2 errors [1] on tests. This
>     error is caused in this case for having a lot of not closed statements.
> 
>     The problem didn't happen using ProcedureCall Hibernate API because the
>     method getOutputs() and release() from the Outputs instance are
>     available.
> 
>     StoredProcedureQuery JPA API doesn't have any way to "close" the query
>     and by that, the statement. Reusing the same instance of
>     StoredProcedureQuery trying to only leak an unclosed statement but that
>     doesn't help either, ProcedureCallImpl is creating a new statement for
>     every call [2].
> 
>     The only option is to unwrap the StoredProcedureQuery to an
>     StoredProcedureQuery and release the Outputs instance.
> 
>     I don't think there is a way to avoid this without enhancing the JPA
>     API. Client code can call an store procedure and in some cases not
>     caring about all the results, so there is no way for a JPA
>     implementation to know when to close the statement.
> 
>     Making StoredProcedureQuery an AutoCloseable may help, but it will
>     contrast with other Query types that don't need to be closed.
> 
>     Note: It is not frequent to call many store procedures on a loop, I
>     would have preferred to just create a new procedure with the loop, but
>     for this application the conditions about when to call the procedure for
>     each iteration are outside the database.
> 
>     [1] http://www-01.ibm.com/support/docview.wss?uid=swg21504334
>     [2]
>     https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/procedure/internal/ProcedureCallImpl.java#L437
> 
> 
>     _______________________________________________
>     hibernate-dev mailing list
>     hibernate-dev at lists.jboss.org <mailto:hibernate-dev at lists.jboss.org>
>     https://lists.jboss.org/mailman/listinfo/hibernate-dev
>