I'm having a problem trying to access a specific stored procedure in SQL Server from Java . The stored procedure receives an XML as a parameter, and returns another. An example call is as follows:
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 itself (with real data) works fine, and returns the desired XML . I did the test to execute the procedure from a C# program and it also works correctly. However from a Java application I don't get any response.
At first I thought it could be a connectivity problem from Java, but no, since other procedures with exactly the same structure return a response . So I started debugging this procedure and discovered that several parts of the code were what were breaking the execution and that, if I commented them out, they made it work correctly. They are the following:
declare @Fechas TABLE (
[fecha] [smalldatetime] NOT NULL
)
INSERT INTO @fechas VALUES (@fecha)
Every time a TABLE variable was declared in the procedure and some kind of insert was done to it, the procedure call from Java returned nothing. Searching for a solution, I found this post on Stack Overflow. Which talks about different behaviors with these TABLE variables depending on the application used, returning results in Java that can be interpreted as the execution of the procedure has already finished.
The call is being made as follows:
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.