Prepared-Batch-Update
Description
Executes a parameterized SQL update command for multiple sets of input parameter values.
Syntax
<ie:webject name="Prepared-Batch-Update" 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="DELIMITER" data="delimiter_used"/>
<ie:param name="INSTANCE" data="instance"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="GROUP_OUT" data="group_out"/>
<ie:param name="PARAMTYPES"
data="delimiter_separated_parameter_types_in_order"/>
<ie:param name="PARAMVALUES"
data="delimiter_separated_parameter_values_in_order"/>
<ie:param name="SQL" data="a_parameterized_SQL_statement"/>
<ie:param name="QUERY_TIMEOUT" data="query_timeout"/>
</ie:webject>
Parameters
|
Required
|
Select
|
Optional
|
|
INSTANCE
|
DELIMITER
|
BLOB_COUNT
|
|
PARAMTYPES
|
|
CONNECTION_ATTEMPTS
|
|
PARAMVALUES
|
|
CONNECTION_ATTEMPT_INTERVAL
|
|
SQL
|
|
DBUSER
|
|
|
GROUP_OUT
|
|
|
PASSWD
|
|
|
QUERY_TIMEOUT
|
|
|
A null value can be specified for a given parameter (provided the associated column allows it) by specifying NULL for the value.
|
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.
DELIMITER
Specifies the delimiter that is used to separate any two consecutive entries in the string specified as values for the webject parameter PARAMTYPES or PARAMVALUES. This webject parameter is required only when the SQL statement being executed takes two or more SQL parameters.
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.
PARAMTYPES
Specifies the types of SQL parameters in the order they occur in the command. Enter the value as a string, with any two consecutively occurring types separated by a delimiter. When entering a value, the case associated with any of the standard SQL types is not important. However, when dealing with ARRAY or STRUCT type parameters, the SQL type name must be specified in exactly the same way as it was created.
The following is a list of possible types:
|
ARRAY
|
DOUBLE
|
SMALLINT
|
|
BIGINT
|
FLOAT
|
STRUCT
|
|
BINARY
|
INTEGER
|
TIME
|
|
BIT
|
NULL
|
TINYINT
|
|
DATE
|
NUMERIC
|
CHAR
|
|
DECIMAL
|
REAL
|
VARCHAR
|
|
NCHAR
|
NVARCHAR
|
|
|
|
The webject provides support for ARRAY and STRUCT-type SQL parameters only when the underlying database type is Oracle. Executing the webject for ARRAY or STRUCT-type SQL parameters for database types other than Oracle would result in an exception getting thrown.
|
PARAMVALUES
Specifies the values of SQL parameters in the order they occur in the command. The parameter should be entered as a string consisting of the SQL parameter values, with any two consecutively-occurring values separated by a delimiter.
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 parameterized SQL update command. There can be only one entry for this parameter. This restriction does not apply to the Do-SQL webject, which can have multiple entries of the SQL webject parameter.
|
|
This parameter value must be a parameterized SQL command that returns a simple update count. If this condition is 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 PreparedBatchUpdate.jsp located in the Windchill installation directory:
codebase/infoengine/jsp/examples/JDBCAdapter/examples
It illustrates the Prepared-Batch-Update 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>Prepared-Batch-Update Webject</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Prepared-Batch-Update webject: </h1>
<h3>Executes a parameterized SQL update command for multiple
input sets as a batch</h3>
<ie:webject name="Prepared-Batch-Update" 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="SQL" data="INSERT INTO EMP VALUES
(?, ?, ?, ?, ?, ?, ?, ?)"/>
<ie:param name="DELIMITER" data="^"/>
<ie:param name="PARAMTYPES"
data="INTEGER^VARCHAR^VARCHAR^INTEGER^DATE^INTEGER^INTEGER^INTE
GER"/>
<ie:param name="PARAMVALUES"
data="8000^'John'^'CLERK'^7902^'11-Jan-1999'^900^NULL^20"/>
<ie:param name="PARAMVALUES"
data="8001^'Jim'^'CLERK'^7902^'21-Jun-1999'^850^NULL^20"/>
<ie:param name="PARAMVALUES"
data="8002^'Jack'^'CLERK'^7902^'27-Dec-1999'^800^NULL^20"/>
<ie:param name="GROUP_OUT" data="PreparedBatchUpdate"/>
</ie:webject>
</body>
</html>
The webject provides support for ARRAY and STRUCT-type SQL parameters in addition to the standard types. The PreparedBatchUpdArray.jsp example file illustrates batch execution for ARRAY type SQL parameters:
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Prepared-Batch-Update Webject</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Prepared-Batch-Update webject: </h1>
<h3>Executes a parameterized SQL update command for multiple
input sets as a batch.</h3>
<h3>Illustrates batch execution for ARRAY type parameters.</h3>
<ie:webject name="Prepared-Batch-Update" 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="SQL" data="INSERT INTO
VARRAY_EXAMPLE2 VALUES (?, ?, ?, ?, ?)"/>
<ie:param name="DELIMITER" data="^"/>
<ie:param name="PARAMTYPES"
data="INTEGER^ARRAY:INT_ARRAY^ARRAY:REAL_ARRAY^ARRAY:STRING_ARR
AY^ARRAY:ADDRESS_ARRAY"/>
<ie:param name="PARAMVALUES" data="1^INT_ARRAY(1, 2,
3)^REAL_ARRAY(1.23, 2.34,
3.45)^STRING_ARRAY('a','sample','string')^ADDRESS_ARRAY(ADDRESS
('Scioto',101),ADDRESS('St. Mary',254))"/>
<ie:param name="PARAMVALUES" data="2^INT_ARRAY(4, 5,
6)^REAL_ARRAY(4.34, 5.67, -
6.78)^STRING_ARRAY('another','sample','string')^ADDRESS_ARRAY(A
DDRESS('Patrick',124),ADDRESS('St. John',22))"/>
<ie:param name="PARAMVALUES" data="3^INT_ARRAY(-7, 8, -
9)^REAL_ARRAY(-7.23, 8.34, -
9.45)^STRING_ARRAY('yet','another','sample','string')^ADDRESS_A
RRAY(ADDRESS('Parker',6),ADDRESS('Bishan',11))"/>
<ie:param name="GROUP_OUT" data="PreparedBatchUpdArray"/>
</ie:webject>
</body>
</html>
The PreparedBatchUpdStruct.jsp example file illustrates batch execution for STRUCT type SQL parameters:
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Prepared-Batch-Update Webject</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Prepared-Batch-Update webject: </h1>
<h3>Executes a parameterized SQL update command for multiple
input sets as a batch.</h3>
<h3>Illustrates batch execution for STRUCT type
parameters.</h3>
<ie:webject name="Prepared-Batch-Update" 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="SQL" data="INSERT INTO PEOPLE
VALUES (?, ?)"/>
<ie:param name="DELIMITER" data="^"/>
<ie:param name="PARAMTYPES" data="INTEGER^STRUCT:PERSON"/>
<ie:param name="PARAMVALUES" data="101^PERSON('Greg',
ADDRESS('Van Ness', 345))"/>
<ie:param name="PARAMVALUES" data="102^PERSON('Patterson',
ADDRESS('Geary', 412))"/>
<ie:param name="PARAMVALUES" data="103^PERSON('Mathews',
ADDRESS('Burntstones', 169))"/>
<ie:param name="GROUP_OUT" data="PreparedBatchUpdStruct"/>
</ie:webject>
</body>
</html>
