The CREATE FUNCTION statement defines a callable function or procedure.
You can also use the CREATE PROCEDURE statement to define a callable function or procedure, also known as a routine.
>>-CREATE--| RoutineType |--RoutineName-------------------------> >--(--| ParameterList |--)--+----------------+------------------> '-| ReturnType |-' >--+--------------+--+---------------+--| RoutineBody |-------->< '-| Language |-' '-| ResultSet |-' RoutineType |--+-FUNCTION--+------------------------------------------------| '-PROCEDURE-' ParameterList .-,-----------------. V | |----+---------------+-+----------------------------------------| '-| Parameter |-' Parameter (1) |--+-IN-----+--ParameterName--+-+----------+--DataType-+--------> +-OUT----+ | '-CONSTANT-' | '-INOUT--' | (2) | +-NAMESPACE--------------+ '-NAME-------------------' .-NULLABLE-. >--+----------+-------------------------------------------------| '-NOT NULL-' ReturnType .-NULLABLE-. |--RETURNS--DataType--+----------+------------------------------| '-NOT NULL-' Language |--LANGUAGE--+-ESQL---------+-----------------------------------| | (3) | +-DATABASE-----+ +-.NET---------+ +-CLR----------+ '-JAVA---------' ResultSet |--DYNAMIC RESULT SETS--integer---------------------------------| RoutineBody |--+-Statement-------------------------------------------------------+--| '-EXTERNAL--NAME--ExternalRoutineName--+------------------------+-' +-.NetTypeInfo-----------+ '-JavaClassLoaderService-' .NetTypeInfo |--ASSEMBLY--AssemblyName---------------------------------------> .----------------------------------------. V (4) | >--------+--------------------------------+-+-------------------| +-APPDOMAIN--DomainName----------+ +-VERSION--Version---------------+ +-CULTURE--Culture---------------+ '-PUBLICKEYTOKEN--PublicKeyToken-' JavaClassLoaderService |--CLASSLOADER--ClassLoaderConfigurableServiceName--------------|
The CREATE FUNCTION and CREATE PROCEDURE statements define a callable function or procedure, also known as a routine.
In previous versions of this product, CREATE FUNCTION and CREATE PROCEDURE had different uses and different capabilities. Subsequent enhancements have resulted in the differences listed previously in notes 1 and 3.
Routines are useful for creating reusable blocks of code that can be run independently many times. You can implement them as a series of ESQL statements, a Java™ method, a .NET method, or a database stored procedure. This flexibility means that some of the clauses in the syntax diagram are not applicable (or allowed) for all types of routine.
Each routine has a name, which must be unique within the schema to which it belongs. Routine names therefore cannot be overloaded; if the broker detects that a routine name has been overloaded, it raises an exception.
Specify the name of the routine by using the RoutineName clause, and its parameters by using the ParameterList clause. If the LANGUAGE clause specifies ESQL, implement the routine by using a single ESQL statement. This statement is most useful if it is a compound statement (BEGIN ... END), because it can then contain as many ESQL statements as necessary to fulfill its function.
Alternatively, instead of providing an ESQL body for the routine, you can specify a LANGUAGE clause other than ESQL. You can then use the EXTERNAL NAME clause to provide a reference to the actual body of the routine, wherever it is located externally to the broker. For more information about using the EXTERNAL NAME clause, see Invoking stored procedures and Calling a Java routine.
Routines of any LANGUAGE type can have IN, OUT, and INOUT parameters. The caller can pass several values into the routine, and receive back several updated values. These returned parameters are in addition to any RETURNS clause that you have defined for the routine. The RETURNS clause defines the value that the routine returns to the caller.
Routines that are implemented in different languages have their own restrictions on which data types can be passed in or returned; these restrictions are documented later in this section. The data type of the returned value must match the data type of the value that is defined to be returned from the routine. Also, if a routine is defined to have a return value, the caller of the routine cannot ignore it. For more information, see CALL statement.
For any language or routine type, the method of invocation of the routine must match the manner of declaration of the routine. If the routine has a RETURNS clause, use either the FUNCTION invocation syntax or a CALL statement with an INTO clause. Conversely, if a routine has no RETURNS clause, you must use a CALL statement without an INTO clause.
If the routine type is FUNCTION, the direction indicator (IN, OUT, INOUT) is optional for each parameter. However, it is good programming practice to specify a direction indicator for all new routines of any type for documentation purposes.
ESQL variables that are declared to be CONSTANT (or references to variables declared to be CONSTANT) are not allowed to have the direction OUT or INOUT.
ESQL routines are written in ESQL, and have a LANGUAGE clause of ESQL. The body of an ESQL routine is typically a compound statement of the form BEGIN … END, that contains multiple statements for processing the parameters that are passed to the routine.
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER )
BEGIN
SET parm2 = parm3;
SET parm3 = parm1;
END;
This example procedure shows the recursive use of an ESQL routine. It parses a tree, visiting all places at and below the specified starting point, and reports what it has found:
SET OutputRoot.MQMD = InputRoot.MQMD;
DECLARE answer CHARACTER;
SET answer = '';
CALL navigate(InputRoot.XMLNS, answer);
SET OutputRoot.XMLNS.Data.FieldNames = answer;
CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER)
BEGIN
SET answer = answer || 'Reached Field... Type:'
|| CAST(FIELDTYPE(root) AS CHAR)||
': Name:' || FIELDNAME(root) || ': Value :' || root || ': ';
DECLARE cursor REFERENCE TO root;
MOVE cursor FIRSTCHILD;
IF LASTMOVE(cursor) THEN
SET answer = answer || 'Field has children... drilling down ';
ELSE
SET answer = answer || 'Listing siblings... ';
END IF;
WHILE LASTMOVE(cursor) DO
CALL navigate(cursor, answer);
MOVE cursor NEXTSIBLING;
END WHILE;
SET answer = answer || 'Finished siblings... Popping up ';
END;
When given the following input message:
<Person>
<Name>John Smith</Name>
<Salary period='monthly' taxable='yes'>-1200</Salary>
</Person>
the procedure produces the following output, which has been manually formatted:
Reached Field... Type:16777232: Name:XML: Value :: Field has children...
drilling down
Reached Field... Type:16777216: Name:Person: Value :: Field has children...
drilling down
Reached Field... Type:16777216: Name:Name:
Value :John Smith: Field has children... drilling down
Reached Field... Type:33554432: Name::
Value :John Smith: Listing siblings... Finished siblings... Popping up
Finished siblings... Popping up
Reached Field... Type:16777216: Name:Salary:
Value :-1200: Field has children... drilling down
Reached Field... Type:50331648: Name:period:
Value :monthly: Listing siblings... Finished siblings... Popping up
Reached Field... Type:50331648: Name:taxable:
Value :yes: Listing siblings... Finished siblings... Popping up
Reached Field... Type:33554432: Name::
Value :-1200: Listing siblings... Finished siblings... Popping up
Finished siblings... Popping up
Finished siblings... Popping up
Finished siblings... Popping up
A .NET routine is implemented as a .NET method, and has a LANGUAGE clause of .NET or CLR. For .NET routines, the ExternalRoutineName must contain the class name and method name of the .NET method to be called. Specify the ExternalRoutineName like this example:
>>--"-- className---.---methodName--"--------------><
Where className identifies
the class that contains the method and methodName identifies
the method to invoke. If the class is part of a Namespace or is a
nested class, the class identifier part must include all Namespace
and nested class names; for example,"IBM.Broker.test.MyOuterClass.MyNestedClass.MyMethod"To find the .NET class, the broker searches the GAC and the AppDomain base location for the specified assembly.
Any .NET method that you want to invoke must be a public static method. In addition, all parameters must be listed in ESQL-to-.NET data-type mapping tables. Also, if the method has a return type, the return type must be listed in the IN data type mapping table.
CREATE PROCEDURE Swap (
IN a INT NOT NULL,
OUT b INT NOT NULL,
INOUT c INT NOT NULL ) RETURNS CHARACTER NOT NULL
LANGUAGE .NET
EXTERNAL NAME "FunctionTests.SwapString"
ASSEMBLY "C:\coding\test projects\MyAssembly"
APPDOMAIN "MyDomain";
CALL Swap( intVar1, intVar2, intVar3 ) INTO ReturnVar;
-- or
SET ReturnVar = Swap ( intVar1, intVar2, intVar3);
Defines a procedure representing a .NET Method that has no return value with three Nullable parameters of varying directions.
CREATE PROCEDURE SwapNullable (
IN a INTEGER NULLABLE,
OUT b INTEGER NULLABLE,
INOUT c INTEGER NULLABLE )
LANGUAGE CLR
EXTERNAL NAME "FunctionTests.SwapStringNullable"
ASSEMBLY "MyAssembly2"
APPDOMAIN "MyDomain";
CALL SwapNullable(intVar1, intVar2, intVar3);
C#
public class FunctionTests
{
public static string Swap(int pIn, out int pOut, ref int pInout)
{
pOut = pInout;
pInout = pIn;
return "Finished";
}
public static void SwapNullable(long? pIn, out long? pOut, ref long? pInout)
{
pOut = pInout;
pInout = pIn;
}
}
VB
Public Class FunctionTests
Shared Function Swap(ByVal pIn As Integer, <Out()> ByRef pOut As Integer, ByRef pInout As Integer) As String
pOut = pInout
pInout = pIn
Return "Finished"
End Function
Shared Sub SwapNullable(ByVal pIn As Long?, ByRef pOut As Long?, ByRef pInout As Long?)
pOut = pInout
pInout = pIn
End Sub
End Class
F#
module FunctionTests
let Swap( pIn : int, [<Out>] pOut : byref<int> , pInOut : byref<int> ) = (
pOut <- pInout
pInout <- pIn
let temp = "Finished"
temp
)
let SwapNullable( pIn : Nullable<int64>, [<Out>] pOut : byref<Nullable<int64>> , pInOut : byref<Nullable<int64>> ) = (
pOut <- pInout
pInout)
)
C++ / CLi
public ref class FunctionTests
{
public:
static String^ Swap(int pIn, [Out] int% pOut, int% pInout)
{
pOut = pInout;
pInout = pIn;
String^ temp = "Finished";
return temp;
}
static void SwapNullable(Nullable<long long> pIn, [Out] Nullable<long long>% pOut, Nullable<long long>% pInout)
{
pOut = pInout;
pInout = pIn;
}
}
>>--"-- className---.---methodName--"--------------><
where className identifies
the class that contains the method and methodName identifies
the method to invoke. If the class is part of a package, the class
identifier part must include the complete package prefix; for example,"com.ibm.broker.test.MyClass.myMethod" To find the Java class, the broker uses the search method that is described in Deploying Java classes.
public static <return-type> <method-name> (< 0 - N parameters>)
Where <return-type> must be in the list of Java IN data types in the table in ESQL to Java data type mapping (excluding the REFERENCE type, which is not permitted as a return value), or the Java void data type. The parameter data types must also be in the ESQL to Java data type mapping table. In addition, the Java method is not allowed to have exception throws clause in its signature.
The clause in the JavaClassLoader section applies only to LANGUAGE JAVA routines. The CLASSLOADER clause is optional; if you do not specify this clause, the Java class is loaded by the EGShared classloader. For more information, see JavaCompute node classloading and JavaClassLoader configurable service.
You can use the Java user-defined node API in your Java method, if you observe the restrictions documented in Restrictions on Java routines. For more information about using the Java API, see Compiling a Java user-defined node.
This routine contains three parameters of varying directions, and returns an integer, which maps to a Java return type of java.lang.Long.
CREATE FUNCTION myProc1( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER )
RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1";
You can use the following ESQL to invoke myProc1:
CALL myProc1( intVar1, intVar2, intVar3) INTO intReturnVar3;
-- or
SET intReturnVar3 = myProc1( intVar1, intVar2, intVar3);
This routine contains three parameters of varying directions and has a Java return type of void.
CREATE PROCEDURE myProc2( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER )
LANGUAGE JAVA
EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod2";
You must use the following ESQL to invoke myProc2:
CALL myProc2(intVar1, intVar2, intVar3);
The following Java class provides a method for each of the preceding Java examples:
package com.ibm.broker.test;
class MyClass {
public static Long myMethod1( Long P1, Long[] P2 Long[] P3) { ... }
public static void myMethod2( Long P2, Long[] P2 Long[] P3) { ... }
/* When either of these methods is called:
P1 might or might not be NULL (depending on the value of intVar1).
P2[0] is always NULL (whatever the value of intVar2).
P3[0] might or might not be NULL (depending on the value of intVar3).
This is the same as with LANGUAGE ESQL routines.
When these methods return:
intVar1 is unchanged
intVar2 might still be NULL or might have been changed
intVar3 might contain the same value or might have been changed.
This is the same as with LANGUAGE ESQL routines.
When myMethod1 returns: intReturnVar3 is either NULL (if the
method returns NULL) or it contains the value returned by the
method.
*/
}
CREATE FUNCTION myMethod1 ( IN P1 INTEGER, IN P2 INTEGER )
RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1"
CLASSLOADER "myClassLoader";
ESQL data types 1 | Java IN data types | Java INOUT and OUT data types |
---|---|---|
INTEGER, INT | java.lang.Long | java.lang.Long [] |
FLOAT | java.lang.Double | java.lang.Double[] |
DECIMAL | java.math.BigDecimal | java.math.BigDecimal[] |
CHARACTER, CHAR | java.lang.String | java.lang.String[] |
BLOB | byte[] | byte[][] |
BIT | java.util.BitSet | java.util.BitSet[] |
DATE | com.ibm.broker.plugin.MbDate | com.ibm.broker.plugin.MbDate[] |
TIME 2 | com.ibm.broker.plugin.MbTime | com.ibm.broker.plugin.MbTime[] |
GMTTIME 2 | com.ibm.broker.plugin.MbTime | com.ibm.broker.plugin.MbTime[] |
TIMESTAMP 2 | com.ibm.broker.plugin.MbTimestamp | com.ibm.broker.plugin.MbTimestamp[] |
GMTTIMESTAMP 2 | com.ibm.broker.plugin.MbTimestamp | com.ibm.broker.plugin.MbTimestamp[] |
INTERVAL | Not supported | Not supported |
BOOLEAN | java.lang.Boolean | java.lang.Boolean[] |
REFERENCE (to a message tree) 3 4 5 6 | com.ibm.broker.plugin.MbElement | com.ibm.broker.plugin.MbElement[] (Supported for INOUT. Not supported for OUT) |
ROW | Not supported | Not supported |
LIST | Not supported | Not supported |
For example, if an ESQL reference to OutputRoot.XML.Test is passed into a Java method as an INOUT MbElement, but a different MbElement is passed back to ESQL when the call returns, the different element must also point to somewhere in the OutputRoot tree.
A REFERENCE to a scalar variable can be used in the CALL of a Java method, provided that the data type of the variable to which the reference refers matches the corresponding data type in the Java program signature.
You can create threads inside your method. However, created threads must not use the Java APIs, and you must return control back to the broker.
All restrictions that apply to the usage of the Java API also apply to Java methods that are called from ESQL.
The most efficient and flexible method of deploying to the broker is to add your JAR file to the BAR file. You can do this manually or automatically using the IBM® Integration Toolkit.
If the IBM Integration Toolkit finds the correct Java class inside a referenced Java project open in the workspace, it automatically compiles the Java class into a JAR file and adds it to the BAR file. This procedure is the same procedure that you follow to deploy a JavaCompute node inside a JAR, as described in User-defined node class loading.
When you deploy a JAR file from the IBM Integration Toolkit, the flow that has been redeployed reloads the JAR file contained in the BAR file.
The files are also reloaded if the message flow that references a Java class is stopped and restarted. There is no need to stop and restart flows or redeploy them, because the ESQL manager is refreshed when JAR file is redeployed and any subsequent external Java calls from ESQL use the new classloader. After the deploy has finished, all flows are running with the new version of the JAR file.
The IBM Integration Toolkit deploys only JAR files; it does not deploy stand-alone Java class files.
You must complete this action manually; you cannot use the IBM Integration Toolkit.
In this method, redeploying the message flow does not reload the referenced Java classes; neither does stopping and restarting the message flow. The only way to reload the classes in this case is to stop and restart the broker itself.
To enable the broker to find a Java class, ensure that it is in one of the preceding locations. If the broker cannot find the specified class, it generates an exception.
Although you have the choices shown previously when you deploy the JAR file, by using the IBM Integration Toolkit to deploy the BAR file provides the greatest flexibility when redeploying the JAR file.
CREATE FUNCTION does not support database routines. Use CREATE PROCEDURE to define a database routine.