Calling sql function through javascript node OR sql node

Ramaswami

18-03-2020

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;

Accepted Solutions (1)

Accepted Solutions (1)

wodnicki

MVP

18-03-2020

Hi,

 

Use sqlGetString(). If you want to expose the function to xtk, follow this guide:

 

Thanks,

-Jon

Answers (0)