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

[Erin Trotter]

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?

[Patrick Huebgen]

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

[Erin Trotter]

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.

[Erin Trotter]

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 $$;