No results from stored procedure on mssql

Support
#1

Hello,
I have a a stored function on mssql which works ok when tested from SSMS.

Calling it from a cuba service modul (shown below) causes the error:
‘Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.14-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.’

I tried hard for two days, hours and hours of crawling the web…any modifications on the call causes syntax problems. Don’t see what’s going wrong.
Does anyone have an idea?
Thanks a lot!
Willi

the SP:

CREATE OR ALTER FUNCTION [dbo].[fn_getInterestCalculation]
(
	@p_dDateRef date,
	@p_DateFrom date,
	@p_DateTo date,
	@p_Amount decimal(12,2),
	@p_RateHike decimal(12,2)
)
RETURNS decimal(12,2)
AS
BEGIN
	
	DECLARE @InterestAmount decimal(12,2) -- RetVal
		:
		:
		:
	-- some DB - access & logics
		:
		:
		:
		:
	-- Return the result of the function
	RETURN @InterestAmount
END

the java call:

        sParm = "@p_dDateRef = '" + sD1 +
                "', @p_DateFrom = '" + sD2 +
                "', @p_DateTo = '" + sD3 +
                "', @p_Amount = " + nAmount +
                ", @p_RateHike = " + nRate;

        Transaction tx = persistence.getTransaction();
        try {
            EntityManager em = persistence.getEntityManager();
            nInterest = (Double) em.createNativeQuery("execute dbo.fn_getInterestCalculation " + sParm).getSingleResult();

            tx.commit();
        }catch (Exception e) {
            e.getMessage();
        }finally {
            tx.end();
        }

test script works fine:

declare @output decimal(12,2)
execute @output = dbo.fn_getInterestCalculation
@p_dDateRef = '2003-04-01',
@p_DateFrom = '2003-04-07',
@p_DateTo = '2019-08-01',
@p_Amount = 83658.26,
@p_RateHike = 5
select @output
#2

Hello Willi,

I work with postrgresDB and do not use stored procedures. So I can only send you my ideas how to come around the problem
a) reduce the problem: Try to call a procedures without any parameters that does nothing than logging. If that works, get complexer
b) check DB logs
c) check user access rights (grand all to the user)
d) debug into the JDBC Implementation

Good luck!
…

#3

@willi
For SQL Server database, I use stored procedure within which functions are called. I’m not sure if we can use SQL server functions from CUBA platform but sure that we can use Stored Procedure.

#4

Hi Mortoza, thanks for the hint. I’m just on the way with that.
Gettting some inspirations on @Mike’s (thanks) thoughts.
Meanwhile, for testing puproses, I inserted the results to a DB-Table (only possible from a procedure),
so the proc internals are ok. It’s all about getting the results back.

#5

Maybe that advice will help?

#6

Hi Wisler
If you want to getting the results back, you can try the following code:

        StoredProcedureQuery storedProcedure = em.getDelegate().createStoredProcedureQuery("spLeaveBalanceUpdateCompany");
        // set parameters
        storedProcedure.registerStoredProcedureParameter("compId", String.class, ParameterMode.IN);
        storedProcedure.registerStoredProcedureParameter("leaveYearId", String.class, ParameterMode.IN);

//        storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);
        storedProcedure.setParameter("compId", company.getId().toString());
        storedProcedure.setParameter("leaveYearId", leaveYear.getId().toString());

        storedProcedure.execute();
// get result
//        Double tax = (Double)storedProcedure.getOutputParameterValue("tax");

Here ParameterMode.OUT is the one you need.

#7

Problem solved. It’s that simple.
Thx for your support.

Java part:

        sParm = "'" + sD1 + 
                "', " + "'" + 
                sD2 + "', " + "'" + 
                sD3 + "', " + 
                nAmount + ", " + 
                nRate;

        Transaction tx = persistence.getTransaction();
        try {
			EntityManager em = persistence.getEntityManager();
			List result = em.createNativeQuery("dbo.pr_getInterestCalculation " + sParm).getResultList();
			nInterestAmount = Double.valueOf(result.get(0).toString());
			:
			:
			:
			:

SQL part:

CREATE OR ALTER PROCEDURE [dbo].[pr_getInterestCalculation]
(
	@p_dDateRef date,
	@p_DateFrom date,
	@p_DateTo date,
	@p_Amount decimal(12,2),
	@p_RateHike decimal(12,2),
	@nInterestAmount decimal(12,2) = NULL OUTPUT
)
AS
	:
	:
	:
	:	
SELECT @nInterestAmount