Error running stored procedure in PostgreSQL

Support
#1

While my application is working with SQL server database, when I am using postgreSQL (v.16), I am getting the following error when my application is executing a Stored procedure.


at io.jmix.flowui.kit.event.EventBus.fireEvent(EventBus.java:82) ~[jmix-flowui-kit-2.3.4.jar:na]
	at io.jmix.flowui.kit.action.BaseAction.actionPerform(BaseAction.java:67) ~[jmix-flowui-kit-2.3.4.jar:na]
	at io.jmix.flowui.action.list.ListDataComponentAction.actionPerform(ListDataComponentAction.java:165) ~[jmix-flowui-2.3.4.jar:na]
	at io.jmix.flowui.kit.component.button.JmixButtonActionSupport.onButtonClick(JmixButtonActionSupport.java:151) ~[jmix-flowui-kit-2.3.4.jar:na]
Caused by: org.eclipse.persistence.exceptions.DatabaseException: 

	at com.vaadin.flow.component.ComponentEventBus.fireEventForListener(ComponentEventBus.java:239) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.component.ComponentEventBus.handleDomEvent(ComponentEventBus.java:488) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.component.ComponentEventBus.lambda$addDomTrigger$dd1b7957$1(ComponentEventBus.java:298) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.internal.nodefeature.ElementListenerMap.lambda$fireEvent$2(ElementListenerMap.java:447) ~[flow-server-24.3.13.jar:24.3.13]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
Caused by: java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgCallableStatement.setObject(String,Object,int) is not yet implemented.

	at com.vaadin.flow.internal.nodefeature.ElementListenerMap.fireEvent(ElementListenerMap.java:447) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.communication.rpc.EventRpcHandler.handleNode(EventRpcHandler.java:62) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.communication.rpc.AbstractRpcInvocationHandler.handle(AbstractRpcInvocationHandler.java:74) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocationData(ServerRpcHandler.java:475) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.communication.ServerRpcHandler.lambda$handleInvocations$5(ServerRpcHandler.java:456) ~[flow-server-24.3.13.jar:24.3.13]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:456) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:324) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:114) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:40) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.VaadinService.handleRequest(VaadinService.java:1577) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.server.VaadinServlet.service(VaadinServlet.java:398) ~[flow-server-24.3.13.jar:24.3.13]
	at com.vaadin.flow.spring.SpringServlet.service(SpringServlet.java:106) ~[vaadin-spring-24.3.13.jar:na]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614) ~[jakarta.servlet-api-6.0.0.jar:6.0.0]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:632) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:408) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:303) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:267) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.springframework.web.servlet.mvc.ServletForwardingController.handleRequestInternal(ServletForwardingController.java:142) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:178) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:51) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:547) ~[jakarta.servlet-api-6.0.0.jar:6.0.0]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.13.jar:6.1.13]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614) ~[jakarta.servlet-api-6.0.0.jar:6.0.0]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at io.jmix.core.impl.logging.LogMdcFilter.doFilterInternal(LogMdcFilter.java:28) ~[jmix-core-2.3.4.jar:na]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.13.jar:6.1.13]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.30.jar:10.1.30]
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:108) ~[spring-web-6.1.13.jar:6.1.13]
	at org.springframework.security.web.FilterChainProxy.lambda$doFilterInternal$3(FilterChainProxy.java:231) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:365) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.access.intercept.AuthorizationFilter.doFilter(AuthorizationFilter.java:100) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:126) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:120) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:131) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:85) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:100) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:110) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:101) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:179) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:151) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:129) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:227) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:221) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374) ~[spring-security-web-6.2.6.jar:6.2.6]
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:107) ~[spring-security-web-6.2.6.jar:6.2.6]

Here is from where I am calling the stored procedure:

 @Transactional
    public void buildHolidayCalendarDetail( HolidayCalendar holidayCalendar){

            StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("spHR_AttendanceBuildHolidayCalendarDetail");
            // set parameters
            storedProcedure.registerStoredProcedureParameter("holidayCalendarId", UUID.class, ParameterMode.IN);
            storedProcedure.setParameter("holidayCalendarId", holidayCalendar.getId());
            storedProcedure.execute();

    }

I am using platform v 2.3.4

#3

Hello!

It looks like the Eclipselink issue:

As a first option the eclipselink.jpa.naming_into_indexed property has been suggested. In Jmix you can set jpa property e.g. by specifying EntityManagerFactory bean in application configuration class:

//...
@SpringBootApplication
public class SomeApplication implements AppShellConfigurator {

    //...

    @Bean
    @Primary
    protected LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
                                                                          JpaVendorAdapter jpaVendorAdapter,
                                                                          DbmsSpecifics dbmsSpecifics,
                                                                          JmixModules jmixModules,
                                                                          Resources resources) {
        return new JmixEntityManagerFactoryBean(Stores.MAIN, dataSource, jpaVendorAdapter, dbmsSpecifics, jmixModules, resources) {
            @Override
            protected void setupJpaProperties() {
                super.setupJpaProperties();
                getJpaPropertyMap().put("eclipselink.jpa.naming_into_indexed", "true");
            }
        };
    }

Alternatively you can use positional parameters instead of named in stored procedure.

If these options don’t help, please add an example project to reproduce the issue.

Regards,
Dmitry

#4

Hi @taimanov
Thank you. Now that the initial error is fixed but encountering another issue.

My application:

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class })
public class MyApplication extends SpringBootServletInitializer implements AppShellConfigurator {

    @Autowired
    private Environment environment;


    //This is required for PostgreSQL - to run stored procedure
    @Bean
    @Primary
    protected LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
                                                                          JpaVendorAdapter jpaVendorAdapter,
                                                                          DbmsSpecifics dbmsSpecifics,
                                                                          JmixModules jmixModules,
                                                                          Resources resources) {
        return new JmixEntityManagerFactoryBean(Stores.MAIN, dataSource, jpaVendorAdapter, dbmsSpecifics, jmixModules, resources) {
            @Override
            protected void setupJpaProperties() {
                super.setupJpaProperties();
                getJpaPropertyMap().put("eclipselink.jpa.naming_into_indexed", "true");
            }
        };
    }

Here is my ServiceBean code from where calling the stored procedure:

@Transactional
    public void buildHolidayCalendarDetail( HolidayCalendar holidayCalendar){

        if (isPostgres()) {
            try (Connection connection = em.unwrap(Connection.class);
                 CallableStatement call = connection.prepareCall("{ CALL spHR_AttendanceBuildHolidayCalendarDetail(?) }")) {
                call.setObject(1, holidayCalendar.getId());
                call.execute();
            } catch (SQLException e) {
                throw new RuntimeException("Error executing PostgreSQL procedure", e);
            }
        } else {
            // SQL Server procedure call
            StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("spHR_AttendanceBuildHolidayCalendarDetail");
            storedProcedure.registerStoredProcedureParameter("holidayCalendarId", UUID.class, ParameterMode.IN);
            storedProcedure.setParameter("holidayCalendarId", holidayCalendar.getId());
            storedProcedure.execute();
        }
    }

The error returned when I run it.

org.postgresql.util.PSQLException: ERROR: sphr_attendancebuildholidaycalendardetail(uuid) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
	at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:84)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.execute(DatabaseAccessor.java:1035)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:651)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:569)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2058)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:311)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:280)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:266)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.execute(DatasourceCallQueryMechanism.java:258)
	at org.eclipse.persistence.queries.ResultSetMappingQuery.executeDatabaseQuery(ResultSetMappingQuery.java:317)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:812)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3063)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1851)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1833)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1798)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:263)
	at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:315)
	at com.inteacc.mdg.service.InteaccMDGGeneralBean.buildHolidayCalendarDetail(InteaccMDGGeneralBean.java:187)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:379)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)
	at com.inteacc.mdg.service.InteaccMDGGeneralBean$$SpringCGLIB$$0.buildHolidayCalendarDetail(<generated>)
	at com.inteacc.mdg.view.gen.holidaycalendar.HolidayCalendarDetailView.onAfterClose(HolidayCalendarDetailView.java:71)
	at com.vaadin.flow.component.ComponentEventBus.fireEventForListener(ComponentEventBus.java:239)
	at com.vaadin.flow.component.ComponentEventBus.fireEvent(ComponentEventBus.java:228)
	at com.vaadin.flow.component.Component.fireEvent(Component.java:411)
	at io.jmix.flowui.view.View.close(View.java:268)
	at io.jmix.flowui.view.View.close(View.java:245)
	at io.jmix.flowui.view.StandardDetailView.lambda$closeWithSave$6(StandardDetailView.java:334)
	at io.jmix.flowui.util.SuccessOperationResult.compose(SuccessOperationResult.java:35)
	at io.jmix.flowui.view.StandardDetailView.closeWithSave(StandardDetailView.java:334)
	at io.jmix.flowui.action.view.DetailSaveCloseAction.execute(DetailSaveCloseAction.java:76)
	at io.jmix.flowui.action.view.ViewAction.actionPerform(ViewAction.java:140)
	at io.jmix.flowui.kit.component.button.JmixButtonActionSupport.onButtonClick(JmixButtonActionSupport.java:151)
	at com.vaadin.flow.component.ComponentEventBus.fireEventForListener(ComponentEventBus.java:239)
	at com.vaadin.flow.component.ComponentEventBus.handleDomEvent(ComponentEventBus.java:488)
	at com.vaadin.flow.component.ComponentEventBus.lambda$addDomTrigger$dd1b7957$1(ComponentEventBus.java:298)
	at com.vaadin.flow.internal.nodefeature.ElementListenerMap.lambda$fireEvent$2(ElementListenerMap.java:447)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at com.vaadin.flow.internal.nodefeature.ElementListenerMap.fireEvent(ElementListenerMap.java:447)
	at com.vaadin.flow.server.communication.rpc.EventRpcHandler.handleNode(EventRpcHandler.java:62)
	at com.vaadin.flow.server.communication.rpc.AbstractRpcInvocationHandler.handle(AbstractRpcInvocationHandler.java:74)
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocationData(ServerRpcHandler.java:475)
	at com.vaadin.flow.server.communication.ServerRpcHandler.lambda$handleInvocations$5(ServerRpcHandler.java:456)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:456)
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:324)
	at com.vaadin.flow.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:114)
	at com.vaadin.flow.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:40)
	at com.vaadin.flow.server.VaadinService.handleRequest(VaadinService.java:1577)
	at com.vaadin.flow.server.VaadinServlet.service(VaadinServlet.java:398)
	at com.vaadin.flow.spring.SpringServlet.service(SpringServlet.java:106)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:632)
	at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:408)
	at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:303)
	at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:267)
	at org.springframework.web.servlet.mvc.ServletForwardingController.handleRequestInternal(ServletForwardingController.java:142)
	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:178)
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:51)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:547)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at io.jmix.core.impl.logging.LogMdcFilter.doFilterInternal(LogMdcFilter.java:28)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:108)
	at org.springframework.security.web.FilterChainProxy.lambda$doFilterInternal$3(FilterChainProxy.java:231)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:365)
	at org.springframework.security.web.access.intercept.AuthorizationFilter.doFilter(AuthorizationFilter.java:100)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:126)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:120)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:131)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:85)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:100)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:110)
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:101)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:179)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:151)
	at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:129)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:227)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:221)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:107)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:93)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:117)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:82)
	at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:69)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:62)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:233)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:191)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
	at org.springframework.web.servlet.handler.HandlerMappingIntrospector.lambda$createCacheFilter$3(HandlerMappingIntrospector.java:195)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
	at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:74)
	at org.springframework.security.config.annotation.web.configuration.WebMvcSecurityConfiguration$CompositeFilterChainProxy.doFilter(WebMvcSecurityConfiguration.java:230)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:352)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:268)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:384)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
	at java.base/java.lang.Thread.run(Thread.java:833)
#5

I also tried this way as suggested by jmix AI.

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                    .withSchemaName("public") // Specify the schema if needed
                    .withProcedureName("sphr_attendancebuildholidaycalendardetail")
                    .declareParameters(
                            new SqlParameter("holiday_calendar_id", Types.OTHER) // Use Types.OTHER for UUID
                    );

            // Define the input parameters
            Map<String, Object> inParams = new HashMap<>();
            inParams.put("holiday_calendar_id", holidayCalendar.getId()); // Provide the actual UUID value

            // Execute the stored procedure
            Map<String, Object> outParams = jdbcCall.execute(inParams);

But getting the following error:


org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call public.sphr_attendancebuildholidaycalendardetail(?)}]
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:112) ~[spring-jdbc-6.1.13.jar:6.1.13]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.13.jar:6.1.13]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116) ~[spring-jdbc-6.1.13.jar:6.1.13]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548) ~[spring-jdbc-6.1.13.jar:6.1.13]
#6

@taimanov hello, i am also facing something similar . The error i am being shown is: org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - no procedure/function/signature for ‘sphr_attendancebuildholidaycalendardetail’
at org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.processProcedureColumns(GenericCallMetaDataProvider.java:358)
at org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.initializeWithProcedureColumnMetaData(GenericCallMetaDataProvider.java:118)
at org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory.lambda$createMetaDataProvider$0(CallMetaDataProviderFactory.java:137)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:354)
at org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory.createMetaDataProvider(CallMetaDataProviderFactory.java:89)
at org.springframework.jdbc.core.metadata.CallMetaDataContext.initializeMetaData(CallMetaDataContext.java:253)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.compileInternal(AbstractJdbcCall.java:315)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.compile(AbstractJdbcCall.java:298)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.checkCompiled(AbstractJdbcCall.java:355)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:393)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:193)
at com.company.sptest.service.InteaccMDGGeneralBean.buildHolidayCalendarDetail(InteaccMDGGeneralBean.java:74)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:379)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)
at com.company.sptest.service.InteaccMDGGeneralBean$$SpringCGLIB$$0.buildHolidayCalendarDetail()
at com.company.sptest.view.holidaycalendar.HolidayCalendarListView.init(HolidayCalendarListView.java:35)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMethod.invoke(InitDestroyAnnotationBeanPostProcessor.java:457)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:401)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:219)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:422)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1798)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:600)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:522)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:321)
at com.vaadin.flow.spring.SpringInstantiator.getOrCreate(SpringInstantiator.java:127)
at io.jmix.flowui.sys.vaadin.JmixSpringInstantiator.getOrCreate(JmixSpringInstantiator.java:57)
at com.vaadin.flow.di.Instantiator.createRouteTarget(Instantiator.java:169)
at io.jmix.flowui.sys.vaadin.JmixSpringInstantiator.createRouteTarget(JmixSpringInstantiator.java:49)
at com.vaadin.flow.router.internal.AbstractNavigationStateRenderer.lambda$getRouteTarget$1(AbstractNavigationStateRenderer.java:138)
at java.base/java.util.Optional.orElseGet(Optional.java:364)
at com.vaadin.flow.router.internal.AbstractNavigationStateRenderer.getRouteTarget(AbstractNavigationStateRenderer.java:137)
at com.vaadin.flow.router.internal.AbstractNavigationStateRenderer.sendBeforeEnterEventAndPopulateChain(AbstractNavigationStateRenderer.java:486)
at com.vaadin.flow.router.internal.AbstractNavigationStateRenderer.createChainIfEmptyAndExecuteBeforeEnterNavigation(AbstractNavigationStateRenderer.java:467)
at com.vaadin.flow.router.internal.AbstractNavigationStateRenderer.handle(AbstractNavigationStateRenderer.java:217)
at com.vaadin.flow.component.internal.JavaScriptNavigationStateRenderer.handle(JavaScriptNavigationStateRenderer.java:78)
at com.vaadin.flow.component.UI.handleNavigation(UI.java:1853)
at com.vaadin.flow.component.UI.renderViewForRoute(UI.java:1816)
at com.vaadin.flow.component.UI.lambda$connectClient$83bb1bf7$1(UI.java:1724)
at com.vaadin.flow.component.UI.connectClient(UI.java:1734)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at com.vaadin.flow.server.communication.rpc.PublishedServerEventHandlerRpcHandler.invokeMethod(PublishedServerEventHandlerRpcHandler.java:227)
at com.vaadin.flow.server.communication.rpc.PublishedServerEventHandlerRpcHandler.invokeMethod(PublishedServerEventHandlerRpcHandler.java:204)
at com.vaadin.flow.server.communication.rpc.PublishedServerEventHandlerRpcHandler.invokeMethod(PublishedServerEventHandlerRpcHandler.java:150)
at com.vaadin.flow.server.communication.rpc.PublishedServerEventHandlerRpcHandler.handleNode(PublishedServerEventHandlerRpcHandler.java:133)
at com.vaadin.flow.server.communication.rpc.AbstractRpcInvocationHandler.handle(AbstractRpcInvocationHandler.java:74)
at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocationData(ServerRpcHandler.java:475)
at com.vaadin.flow.server.communication.ServerRpcHandler.lambda$handleInvocations$5(ServerRpcHandler.java:456)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:456)
at com.vaadin.flow.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:324)
at com.vaadin.flow.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:114)
at com.vaadin.flow.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:40)
at com.vaadin.flow.server.VaadinService.handleRequest(VaadinService.java:1577)
at com.vaadin.flow.server.VaadinServlet.service(VaadinServlet.java:398)
at com.vaadin.flow.spring.SpringServlet.service(SpringServlet.java:106)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:632)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:408)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:303)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:267)
at org.springframework.web.servlet.mvc.ServletForwardingController.handleRequestInternal(ServletForwardingController.java:142)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:178)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:51)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:547)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at io.jmix.core.impl.logging.LogMdcFilter.doFilterInternal(LogMdcFilter.java:28)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:108)
at org.springframework.security.web.FilterChainProxy.lambda$doFilterInternal$3(FilterChainProxy.java:231)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:365)
at org.springframework.security.web.access.intercept.AuthorizationFilter.doFilter(AuthorizationFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:126)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:120)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:131)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:85)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:110)
at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:101)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:179)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:151)
at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:129)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:227)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:221)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:107)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:93)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:117)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:82)
at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:69)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:62)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:233)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:191)
at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
at org.springframework.web.servlet.handler.HandlerMappingIntrospector.lambda$createCacheFilter$3(HandlerMappingIntrospector.java:195)
at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:74)
at org.springframework.security.config.annotation.web.configuration.WebMvcSecurityConfiguration$CompositeFilterChainProxy.doFilter(WebMvcSecurityConfiguration.java:230)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:352)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:268)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:384)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
at java.base/java.lang.Thread.run(Thread.java:1583)

here is my sample application: sp-test.zip - Google Drive
this is how the procedure was called :

@Transactional
public void buildHolidayCalendarDetail(HolidayCalendar holidayCalendar) {

    String databaseType = detectDatabaseType();
    UUID holidayCalendarId = holidayCalendar.getId();

    if ("PostgreSQL".equalsIgnoreCase(databaseType)) {
        log.info("Connected to PostgreSQL");


        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withSchemaName("dbo") // Replace with actual schema if different
                .withProcedureName("sphr_attendancebuildholidaycalendardetail")
                .declareParameters(
                        new SqlParameter("holiday_calendar_id", Types.OTHER) // Using Types.OTHER for UUID in PostgreSQL
                );

        // Define the input parameters
        Map<String, Object> inParams = new HashMap<>();
        inParams.put("holiday_calendar_id", holidayCalendarId);

        // Execute the stored procedure
        jdbcCall.execute(inParams);

    }
#7

Thanks @ftanvir50 for the sample app.

Hi @taimanov
Looking forward for your help.

#8

Hello @mortoza_khan, @ftanvir50!

Thank you for the example project! Could you please also add the sphr_attendancebuildholidaycalendardetail stored procedure definition too?
I will dig into it in the nearest future and return as soon as I get any result.

Regards,
Dmitry

#9

Hi @taimanov , here is a short description of the procedure: This stored procedure builds a detailed holiday calendar for a specified holidayCalendarId and current leave year by adding fixed, floating, and weekend holidays. First, it deletes any existing holiday data for that calendar and year to avoid duplicates. It then inserts fixed holidays by checking if each holiday date falls within the current or next leave year. Next, it adds floating holidays directly based on specific dates provided in a separate table. Finally, it loops through each day in the leave year, adding weekends to the calendar based on predefined weekend days, and handles all operations within a transaction to maintain data integrity.

The definition of the procedure is as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: inteacc.com
– Create date: May 2021
– Last update: 03 Jun 2024
– Update 18 Oct 2024, Mortoza
– Description: <Description,>
– =============================================

ALTER PROCEDURE [dbo].[spHR_AttendanceBuildHolidayCalendarDetail]
@holidayCalendarId uniqueidentifier

AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @leaveYearStartDate DATE;
DECLARE @leaveYearEndDate DATE;
DECLARE @currentDate DATE;
DECLARE @leaveYearId uniqueidentifier

-- Get the start and end date of the leave year
SELECT @leaveYearStartDate = y.START_DATE, @leaveYearEndDate = y.END_DATE, @leaveYearId = y.ID  
	FROM MDG_LEAVE_YEAR y WHERE y.CURRENT_LEAVE_YEAR = 1;

-- Initialize the current date to the start date of the leave year
SET @currentDate = @leaveYearStartDate;

BEGIN TRY
    BEGIN TRAN;

    -- Delete existing holiday calendar details for the given year and calendar ID
    DELETE FROM MDG_HOLIDAY_CALENDAR_DETAIL 
    WHERE LEAVE_YEAR_ID = @leaveYearId AND HOLIDAY_CALENDAR_ID = @holidayCalendarId;

--1 - Fixed leave dates
--============================
INSERT INTO MDG_HOLIDAY_CALENDAR_DETAIL 
			(ID, VERSION, CREATED_DATE, HOLIDAY_DATE, HOLIDAY_CALENDAR_ID, HOLIDAY_ID, LEAVE_YEAR_ID, HOLIDAY_WEEKEND)
		SELECT 
			NEWID(), 1, CURRENT_TIMESTAMP, 
			-- Dynamically calculate the holiday date based on the leave year
			CASE 
				WHEN 
					DATEFROMPARTS(YEAR(ly.START_DATE), h.MONTH_, h.DAY_) BETWEEN ly.START_DATE AND ly.END_DATE 
					THEN DATEFROMPARTS(YEAR(ly.START_DATE), h.MONTH_, h.DAY_)  -- Holiday date within the current leave year
				--WHEN 
				else
					--DATEFROMPARTS(YEAR(ly.START_DATE) + 1, h.MONTH_, h.DAY_) BETWEEN ly.START_DATE AND ly.END_DATE 
					--THEN 
					DATEFROMPARTS(YEAR(ly.START_DATE) + 1, h.MONTH_, h.DAY_)  -- Holiday date in the next year within the leave year
						
				--ELSE 
					--NULL  -- Exclude holidays that do not fall within the leave year
			END , 
			
			@holidayCalendarId, e.HOLIDAY_ID, @leaveYearId, 10  -- HOLIDAY_WEEKEND value
			FROM 
				MDG_HOLIDAY_CALENDAR_DAY e
			INNER JOIN 
				MDG_HOLIDAY h ON e.HOLIDAY_ID = h.ID 
			INNER JOIN 
				MDG_LEAVE_YEAR ly ON ly.ID = @leaveYearId
			WHERE 
				h.HOLIDAY_TYPE = 10  -- Filter by holiday type
				AND e.HOLIDAY_CALENDAR_ID = @holidayCalendarId  -- Filter by holiday calendar ID
			--	AND HOLIDAY_DATE IS NOT NULL;  -- Ensure only valid holiday dates are inserted

--2- floating leave dates
--=====================
INSERT INTO MDG_HOLIDAY_CALENDAR_DETAIL 
(ID, VERSION, CREATED_DATE, HOLIDAY_DATE, HOLIDAY_CALENDAR_ID, HOLIDAY_ID, LEAVE_YEAR_ID, HOLIDAY_WEEKEND)
SELECT 
	NEWID(), 
	1, 
	CURRENT_TIMESTAMP, 
	-- Use the HOLIDAY_DATE directly from MDG_HOLIDAY_CALENDAR_DAY_FLOAT
	fh.HOLIDAY_DATE, 
	@holidayCalendarId, 
	h.ID, 
	@leaveYearId, 
	10  -- HOLIDAY_WEEKEND value
FROM 
	MDG_HOLIDAY_CALENDAR_DAY_FLOAT fh
INNER JOIN 
	MDG_HOLIDAY_CALENDAR_DAY hcd ON fh.HOLIDAY_CALENDAR_DAY_ID = hcd.ID 
INNER JOIN 
	MDG_HOLIDAY h ON hcd.HOLIDAY_ID = h.ID
INNER JOIN 
	MDG_LEAVE_YEAR ly ON ly.ID = @leaveYearId
WHERE 
	-- Ensure that the HOLIDAY_DATE falls within the leave year start and end dates
	fh.HOLIDAY_DATE BETWEEN ly.START_DATE AND ly.END_DATE
	AND h.HOLIDAY_TYPE = 20  -- Assuming 20 is the type for floating holidays
	AND hcd.HOLIDAY_CALENDAR_ID = @holidayCalendarId;  -- Filter by holiday calendar ID


---3 WEEKENDS
--=================================
 
    -- Insert weekends for the entire leave year
  WHILE @currentDate <= @leaveYearEndDate
	BEGIN
		DECLARE @dayName VARCHAR(255);
		SET @dayName = DATENAME(WEEKDAY, @currentDate);

		IF EXISTS (
			SELECT 1 
			FROM MDG_HOLIDAY_CALENDAR_WEEK_END we 
			WHERE we.DAY_NAME = @dayName 
			AND we.HOLIDAY_CALENDAR_ID = @holidayCalendarId -- Added condition to match holiday calendar ID
		)
		BEGIN
			INSERT INTO MDG_HOLIDAY_CALENDAR_DETAIL (ID, VERSION, CREATED_DATE, HOLIDAY_DATE, HOLIDAY_CALENDAR_ID, HOLIDAY_ID, LEAVE_YEAR_ID, HOLIDAY_WEEKEND)
			VALUES (
				NEWID(),
				1,
				CURRENT_TIMESTAMP,
				@currentDate,
				@holidayCalendarId,
				NULL, -- Set to NULL since it's a weekend
				@leaveYearId, 
				20
			);
		END;

		-- Move to the next day
		SET @currentDate = DATEADD(DAY, 1, @currentDate);
	END;


    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN;
    
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

END;
GO

If you need any more information please let me know.

#10

Hi @taimanov
Do you have any update for us? Thank you in advance for your help.
regards

#11

Hello!

I’ve looked into the project and procedure.
The procedure is called through spring jdbc api and problem doesn’t look like related with Jmix.

Here is an example of the project with sucessfully working procedure call: jmix-samples-2/stored-procedures-sample at main · jmix-framework/jmix-samples-2 · GitHub

Maybe defining the procedure in more standard way like in project above will help?

Regards,
Dmitry

#12

Thank you Dmitry. It worked.