Impossible to call a stored procedure using @Procedure annotation after migrating to Spring Boot 3

[SchlauFuchs]

Hi,

Not sure where the best place is for this regression, because it might also be a hibernate ORM issue, but lets get started.

After migrating our project from Spring Boot 2.7 to 3.0 and from Hibernate 5.6 to 6.0 (minor minors not included) we noticed that our Spring Data JPA repositories failed to call stored procedures against our Informix database (we are migrating, but takes time). We noticed that Hibernate has dropped general support for Informix and there is now a community support library we have to import to provide the dialect.

So in the past, this piece of code would have worked:

    @Procedure(procedureName = "cp_updcandidateflag")
    fun updateCandidateFlag(
       jobId: Int,
       candidateFlag: Char
    )

This code now generates a SQL exception when called against the database:

2023-11-14 14:43:07.156 DEBUG 10444 --- [    Test worker] org.hibernate.SQL                        :
                {call cp_updcandidateflag(:jobId,:candidateFlag)}
Hibernate: {call cp_updcandidateflag(:jobId,:candidateFlag)}
2023-11-14 14:43:07.709  WARN 10444 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   :
                SQL Error: -201, SQLState: 42000
2023-11-14 14:43:07.715 ERROR 10444 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   :
                A syntax error has occurred.

It is not as if we cannot call stored procedures any more, to make this code work we have it now as:

    @Modifying()
    @Query("CALL cp_updcandidateflag(:ai_job_id, :ac_candidateFlag)", nativeQuery = true)
    fun updateCandidateFlag(
        @Param("ai_job_id") id: Int,
        @Param("ac_candidateFlag") ac_candidateFlag: Char
    )

I don't know what happens behind the scenes. Is it that the repository is trying to prepare it as a statement instead of just calling the procedure? This is the stack traced I capture on the thrown SQL exception:

java.sql.SQLSyntaxErrorException: A syntax error has occurred.
	at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:413)
	at com.informix.util.IfxErrMsg.buildIsamException(IfxErrMsg.java:400)
	at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3023)
	at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3274)
	at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2269)
	at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2194)
	at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1194)
	at com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:245)
	at com.informix.jdbc.IfxCallableStatement.<init>(IfxCallableStatement.java:143)
	at com.informix.jdbc.IfxSqliConnect.prepareCall(IfxSqliConnect.java:5913)
	at com.informix.jdbc.IfxSqliConnect.prepareCall(IfxSqliConnect.java:2488)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareCall(ProxyConnection.java:306)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareCall(HikariProxyConnection.java)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:90)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:177)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:82)
	at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:656)
	at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:605)
	at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:858)
	at org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:842)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:345)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:148)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:72)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:94)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244)
	at jdk.proxy3/jdk.proxy3.$Proxy300.updateCandidateFlag(Unknown Source)
	at com.ourcompany.api.jobs.assign.AssignJobService.withCandidateFlagSet(AssignJobService.kt:260)
[...]

[mp911de]

Did you check how the SQL looks like with Hibernate 5.6? I suspect that {call cp_updcandidateflag(:jobId,:candidateFlag)} looks a bit different.

[SchlauFuchs]

when I compare the output by hibernate before throwing up, this prepared statement uses named parameters, while other cases (the working ones) use just indexes, as in (?,?)

[SchlauFuchs]

I have crossposted it on hibernate tracker just to be sure: https://hibernate.atlassian.net/browse/HHH-17426

[mp911de]

Thanks a lot. I'm going to close the ticket for the time being.