Mapping functions and operators: FunctionMapping element

The Generic_ODBC mappings.xml file contains empty elements for functions and operators. You customize function mappings by finding the appropriate element, such as BooleanOpMapper or NumericFuncMapper, and adding a FunctionMapping child element. The FunctionMapping element contains the attributes listed in Table 11-1.

Table 11-1

Attributes of the FunctionMapping element

Attribute	Description	Required?
FunctionName	Name of the Actuate SQL function.	Yes.
OperandTypes	Space-separated list of the Actuate SQL data types of the operands. The list can consist of the tokens BOOLEAN, INTEGER, DECIMAL, DOUBLE, VARCHAR, TIMESTAMP, and TABLE.	No. If the function can have many different operand types (overloading), then the mapping applies to all versions of the function. For DATEDIFF, DATEADD, and DATEPART, use the DatePart attribute instead.
DatePart	Used only for DATEDIFF, DATEADD, and DATEPART. Specifies the date part being mapped. Must be one of: yyyy (year) q (quarter) m (month) d (day) h (hour) n (minute) s (second) w (weekday) y (day of year)	Yes, for DATEDIFF, DATEADD, and DATEPART. Not required for other functions.
Disabled	Whether the mapping is disabled. If set to True, then the expression is not sent to the database. It is handled by the Integration service.	No. Default is False.

About function templates

You use the syntax shown in Table 11-2 to define function templates.

Table 11-2

Syntax used to define function templates

Syntax	Represents
$Pn	The (n+1)th operand. $P0 is the first operand, $P1 is the second operand, $P2 is the third operand, and so on. An operand can be a literal like 'Hello' or 6. An operand can also be a column on the database or a parameter.
$R	The database data type that the expression returns, for example CAST $P0 AS $R.
$$	The dollar sign ($).

The Integration service calculates the return data type for a function. You cannot calculate the return data type manually in a reliable manner. For example, when mapping the CAST function, do not use CAST ($P0 AS NVARCHAR). Instead, use CAST ($P0 AS $R). The Integration service determines the correct data type. When you use the $R syntax, you must explicitly declare the name of the database data type in the DataTypeMapper element, otherwise the ODBC name, for example SQL_VARCHAR, is used.

To help you understand how to use function templates, three examples of customized function mappings are given below.

Example: Mapping the POWER function

When you test your queries, you discover that the default mapping for the Actuate SQL POWER function returns an error. The Integration service uses the ODBC escape sequence {FN POWER ($P0, $P1)} as the default mapping. However, your JDBC driver does not have an implementation for the escape sequence and thus cannot rewrite the expression. Therefore, you must map the POWER function.

After checking your database documentation, you determine that the database has a function called POWER and that it takes two arguments. You compare this to the Actuate SQL POWER prototypes:

 

Integer POWER( base Integer, exponent Integer )

Decimal POWER( base Decimal, exponent Integer )

Double POWER( base Double, exponent Integer )

You use the NumericFuncMapper element. Because there is no OperandTypes attribute, the mapping applies to all versions of the POWER function:

<NumericFuncMapper>

  <FunctionMappings>

    <FunctionMapping

      FunctionName="POWER"> <!-- The Actuate SQL function -->

      POWER ($P0, $P1) <!-- The database function -->

    </FunctionMapping>

  </FunctionMappings>

</NumericFuncMapper>

For another example, refer to the mappings.xml file for Oracle, which requires a very different mapping for the POWER function. To map POWER, you use the OperandTypes attribute and define three different mappings:

<NumericFuncMapper>

  <FunctionMappings>

    …

    <FunctionMapping FunctionName="POWER"

      OperandTypes="INTEGER INTEGER">

      CAST( TRUNC(POWER( $P0, $P1 )) AS $R )

    </FunctionMapping>

    <FunctionMapping FunctionName="POWER"

      OperandTypes="DECIMAL INTEGER">

      CAST(POWER( $P0, $P1 ) AS $R)

    </FunctionMapping>

    <FunctionMapping FunctionName="POWER"

      OperandTypes="DOUBLE INTEGER">

      POWER( $P0, $P1 )

    </FunctionMapping>

    …

  </FunctionMappings>

</NumericFuncMapper>

Example: Mapping the DATEDIFF function with date part yyyy

The Actuate SQL DATEDIFF function uses a prototype that enables you to provide a date part such as yyyy. Thus, you can use the same function to do date subtraction for years, months, days, etc.:

Integer datediff( datepart Varchar, start Timestamp, end Timestamp )

When you test your queries you discover there is a problem with the default mapping for the DATEDIFF function with date part yyyy. The driver is mapping the DATEDIFF function to the YEARS_BETWEEN function on the database. The query gives incorrect results because the year is consistently off by one.

You can resolve the problem using the following mapping:

<DateDiffMapper>

  <FunctionMappings>

    <FunctionMapping FunctionName="DATEDIFF" DatePart="yyyy">

      YEARS_BETWEEN ($P0, $P1) - 1

    </FunctionMapping>

  </FunctionMappings>

</DateDiffMapper>

As another example, DB2 uses the following mapping for the DATEDIFF function with date part yyyy:

<FunctionMapping FunctionName="DATEDIFF" DatePart="yyyy">

  (YEAR( $P1 ) - YEAR ( $P0 ))

</FunctionMapping>

Example: Disabling the POSITION function

You determine that your database does not support the POSITION function. Therefore, you must disable the mapping of the POSITION function:

<SubStringFuncMapper>

  <FunctionMappings>

    <FunctionMapping FunctionName="POSITION"

      Disabled="true" />

  </FunctionMappings>

</SubStringFuncMapper>

Copyright Actuate Corporation 2012