Erin Trotter Posted May 2, 2023 Share Posted May 2, 2023 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 More sharing options...
Solution Patrick Huebgen Posted May 2, 2023 Solution Share Posted May 2, 2023 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 SQLOUTPRINT *END-RUNInstead 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 More sharing options...
Erin Trotter Posted May 2, 2023 Author Share Posted May 2, 2023 It seems like I'm almost there but not quite. Below is my Snowflake procedure code and my webfocus report codeCREATE 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-RUNError 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 More sharing options...
Erin Trotter Posted May 2, 2023 Author Share Posted May 2, 2023 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now