Is there any way i can consume responses (multiple) of OUT clause of @Procedure

[satscreate]

My repository calling stored procedure like this,

@Repository

@Procedure(procedureName = "sp_name")
Map<String, Integer> callingSP();

sp_name is look like this

CREATE PROCEDURE [dbo].[sp_name] AS BEGIN
SET NOCOUNT ON;
MERGE dbo.table1 AS Target
USING (
    SELECT *
    FROM
      dbo.table2
  )
  AS Source
ON
  (
    Target.id = Source.id
  )
WHEN MATCHED
  THEN
  UPDATE
  ...
  WHEN NOT MATCHED BY TARGET THEN
  INSERT
    ....
    OUTPUT DELETED.ID as DELETED , $action AS [Action] , INSERTED.ID as INSERTED
  ;
END;
GO

Am trying to see if I could return a map of key and values pair of results from sql server stored procedure but am facing below error,

Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null"

Is it possible to return such a Map entries from CrudRepository by calling database procedure name using just @Procedure annotation?

Note: am not using any names procedure queries btw and calling generic procedure with no input expecting OUTPUT DELETED.ID as DELETED , $action AS [Action] , INSERTED.ID as INSERTED parameter results.

Questions:

1. Does @Procedure support multiple output parameter?
2. Does @Procedure support object return type?
3. If this is not something achievable with @procedure annotation why not this as a feature request?
4. I do not want to specify any NamedStoredProcedure as my database stored procedure is playing with multiple table and return some OUTPUT clause results or @table as an object. from application side only calling the SP and getting the results using @procedure annotation is the requirement.

[schauder]

In principle this does work. See this integration test for reference: https://github.com/spring-projects/spring-data-jpa/blob/899f8f97b873a73c87bc733142f343b109d7952a/src/test/java/org/springframework/data/jpa/repository/UserRepositoryStoredProcedureIntegrationTests.java#L95

We would need a reproducer for this, which includes the database used either as a in-memory database or using test-containers. Also a full stack trace is always welcome when something breaks.

[schauder]

Cross posted as https://stackoverflow.com/questions/66329964/spring-jpa-procedure-not-working-correctly-with-multiple-out-clause

[satscreate]

In principle this does work. See this integration test for reference:

https://github.com/spring-projects/spring-data-jpa/blob/899f8f97b873a73c87bc733142f343b109d7952a/src/test/java/org/springframework/data/jpa/repository/UserRepositoryStoredProcedureIntegrationTests.java#L95

We would need a reproducer for this, which includes the database used either as a in-memory database or using test-containers. Also a full stack trace is always welcome when something breaks.

Its not using inmemory database. Using SQL Server Procedure. Trying to add the reproducible code here.

[satscreate]

In principle this does work. See this integration test for reference: https://github.com/spring-projects/spring-data-jpa/blob/899f8f97b873a73c87bc733142f343b109d7952a/src/test/java/org/springframework/data/jpa/repository/UserRepositoryStoredProcedureIntegrationTests.java#L95

We would need a reproducer for this, which includes the database used either as a in-memory database or using test-containers. Also a full stack trace is always welcome when something breaks.

Its not using inmemory database. Using SQL Server Procedure. Trying to add the reproducible code here.

Hi @schauder Please find the code example test.zip

It does have swagger url exposed with below link, http://localhost:8080/swagger-ui/index.html?configUrl=/v3/api-docs/swagger-config#/

There are two API's,

Controller:

@RestController
@RequestMapping("/")
public class DatabaseController {

    private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseController.class);

    @Autowired
    private DatabaseRepository repository;

    @GetMapping(path = "test1", produces = MediaType.APPLICATION_JSON_VALUE)
    public Map<String, Object> callProcedure1() {
        LOGGER.info("Inside  -->/callProcedure1");
        Map<String, Object> response = repository.callProcedure1();
        return response;
    }

    @GetMapping(path = "test2", produces = MediaType.APPLICATION_JSON_VALUE)
    public void callProcedure2() {
        LOGGER.info("Inside  -->/callProcedure2");
        repository.callProcedure2();
    }

}

Repository :

@Repository
@Transactional
public interface DatabaseRepository extends JpaRepository<UpdatedProducts, String> {

    @Procedure(procedureName = "SP_NAME")
    Map<String, Object> callProcedure1();

    @Procedure(procedureName = "SP_NAME")
    void callProcedure2();

}

Table & Stored Procedure Entire SQL:

--Create a target table
CREATE TABLE Products
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
) 
GO

--Insert records into target table
INSERT INTO Products
VALUES
   (1, 'Tea', 10.00),
   (2, 'Coffee', 20.00),
   (3, 'Muffin', 30.00),
   (4, 'Biscuit', 40.00)
GO

--Create source table
CREATE TABLE UpdatedProducts
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
) 
GO

--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
   (1, 'Tea', 10.00),
   (2, 'Coffee', 25.00),
   (3, 'Muffin', 35.00),
   (5, 'Pizza', 60.00)
GO

SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

CREATE PROCEDURE SP_NAME AS BEGIN
SET NOCOUNT ON;
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate 
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 
--$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
--one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE' according to the action that was performed on that row
OUTPUT $action as [Action], 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 
END;
GO

Execute SP: controller endpoints will call this statement. if the repository does not have return type then its calling SP_NAME. When Map<String,Object> then its not.

EXEC SP_NAME;

Exception trace from test1 endpoint:

{
  "timestamp": "2021-02-23T10:20:40.905+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "trace": "org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null\r\n\tat org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374)\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)\r\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)\r\n\tat org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)\r\n\tat org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)\r\n\tat com.sun.proxy.$Proxy91.callProcedure1(Unknown Source)\r\n\tat com.test.controllers.DatabaseController.callProcedure1(DatabaseController.java:28)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)\r\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)\r\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\r\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)\r\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)\r\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)\r\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:626)\r\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:733)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)\r\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)\r\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)\r\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)\r\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)\r\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)\r\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)\r\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)\r\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)\r\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)\r\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)\r\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)\r\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\r\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\r\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\r\n\tat java.lang.Thread.run(Thread.java:748)\r\nCaused by: java.lang.IllegalArgumentException: Type cannot be null\r\n\tat org.hibernate.query.procedure.internal.ProcedureParameterImpl.setHibernateType(ProcedureParameterImpl.java:130)\r\n\tat org.hibernate.query.procedure.internal.ProcedureParameterImpl.<init>(ProcedureParameterImpl.java:88)\r\n\tat org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:290)\r\n\tat org.hibernate.procedure.internal.ProcedureCallImpl.registerStoredProcedureParameter(ProcedureCallImpl.java:579)\r\n\tat org.hibernate.procedure.internal.ProcedureCallImpl.registerStoredProcedureParameter(ProcedureCallImpl.java:70)\r\n\tat org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.lambda$newAdhocStoredProcedureQuery$1(StoredProcedureJpaQuery.java:228)\r\n\tat java.util.stream.Streams$RangeIntSpliterator.forEachRemaining(Streams.java:110)\r\n\tat java.util.stream.IntPipeline$Head.forEach(IntPipeline.java:559)\r\n\tat org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.newAdhocStoredProcedureQuery(StoredProcedureJpaQuery.java:219)\r\n\tat org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createStoredProcedure(StoredProcedureJpaQuery.java:179)\r\n\tat org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.doCreateQuery(StoredProcedureJpaQuery.java:103)\r\n\tat org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createQuery(StoredProcedureJpaQuery.java:93)\r\n\tat org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:314)\r\n\tat org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)\r\n\tat org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)\r\n\tat org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)\r\n\tat org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)\r\n\tat org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)\r\n\tat org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)\r\n\tat org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)\r\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)\r\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)\r\n\t... 58 more\r\n",
  "message": "Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null",
  "path": "/test1"
}

To run the code change ADD_YOURS from application.yml:

spring:
  datasource:
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://ADD_YOURS:2048;databaseName=ADD_YOURS
    username: ADD_YOURS
    password: ADD_YOURS

Please let me know if this helps?

[satscreate]

Any updates @schauder !!

[satscreate]

Any Updates @schauder

[schauder]

Please provide a reproducer that includes the database, not one that includes instructions for a database. Using Testcontainers works fine for that when an in-memory database is not sufficient

Also please abstain from including parts that are not relevant to the issue (like a REST API).

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.