Differences

This shows you the differences between two versions of the page.

Link to this comparison view

chapi_url_endpoint_listener [2019/01/30 09:22] (current)
Line 1: Line 1:
 +====== Description ======
 +
 +
 +
 +This page describes functionality in CHAPI that provides a TCP endpoint that a website can hit to trigger a stored procedure in the StoreData database. This functionality may be used in the following scenarios:
 +  * Allow a website to initiate an SQL import into Control
 +  * Allow an external program to initiate a SQL command in Control.
 +
 +
 +
 +====== Usage ======
 +
 +
 +
 +The endpoint on CHAPI is preconfigured. To use it, you need only create a stored procedure in SQL for the StoreData database.
 +
 +
 +
 +<​html><​div style="​margin-left:​ 20px;">​ *Note:* Creation or work with SQL Stored Procedures requires development expertise in T-SQL and is not included in Cyrious Technical Support. </​div></​html>​
 +
 +
 +
 +Any parameters on the stored procedure are set as URL parameters. The general format is:
 +
 +
 +
 +<​code>​
 +   ​http://​{serverurl}:​12556/​chapi/​sqlmacro?​name={StoredProcName}&​{stripquotes=true}&​{paramName1=value1}&​..&​{paramNameN=valueN}
 +</​code>​
 +
 +
 +
 +where:
 +  * {serverurl} is the DNS name or IP address of the server.
 +  * {StoredProcName} is the name of the SQL Stored Procedure to be invoked.
 +  * {stripquotes=true} will strip down the quotes on both ends of the parameter value. Optional.
 +  * {paramName1=value1} sets the value of @param1="​value1"​ after converting HTML to plain text. Quotes may be used in the URL to separate parameters, but are usually not required.
 +  * {paramNameN=valueN} indicates that an arbitratry number of parameters can be supported.
 +
 +
 +
 +The routine works by running a SQL command as such:
 +
 +
 +
 +<​code>​
 +   EXEC StoredProcName @paramName1="​value1",​ @paramName2="​value2",​ ...
 +</​code>​
 +
 +
 +
 +====== Example ======
 +
 +
 +
 +For this example, create the following stored procedure in the StoreData SQL database used by Control.
 +
 +
 +
 +<​code>​
 +--- =============================================
 +-– Author: ​   Cyrious Software
 +-– Description: ​   This stored procedure is used to test CHAPI endpoints
 +-– =============================================
 +CREATE PROCEDURE TestProcedureCall
 +    @IntValue int = NULL,
 +    @StringValue varchar(MAX) = NULL
 +AS
 +BEGIN
 +    SELECT @IntValue as [IntValue], ​ @StringValue as [StringValue]
 +END
 +</​code>​
 +
 +
 +
 +Now, to call this port open a web browser and paste these links in the address bar and hit enter.This example assumes CHAPI is running at the standard port. If a customer port was configured, replace the port number accordingly. Each one will run the stored procedure above with the relevant parameters:
 +
 +
 +
 +<​code>​
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​IntValue=3&​StringValue=Red
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​IntValue=3&​StringValue=Red Blue
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​IntValue=3&​StringValue=Red%20Blue
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​StringValue="​Red"​
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​IntValue=3
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​IntValue=3&​StringValue="​Red"​
 +http://​localhost:​12556/​chapi/​sqlmacro?​name=TestProcedureCall&​IntValue=3&​StringValue="​Red Blue"
 +</​code>​
 +
 +
 +
 +The output you get should be similar to the this:
 +
 +
 +
 +<​code>​
 +200 OK
 +Date: Tue, 13 Jun 2017 21:38:08 GMT
 +Server: Microsoft-HTTPAPI/​2.0
 +Content-Length:​ 43
 +Content-Type:​ application/​json;​ charset=utf-8
 +[{"​IntValue":"​3","​StringValue":"​Red Blue"​}]
 +</​code>​
 +
 +
 +