User Exit—%USERFUNC

Use this function to call a Java™ class user exit program or a stored procedure from an expression.

This function provides flexibility when complex logic that cannot be expressed using the provided column functions is required. You can use this function to call a user exit program with input parameters. This function also supports MBCS data.

Syntax

%USERFUNC(function_type, program_name, [parm1, parm2, ..., parmn]

Parameters

function_type: Indicate the type of user exit. Specify “JAVA” to call a Java class user exit program or STOREDPROC to call a stored procedure user exit program. You must enclose these values in double quotation marks to indicate they are strings, not column names.; The Java class should be placed in your lib directory. If your Java class has a package name then you must create the appropriate directories under the lib directory.; The stored procedure must exist in your database and you must specify the database owner or schema.
program_name: The name of your Java class or stored procedure. Stored procedures must exist in your database you must specify the database owner or schema as well as the name of the stored procedure.
parm1, parm2, ..., parmn: Specify columns or literals that are passed as parameters to the Java class user exit or stored procedure user exit.

Result data type

The data type of the result returned by the stored procedure.

Example 1

%USERFUNC(JAVA, USERSEL1,
BRANCH)

USERSEL1 checks whether or not BRANCH is set to 11. If it is, then the user exit program returns a "Y" string. Otherwise, it returns an "N" string.

public class UserSel1 implements DEUserExitIF
{
   public Object invoke(Object[] aobjList) throws UserExitInvalidArgumentException, UserExitInvokeException
   {
      if (aobjList.length < 1)
      {
         throw new UserExitInvalidArgumentException("UserSel1: insufficient number of arguments, expect 1.");
      }
      if (!(aobjList[0] instanceof Number))
      {
         throw new UserExitInvalidArgumentException(aobjList[0] + " is not a number");
      }
      int branch = ((Number) aobjList[0]).intValue();

      if (branch == 11)
      {
         return "Y";
      }
      else
      {
         return "N";
      }
   }
}

Example 2

%USERFUNC(STOREDPROC,dbo.sp_date_diff,COL_DATE)
%USERFUNC(STOREDPROC,dbo.sp_date_diff,12-jan-2000)

The first call example specifies a source column name as input parameter (COL_DATE), while the second call example specifies a value as input parameter (12-jan-2000). These examples assume that a stored procedure, similar to the following, is defined in the database. The example stored procedure calculates the difference in days between the current date and a date specified as parameter. The stored procedure must exist in your database and you must specify the stored procedure name and database owner or schema:

	CREATE PROCEDURE dbo.sp_date_diff 
	@out_int int output, 
	@in_date datetime 
	AS 
	select @out_int = DATEDIFF(day, @in_date, getdate()) 
	GO

Example 3

%USERFUNC(STOREDPROC,dbo.sp_join,col_item_number)
%USERFUNC(STOREDPROC,dbo.sp_join,12)

The first call example specifies a source column name as input parameter (col_item_number), while the second call example specifies a value as input parameter (12). These examples assume that a stored procedure, similar to the following, is defined in the database. The example stored procedure performs a join to a description table to get the description of an item given its item number. The stored procedure must exist in your database and you must specify the stored procedure name and database owner or schema.

	CREATE PROCEDURE dbo.sp_join 
	@out_item_description varchar(10) output 
	@in_item_number int 
	AS 
	SELECT @out_item_description = inventory_db.dbo.description.item_desc 
	FROM inventory_db.dbo.item_list INNER JOIN 
	inventory_db.dbo.description ON 
	inventory_db.dbo.item_list.item_number = 
	inventory_db.dbo.description.item_number 
	WHERE (inventory_db.dbo.description.item_number = @in_item_number) 
	GO