Data Source-specific User-defined Functions (UDF) (Preview)

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.

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