Execute-Procedure
Description
Executes a stored procedure or function and returns any OUT or RETURN arguments. This is the only JDBC adapter webject that can return more than one group.
This webject requires a procedure or function that has been previously stored in a database that supports stored procedures. Refer to your database documentation for a complete description of how to use stored procedures and functions with your database.
|
|
The webject provides support for multiple IN arguments, multiple OUT arguments, as well as multiple IN OUT arguments. It also provides support for STRUCT and ARRAY type arguments.
The webject is recommended for use with ARRAY and STRUCT type arguments only when the underlying database type is Oracle.
|
Syntax
<ie:webject name="Execute-Procedure" type="ACT">
<ie:param name="ATTRIBUTE" data="name.datatype"/>
<ie:param name="BLOB_COUNT" data="number_of_BLOBs"/>
<ie:param name="CONNECTION_ATTEMPT_INTERVAL" data="interval"/>
<ie:param name="CONNECTION_ATTEMPTS" data="attempts"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="FIELD" data="data"/>
<ie:param name="INSTANCE" data="instance"/>
<ie:param name="PASSWD" data="dbpassword"/>
<ie:param name="RETURN" data="datatype"/>
<ie:param name="SQL" data="procedure_call"/>
</ie:webject>
Parameters
|
Required
|
Select
|
Optional
|
|
INSTANCE
|
ATTRIBUTE
|
BLOB_COUNT
|
|
SQL
|
FIELD
|
CONNECTION_ATTEMPTS
|
|
RETURN
|
CONNECTION_ATTEMPT_INTERVAL
|
|
|
DBUSER
|
|
|
PASSWD
|
ATTRIBUTE
Specifies an OUT argument to the stored procedure. The value for this parameter is typically written in the following format:
<name>.<datatype>
where <name> is the name of the argument and <datatype> is the SQL parameter type.
The following is a list of possible types:
|
ARRAY
|
DOUBLE
|
REAL
|
|
BIGINT
|
FLOAT
|
SMALLINT
|
|
BINARY
|
INTEGER
|
STRUCT
|
|
BIT
|
LONGVARBINARY
|
TIME
|
|
CHAR
|
LONGVARCHAR
|
TIMESTAMP
|
|
CURSOR
|
NULL
|
TINYINT
|
|
DATE
|
NUMERIC
|
VARBINARY
|
|
DECIMAL
|
OTHER
|
VARCHAR
|
|
NCHAR
|
NVARCHAR
|
|
If there are multiple OUT arguments for the stored procedure, each OUT argument results in an output group upon executing the procedure. The name of the resulting group is the same as the value specified for the ATTRIBUTE parameter. For example, consider the following ATTRIBUTE parameter specifications:
<ie:param name="ATTRIBUTE" data="sal.NUMBER"/>
<ie:param name="ATTRIBUTE" data="deptno.NUMBER"/>
Executing the procedure results in two output groups:sal.NUMBER and deptno.NUMBER. You can then use these names as the input values for the GROUP_IN parameter values in a display webject.
To pass an IN OUT argument to the stored function or procedure using this webject, the argument name must be specified in both the ATTRIBUTE and FIELD parameters.
The ATTRIBUTE parameter can be multi-valued. The number of values specified depends on the number of IN OUT and OUT arguments passed to the stored procedure.
For ARRAY and STRUCT type arguments, the value specified for this parameter is as follows:
<name>.<datatype>.<type_name>
where:
◦ <name> is the name of the argument
◦ <datatype> is either ARRAY or STRUCT
◦ <type_name> is the SQL type name associated with the ARRAY or STRUCT type argument
BLOB_COUNT
Specifies how many BLOBs to deliver to the webject. Specifying a value of 0 results in no BLOBs being delivered. Specifying a value of more than 0 results in up to that specified number of BLOBs being delivered. For example, if this parameter is specified with a value of 5, then no more than five BLOBs are delivered to the webject.
The default behavior for this parameter is that all remaining BLOBs are delivered to the webject. This parameter is optional.
CONNECTION_ATTEMPTS
Defines the maximum number of times to attempt establishing a connection to an adapter before returning an error. The default value is 1. This parameter is optional.
If multiple INSTANCE parameter values are specified, the value of CONNECTION_ATTEMPTS defines the maximum number of times to iterate through the list of adapter instances.
CONNECTION_ATTEMPT_INTERVAL
Defines the amount of time, in seconds, to delay between connection attempts. The default value is 60 seconds. This parameter is optional.
If multiple INSTANCE parameter values are specified, the value of CONNECTION_ATTEMPT_INTERVAL defines the number of seconds to wait between the attempts to iterate through the entire list of adapter instances.
DBUSER
Specifies the name to use when logging in to the data repository. If this parameter is specified in this webject, the webject value takes precedence over any value specified in the credentials mapping settings or in the adapter LDAP entry. If this parameter is not specified here, it must be specified in the credentials mapping settings or in the adapter LDAP entry. For more information about credentials mapping, see the section
Credentials Mapping.
FIELD
Specifies an IN argument to the stored procedure. The value for this parameter is typically of the form:
<name>.<type>=<value>
where <name> is the name of the argument, <type> is the SQL parameter type, and <value> is the value of the IN argument.
To pass an IN OUT argument to the stored function or procedure using this webject, the argument name must be specified in both the ATTRIBUTE and FIELD parameters.
The FIELD parameter can be multi-valued. The number of values specified depends on the number of IN and IN OUT arguments passed to the procedure.
For ARRAY and STRUCT type IN arguments, the value specified for this parameter is as follows:
<name>.<type>.<type_name>=<value>
where:
◦ <name> is the name of the IN argument
◦ <type> is either ARRAY or STRUCT
◦ <type_name> is the SQL type name associated with the ARRAY or STRUCT type argument
◦ <value> is a comma-separated set of either ARRAY elements or STRUCT attribute values
INSTANCE
Specifies the name of the adapter that executes the webject. Adapter names are defined when the adapter is configured for use in your Info*Engine environment. This parameter is required.
In order to provide the ability to connect to other adapters if a specific adapter is not available, this parameter can be multi-valued. Info*Engine attempts to connect to the adapters in the order given. If the first adapter specified is not available, the next adapter listed is tried, and so on, until a connection is made. If a connection cannot be established with any listed adapter, an error is returned.
In conjunction with this parameter, you can include two other parameters: CONNECTION_ATTEMPTS and CONNECTION_ATTEMPT_INTERVAL.
PASSWD
Specifies the password to use when logging in to the data repository. If this parameter is specified in this webject, the webject value takes precedence over any value specified in the credentials mapping settings or in the adapter LDAP entry. If this parameter is not specified here, it must be specified in the credentials mapping settings or in the adapter LDAP entry. For more information about credentials mapping, see the section
Credentials Mapping.
RETURN
Specifies the data type of the value returned by the stored function.
This parameter is required only if the webject is executing a stored function. If this parameter is specified when the webject is executing a stored procedure, then an error is returned.
The following is a list of possible data types:
|
ARRAY
|
DOUBLE
|
REAL
|
|
BIGINT
|
FLOAT
|
SMALLINT
|
|
BINARY
|
INTEGER
|
TIME
|
|
BIT
|
LONGVARBINARY
|
TIMESTAMP
|
|
CHAR
|
LONGVARCHAR
|
TINYINT
|
|
CURSOR
|
NULL
|
VARBINARY
|
|
DATE
|
NUMERIC
|
VARCHAR
|
|
DECIMAL
|
OTHER
|
|
|
NCHAR
|
NVARCHAR
|
|
SQL
Describes the signature of the call. The names defined in the FIELD and ATTRIBUTE parameters are used here. This parameter is required.
The value for this parameter is typically formatted as follows:
<proc>(<in>, <in_out>, <out>)
where <proc> is the name of the stored procedure or stored function and <in>, <in_out>, and <out> are the IN, IN OUT and OUT arguments of the stored procedure.
If there are no IN OUT arguments for the stored procedure, the number of FIELD and ATTRIBUTE parameter values should equal the number of IN and OUT arguments (respectively) specified in the SQL parameter. For example, consider the following webject parameter specifications:
<ie:param name=FIELD data="in1.varchar='first in'"/>
<ie:param name=FIELD data="in2.varchar='second in'"/>
<ie:param name=ATTRIBUTE data="out1.varchar"/>
<ie:param name=SQL="proc(in1, in2, out1)"/>
The FIELD and ATTRIBUTE parameters describe the data, and the SQL parameter describes how the information is passed to the procedure or function. By keeping the information and the signature separate, the webject accepts any combination of arguments.
|
|
If the stored procedure takes IN OUT arguments, there is a FIELD and an ATTRIBUTE parameter for each IN OUT argument in addition to those specified for the IN and the OUT arguments.
|
Executing a Stored Procedure
|
|
The stored functions and procedures executed in the following examples are created when the demo database is built. For more information, see Running the Webject Examples. All example files are located in
<Windchill>/codebase/infoengine/jsp/examples/JDBCAdapter/examples
To run these examples on your own system, you need to replace the values of the INSTANCE, DBUSER and PASSWD parameters with values appropriate to your installation.
|
The following example documents ExecuteProcedure.jsp.
In this example, the stored procedure TESTPROC is executed. The results of both the OUT arguments of the stored procedure are then displayed using Display-Table webjects.
|
|
If the database is run from an SQL server, see the ExecuteProcedureForSQLServer.jsp example.
|
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Execute Procedure Webject</title>
<BASE>
</head>
<body>
<h1>Execute-Procedure webject: </h1>
<h3>Execute a SQL stored procedure</h3>
<ie:webject name="Execute-Procedure" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="FIELD" data="IN_ARG.varchar='in_param'"/>
<ie:param name="FIELD"
data="INOUT_ARG.varchar='inout_param'"/>
<ie:param name="ATTRIBUTE" data="INOUT_ARG.varchar"/>
<ie:param name="ATTRIBUTE" data="OUT_ARG.varchar"/>
<ie:param name="SQL"
data="TESTPROC(IN_ARG,OUT_ARG,INOUT_ARG)"/>
</ie:webject>
</hr>
<ie:webject name="Display-Table" type="DSP">
<ie:param name="GROUP_IN" data="INOUT_ARG.varchar"/>
</ie:webject>
<ie:webject name="Display-Table" type="DSP">
<ie:param name="GROUP_IN" data="OUT_ARG.varchar"/>
</ie:webject>
</body>
</html>
Executing a Stored Procedure That Takes a STRUCT Type IN Argument
The following example documents Executeinsert_emprec.jsp.
In this example, the stored procedure insert_emp_rec is executed. This procedure takes two IN arguments, and the values specified for these (using the FIELD parameters) are used to insert a row into the EMP_LIST table. Note that this table has a STRUCT type column with the SQL type name EMP_REC.
%@page language="java" session="false" errorPage="IEError.jsp"%
%@taglib uri="http://www.ptc.com/infoengine/taglib/core" prefix="ie"%
<html>
<head><title>Execute Procedure Webject</title>
<BASE>
</head>
<body>
<h1>Execute-Procedure webject: </h1>
<h3>Execute a SQL stored function</h3>
<ie:webject name="Execute-Procedure" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}" default="jdbcAdapter"/>
<ie:param name="DBUSER" data="db_user"/>
<ie:param name="PASSWD" data="db_passwd"/>
<ie:param name="FIELD" data="in1.INTEGER=667"/>
<ie:param name="FIELD" data="in2.STRUCT.EMP_REC=(George M
Willis,ARRAY.STRINGARRAY=('196 Florence','Arden Hills'),150000.50,23-Aug-1999)"/>
<ie:param name="SQL" data="insert_emp_rec(in1,in2)"/>
</ie:webject>
</body>
</html>
Executing a Stored Procedure That Takes a STRUCT Type OUT Argument
The following example documents Executeget_emprec.jsp.
In this example, the stored procedure get_emp_rec is executed. This procedure takes an INTEGER type IN argument and a STRUCT type OUT argument. Executing the procedure results in the retrieval of the row that was inserted into the EMP_LIST table by the previous example. The STRUCT type value that is retrieved is stored in an output group, which is then displayed using a Display-Table webject.
%@page language="java" session="false" errorPage="IEError.jsp"%
%@taglib uri="http://www.ptc.com/infoengine/taglib/core" prefix="ie"%
<html>
<head><title>Execute Procedure Webject</title>
<BASE>
</head>
<body>
<h1>Execute-Procedure webject: </h1>
<h3>Execute a SQL stored function</h3>
<ie:webject name="Execute-Procedure" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}" default="jdbcAdapter"/>
<ie:param name="DBUSER" data="db_user"/>
<ie:param name="PASSWD" data="db_passwd"/>
<ie:param name="FIELD" data="in1.INTEGER=667" />
<ie:param name="ATTRIBUTE" data="in2.STRUCT.EMP_REC" />
<ie:param name="SQL" data="get_emp_rec(in1,in2)"/>
</ie:webject>
<ie:webject name="Display-Table" type="DSP">
<ie:param name="GROUP_IN" data="in2.STRUCT.EMP_REC"/>
</ie:webject>
</body>
</html>
Executing a Stored Function with Cursor Data Type
The following example documents ExecuteReturnCursor.jsp.
In this example, the stored function named return_allemployees is executed. The result of the OUT argument of the stored function has a data type of CURSOR, and is then displayed using the Display-Table webject.
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Execute Procedure Webject</title>
<BASE>
</head>
<body>
<h1>Execute-Procedure webject: </h1>
<h3>Execute a SQL stored function</h3>
<ie:webject name="Execute-Procedure" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="RETURN" data="CURSOR"/>
<ie:param name="SQL" data="return_allemployees"/>
</ie:webject>
<ie:webject name="Display-Table" type="DSP"/>
</body>
</html>
Executing a Stored Function with Array Data Type
The following example documents ExecuteReturnVarray.jsp.
In this example, the stored function named return_varray is executed. The OUT argument of the stored function has a data type of NUMARRAY, and is then displayed using the Display-Table webject. The NUMARRAY data type is created in the database when the BuildDemo.Db.jsp file runs.
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Execute Procedure Webject</title>
<BASE>
</head>
<body>
<h1>Execute-Procedure webject: </h1>
<h3>Execute a SQL stored function</h3>
<ie:webject name="Execute-Procedure" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="RETURN" data="ARRAY.NUMARRAY"/>
<ie:param name="FIELD" data="i.varchar='decimal'"/>
<ie:param name="SQL" data="return_varray(i)"/>
</ie:webject>
<ie:webject name="Display-Table" type="DSP"/>
</body>
</html>
