I want to respond to a URL request from another platform (OptiSigns) where I am able to attach data.

[mrgillus1976]

I am working with a Digital Signage platform and am trying to pass data from a SQL stored procedure to OptiSigns. OptiSigns accepts data through a JSON/XML URL and although I'm able to figure out how to output that internally through IIS, I need to be able to get it to output through our Workato on prem agent so OptiSigns can get to the URL. 

In any case, I've tried so many different things but I'm unsure of how to get this to work correctly. The basic concept is that I enter a URL into OptiSigns where it will call that URL and the response it gets should be the data of the stored procedure (just one row of data in this result) formatted as XML or JSON.

Thanks in advance!

[Prudvi]

Hi @mrgillus1976,
If you can leverage API platform service, you can achieve your requirement.

1. You must create an API endpoint which can accepts parameters as per your requirement. [If you need to extract different values from Stored Procedure.]
2. The endpoint would be connected to a recipe where it will internally call SQL to fetch the data.
3. You can transform your data to either JSON or XML and send it as the response of the API.

Regards,
Prudvi

[Obugari]

Hi @mrgillus1976 ,
For your scenario you can go with below approach also ,
1.Create a Workato Recipe with a Webhook Trigger
Trigger: Choose "Webhook – Trigger when a webhook is received"
Workato will give you a public URL (e.g., https://www.workato.com/webhooks/abc123...).This is the URL you will enter in OptiSigns.Add Input Parameters (optional):
You can set parameters in the URL query (e.g., ?screen=welcome) and use those in your SQL stored procedure if needed.
2.Action – Run SQL Stored Procedure
Add the “Execute Stored Procedure” action.Choose your SQL connection (via On-Prem Agent).Select the stored procedure and pass any input parameters.The result will be available in the output.Ensure the stored procedure returns only one row as required.
3.Format Output -
Depending on what OptiSigns expects:If JSON: Just return the stored procedure result directly.If XML: Use Workato’s “Create XML” action or format it manually.
4.Response to OptiSigns - Use the “Return Response to Webhook” action: Set response type: JSON or XML.Pass the formatted result.
5.This sends the response back to OptiSigns when it calls the webhook URL.