Develop an SQL Custom Function

• SQL Custom Function development overview
• SQL Custom Function return values
• Error handling

SQL Custom Function development overview

The SQL drivers defined in the Channel object must exist on the Map Engine server. In the absence of the appropriate drivers, the execution of the integration fails and an error is generated.

Create the SQL Custom Function object

The creation of an SQL Custom Function involves the following general steps:

1. In Mapping Services, create a Custom Function object.
2. Complete the General tab of the Custom Function properties window.

   Field	Contents
   Name	Enter a name for the Custom Function that is unique in the folder where you create it. This name is the logical name that is used to reference the Custom Function from DML expressions. The name of the Function must begin with an upper case letter. For example: MyFunction.
   Label	Enter an optional free-text description of the Custom Function. This description appears only on this tab.
   Status	This field displays the current status of the Custom Function, set to ToBeChecked by default. You cannot directly change the status in this field.
   Category	From the drop-down list, select SQL.
   Use JDBC Channel	Select this option if the function requires a connection to a data base via a JDBC Channel.
   Class	Select the of the value that the Custom Function returns. This field is mandatory. You can choose from among the following classes: Boolean String Integer Real number Data and Time Very Large Object
   Class name [For Java functions only]	Enter the name of the Java class that defines the function.
   Method name [For Java functions only]	Name of the method that executes the function.

When you select the SQL value for the Category field, Mapping Services adds the Body tab to the Custom Function properties window.

3. Complete the Parameters tab of the Custom Function properties window

   The Parameters tab, displays a table in which you define and view the parameters of the Custom Function. The Parameters table contains a line for each parameter. You can add a maximum of 128 parameters to a Custom Function.

   To add a new parameter to the table, click and complete the table columns.

   Column	Contents
   Name	Enter a name for each parameter that you define. Although a parameter is not an object, follow the object naming conventions. Each parameter name in a Custom Function must be unique.
   Label	Enter a description of each parameter. Although a parameter is not an object, respect the conventions for describing objects. This description is optional and appears only on this tab.
   Class	Select the   of the data that the parameter can contain. The data class of the parameters for Database Functions must be String. Via String parameters, you can use DML to pass any value to a Custom Function. For example, the parameter can be a Business Document path that contains an Integer (/segment name/leaf element name) or a Mapping Services Variable that stores Real numbers (%MyVariable%).

4. Complete the Implementation tab of the Custom Function properties window, entering a SELECT statement.

   Mapping Services displays the Implementation tab of the Custom Function properties window only when you select the SQL value for the Category field of the General tab.

   The Implementation tab comprises a single field in which you enter an SQL SELECT statement to retrieve a single value from a table.

   Use the syntax:

   Select col1, col2 from mytable where col3=#param0# and col4=#param1#

   To refer to the function parameters, place them between hash ("#") characters.

   You can enter only one SELECT expression in the Implementation tab. Multiple expressions are not authorized.

   The maximum length for an expression is 5000 characters.

   When you check the Custom Function object, Mapping Services verifies the syntax of the expression you enter in the Implementation tab. However, this syntax validation is only formal. That is, Mapping Services does not perform an exhaustive check of grammar for all SGBDs. There is no check for the existence of the tables and columns referred to in the expression.

5. In Mapping Services, in an Integration Process object, create a Mapping Flow with a DML expression that references the Custom-Fuction.

You must select the option Use JDBC Channel in the Mapping Flow. It is not possible use a single SQL statement to access to different data sources in the same DML Mapping Flow.

6. In Mapping Services, create an Integration Task in which you associate the Mapping Flow that contains the SQL Custom Function to a JDBC Channel.
7. In Mapping Services, Execute a Send to Server command on the Integration Task.

SQL Custom Function return error values

If the SQL SELECT statement:

• Does not return a result, the function generates the error message "No row returned by SQL function"
• Returns a null value, the function generates the value: NULL
• Returns more than one line of results, the function generates the an error message "More than one row is returned by the SQL function"

Error handling

If the SQL SELECT statement fails, (for example, if the referenced table does not exist or the request type is not SELECT) an error is written to the Message Log.

Example Message Log error:

[EXE – 12354] error executing SQL function MySQLFct

ORA 2013 : table not found

The Map Engine abandons processing of the current transaction, and either triggers processing via an Event object, or creates a SynchPoint.

Related topics

Use Custom Functions