Integrazione con altre applicazioni > Adattatori Info*Engine > JDBC Adapter Guide > JDBC Webject Library > Transaction
  
Transaction
Description
This webject can be used to perform the following actions:
Start or end a database transaction
Create a save point
Perform a database commit
Rollback changes either wholly or to a given save point within the transaction
These actions are implemented by providing the appropriate webject parameters.
Syntax
<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="instance"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<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="ACTION" data="type_of_action"/>
<ie:param name="SAVEPNTNAME" data="savepoint_name"/>
<ie:param name="SESSION_ID" data="session_id_string"/>
<ie:param name="GROUP_OUT" data="group_out"/>
</ie:webject>
* 
The parameter SAVEPNTNAME can be used only when the ACTION parameter takes a value of SAVEPOINT or ROLLBACK.
Parameters
Required
Select
Optional
INSTANCE
SESSION_ID
BLOB_COUNT
ACTION
SAVEPNTNAME
CONNECTION_ATTEMPTS
GROUP_OUT
CONNECTION_ATTEMPT_INTERVAL
DBUSER
PASSWD
Parameters Used With a START Transaction
This is to begin a database transaction. This would be the first action to occur in a task composed of a series of webjects participating in a given transaction.
* 
Executing the webject for this action results in a connection instance being checked out from an existing pool of connections, which in turn is used for the given transaction.
INSTANCE, ACTION and GROUP_OUT are the required webject parameters. The remaining parameters are optional.
ACTION
Specifies the type of action associated with the given transaction. In order to begin a database transaction, this must be set to START. Specifying multiple ACTION webject parameters within a given <ie:webject> element is an error.
* 
The case associated with the value provided for the ACTION parameter is unimportant. For example, the value can be "START", "start", and so on.
GROUP_OUT
Specifies the name of the group returned by the webject. A START transaction webject stores the session ID in this group, which in turn would be accessed by the subsequently executed webjects that participate in the same transaction.
SESSION_ID
This webject parameter is optional. If the user does not provide this parameter, a session ID is generated internally and stored in the output group. However, if the user does provide this parameter, the specified value is simply stored in the output group.
* 
The webject throws an exception if the value provided for the SESSION_ID parameter is an ID that is already being used by some other concurrently executing webject.
Parameters Used With a COMMIT Transaction
This is to perform a database commit. Executing this webject causes any previously made changes to be saved permanently to the database.
* 
Executing the webject for this action results in the database connection corresponding to the given transaction to be released back into a pool of connections. However, the session ID associated with the transaction would still be intact and can be referenced by any of the subsequently executed webjects that are participating in the same transaction.
INSTANCE, ACTION and SESSION_ID are the required webject parameters while the remaining ones are optional.
ACTION
Specifies the type of action associated with the given transaction. In order to perform a database commit, this must be set to COMMIT. Specifying multiple ACTION webject parameters within a given <ie:webject> element is an error.
* 
The case associated with the value provided for the ACTION parameter is unimportant. Thus, the value can be "COMMIT", "commit", and so on.
SESSION_ID
The webject uses this to fetch the database connection corresponding to the given transaction and it is on this connection that a commit is performed.
Specify a value for this webject parameter as follows:
<ie:param name="SESSION_ID" data="${<value>[]session_id[]}"/>
The above syntax makes use of dynamic parameter value substitution, where <value> is the value provided for the webject parameter GROUP_OUT in the START transaction webject that was used to start the given transaction.
Parameters Used With a SAVEPOINT Transaction
This is to set a save point within a given transaction. Once a save point has been created, the transaction can be rolled back to that save point without affecting any of the changes that occurred prior to its creation.
INSTANCE, ACTION, SAVEPNTNAME and SESSION_ID are the required webject parameters while the remaining ones are optional.
ACTION
Specifies the type of action associated with the given transaction. In order to create a save point within the transaction, this must be set to SAVEPOINT. Specifying multiple ACTION webject parameters within a given <ie:webject> element is an error.
* 
The case associated with the value provided for the ACTION parameter is unimportant. Thus, the value can be “SAVEPOINT”, “savepoint”, and so on.
SAVEPNTNAME
The name of the save point instance to be created.
The following rules apply to the parameter value:
The first character must be a letter
The following characters must either be a letter, a numerical digit, or any of the characters #, $, and _
An exception is thrown upon violating any of these restrictions.
* 
If the value provided for SAVEPNTNAME is also the name of a previously created save point instance that exists within the same transaction, the most recent instance replaces the old instance.
SESSION_ID
Identifies the given transaction, which is where the save point is created.
Specify a value for this webject parameter as follows:
<ie:param name="SESSION_ID" data="${<value>[]session_id[]}"/>
where <value> is the value provided for the webject parameter GROUP_OUT in the START transaction webject that was used to start the given transaction.
Parameters Used With a ROLLBACK Transaction
This is to rollback previously made changes either wholly or to a given save point within the transaction.
INSTANCE, ACTION and SESSION_ID are the required webject parameters while the remaining ones are optional.
ACTION
Specifies the type of action associated with the given transaction. In order to rollback any changes, this must be set to ROLLBACK. Specifying multiple ACTION webject parameters within a given <ie:webject> element is an error.
* 
The case associated with the value provided for the ACTION parameter is unimportant. Thus, the value can be "ROLLBACK", "rollback", and so on.
SAVEPNTNAME
Specifies the name of the save point to rollback to. This must correspond to an existing save point instance within the given transaction. This is an optional parameter.
To rollback all of the changes made during the given transaction, do not provide a value for this parameter.
SESSION_ID
The webject uses this to fetch the database connection corresponding to the given transaction and it is on this connection that a rollback is performed. This parameter is required.
Specify a value for this webject parameter as follows:
<ie:param name="SESSION_ID" data="${<value>[]session_id[]}"/>
where <value> is the value provided for the webject parameter GROUP_OUT in the START transaction webject that was used to start the given transaction.
Parameters Used With an End Transaction
This is to end a given transaction, ensuring that previously made changes are saved permanently to the database.
INSTANCE, ACTION and SESSION_ID are the required webject parameters while the remaining ones are optional.
ACTION
Specifies the type of action associated with the given transaction. In order to end the transaction, this must be set to END. Specifying multiple ACTION webject parameters within a given <ie:webject> element is an error.
SESSION_ID
Identifies the given transaction. Once the transaction has been identified, a database commit is performed, thereby ending the transaction. This parameter is required.
Specify a value for this webject parameter as follows:
<ie:param name="SESSION_ID" data="${<value>[]session_id[]}"/>
where <value> is the value provided for the webject parameter GROUP_OUT in the START transaction webject that was used to start the given transaction.
Common Parameters
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.
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.
Example
The following example documents Transaction.jsp located in the Windchill installation directory:
codebase/infoengine/jsp/examples/JDBCAdapter/examples
It illustrates how the Transaction webject can be used in a task to effectively manage a database transaction. The example modifies the table EMP using the following actions:
1. A transaction is first started using the START Transaction webject.
2. Three arbitrary rows are inserted into the table using the Do-Sql webject, which are then updated using the Prepared-Batch-Update webject.
3. The changes are saved permanently into the database through the COMMIT transaction webject.
4. A save point named “svpnt#1” is set through the SAVEPOINT transaction webject.
5. Two more rows are inserted using the Do-Sql and Create-Object webjects and a save point named “svpnt#1” is set once again, replacing the previous save point.
6. The row that was inserted using the Create-Object webject is deleted using the Delete-Objects webject.
7. A rollback is then performed to the save point through the ROLLBACK transaction webject. This undoes the deletion caused by the Delete-Objects webject.
Note the use of the success and failure blocks within a unit in the example to provide for success and failure processing.
If all of the above described actions went through fine, control would enter the success block; here, an output group called “success” is created and the transaction is ended using the END transaction webject. This saves the previously made changes permanently into the database.
If any of the actions had failed, control would directly enter the failure block; here, an output group called “failure” is created and the previously made changes are then rolled back completely. This ensures that the database is not left in an inconsistent state.
Also, note the use of a number of other JDBC adapter webjects in the example. In order for these to participate in a transaction, it is only required to use the SESSION_ID webject parameter in each of these. This ensures that all such webjects get executed as part of the same transaction. If the SESSION_ID parameter is not provided for any of these other webjects, they would still get executed, but not as part of the given transaction.
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>

<html>
<head><title>Transaction Management</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Transaction Management: </h1>
<h3>Illustrates the various transaction webjects.</h3>

<ie:unit>

<ie:webject name="Transaction" 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="ACTION" data="START"/>
<ie:param name="GROUP_OUT" data="session"/>
</ie:webject>

<ie:webject name="Do-SQL" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8000,'John','CLERK',7902,'11-Jan-1999',900,NULL,20)"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8001,'Jim','CLERK',7902,'21-Jun-1999',850,NULL,20)"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8002,'Jack','CLERK',7902,'27-Dec-1999',800,NULL,20)"/>
<ie:param name="MODE" data="batch"/>
<ie:param name="GROUP_OUT" data="DoSql"/>
</ie:webject>

<ie:webject name="Prepared-Batch-Update" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="SQL" data="UPDATE EMP SET SAL = ?,
HIREDATE = ? WHERE EMPNO = ?"/>
<ie:param name="DELIMITER" data="^"/>
<ie:param name="PARAMTYPES" data="INTEGER^DATE^INTEGER"/>
<ie:param name="PARAMVALUES" data="800^'12-Jan-
1999'^8000"/>
<ie:param name="PARAMVALUES" data="750^'22-Jun-
1999'^8001"/>
<ie:param name="PARAMVALUES" data="700^'28-Dec-
1999'^8002"/>
<ie:param name="GROUP_OUT" data="PreparedBatchUpdate"/>
</ie:webject>

<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="ACTION" data="COMMIT"/>
</ie:webject>

<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="ACTION" data="SAVEPOINT"/>
<ie:param name="SAVEPNTNAME" data="svpnt#1"/>
</ie:webject>

<ie:webject name="Do-SQL" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8003,'Jerry','CLERK',7902,'20-DEC-1999',800,NULL,20)"/>
<ie:param name="GROUP_OUT" data="DoSql"/>
</ie:webject>

<ie:webject name="Create-Object" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="CLASS" data="EMP"/>
<ie:param name="FIELD" data="EMPNO='1110'"/>
<ie:param name="FIELD" data="ENAME='Herman'"/>
<ie:param name="FIELD" data="JOB='ENGINEER'"/>
<ie:param name="FIELD" data="MGR='7990'"/>
<ie:param name="FIELD" data="HIREDATE='23-MAY-1999'"/>
<ie:param name="FIELD" data="SAL='2200'"/>
<ie:param name="FIELD" data="COMM=''"/>
<ie:param name="FIELD" data="DEPTNO='40'"/>
<ie:param name="GROUP_OUT" data="CreateObject"/>
</ie:webject>

<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="ACTION" data="SAVEPOINT"/>
<ie:param name="SAVEPNTNAME" data="svpnt#1"/>
</ie:webject>

<ie:webject name="Delete-Objects" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="CLASS" data="${@FORM[]table[]}"
default="EMP"/>
<ie:param name="WHERE" data="${@FORM[]where[]}"
default="ENAME='Herman'"/>
<ie:param name="GROUP_OUT" data="DeleteObject"/>
</ie:webject>

<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="ACTION" data="ROLLBACK"/>
<ie:param name="SAVEPNTNAME" data="svpnt#1"/>
</ie:webject>

<ie:success>
<ie:webject name="Create-Group" type="GRP">
<ie:param name="ELEMENT" data="SUCCESS=success"/>
<ie:param name="DELIMITER" data=":"/>
<ie:param name="GROUP_OUT" data="success"/>
</ie:webject>
<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="ACTION" data="END"/>
</ie:webject>
</ie:success>
<ie:failure>
<ie:webject name="Create-Group" type="GRP">
<ie:param name="ELEMENT" data="FAILURE=failure"/>
<ie:param name="DELIMITER" data=":"/>
<ie:param name="GROUP_OUT" data="failure"/>
</ie:webject>
<ie:webject name="Transaction" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="SESSION_ID"
data="${session[]session_id[]}"/>
<ie:param name="ACTION" data="ROLLBACK"/>
</ie:webject>
<ie:webject name="Throw-Exception" type="MGT"/>
</ie:failure>
</ie:unit>
</body>
</html>