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.
I found a solution. As I mentioned in the question, each time an insertion was made in a TABLE variable in the procedure, the count of the affected rows is returned. This in C # or in SQL Server itself did not affect anything, since it was not taken into account. But Java interpreted it as the final result and cut the execution.
NOCOUNT
The best way I found to avoid this is to modify the procedure and define the a property at the beginning of itON
. As follows:I also attach the link to the official documentation .