Cinchy User Defined Functions (UDFs)

Overview

Cinchy User Defined Functions (UDFs) give customers a way to specify and use more particular logic in your solutions than basic CQL. You can use CQL to simplify calculations and orchestrate automation to accommodate your business requirements.

UDFs are written in JavaScript.

Cinchy UDFs divide into two groups:

• Table-Valued Functions - Similar to the SQL construct of table-valued functions, you can SELECT or CROSS JOIN from a Cinchy UDF as if it's a table.
• Scalar-Valued Functions - Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.

Cinchy UDFs run https://github.com/sebastienros/jint, which uses ECMAScript 5.1.

info

If you are having issues with your script, try pasting it into JSHint, a tool that helps to detect errors and potential problems in your JavaScript code, as it also runs ECMAScript 5.1.

All UDFs are registered in the Cinchy User Defined Functions table (Image 1).

caution

Don't name UDFs the same names as SQL or CQL functions. Doing so may cause your platform to break. For example, don't name your UDF CONCAT.

Column Name	Description
Name	The Name column contains the name of the User Defined Function.
Script	The Script column can contain any number of JavaScript functions that are necessary and referenced by the single function that's registered in the Name column

Structure

All Cinchy UDFs use JavaScript, and follow the convention below:

function name(parameter1, parameter2, parameter3,...) {
  // code to be executed
  return something;
}

UDFs can perform external API calls and execute Cinchy Queries. All UDFs should return an indication of success or failure, even if you don't want to return any values.

You can create helper functions within a UDF, but you can't reference other UDFs.

Imports

To use advanced functions in UDFs, import the following:

var helpers = importNamespace("Cinchy.UDFExtensions");
var adoNet = importNamespace("Cinchy.AdoNet");
var sysData = importNamespace("System.Data");

Cinchy Functions in UDFs

You can use the following functions in a Cinchy UDF (but not in CQL directly).

External API calls

You can use XMLHttpRequest() helper to help POST or GET data from an external API. You can also access Cinchy basicAuthAPIs this way.

Supported methods

Method	Description
Open	This creates the HttpClient().
setRequestHeader	This adds the header to the client.
Send	This uses the client to call Get().
Send (postdata)	This uses the client to call POST, PUT, etc.
Status	This is attributed to show the status of a client response.
responseText	This is attributed to show the response text after the client is called.

var xmlHttp = new helpers.XMLHttpRequest();
xmlHttp.open("POST", "yourURLhere", false);
xmlHttp.setRequestHeader("Content-Type", "application/json");
xmlHttp.setRequestHeader("apikey", "yourapikeyhere");
xmlHttp.send(JSON.stringify(payload));
if (xmlHttp.status === 200) {
  var response = JSON.parse(xmlHttp.responseText);
  return response.result;
} else {
  return "Request failed.";
}

Cinchy Query Call

You can execute a Cinchy query or a non query (not expecting a result back) in a UDF.

info

Query refers to SELECT statements.

Non-query refers to INSERT, UPDATE, or DELETE statements.

Query.execute

tip

Query.execute is an update version (v5.7+) of Query.executeQuery and Query.executeNonQuery.

Description
Executes a query or non-query in a UDF. Query.execute also provides error catching information: if you query fails it will return a QueryResult object relaying error information to the UDF to allow for further testing and troubleshooting. For more information, see the QueryResult return object section below.

Syntax

Query.execute(query, parameter, timeout, tableInstance, returnDataTable)

Argument	Description	Data Type
query	The query to execute as written in CQL.	string
parameter	The parameter(s) associated with the query. (See Parameters section below for more detail) Note: Scalar-valued (SELECT) functions have to be invoked with a parameter, even if the definition of the function doesn't require a parameter.	array of CinchyParameter
timeout	Sets a timeout on the query. null = 30 seconds 0 = infinite	int
tableInstance	Specifies whether you want to return Primary, Draft, or History data in a SELECT statement. Using null will default the value to Primary.	string
returnDataTable	Determines whether to execute a query or a non-query. true = query (SELECT statement) false = non query (UPDATE/DELETE/INSERT statements)	boolean

Syntax Example

Query.execute('SELECT TOP 7 [Username] FROM [Cinchy].[Users] ORDER BY [Cinchy Id]', param, 0, 'Primary', true)

Non-Query Example

var helpers = importNamespace('Cinchy.UDFExtensions');
var adoNet = importNamespace('Cinchy.AdoNet');
var sysData = importNamespace('System.Data');
function InsertProperty(property,column_name)   
{ 
	var query = "INSERT INTO [Marketing].[Property Square Footage] ([Property]) VALUES (ResolveLink('" + property + "', '" + column_name + "'))";
	
	return records = Query.execute(query, null, null, null, false);  
}

Query Example with Parameters

var helpers =  importNamespace('Cinchy.UDFExtensions');
var adoNet = importNamespace('Cinchy.AdoNet');
var sysData =  importNamespace('System.Data');

function selectData(list)
{
    const query = "SELECT TOP 1 @list FROM [Cinchy].[Users];"

    let param = []
    param.push(generateCinchyParam('list', sysData.DbType.String, list.toString()));

    const dt = Query.execute(query, param, null, null, true);

    return dt.table.Rows[0];
}

function generateCinchyParam(name, type, value){
    let cinchyParam = new adoNet.CinchyParameter('@' + name, type);
    cinchyParam.value = value;
    return cinchyParam;
}

Other methods

Method	Description
Query.executeNonQuery	Query.executeNonQuery is a pre-v5.7 method. Though it is currently still supported, it is recommended to build all new UDFs with the above Query.execute method instead, as it returns additional information including error catching. This is used for INSERTS, DELETES, and UPDATES. It returns a Long value.
Query.executeQuery	Query.executeQuery is a pre-v5.7 method. Though it is currently still supported, it is recommended to build all new UDFs with the above Query.execute method instead, as it returns additional information including error catching. This is used for SELECT statement. It returns system.data values.
executeBatchUpsert	This performs a batch UPSERT into Cinchy. It returns int values.

Examples

function mainFunction(query, p1, p2) {
  var sysData = importNamespace("System.Data");
  var param = [];
  param.push(
    generateCinchyParam("parameterName1", sysData.DbType.String, p1.toString())
  );
  param.push(generateCinchyParam("parameterName2", sysData.DbType.Double, p2));

  var dataTable = [];
  dataTable = Query.executeQuery(query, param, null, null);

  return getSingleValue(dataTable, "colName");
}

function generateCinchyParam(name, type, value) {
  var adoNet = importNamespace("Cinchy.AdoNet");
  cinchyParam = new adoNet.CinchyParameter("@" + name, type);
  cinchyParam.value = value;
  return cinchyParam;
}

function getSingleValue(dataTable, colName) {
  var sysData = importNamespace("System.Data");
  var result = "";
  var enumerator = dataTable.Rows.GetEnumerator();
  while (enumerator.MoveNext()) {
    var record = enumerator.Current;
    result = record[colName];
  }
  return result;
}

QueryResult return object

// public class QueryResult
{
	//plain error message
	public string error { get; set; }
	//error with stacktrace
	public string errorDetail { get; set; }
	//data table from Query if returnDataTable is true
	public DataTable table { get; set; }
	//value of num of rows affected if returnDataTable is false
	public long rowsAffected { get; set; }
	//how many ms took to run query
	public double executionTimeMs { get; set; }
}

QueryResult examples

/*function testScalarUdf */
function testScalarUdf(p1, p2) {
  var sysData = importNamespace("System.Data");
  var param = [];

  var dataTable = [];

  var queryResult = Query.execute(
    "SELECT TOP 1 [Username] FROM [Cinchy].[Users] ORDER BY [Cinchy Id]",
    param,
    null,
    null,
    true
  );

  return getSingleValue(queryResult.table, "Username");
}

function getSingleValue(dataTable, colName) {
  var sysData = importNamespace("System.Data");
  var result = "";
  var enumerator = dataTable.Rows.GetEnumerator();
  while (enumerator.MoveNext()) {
    var record = enumerator.Current;
    result = record[colName];
  }
  return result;
}

/* end function testScalarUdf */

CQL

//CQL
SELECT testScalarUdf(1), [Username]
FROM [Cinchy].[Users]
WHERE [Deleted] IS NULL

JavaScript

/*function testTableUdf */

function testTableUdf(query) {
  var sysData = importNamespace("System.Data");
  var param = [];

  var dataTable = [];

  var queryResult = Query.execute(query, param, null, null, true);

  var singleVal = getSingleValue(queryResult.table, "Username");

  var result = {};
  result["schema"] = [
    {
      columnName: "rowsAffected ",
      type: "String",
    },
    {
      columnName: "UserName",
      type: "String",
    },
    {
      columnName: "error ",
      type: "String",
    },
    {
      columnName: "errorDetail ",
      type: "String",
    },
    {
      columnName: "executionTimeMs",
      type: "String",
    },
  ];
  result["data"] = [];
  result["data"].push([
    queryResult.rowsAffected,
    singleVal,
    queryResult.error,
    queryResult.errorDetail,
    queryResult.executionTimeMs,
  ]);
  return JSON.stringify(result);
}

function getSingleValue(dataTable, colName) {
  var sysData = importNamespace("System.Data");
  var result = "";
  if (dataTable == undefined) {
    return null;
  }
  var enumerator = dataTable.Rows.GetEnumerator();
  while (enumerator.MoveNext()) {
    var record = enumerator.Current;
    result = record[colName];
  }
  return result;
}

/* end function testTableUdf */

CQL

//CQL
SELECT t.*
FROM testTableUdf('SELECT TOP 1 [Username] FROM [Cinchy].[Users] ORDER BY [Andrew]') t

Parameters

The query parameter can be generated in the UDF as follows:

/**
 * Generates query parameters.
 * @param {string} name - The parameter name.
 * @param {sysData.DbType} type - The parameter type.
 * @param {string} value - The parameter value.
 */
function generateCinchyParam(name, type, value){
    // Generate the parameter
    let cinchyParam = new adoNet.CinchyParameter('@' + name, type);
    cinchyParam.value = value;
    return cinchyParam;
}return cinchyParam;
}

Example UDF using a generated Parameter:

var helpers =  importNamespace('Cinchy.UDFExtensions');
var adoNet = importNamespace('Cinchy.AdoNet');
var sysData =  importNamespace('System.Data');

function splitString(list)
{
    const query = "SELECT \
    x.f.value( '.', 'varchar(50)' ) AS [value] \
    FROM ( \
    SELECT CAST ( '<v><i>' + REPLACE ( @list, ',', '</i><i>' ) + '</i></v>' AS xml ) AS x \
    ) AS d \
    CROSS APPLY x.nodes( '//v/i' ) x( f );"

    let param = []
    param.push(generateCinchyParam('list', sysData.DbType.String, list.toString()));

    const dt = Query.execute(query, param, null, null, true);

    return dt.table.Rows;
}

/**
 * Generates query parameters.
 * @param {string} name - The parameter name.
 * @param {sysData.DbType} type - The parameter type.
 * @param {string} value - The parameter value.
 */
function generateCinchyParam(name, type, value){
    // Generate the parameter
    let cinchyParam = new adoNet.CinchyParameter('@' + name, type);
    cinchyParam.value = value;
    return cinchyParam;
}return cinchyParam;
}

info

Scalar-valued (SELECT) functions have to be invoked with a parameter, even if the definition of the function doesn't require a parameter. You can pass a string: `SELECT my_scalar('a') FROM [Cinchy].[Tables] WHERE [Deleted] IS NULL AND [Cinchy Id]=1