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

[Robert Marcano]

On 10/11/2017 03:15 PM, Vlad Mihalcea wrote:
> You can open a GitHub issue for JPA. Meanwhile, you can work around it 
> as you suggested.

Opened an issue on JPA spec https://github.com/javaee/jpa-spec/issues/162

> 
> On 11 Oct 2017 9:08 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
>     <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
>     <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
>     <https://lists.jboss.org/mailman/listinfo/hibernate-dev>
>