UDFs: Example

This section contains an example that creates a UDF that calculates the weighted average of 3 values.

Here is the C signature of our UDF, along with declarations of the local variables that we use:

/**
* Calculates a weighted average of 3 values.
* Performs (var1*wt1 + var2*wt2 + var3*wt3)/3.0
*/
void weightedAverage3(C8Udf *ctx)
 float var1;     /* first user variable */
 float weight1;  /* 2nd user var: weight for var1 */
 float var2;     /* 3rd user var */
 float weight2;  /* 4th user var */
 float var3;     /* ... */
 float weight3);

You must provide an xml implementation of this signature and describe the parameters that will be passed from the CCL statement. The XML looks like:

<UserDefinedFunctions
  Name="TestingUserDefinedFunctions"
  Type="ns1:UserDefinedFunctionType"
  xmlns="http://www.sybase.com/udf/2005/04/"
  xmlns:udf="http://www.sybase.com/udf/2005/04/"
  xmlns:ns1="http://www.sybase.com/cpx/2005/04/"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>
<Description>Sybase CEP User Function Definition test cases
</Description>
<Vendor>Sybase Inc.</Vendor>
<Version>1.0</Version>
<Functions>
    <!-- Name         : Name in C code                     -->
    <!-- Library      : Library that the C func is in      -->
    <!-- CclName      : Name used in call from in CCL      -->
    <!-- IsAggregator : This function is not an aggregator -->
    <Function Name="weightedAverage3" 
              Library="user_function_lib" 
              CclName="WAvg3">
      <Description> 
       XML to describe a C function that performs a 
       weighted average of 3 values.
      </Description>
      <Input>
          <Parameter Name="var1"    Type="C8Float"/>
          <Parameter Name="weight1" Type="C8Float"/>
          <Parameter Name="var2"    Type="C8Float"/>
          <Parameter Name="weight2" Type="C8Float"/>
          <Parameter Name="var3"    Type="C8Float"/>
          <Parameter Name="weight3" Type="C8Float"/>
      </Input>
      <Output>
          <Parameter Type="C8Float"/>
      </Output>
    </Function>
   </Functions>
</UserDefinedFunctions>

This xml file is placed in the plugins subdirectories of both the server and Studio directory. See Compiling a UDF and Putting It in the Correct Directory for more details.

Please note the following points:

You must specify both the name of the C function and the name that will be used by CCL statements. These names are usually the same.
1. The name of the C function is specified in the line
  
  Function Name="weightedAverage3"
  
  and is case-sensitive.
2. The name of the function called from CCL is specified in the line
  
  CclName="WAvg3"
  
  and is not case-sensitive.
The "Library" is the name of the file that contains the compiled UDF code. In the example above, the library name is

"user_function_lib"

When Sybase CEP loads the library code, it will look for a file with this name and with an extension that is appropriate for the operating system (.lib, .dll, or .so).
For each parameter, the file specifies the name and the data type.
The data type of the return value (output parameter) is also specified.

This UDF is not an aggregator function, so we omitted the optional IsAggregator portion of the function specification.

An example call to this function looks like:

INSERT INTO OutStream
SELECT WAvg3(var1, weight1, var2, weight2, var3, weight3)...

Use the CCL name (WAvg3), not the C name (weightedAverage3). In the CCL code, the name is not case-sensitive.

Sample C code containing the UDF is below. A file with this code is aksi included in the installation, under SybaseC8Repository/version/examples/FeatureExamples/FunctionsAndOperators/UserDefinedFunctions.

#include <stdio.h>
#include "c8udf.h"
#if defined(_MSC_VER) 
// Exporting functions from dll
#if defined(user_function_lib_EXPORTS)
#define USER_FUNCTION_EXPORT __declspec( dllexport )
#else
#define USER_FUNCTION_EXPORT __declspec( dllimport )
#endif //defined(user_function_lib_EXPORTS)
#else // defined(_MSC_VER)
#define USER_FUNCTION_EXPORT 
#endif // defined(_MSC_VER)
extern "C" {
USER_FUNCTION_EXPORT void weightedAverage3(C8Udf* ctx);
}; // extern "C" 
/* ------------------------------------------------
* Return the weighted average of 3 float values.
* ------------------------------------------------
*/
void weightedAverage3(C8Udf* ctx)
{
  C8Float value;   /* The output value that we return. */
  C8Float var1, weight1;
  C8Float var2, weight2;
  C8Float var3, weight3;
  int i = 0;    /* Used when looping thru the NULL indicators. */
  /* If any of the input parameters are NULL, return NULL. */
  for (i = 0; i <= 5; i++)  {
      if (C8GetIsNull(ctx, (C8UInt) i) == C8_TRUE)   {
          C8SetOutputIsNull(ctx);
          return;
          }
      }
  /* "Unpack" the actual param values intended for the UDF */
  var1    = C8GetFloat(ctx, 0);
  weight1 = C8GetFloat(ctx, 1);
  var2    = C8GetFloat(ctx, 2);
  weight2 = C8GetFloat(ctx, 3);
  var3    = C8GetFloat(ctx, 4);
  weight3 = C8GetFloat(ctx, 5);
  /* Do the real work. */
  value = (var1*weight1 + var2*weight2 + var3*weight3)/3.0;
  /* "Pack" the output value into the context variable. */
  C8SetOutputFloat(ctx, value);
  return;
}

You can write any C function and may call other C routines. For the above example, this may involve placing the parameters in an array and using the array or any other useful technique.

Notice the various "#if" statements necessary to externalize symbols on Windows and UNIX-like operating systems.

In this example, we use a single function to do the real work (for example, return the weighted average of some values) and to pack and unpack the parameter values. If you already have a function foo() that you would like to use without modifying, then you can simply write an intermediate function that unpacks the values, calls foo(), packs the result, and returns. When you use an intermediate function the Sybase CEP Server calls the intermediate function, and the intermediate function calls your UDF. Since the Sybase CEP Server actually calls the intermediate function, the XML file that describes the UDF must contain the name of the intermediate function.