Using Custom Scalar Functions With a Java IP

1. Register the custom scalar function with the OpenAccess SDK SQL engine. Call the ipGetScalarFunctions() method to return a scalar_function array with an entry for each method you want to expose. This example registers a custom scalar function named NEAR:

IpFuncs[x] = new scalar_function("NEAR");

Note: You only need to specify the name of the scalar function; you do not need to specify the implementation or handle.

Note: You can specify multiple literal parameters (for example, 0*).

2. Obtain the custom conditions in the query. When IP EXECUTE is called, call dam_getSetOfCustomConditionLists to validate the custom scalar function and get the custom condition lists. For example:

pSetOfCustCondList = jdam.dam_getSetOfCustomConditionLists(dam_hstmt, pbPartialLists);

If pbPartialLists is returned as TRUE, a partial list is returned. A partial list is returned in the following cases:

If a custom scalar function is an operand in a condition and another operand is not a COLUMN of the table.

If a custom scalar function is used with non-literal parameters.

If a custom scalar function is used in an expression.

If a custom scalar function is used in a JOIN that is bound by the OR operator.

When a partial list is returned, your IP cannot mark all the custom conditions in the list as having been evaluated. In this case, generate an exception and exit from the query execution; otherwise, the SQL engine reports an error on each target row validation.

3. Get details about each custom condition. Iterate through each set of custom condition lists (using dam_getFirstCondList and dam_getNextCondList, for example) and fetch the custom conditions (using dam_getFirstCond and dam_getNextCond, for example).

For each condition, call dam_describeCustomCond to get the following information:

Parameters passed to the method. Parameters are returned as a ValExpList. You can navigate this list by calling dam_getFirstValExp and dam_getNextValExp. You can get details of each expression by calling dam_getValueofExp.

This example requests this information from the scalar function named NEAR:

jdam.dam_describeCustomCond(hstmt, hcond, phCol, piOpType, "NEAR", phValExpList);

4. Mark each custom condition list as having been processed. Call dam_setOption for each condition list, marking each as having been processed. This example marks the DAM_CONDLIST_OPTION_EVALUATION option to a value of DAM_PROCESSING_OFF:

jdam.dam_setOption(DAM_CONDLIST_OPTION, pCustCondList, DAM_CONDLIST_OPTION_EVALUATION, DAM_PROCESSING_OFF)

See dam_setOption for more information about this method.

The SQL engine marks these condition lists as PROCESSED by the IP and skips their evaluation when validating target rows; otherwise, the SQL engine reports an error for each target row validation.

Note: Custom scalar functions are only supported on INTERSECT type condition lists.

Note: The dam_getOptimalIndexAndConditions(), dam_getRestrictionList(), dam_getSetOfConditionLists() and dam_getSetOfConditionListsEx() methods only return non-custom scalar condition lists.

Note: If a standard method and a custom scalar function are registered with same name, the first method that is registered has precedence.

For information about error messages that can occur while using custom scalar functions, see "Error Message Codes" in Chapter 4 of the OpenAccess SDK Troubleshooting Guide and Reference.