Foreign Functions (Preview)

Foreign functions are functions that leverage the native capabilities or syntax of the underlying data source to perform tasks such as querying, transforming, or formatting data or custom functions created by the user to extend the capabilities of the data integration tool.

CData Virtuality provides a mechanism to access custom or system functions defined in the underlying DBMS of your data source through user-defined functions (UDFs). These UDFs can be seamlessly mapped to source functions, as illustrated in the example below.

Information about UDFs could be found in SYS.Functions, SYS.FunctionParams, SYSADMIN.FunctionDefinitions and SYSLOG.FunctionDefinitionHistory tables.

Information about UDFs is located in SYS.Functions, SYS.FunctionParams, SYSADMIN.FunctionDefinitions and SYSLOG.FunctionDefinitionHistory tables since v4.8

Example

Consider the following function in the Microsoft SQL Server test_tables database:

SQL

CREATE FUNCTION [dbo].[Fun_Addition](@Num1 Decimal(7,2), @Num2 Decimal(7,2))
RETURNS Decimal(7,2)
BEGIN
  DECLARE @Result Decimal(7,2)
  SET @Result = @Num1 + @Num2 RETURN @Result
END

To use this function on the CData Virtuality Server, create a FOREIGN FUNCTION and map it to the Microsoft SQL Server UDF:

SQL

CREATE FOREIGN FUNCTION
<The CData Virtuality datasource name>.<function_name>(num1 integer, num2 integer)
RETURNS integer
OPTIONS (NAME_IN_SOURCE '"<Microsoft SQL Server database name(test_tables)>"."<schema name(dbo)>"."<UDF name>(Fun_Addition)"');

Usage and restrictions for user-defined functions are the same as those for built-in functions.

Data source-specific user-defined functions support available since v4.6