Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

[SOLVED] How can I execute custom javascript in WebFocus 9.1.1 to send an API call to Snowflake


Erin Trotter
Go to solution Solved by Patrick Huebgen,

Recommended Posts

So, we had the need to trigger a Snowflake stored procedure through WebFocus. However, we were unable to connect to Snowflake stored procedures the way we can to SQL stored procedures using the Snowflake connector, ODBC connector, or JDBC connector. So, we wrote some custom javascript code that sends a post and fetch through an API that we created and it works when running in Postman to Snowflake. However, we need to create a button on a WebFocus dashboard that will execute this custom code and trigger the stored procedure like we can through Postman. I Have tried adding the code into App Studio, but the button event doesn't respond at all. I also tried adding it into the Designer page javascript section and am having a problem even getting the button to appear there.

Anyone have any tips for this? Or has anyone written any custom Javascript to send an API call to an application outside of WebFocus and had it work successfully?

Link to comment
Share on other sites

  • Solution

You can create a Snowflake procedure ike this sample with JavaScript

CREATE OR REPLACE PROCEDURE my_test_1(

   VALUE1 double,

   VALUE2 double

   )

   returns double

   language javascript

   strict

   execute as caller

   as $$

   var result = VALUE1 + VALUE2;

  

    return result;

   $$;

 and create a WebFOCUS Report like this:

-SET &value1 = 5;

-SET &value2 = 2;

ENGINE SQLSNO SET DEFAULT_CONNECTION <NAME_OF_YOUR_CONNECTION_HERE>

SQL SQLSNO EX <ADD_TABLE_SCHEMA_HERE>.my_test_1 &value1, &value2;

TABLE FILE SQLOUT

PRINT *

END

-RUN

Instead of sending the values via SET you can of course create a Designer form to send the parameters from a prompt.

Patrick

Link to comment
Share on other sites

It seems like I'm almost there but not quite. Below is my Snowflake procedure code and my webfocus report code

CREATE OR REPLACE PROCEDURE SP_FACT_RECEIVABLE_AGING(USERNAME VARCHAR(100))RETURNS VARCHAR(16777216)LANGUAGE SQLEXECUTE AS OWNERAS $$BEGIN    INSERT INTO TESTTABLE(USERNAME, CURRENTDATETIME) VALUES(:USERNAME, current_timestamp());  return 0;  END $$;-SET &USERNAME = &FOCSECUSER; ENGINE SQLSNO SET DEFAULT_CONNECTION SF-FINANCE-TEST SQL SQLSNO EX FINANCETEST.SP_FACT_RECEIVABLE_AGING &USERNAME.QUOTEDSTRING; TABLE FILE SQLOUTPRINT *END-RUN

Error message comes back saying "Scoped transaction started in stored procedure is incomplete and it was rolled back." I looked that up and it says something about having a try / catch and no commit or rollback in the code, but I don't have a try / catch block as you can see.

Link to comment
Share on other sites

After searching some more, I added another tweak to my procedure and now it works!! Thank you so much @Patrick Huebgen​ !

Here was the procedure solution:

CREATE OR REPLACE PROCEDURE SP_FACT_RECEIVABLE_AGING(USERNAME VARCHAR(100))RETURNS VARCHAR(16777216)LANGUAGE SQLEXECUTE AS OWNERAS $$BEGIN BEGIN TRANSACTION; INSERT INTO TESTTABLE(USERNAME, CURRENTDATETIME) VALUES(:USERNAME, current_timestamp()); COMMIT;  return 1;EXCEPTION WHEN OTHER THEN ROLLBACK; RAISE;   END $$;
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...