Problem calling procedure stored from Java

2

I have a problem trying to access a specific procedure stored in SQL Server from Java . The stored procedure receives a XML as a parameter, and returns another. An example of a call is the following:

DECLARE @xml AS NVARCHAR(2000)
DECLARE @result AS NVARCHAR(2000)

SET @xml='<ROOT>
            <PARAMETROS>
              <PARAM valor="..." nombre="..."/>
              <PARAM valor="..." nombre="..."/>
              (...)
            </PARAMETROS>
          </ROOT>'
SET @result=''

EXEC dbo.procedimiento @xml, @result output

This call from SQL Server (with real data) works fine, and returns the XML desired. I did the test to run the procedure from a program in C # and it also works correctly. However from a Java application I do not receive any response.

At first I thought it might be a connectivity problem from Java, but no, since other procedures with exactly the same structure give me a response . So I started debugging this procedure and discovered that several parts of the code were the ones that were cutting the execution and that, if I commented, they made it work correctly. They are the following:

declare @Fechas TABLE (
  [fecha] [smalldatetime] NOT NULL 
)

INSERT INTO @fechas VALUES (@fecha)

Every time in the procedure a variable TABLE was declared and some type of insertion was made in it, the call to the procedure from Java did not return anything to me. Looking for a solution, I found this post in Stack Overflow. That talks about different behaviors with these variables TABLE depending on the application that is used, returning results in Java that can be interpreted as having finished the execution of the procedure.

The call is being made in the following way:

Object result = getEm().createNativeQuery("Exec [dbo].[procedimiento] :xml, :result")
    .setHint("javax.persistence.query.timeout", 10000)
    .setParameter("xml", xml)
    .setParameter("result", result)
    .getResultList();

What would be the best way to solve this? Thank you very much.

    
asked by dn. 04.10.2018 в 11:45
source

1 answer

1

I found a solution. As I commented in the question, every time an insert in a variable TABLE was made in the procedure, the count of the affected rows is returned. This in C # or in the SQL Server did not affect at all, since I did not take it into account. But Java interpreted it as the final result and cut the execution.

The best way I found to avoid this is to modify the procedure and define at the beginning of it the property NOCOUNT a ON . As follows:

SET NOCOUNT ON;  
GO  
-- Código del procedimiento almacenado.

I also attach the link to official documentation .

    
answered by 11.10.2018 / 13:13
source