10 Designing the Database Adapter to Insert Data

10.2.3.1 Configuring a Database Instance Design-Time Connection

Follow these steps to configure and connect to the database instance.

1. Select Manage Libraries from the Tools main menu.

2. Select System Libraries > Oracle8i Lite.

   Oracle8i Lite appears in the Library Name field.

3. Enter Oracle_Home\integration\orabpel\lib\olite40.jar in the Class Path field or click Edit to select the path.

4. Leave the remaining fields as they are.

5. Click OK.

10.2.3.2 Configuring a BPEL Server Runtime Connection

1. Open a text editor.

2. Open the Oracle_Home\integration\orabpel\system\appserver\oc4j\j2ee\home\application-deployments\default\DbAdapter\oc4j-ra.xml file.

3. Locate the following connection factory settings in the file and ensure that the values in bold appear. If not, change them to these values.

   <connector-factory location="eis/DB/BPELSamples" connector-name="Database Adapter">   <config-property name="driverClassName" value="oracle.lite.poljdbc.POLJDBCDriver"/>
      <config-property name="connectionString" value="jdbc:polite4@localhost:100:orabpel"/>
      <config-property name="userName" value="system"/>
      <config-property name="password" value="manager"/>
      <config-property name="minConnections" value="5"/>
      <config-property name="maxConnections" value="5"/>
      <config-property name="minReadConnections" value="1"/>
      <config-property name="maxReadConnections" value="1"/>
      <config-property name="usesExternalConnectionPooling" value="false"/>
      <config-property name="dataSourceName" value=""/>
      <config-property name="usesExternalTransactionController" value="false"/>
      <config-property name="platformClassName" value="oracle.toplink.internal.databaseaccess.DatabasePlatform"/>
      <config-property name="usesNativeSequencing" value="true"/>
      <config-property name="sequencePreallocationSize" value="50"/>
      <config-property name="tableQualifier" value=""/>
   </connector-factory>
   
   

4. Stop and restart Oracle BPEL Server from the Start Menu, as described in "Starting Oracle BPEL Process Manager Components".

10.2.3.3 Creating a JDeveloper Database Connection

1. Return to JDeveloper BPEL Designer.

2. Select Connection Navigator from the View main menu.

3. Right-click Database in the Connections - Navigator window and select New Database Connection.

   This starts the Create Database Connection Wizard.

4. Click Next on the Welcome window.

5. Enter myConnection in the Connection Name field of the Type window.

6. Select Third Party JDBC Driver from the Connection Type list, and click Next.

7. Enter system in the Username field of the Authentication window.

8. Enter manager in the Password field.

9. Leave the remaining fields as they are, and click Next.

10. Click New to the right of the Driver Class field on the Connection window.

    The Register JDBC Driver window appears.

11. Enter oracle.lite.poljdbc.POLJDBCDriver in the Driver Class field. Ensure that there are no blank spaces at the end of this entry.

12. Click New to the right of the Library field.

    The Create Library window appears.

13. Enter a new library name in the Library Name field (for example, newOracle8Lite).

14. Enter Oracle_Home/integration/orabpel/lib/olite40.jar in the Class Path field or click Edit to select the path.

15. Leave the remaining fields as they are.

16. Click OK to close the Create Library and Register JBDC Driver windows.

17. Enter jdbc:polite4@localhost:100:orabpel in the URL field of the Connection window.

18. Click Next.

19. Click Test Connection on the Test window.

    If the connection was successful, the following message appears:

    Success!
    
    

20. Click Finish.

10.2.3.4 Creating Sample Tables

1. Ensure that Oracle BPEL Server is running.

2. Select Start > All Programs > Oracle - Oracle_Home > Oracle BPEL Process Manager 10.1.2 > Developer Prompt to open up an operating system command prompt.

   
   

   Note: Always use the Developer Prompt to open an operating system command prompt in all phases of the Order Booking tutorial. Opening an operating system command prompt in any other way to run tutorial commands is not supported.

   
   

3. Change directories to the tutorials\127.OrderBookingTutorial directory:

   cd tutorials\127.OrderBookingTutorial
   
   

4. Enter the following command:

   polsql.cmd  @PracticeFiles\insertTable.sql
   
   

   Messages appear indicating table creation and sample row insertion into the default Oracle Lite instance.

10.2.3.5 Testing the Database Connection

1. Select Connection Navigator from the View main menu.

2. Expand Database.

3. Right-click myConnection and select SQL Worksheet.

4. Enter SELECT * FROM system.orders; in the Enter SQL Statement field.

5. Right-click the statement and select Execute SQL Statement.

   The sample table created with data appears in the Results field.

   
   Description of the illustration obph10database.gif
   
   

10.2.11.1 Creating an Order FulFillment Partner Link

1. Go to the OrderBooking process in the Applications Navigator section.

2. Double-click OrderBooking.bpel.

3. Drag and drop a PartnerLink activity from the Component Palette section onto the right side of the designer window.

4. Enter the following values to create a partner link for the Order Booking process:

   
   

   Note: For the WSDL File field below, click the flashlight (the second icon from the left named WSIL Browser) to access the WSDL Chooser window for automatically selecting the FulFillOrder process as a service.

   
   

   Field	Value
   Name	FulfillOrder
   WSDL File	Access this URL by clicking the WSIL Browser flashlight icon and expanding and selecting LocalBPELServer > processes > default > FulfillOrder. http://localhost:9700/orabpel/default/FulfillOrder/FulfillOrder?wsdl See Also: "Setting the Hostname in Your JDeveloper BPEL Designer Web Browser Preferences" if you receive a parsing error when attempting to add a WSDL file in the WSDL Chooser window.
   Partner Link Type	FulfillOrder
   My Role	FulfillOrderRequester
   Partner Role	FulfillOrderProvider

   
   

5. Click OK.

6. Select Save from the File main menu.

10.2.11.2 Creating a Scope Activity

1. Drag and drop a Scope activity from the Component Palette section to below the SelectSupplier Switch activity.

2. Double-click the scope icon to display the Scope window.

3. Enter OrderFulFillment in the Name field of the General tab.

4. Click OK.

   In this phase of the tutorial, you create the Invoke, Receive, and Assign activities inside this Scope activity.

5. Click the + sign to expand the Scope activity.

6. Select Save from the File main menu.

10.2.11.3 Creating Invoke and Receive Activities

1. Drag and drop an Invoke activity from the Component Palette section into the OrderFulFillment Scope activity.

2. Double-click the Invoke icon to display the Invoke window.

3. Enter the following details:

   Field	Value
   Name	invokeFulfillOrder
   Partner Link	FulfillOrder

   
   

   The Operation (initiate) field is automatically filled in.

4. Click the first icon to the right of the Input Variable field. This is the automatic variable creation icon.

5. Click OK on the Create Variable window that appears.

   A variable named invokeFulfillOrder_initiate_InputVariable is automatically created in the Input Variable field. This variable is automatically assigned a message type of FulfillOrderRequestMessage.

6. Click OK.

7. Drag and drop a Receive activity from the Component Palette section to below the invokeFulfillOrder Invoke activity inside the OrderFulFillment Scope activity.

8. Double-click the Receive icon to display the Receive window.

9. Enter the following details:

   Field	Value
   Name	receiveFulfillOrder
   Partner Link	FulfillOrder

   
   

   The Operation (onResult) field is automatically filled in.

10. Click the first icon to the right of the Variable field. This is the automatic variable creation icon.

11. Click OK on the Create Variable window that appears.

    A variable named receiveFulfillOrder_onResult_InputVariable is automatically created in the Output Variable field. This variable is automatically assigned a message type of FulfillOrderResponseMessage.

12. Click OK.

13. Select Save from the File main menu.

10.2.11.4 Creating an Initial Assign Activity

1. Drag and drop an Assign activity from the Component Palette section to above the invokeFulfillOrder Invoke activity inside the OrderFulFillment Scope activity.

2. Double-click the assign icon to display the Assign window.

3. Click the General tab.

4. Enter assignDBIn in the Name field.

5. Click Apply.

6. Click the Copy Rules tab.

7. Click Create to display the Create Copy Rule window.

8. Enter the following values:

   Field	Value
   From
   Type	Variable
   Variables	Expand and select Variables > inputVariable > payload
   To
   Type	Variable
   Variables	Expand and select Variables > invokeFulFillOrder_initiate_InputVariable > payload

   
   

9. Click OK to close the Create Copy Rule window and the Assign window.

10. Select Save from the File main menu.

10.2.11.5 Creating a Second Assign Activity

1. Drag and drop an Assign activity from the Component Palette section to below the receiveFulfillOrder Receive activity inside the OrderFulFillment Scope activity.

2. Double-click the assign icon to display the Assign window.

3. Click the General tab.

4. Enter assignDBWriteComments in the Name field.

5. Click Apply.

6. Click the Copy Rules tab.

7. Click Create to display the Create Copy Rule window.

8. Enter the following values:

   
   

   Note: Instead of manually entering an expression, you can press Ctrl and then the space bar in the Expression field. Scroll through the list of values that appears and double-click the value you want. Edit the value as necessary. As you enter information, a trailing slash can appear. This means you are being prompted for additional information. Either enter additional information, or press the Esc key and delete the trailing slash to complete the input of information.

   
   

   Field	Value
   From
   Type	Expression
   Expression	concat(bpws:getVariableData('inputVariable','payload','/ns1:PurchaseOrder/ns1:OrderInfo/ns1:OrderComments'), ' - Order FulFillment Complete')
   To
   Type	Variable
   Variables	Expand and select > Variables > inputVariable > payload > ns1:PurchaseOrder > ns1:OrderInfo > ns1:OrderComments

   
   

9. Click OK to close the Create Copy Rule window and the Assign window.

   When complete, the designer window looks as follows:

   
   Description of the illustration obph9.gif
   
   

10. Click the - sign to close the OrderFulfillment Scope activity.

11. Select Save from the File main menu.

10.2.11.6 Adding Database Exception Handling

1. Ensure that you are in the FulFillOrder process. If not, double-click FulFillOrder.bpel in the Applications Navigator section of the designer window.

2. Drag and drop a Scope activity from the Component Palette section to below the transformDBRecord Transform activity.

3. Double-click the Scope icon to display the Scope window.

4. Enter db_write_scope in the Name field of the General tab.

5. Click OK.

6. Click the + sign to expand the Scope activity.

7. Drag and drop the writeDBrecord Invoke activity into the db_write_scope Scope activity.

8. Click Add Catch All Branch in the icons on the left side of db_write_scope.

   
   Description of the illustration obph9catchall.gif
   
   

   A new box displays to the right with an asterisk and the words Drop Activity Here.

9. Select Variables > Process > Variables in the Structure section.

10. Right-click Variables and select Create Variable.

11. Enter dbStatus in the Name field.

12. Select Simple Type and click the flashlight icon to the right of the Simple Type field.

13. Select boolean.

14. Click OK to close the Type Chooser window and the Create Variable window.

15. Drag and drop an Assign activity from the Component Palette section to above the db_write_scope Scope activity.

16. Double-click the Assign icon to display the Assign window.

17. Click the General tab.

18. Enter assign_db_0 in the Name field.

19. Click Apply.

20. Click the Copy Rules tab.

21. Click Create to display the Create Copy Rule window.

22. Enter the following values:

    Field	Value
    From
    Type	Expression
    Expression	'0'
    To
    Type	Variable
    Variables	Expand and select Variables > dbStatus

    
    

23. Click OK to close the Create Copy Rule window and the Assign window.

24. Drag and drop another Assign activity from the Component Palette section to below the writeDBrecord Invoke activity inside the db_write_scope Scope activity.

25. Double-click the Assign icon to display the Assign window.

26. Click the General tab.

27. Enter assign_db_1 in the Name field.

28. Click Apply.

29. Click the Copy Rules tab.

30. Click Create to display the Create Copy Rule window.

31. Enter the following values:

    Field	Value
    From
    Type	Expression
    Expression	'10'
    To
    Type	Variable
    Variables	Expand and select Variables > dbStatus

    
    

32. Click OK to close the Create Copy Rule window and the Assign window.

33. Drag and drop a third Assign activity from the Component Palette section into the Catch All branch on the right side of the db_write_scope Scope activity.

34. Double-click the Assign icon to display the Assign window.

35. Enter assign_db_error in the Name field of the General tab.

36. Click Apply.

37. Click the Copy Rules tab.

38. Click Create to display the Create Copy Rule window.

39. Enter the following values:

    Field	Value
    From
    Type	Expression
    Expression	bpws:getVariableData('dbStatus') + 1
    To
    Type	Variable
    Variables	Expand and select Variables > dbStatus

    
    

40. Click OK to close the Create Copy Rule window and the Assign window.

41. Drag and drop a Wait activity from the Component Palette section into the Catch branch below the assign_db_error Assign activity.

42. Double-click the Wait activity to display the Wait window.

43. Enter waitDB in the Name field.

44. Set the value to 30 seconds in the HH:mm:ss field. This means this block is retried every 30 seconds until the FulFillOrder process is successful (that is, dbStatus is 1).

45. Click OK.

46. Drag and drop a While activity from the Component Palette section to immediately above the db_write_scope Scope activity.

    The designer window looks as follows:

    
    Description of the illustration obph9except.gif
    
    

47. Click the - sign to close the db_write_scope Scope activity.

48. Expand the While activity.

49. Drag the db_write_scope Scope activity inside the While activity.

50. Double-click the While activity and press Ctrl and then the space bar to enter the following in the Expression field:

    bpws:getVariableData('dbStatus') < 5
    
    

51. Click OK.

52. Select Save from the File main menu.

10.2.11.7 Validating, Compiling, and Deploying the OrderBooking Process

1. Go to the Applications Navigator section.

2. Right-click OrderBooking.

3. Select Deploy > LocalBPELServer > Deploy to default domain.

4. Enter the domain password of bpel when prompted.

5. Increment the version number of the project when prompted (for example, enter 1.6) and click OK.

   This compiles the BPEL process. Review the bottom of the window for any errors. If there are no errors, deployment was successful. If deployment was unsuccessful, see Step 5.

10.2.11.8 Running the Order Booking Process

1. Log into Oracle BPEL Console by selecting Start > All Programs > Oracle - Oracle_Home > Oracle BPEL Process Manager 10.1.2 > BPEL Console (or refresh the page if it is already open).

   The Dashboard tab of Oracle BPEL Console appears. Note that your BPEL process, OrderBooking, now appears in the Deployed BPEL Processes list.

2. Click the BPEL Processes tab.

3. Note which OrderBooking version has an asterisk next to the version number. This is the default version.

4. Click the latest version number of the OrderBooking process. If you have been incrementing the version numbers as described in previous chapters, this is the highest number.

   The Managing this BPEL Process window appears.

5. Scroll down and click Mark as Default to make this the default version of OrderBooking.

6. Click Done when prompted and note that this OrderBooking version now has an asterisk next to the version number, indicating that it is the default version.

7. If you have installed Patch 1, go to Step 8. Otherwise, you must first perform Steps 7a through 7b. See Chapter 1, "Introduction" for information about where to obtain Patch 1.

8. Click the Dashboard tab.

9. Start the correct OrderBooking version instance in one of the following ways:

   • Open the provided CreateOrderBookingUI application at http://localhost:9700/CreateOrderBookingUI and provide inputs to initiate a process.

   • Click OrderBooking in the Deployed BPEL Processes list.

     • Enter details in all fields of the HTML Form and click Post XML Message.

       or

     • Select XML Source from the Initiating a test instance list. Use a text editor to copy and paste the contents of the OrderBookingPO_1.xml file into the field that appears. Click Post XML Message.

       
       

       Note: Do not copy and paste from Internet Explorer or Mozilla Firefox. You must use a text editor to perform this task.

       
       

   • Optionally copy the OrderBookingPO_1.xml file to the temp directory to invoke the process instance (this assumes you have completed Chapter 8, "Using the File Adapter's Read Functionality").

10. Complete the manual price quote task for SelectManufacturing by using the graphical user interface JSP page, as described in Step 10.

11. Return to Oracle BPEL Console.

12. Refresh Oracle BPEL Console.

13. Click the Instances tab at the top.

14. Click the OrderBooking instance.

15. Click the Audit link to view the transformation logic and the output file written in the temporary directory you specified (drive_letter:\temp on Windows operating systems or /temp on Unix operation systems).

16. Click the More link for each sequence to view the activity details. (Similar instance management can also be obtained in a graphical fashion under the Flow link.)

17. Check the contents of the temp directory. An output file named po_timestamp.xml should be created. Verify the attributes (for example the concatenation of the first and last names, and so on).

18. Select Connection Navigator from the View main menu in JDeveloper BPEL Designer.

19. Expand Database.

20. Right-click myConnection and select SQL Worksheet.

21. Enter the statement SELECT * FROM system.orderitems; and right-click and select Execute SQL Statement to see your orders persisted in the database.

    
    Description of the illustration obph9dbitems.gif