Using External Transaction for DB Component

External Transaction is used to provide explicit commands to begin and end transactions instead of running a component in Scheduler mode.

Configuring External Transaction

To configure the DB component for using External Transaction in queries:

1. Double-click the DB component to open CPS and configure it.
2. In the Managed Connection Factory dialog box, click the Database Configuration ellipsis

   

   button.
   
   

   

   
   
   
3. Connect to a database by setting the Component Configuration or using a pre-configured database from Named Configuration and then click OK.
   
   

   

   
   
   
4. Click Next in the Managed Connection Factory dialog box. In the Interaction Configurations dialog box, click the SQL Configuration Ellipsis

   

   button
   
   

   

5. In the SQL Configuration dialog box, click Add button to configure a query, and then click Next.
   
   

   

   
   
   

6. In the Advanced Properties dialog box, change the value of the Use External Transactions property from "false" to "true" by selecting from the drop-down, and click Finish.
   
   

   

   
    
   

   "Scheduler" mode (One of the two Adapter Modes in SQL Configuration dialog box) does not work for External Transaction which runs only by sending designated commands.

   

7. Click Finish in the Interaction Configurations dialog box, which closes the CPS marking the end of configuration.

Running a DB Event Process using External Transaction

External Transaction Commands

To run the Event Process through External Transaction, send the commands in the below order:

1. BEGINTRANSACTION Starts an external transaction.
2. <QueryName> Query name as created in SQL Configuration.
3. ROLLBACK This command is not a mandatory one, but used only to rollback the transactions done upto that particular point in the current transaction.
4. ENDTRANSACTION DB component commits the database operations done in current transaction and ends the external transaction.
   
   

• If the commands: ENDTRANSACTION or ROLLBACK, or any other commands are used before BEGINTRANSACTION command, then the component throws an exception: "INVALID REQUEST ERROR".

• ROLLBACK does not work after ending the current transaction, that is, after the ENDTRANSACTION command is used.

Usage Example

Create a DB Event Process with the components: Feeder and Display connected to the DB component as below.

Follow the steps below:

1. Configure the DB component as explained in the section: Configuring External Transaction.
2. Configure Feeder component by connecting port schemas.
3. Run the Event Process after checking resource and connectivity.
4. In the Feeder that appears, click the Load Default Message button and check whether the second line of the message in the Body tab shows the command: "BEGINTRANSACTION". If any other command is displayed, replace it with BEGINTRANSACTION.
   
   

   

   
   
   
5. Click Send.
   
   
6. Confirm that the message is received in the Display component and same message as in the Feeder component displayed in the Body tab.
   
   

   

   
   
   
7. Switch back to Feeder, type the query name (as created during SQL Configuration) in the second line and click Send.
   
   

   

   
   
   
8. Confirm the receipt of the query in Display and message which shows the status of updation.
   
   

   

   
   
   
9. Type ENDTRANSACTION in Feeder, click Send.
   
   

   

   
   
   

10. Validate ENDTRANSACTION info in Display.
    
    

    

    

    Use ROLLBACK to cancel the query and send the query again, if required.