Calling sql function through javascript node OR sql node | Community
Skip to main content
Level 4
March 18, 2020
Solved

Calling sql function through javascript node OR sql node

  • March 18, 2020
  • 1 reply
  • 5327 views

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;

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jonathon_wodnicki

Hi,

 

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

 

Thanks,

-Jon

1 reply

Jonathon_wodnicki
Community Advisor
Jonathon_wodnickiCommunity AdvisorAccepted solution
Community Advisor
March 18, 2020
RamaswamiAuthor
Level 4
March 19, 2020
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?