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.