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.

Inserting records example

Consider the following table:

The following UDF will read the Name of the Film from the selected results and then insert a new row with that record found.

Script:

function InsertAPIFilmCall(name)
{
var data = GetDataTableHelper(name);
return InsertIntoTable(data);
}
function GetDataTableHelper(name)
{
var data = [];
data.push(MakeAPICall(name));
return data;
}
function MakeAPICall(name)
{
var film = [];
var helpers = importNamespace('Cinchy.UDFExtensions');
var xmlHttp = new helpers.XMLHttpRequest();
xmlHttp.open('GET', 'http://www.omdbapi.com/?apikey=1a31c24e' + '&t=' + name, false);
xmlHttp.send();
if (xmlHttp.status === 200) {
var json = JSON.parse(xmlHttp.responseText);
var year = json.Year;
var genre = json.Genre;
film.push(name);
film.push(year);
film.push(genre);
}
return film;
}
function InsertIntoTable(data)
{
var helpers = importNamespace('Cinchy.UDFExtensions');
var adoNet = importNamespace('Cinchy.AdoNet');
var sysData = importNamespace('System.Data');
var recordCount = 0;
for(var i = 0; i < data.length; i++)
{
var d = data[i];
var queryparms = [
new adoNet.CinchyParameter('@name', sysData.DbType.string),
new adoNet.CinchyParameter('@year', sysData.DbType.string),
new adoNet.CinchyParameter('@genre', sysData.DbType.string)
]
queryparms[0].value = d[0];
queryparms[1].value = d[1];
queryparms[2].value = d[2];
recordCount += Query.executeNonQuery('INSERT INTO [Film].[Film] ([Name], [Year], [Genre]) VALUES (@name, @year, @genre)', queryparms, null, null);
}
return recordCount;
}

Name:

InsertAPIFilmCall

Following is the response when executing the UDF

The table now contains the results below. The UDF read the Names from the Film table and inserted copies of the data fetched from the API. This can easily be changed to an update to avoid duplication.