Batch-Execute-Procedure

Integration with Other Applications > Info*Engine Adapters > JDBC Adapter Guide > JDBC Webject Library > Batch-Execute-Procedure

Description

Executes an SQL stored procedure for multiple sets of input parameter values.

The webject provides support for ARRAY and STRUCT-type IN arguments only when the underlying database type is Oracle. Executing the webject for ARRAY or STRUCT-type IN arguments for database types other than Oracle would result in an exception getting thrown.

Syntax

<ie:webject name="Batch-Execute-Procedure" type="ACT">
<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="input_data"/>
<ie:param name="GROUP_OUT" data="group_out"/>
<ie:param name="INSTANCE" data="instance"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="SQL" data="procedure_call"/>
<ie:param name="QUERY_TIMEOUT" data="query_timeout"/>
</ie:webject>

Parameters

Required	Select	Optional
FIELD		BLOB_COUNT
INSTANCE		CONNECTION_ATTEMPTS
SQL		CONNECTION_ATTEMPT_INTERVAL
		DBUSER
		GROUP_OUT
		PASSWD
		QUERY_TIMEOUT

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 Info*Engine User’s Guide.

FIELD

Specifies type and value information for a given IN argument to the stored procedure. The value specified for this webject parameter is typically as follows:

where <name> is the name of the argument, <type> is the SQL parameter type, and <value> is the value of the IN argument. This parameter is required.

You can specify a null value for a given IN argument by entering NULL as its value.

For a stored procedure that takes a certain number of IN arguments (A), the same number of FIELD entries defines an input set (and there are would typically two or more such input sets). Thus, if the stored procedure is to be executed for several sets of input parameter values as a batch, there number of FIELD entries in the webject is the number of input sets multiplied by the number of IN arguments.

The following rules apply to certain IN argument types:

◦ VARCHAR—Values must be enclosed within single quotes.

◦ DATE—Values must use the form:

dd-<month_name>-yyyy

where <month_name> is either the first three characters of the given month’s name or its full name. The value can be optionally enclosed within single quotes.

◦ DATE—If using an Oracle database, you can also specify either of the following:

▪ sysdate uses the system date as defined on the computer in use.

▪ An empty string (“), which passes the date as is to the stored procedure being executed. For example, when passing this to a procedure that inserts table rows, a NULL value is inserted into the DATE type column (assuming the table allows it).

◦ TIME—Values must use the form:

hh:mm:ss

For ARRAY and STRUCT type IN arguments, the value specified for this parameter is as follows:

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

The case associated with any of the specified SQL types is not important. However, the case is important for any of the SQL type names (relevant for ARRAY and STRUCT types).

GROUP_OUT

Identifies the group returned by the webject. This parameter is optional.

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.

SQL

Specifies the signature of the call to the stored procedure. The value specified for this webject parameter is typically as follows:

<procedure_name>(<in1>, <in2>, <in3>, <in4>)

where <procedure_name> is the name of the stored procedure and <in1>, <in2>, <in3>, and <in4> are the IN arguments to the procedure. This parameter is required.

The value must be the signature of a stored procedure that does not have OUT or IN OUT arguments and that returns a simple update count. If any of these conditions are violated, an exception is thrown upon executing the webject.

QUERY_TIMEOUT

Specifies the minimum amount of time (in seconds) the driver would wait before it cancels a running statement (or times out). This parameter is optional.

This parameter does not work on Windows-based installations. Use QUERY_TIMEOUT only when the database is installed on a Linux or UNIX machine. It works with the Oracle 11g Release 2 (11.2.0.1.0) and Oracle 11g Release 1 (11.1.0.7.0) drivers. However, the query timeout does not work with the Oracle 11g Release 1 (11.1.0.6.0) on Linux or UNIX.

Example

The following example documents BatchExecuteProcedure.jsp located in the Windchill installation directory:

codebase/infoengine/jsp/examples/JDBCAdapter/examples

This example illustrates the Batch-Execute-Procedure webject for standard SQL types.

<%@page language="java" session="false" errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core" prefix="ie"%>
<html>
<head><title><Batch-Execute-Procedure Webject</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Batch-Execute-Procedure webject:</h1>
<h3>Executes an SQL stored procedure for multiple input sets as a batch</h3>

<ie:webject name="Batch-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="INP_EMPNO.INTEGER=8000"/>
<ie:param name="FIELD" data="INP_JOB.VARCHAR='SR. CLERK'"/>
<ie:param name="FIELD" data="INP_MGR.INTEGER=7566"/>
<ie:param name="FIELD" data="INP_HD.DATE='14-Jan-2002'"/>
<ie:param name="FIELD" data="INP_SAL.INTEGER=1000"/>
<ie:param name="FIELD" data="INP_DN.INTEGER=20"/>

<ie:param name="FIELD" data="INP_EMPNO.INTEGER=8001"/>
<ie:param name="FIELD" data="INP_JOB.VARCHAR='SR. CLERK'"/>
<ie:param name="FIELD" data="INP_MGR.INTEGER=7566"/>
<ie:param name="FIELD" data="INP_HD.DATE='24-Jun-2002'"/>
<ie:param name="FIELD" data="INP_SAL.INTEGER=950"/>
<ie:param name="FIELD" data="INP_DN.INTEGER=20"/>

<ie:param name="FIELD" data="INP_EMPNO.INTEGER=8002"/>
<ie:param name="FIELD" data="INP_JOB.VARCHAR='SR. CLERK'"/>
<ie:param name="FIELD" data="INP_MGR.INTEGER=7566"/>
<ie:param name="FIELD" data="INP_HD.DATE='30-Dec-2002'"/>
<ie:param name="FIELD" data="INP_SAL.INTEGER=900"/>
<ie:param name="FIELD" data="INP_DN.INTEGER=20"/>

<ie:param name="SQL" data="UPDATE_EMP_RECORD(INP_EMPNO, INP_JOB,
INP_MGR, INP_HD,INP_SAL, INP_DN)"/>
</ie:webject>

</body>
</html>

The above example would execute the SQL stored procedure called UPDATE_EMP_RECORD for three different sets of input parameter values. The three input sets have been shown as separate blocks in the above example for clarity. Each set consists of six FIELD entries, which provide type and value information for the six IN arguments to the stored procedure.

Besides standard types, the webject provides support for ARRAY and STRUCT type IN arguments. The following example documents BatchExecuteProcArray.jsp, which illustrates batch execution of a stored procedure that takes ARRAY type IN arguments:

<%@page language="java" session="false" errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core" prefix="ie"%>
<html>
<head><title> Batch-Execute-Procedure Webject </title>
<BASE
</head>
<body bgcolor="#AABBCC">
<h1> Batch-Execute-Procedure webject: </h1>
<h3>Executes an SQL stored procedure for multiple input sets as a batch.</h3>
<h3>Illustrates batch execution for ARRAY type parameters.</h3>

<ie:webject name="Batch-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="INP_NUM.INTEGER=1"/>
<ie:param name="FIELD" data="INP_ARRAY1.ARRAY.INT_ARRAY=(10, 20, 30)"/>
<ie:param name="FIELD" data="INP_ARRAY2.ARRAY.REAL_ARRAY=(12.3, 23.4, 34.5)"/>
<ie:param name="FIELD" data="INP_ARRAY3.ARRAY.STRING_ARRAY=
('A','SAMPLE','STRING')"/>
<ie:param name="FIELD" data="INP_ARRAY4.ARRAY.ADDRESS_ARRAY=
(ADDRESS('SCIOTO',101),ADDRESS('ST. MARY',254))"/>

<ie:param name="FIELD" data="INP_NUM.INTEGER=2"/>
<ie:param name="FIELD" data="INP_ARRAY1.ARRAY.INT_ARRAY=(40, 50, 60)"/>
<ie:param name="FIELD" data="INP_ARRAY2.ARRAY.REAL_ARRAY=(43.4, 56.7,-67.8)"/>
<ie:param name="FIELD" data="INP_ARRAY3.ARRAY.STRING_ARRAY=
('ANOTHER','SAMPLE','STRING')"/>
<ie:param name="FIELD"
data="INP_ARRAY4.ARRAY.ADDRESS_ARRAY=(ADDRESS('PATRICK',124),ADDRESS('ST.
JOHN',22))"/>

<ie:param name="FIELD" data="INP_NUM.INTEGER=3"/>
<ie:param name="FIELD" data="INP_ARRAY1.ARRAY.INT_ARRAY=(-70, 80, -90)"/>
<ie:param name="FIELD" data="INP_ARRAY2.ARRAY.REAL_ARRAY=(-72.3, 83.4,-94.5)"/>
<ie:param name="FIELD"
data="INP_ARRAY3.ARRAY.STRING_ARRAY=('YET','ANOTHER','SAMPLE','STRING')"/>
<ie:param name="FIELD"
data="INP_ARRAY4.ARRAY.ADDRESS_ARRAY=(ADDRESS('PARKER',6),ADDRESS('BISHAN',11))"/>

<ie:param name="SQL" data="UPDATE_VARRAY_EXAMPLE2(INP_NUM, INP_ARRAY1,
INP_ARRAY2, INP_ARRAY3, INP_ARRAY4)"/>
</ie:webject>

</body>
</html>

The following example documents BatchExecuteProcStruct.jsp, which illustrates batch execution of a stored procedure that takes a STRUCT type IN argument.