See information about the latest product version
CREATE PROCEDURE statement
The CREATE PROCEDURE statement defines a callable function or procedure.
You can also use the CREATE FUNCTION statement to define a callable function or procedure, also known as a 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.
Syntax
>>-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--------------|
- If the routine type is FUNCTION, the direction indicator (IN, OUT, or INOUT) is optional for each parameter. However, for documentation purposes, it is good programming practice to specify a direction indicator for all new routines; if you do not specify the direction, a default value of IN is used.
- When the NAMESPACE or NAME clause is used, its value is implicitly CONSTANT and of type CHARACTER. For information about the use of CONSTANT variables, see the DECLARE statement.
- If the routine type is FUNCTION, you cannot specify a LANGUAGE of DATABASE.
- Each can be specified once only.
Parameter directions
- IN
- The value of the parameter cannot be changed by the routine. A NULL value for the parameter is allowed, and can be passed to the routine.
- OUT
- When it is received by the called routine, the parameter that is passed into the routine always has a NULL value of the correct data type. This value is set irrespective of its value before the routine is called. The routine is allowed to change the value of the parameter.
- INOUT
- INOUT is both an IN and an OUT parameter. It passes a value into the routine, and the value that is passed in can be changed by the routine. A NULL value for the parameter is allowed, and can be passed both into and out of the routine.
- NULLABLE
- This optional clause indicates the value of the parameter is allowed to contain a NULL value. NULL is the default clause if this clause is omitted, unless the NOT NULL clause is specified.
- NOT NULL
- This optional clause indicates the value of the parameter cannot contain a NULL value. If a parameter is marked as NOT NULL and a NULL value is passed on the call, an exception is thrown. Note: Currently this clause can be used only with LANGUAGE CLR or LANGUAGE .NET.
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
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.
ESQL example 1
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER )
BEGIN
SET parm2 = parm3;
SET parm3 = parm1;
END;
ESQL example 2
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
.NET routines
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.
- Ensure that the .NET method name, including the class name and any Namespace qualifiers, matches the ESQL procedures EXTERNAL NAME.
- If the .NET method does not have a return type, do not put a RETURNS clause on the ESQL routines definition. Conversely, if the .NET method does have a return type, you must put a RETURNS clause on the ESQL routines definition.
- Ensure that every parameters type and direction matches the ESQL declaration, according to the rules listed in ESQL-to-.NET data-type mapping tables.
- Ensure that the methods return type matches the data type of the RETURNS clause.
- Enclose EXTERNAL NAME in quotation marks because it must contain at least "Class.Method".
- If you want to invoke an overloaded .NET method, you must create a separate ESQL definition for each overloaded method and give each ESQL definition a unique routine name.
- .NET Type Info
- The clause in the .NET Type Info section applies only to LANGUAGE .NET routines.
- ASSEMBLY
- The ASSEMBLY indicates the .NET assembly that the method to be invoked resides in. If the assembly resides in the GAC, it can simply be the assembly name (for example, “MyAssembly”). However, if the assembly is not in the GAC, it needs to contain the fully qualified path to the assembly.
- APPDOMAIN
- This parameter provides the name of the APPDOMAIN in which to load the assembly and execute the method. If this clause is omitted, the APPDOMAIN is set to the name of the application that the flow belongs to. If the flow does not belong to an application, the APPDOMAIN is set to the name of the message flow.
- VERSION
- This provides the exact version of the assembly to be loaded. If the version is omitted, the first version found of the named assembly is used.
- CULTURE
- This provides the ability to specify an exact culture for the assembly. The default is to use the “neutral” culture.
- PUBLICKEYTOKEN
- If the assembly to be loaded resides in the GAC, its public key token needs to be provided. However, if the assembly does not reside in the GAC, this clause is optional. When searching for an assembly, the search order is as defined by the .NET framework, the full details of which are listed in MSDN. However, the abridged version states; If the assembly name is not fully qualified, the base for the AppDomain is used. If the assembly name is fully qualified (that is the version and public key token have been specified), the GAC is searched before the App Domains base folder.
.NET routine example 1
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);
.NET routine example 2
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);
Example code for various .NET languages providing methods for both examples
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;
}
}
Java routines
>>--"-- 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.
- Ensure that the Java method name, including the class name and any package qualifiers, matches the procedures EXTERNAL NAME.
- If the Java return type is void, do not put a RETURNS clause on the ESQL routines definition. Conversely, if the Java return type is not void, you must put a RETURNS clause on the ESQL routines definition.
- Ensure that every parameters type and direction matches the ESQL declaration, according to the rules listed in the table in ESQL to Java data type mapping.
- Ensure that the methods return type matches the data type of the RETURNS clause.
- Enclose EXTERNAL NAME in quotation marks because it must contain at least "class.method".
- If you want to invoke an overloaded Java method, you must create a separate ESQL definition for each overloaded method and give each ESQL definition a unique routine name.
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.
Java routine example 1
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);
Java routine example 2
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.
*/
}
Java routine example 3
CREATE FUNCTION myMethod1 ( IN P1 INTEGER, IN P2 INTEGER )
RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1"
CLASSLOADER "myClassLoader";
ESQL to Java data type mapping
- Only the Java scalar wrappers are passed to Java.
- The ESQL scalar types are mapped to Java data types as object wrappers, or object wrapper arrays, depending upon the direction of the procedure parameter. Each wrapper array contains exactly one element.
- Scalar object wrappers are used to allow NULL values to be passed to and from Java methods.
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 |
- Variables that are declared to be CONSTANT (or references to variables that are declared to be CONSTANT) are not allowed to have the direction INOUT or OUT.
- The time zone set in the Java variable is not important; you obtain the required time zone in the output ESQL.
- The reference parameter cannot be NULL when passed into a Java method.
- The reference cannot have the direction OUT when passed into a Java method.
- If an MbElement is passed back from Java to ESQL as an INOUT parameter,
it must point to a location in the same message tree as that pointed
to by the MbElement that was passed into the called Java method.
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.
- An MbElement cannot be returned from a Java method with the RETURNS clause, because no ESQL routine can return a reference. However, an MbElement can be returned as an INOUT direction parameter, subject to the conditions described in point 5.
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.
Restrictions on Java routines
- The Java method must be threadsafe (reentrant).
- Database connections must be JDBC type 2 or type 4. Furthermore, database operations are not part of a broker transaction and therefore cannot be controlled by an external resource coordinator (as is the case in an XA environment).
- The Java user-defined node
API must be used only by the same thread that invoked the Java method.
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.
- Java methods that are called from ESQL must not use the MbNode class. Therefore, they cannot create objects of type MbNode, or call any of the methods on an existing MbNode object.
- WebSphere® MQ or JMS work done inside a Java method that is called from ESQL must be done in accordance with the guidelines for performing WebSphere MQ and JMS work in a user-defined node. See Planning user-defined input nodes.
Deploying Java classes
- Add the JAR file to the broker archive (BAR) file
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 WebSphere Message Broker Toolkit.
If the WebSphere Message Broker 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 WebSphere Message Broker 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. Ensure that you stop and restart (or redeploy) all flows that reference the JAR file that you want to update. This action avoids the problem of some flows running with the old version of the JAR file and other flows running with the new version.
The WebSphere Message Broker Toolkit deploys only JAR files; it does not deploy stand-alone Java class files.
- Store the JAR file in either of the following locations:
- The workpath/shared-classes/ folder on the machine running the broker
- The CLASSPATH environment variable on the computer running the broker
You must complete this action manually; you cannot use the WebSphere Message Broker 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 WebSphere Message Broker Toolkit to deploy the BAR file provides the greatest flexibility when redeploying the JAR file.
Database routines
Database routines are implemented as database stored procedures. Database routines have a LANGUAGE clause of DATABASE, and must have a routine type of PROCEDURE.
When writing stored procedures in languages like C, you must use NULL indicators to ensure that your procedure can process the data correctly.
Although the database definitions of a stored procedure vary between the databases, the ESQL used to invoke them does not. The names given to parameters in the ESQL do not have to match the names they are given on the database side. However, the external name of the routine, including any package or container specifications, must match its defined name in the database.
The DYNAMIC RESULT SETS clause is allowed only for database routines. It is required only if a stored procedure returns one or more result sets. The integer parameter to this clause must be 0 (zero) or more, and specifies the number of result sets to be returned.
The optional RETURNS clause is required if a stored procedure returns a single scalar value.
- useDefaultSchemaForStoredProcedures is set to true (True is the default value): The database connection user name is used as the schema in which to locate the procedure. If the required procedure does not exist in this schema, you must provide an explicit schema name, either on the routine definition or on the CALL to the routine at run time. For more information about dynamically choosing the schema that contains the routine, see the CALL statement.
- useDefaultSchemaForStoredProcedures is set to false: No default for the schema is set, and the default behavior of the database connection for this situation is carried out instead.
EXTERNAL NAME "mySchema.myProc";
EXTERNAL NAME "mySchema.myPackage.myProc";
This form allows the schema, but not the package name, to be chosen dynamically in the CALL statement.
EXTERNAL NAME "mySchema.Proc_";
- A stored procedure cannot be overloaded on the database side. A stored procedure is considered overloaded if there is more than one procedure of the same name in the same database schema. If the broker detects that a procedure has been overloaded, it raises an exception.
- Parameters cannot be of the ESQL REFERENCE, ROW, LIST, or INTERVAL data types.
- User-defined types cannot be used as parameters or as return values.
>>--"schemaName---.---packageName---.---procedureName--"--------------><
where:- schemaName is optional.
- packageName is optional and applies only to Oracle data sources. If you supply a packageName you must supply a schemaName.
- procedureName is not optional.
Database routine example 1
The following example shows an ESQL definition of a stored procedure that returns a single scalar value and an OUT parameter:
CREATE PROCEDURE myProc1(IN P1 INT, OUT P2 INT)
RETURNS INTEGER
LANGUAGE DATABASE
EXTERNAL NAME "myschema.myproc";
Use this ESQL to invoke the myProc1 routine:
/*using CALL statement invocation syntax*/
CALL myProc1(intVar1, intVar2) INTO intReturnVar3;
/*or using function invocation syntax*/
SET intReturnVar3 = myProc1(intVar1, intVar2);
Database routine example 2
The following ESQL code demonstrates how to define and call DB2 stored procedures:
ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;
To register this stored procedure with DB2, copy the following script to a file (for example, test1.sql)
-- DB2 Example Stored Procedure
DROP PROCEDURE dbSwapParms @
CREATE PROCEDURE dbSwapParms
( IN in_param CHAR(32),
OUT out_param CHAR(32),
INOUT inout_param CHAR(32))
LANGUAGE SQL
BEGIN
SET out_param = inout_param;
SET inout_param = in_param;
END @
Now run the file from the DB2 command
prompt:db2 -td@ -vf test1.sql
- The value of the IN parameter does not (and cannot, by definition) change.
- The value of the OUT parameter becomes "World".
- The value of the INOUT parameter changes to "Hello".
Database routine example 3
The following ESQL code demonstrates how to define and call Oracle stored procedures:
ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;
To register this stored procedure with Oracle, copy the following script to a file (for example, test1.sql)
CREATE OR REPLACE PROCEDURE dbSwapParms
( in_param IN VARCHAR2,
out_param OUT VARCHAR2,
inout_param IN OUT VARCHAR2 )
AS
BEGIN
out_param := inout_param;
inout_param := in_param;
END;
/
Now run the file:sqlplus userID/password @test1.sql
- The value of the IN parameter does not (and cannot, by definition) change.
- The value of the OUT parameter becomes "World".
- The value of the INOUT parameter changes to "Hello".
Database routine example 4
The following ESQL code demonstrates how to define and call SQL Server stored procedures:
ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
INOUT parm2 CHARACTER,
INOUT parm3 CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;
To register this stored procedure with SQL Server, copy the following script to a file (for example, test1.sql)
-- SQLServer Example Stored Procedure
DROP PROCEDURE dbSwapParms
go
CREATE PROCEDURE dbSwapParms
@in_param CHAR(32),
@out_param CHAR(32) OUT,
@inout_param CHAR(32) OUT
AS
SET NOCOUNT ON
SET @out_param = @inout_param
SET @inout_param = @in_param
go
Now run file:isql -UuserID -Ppassword -Sserver -ddatasource -itest1.sql
SQL Server considers OUTPUT parameters from stored procedures as INPUT/OUTPUT parameters. If you declare them as OUT parameters in your ESQL you encounter a type mismatch error at run time. To avoid that mismatch you must declare SQL Server OUTPUT parameters as INOUT in your ESQL.
- To limit the amount of data returned from SQL Server to the broker.
- To allow result sets to be returned correctly.
- The value of the IN parameter does not (and cannot, by definition) change.
- The value of the OUT parameter becomes "World".
- The value of the INOUT parameter changes to "Hello".
Database routine example 5
The following ESQL code demonstrates how to define and call Sybase stored procedures:
ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
INOUT parm2 CHARACTER,
INOUT parm3 CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;
To register this stored procedure with Sybase, copy the following script to a file (for example, test1.sql)
-- SYBASE Example Stored Procedure
DROP PROCEDURE dbSwapParms
go
CREATE PROCEDURE dbSwapParms
@in_param CHAR(32),
@out_param CHAR(32) OUT,
@inout_param CHAR(32) OUT
AS
SET @out_param = @inout_param
SET @inout_param = @in_param
go
Now run file:isql -U<userID> -P<password> -S<server> -D<datasource> -itest1.sql
Sybase considers OUTPUT parameters from stored procedures as INPUT/OUTPUT parameters. If you declare them as OUT parameters in your ESQL, you encounter a type mismatch error at run time. To avoid that mismatch, declare Sybase OUTPUT parameters as INOUT in your ESQL.
- The value of the IN parameter does not (and cannot, by definition) change.
- The value of the OUT parameter becomes "World".
- The value of the INOUT parameter changes to "Hello".
Database routine example 6
The following ESQL code demonstrates how to define and call Informix® stored procedures:
ESQL Definition:
DECLARE inputParm CHARACTER 'Hello';
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
INOUT parm2 CHARACTER,
INOUT parm3 CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;
To register this stored procedure with Informix, copy the following script to a file (for example, test1.sql)
DROP SPECIFIC PROCEDURE dbSwapParms;
CREATE PROCEDURE dbSwapParms
( inParm CHAR(20),
OUT outParm CHAR(20),
INOUT inoutParm CHAR(20))
SPECIFIC dbSwapParms
LET outParm = inoutParm;
LET inoutParm = inParm;
END PROCEDURE;
Now run file:From the Informix server shell environment enter:
dbaccess <dataBaseName> <fully qualified path/test1.sql>
- The value of the IN parameter does not (and cannot, by definition) change.
- The value of the OUT parameter becomes "World".
- The value of the INOUT parameter changes to "Hello".
The following restrictions apply to Informix stored procedures:
- Procedures that use the Informix INTERVAL datatype cannot be invoked from the broker.
- Procedures can return only one result set.
- Procedures that return a result set must contain only IN parameters.
- Procedures cannot return CLOBs or BLOBs in result sets or as scalar return values.
- Procedures can return either a result set or a scalar value, but not both.
Database routine example 7
This example shows how to call a stored procedure that returns two result sets, in addition to an out parameter:
CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 2
EXTERNAL NAME "myschema.myproc";
Use the following ESQL to invoke myProc1:
/* using a field reference */
CALL myProc1(intVar1, intVar2, Environment.RetVal[], OutputRoot.XMLNS.A[])
/* using a reference variable*/
CALL myProc1(intVar1, intVar2, myReferenceVariable.RetVal[], myRef2.B[])