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.

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

*Note:* Creation or work with SQL Stored Procedures requires development expertise in T-SQL and is not included in Cyrious Technical Support.

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

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