Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

Calling sql function through javascript node OR sql node

Avatar

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

Avatar

Correct answer by
Community Advisor
4 Replies

Avatar

Correct answer by
Community Advisor

Hi,

 

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

 

Thanks,

-Jon

Avatar

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?

Avatar

Community Advisor
You can call the function directly with raw sql, e.g. sqlGetString(). To use xtk you have to follow the posted guide.