fn!executestoredprocedure error

Question

simple expression rule using fn!executestoredprocedure gives me "java.lang.Integer cannot be cast to java.lang.Long" error, while smart service works fine in process model.

SP code:
CREATE PROCEDURE SCL_SumContract_Dealer (
IN dealer_id int,
OUT total_value int
)
BEGIN
SELECT SUM(price) INTO total_value FROM contract
WHERE contractstatus_status_id = 1 AND dealer_dealer_id = dealer_id;
END

test_sp.txt

Sally Mason · Accepted Answer

As fn!executestoredprocedure() is part of the shared component, "Execute Stored Procedure" and is designed to be used in a Process Model, so that may explain the variation/why the behavior is different outside the Process Model

Another idea is using MySQL to create a new View at the Database level and use MySQL cast() as UNSIGNED to handle the conversion from SMALLINT, then query the view eg:
---
CREATE VIEW SCL_SumContract_Dealer_View AS SELECT CAST(total_value AS UNSIGNED) FROM SCL_SumContract_Dealer`;
---

Or, potentially use the SELECT statement in executestoredprocedure() to cast() as:
---
SELECT CAST(total_value AS UNSIGNED) FROM SCL_SumContract_Dealer`;
---

Let us know if that works in your scenario?

Philip Kang · Answer

Figure it out. When I change the SP OUT parameter to UNSIGNED, it gets working! Thanks a lot for the tips. I would not able to figure it out otherwise.