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.

The endpoint on CHAPI is preconfigured. To use it, you need only create a stored procedure in SQL for the StoreData database.

Any parameters on the stored procedure are set as URL parameters. The general format is:

   http://{serverurl}:12556/chapi/sqlmacro?name={StoredProcName}&{stripquotes=true}&{paramName1=value1}&..&{paramNameN=valueN}

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:

   EXEC StoredProcName @paramName1="value1", @paramName2="value2", ...

For this example, create the following stored procedure in the StoreData SQL database used by Control.

--- =============================================
-– 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

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:

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"

The output you get should be similar to the this:

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"}]