Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Calling sql function through javascript node OR sql node

Ramaswami
Level 4
Level 4

Hi Team, 

 

I have a SQL function which is returning some value and i need to check what is the value it is returning. Do we have any javascript function in adobe check what the function is returning through a workflow. 

 

i tried like below inside javascript node of workflow but it errored out: 

nova_offer_validateActiveOfferCountTest(x integer) : this is my postgre function 

 

 function getCampaignLabel1()
{
return sqlGetString(nova_offer_validateActiveOfferCountTest(5560752))
}
logInfo("Function is returning:"+getCampaignLabel1())

 

SQL function under Administration>Configuration>SQL scripts>rsn:postgresql-rsn-novaOffer.sql : 

 

CREATE OR REPLACE FUNCTION nova_offer_validateActiveOfferCountTest(P_IOPERATIONID INTEGER)
RETURNS VARCHAR AS $func$
DECLARE
L_overActiveOfferLimit INTEGER := 0;
OFFERCOUNT INTEGER :=0;
L_errorCode VARCHAR(8) := '1';
BEGIN
SELECT IRSNMAXACTIVENOVAOFFERS INTO L_overActiveOfferLimit
FROM NMSOPERATION no
WHERE no.IOPERATIONID = P_IOPERATIONID;
SELECT COUNT(RNO.INOVAOFFERSID) cnt INTO OFFERCOUNT
FROM RN_NOVA_OFFER RNO
WHERE RNO.IOPERATIONID = P_IOPERATIONID
AND RNO.IACTIVE = 1;
if OFFERCOUNT > L_overActiveOfferLimit then
L_errorCode := '0';
END IF;
RETURN L_errorCode;
EXCEPTION
WHEN others THEN
RAISE EXCEPTION 'nova_offer__validateActiveOfferCount ERROR: %', SQLERRM;
END;
$func$ language plpgsql;

1 Accepted Solution
wodnicki
Correct answer by
Community Advisor
Community Advisor
4 Replies
wodnicki
Correct answer by
Community Advisor
Community Advisor
Ramaswami
Level 4
Level 4
Hi Nick, Thanks for the reply. Here my function already exist under (SQL function under Administration>Configuration>SQL scripts>rsn:postgresql-rsn-novaOffer.sql) and i have compiled the sql script successfull and i am using the function name in the function which you posted "sqlGetString()" but it is erroring out saying it does not found the function. So if we have the function in sql script this is not enough for a javascript in a workflow to call that function? OR do we need to do anything additional. Can you explain the steps how to call this function through javascript?
wodnicki
Community Advisor
Community Advisor
You can call the function directly with raw sql, e.g. sqlGetString(). To use xtk you have to follow the posted guide.