What you will need:



•Download Oracle Express Addition (free): http://www.oracle.com/technology/soft ... ts/database/xe/index.html

•Make sure you also download and install Oracle 10g Express Client before proceeding

•Tutorial Part 1 Sonic ESB project open











Oracle Express Database



We will now create the Oracle Database to connect Sonic ESB to and write the responses from the web service-call. I am making the assumption you have installed Oracle Express and are ready to create a database. I will be creating this tutorial on Windows Vista.

1. Go to Oracle Web interface Start ==> Programs ==> Oracle Database 10g Express Edition







2.Select Go to Database Home Page







3.Enter System as User and the password you created on installation









4. If all is well you will see the above page on Login

5. Now we create a database and table for the Sonic ESB service







6.From the Object Browser Icon click on the Arrow down and Select Create ? Table

Note: If you have IIS on port 8080 make sure you turn it off before proceeding



7. Create the following Table Structure:







a. ID Type Number (Primary Key) Auto Number

b. IPCheckResult VARCHAR2

c. WriteDateTime TIMESTAMP

d. Author VARCHAR2 100



8. Click Next to continue









9. Create ID as Primary Key with Sequence









10. Click Next









11. There will be no Foreign Key so Click Next









12. Add ID as unique constraint and click Finished









13. Click on Create Button to Build Table







14. Table Completed







Build Sonic Service to write to database:

Now we go to the open Sonic IDE and Create a database service.

1. We will now create an XML file PublicIPOutput.xml







2. New ==> XML









3. Click Next











4. If you click on recent you will see the original WSDL available select:

5. Click on IPCheckResponse and Click Finish









6. XML response file will be created:









7. Now we drag Database Service Type under Public IP Monitor Service













8. Rename to PublicIPDBService









9. Right Click Database Folder: New ==> Database Operation

Name: PublicIPDBInvocation

10. Click Finish



11. We will now create a database connection to Test our service to Oracle







12. Create on Connections View Link









13. Right Click Connection Profile and Add Connection Profile:









14. Name: Oracle Express Connection

15. Select from Driver drop down list Oracle JDBC Driver (Direct)







16. Port: 1521 – Database: XE









17. Click Test Connection









18. Right Click the Oracle 10G Connection and select Open Connection









19. Click OK









20. Select Connection from Drop down list









21. Enter Test SQL:

22. The bottom properties show the results pane

Insert a record to Test:

23. Run the query with the SQL pane:







24. Check the database with Oracle web interface:









25. Record inserted correctly.



26. We now set variables to pass the information from the incoming response to the insert statement which in turn will update the Oracle Database:

Use @name to use variable Sonic will generate parameters for you:









27. For this sample we will use a constant for the Author and Query for TimeStamp field:







28. @Author passes constant for Oracle use Sub Query command to enter Timestamp







29. CheckIP we are going to use the output XML file we created to resemble a return message and apply an XPath query to get the Tag required.



30. On the CheckIP Transformation drop down list select XPath









31. Double click on the Transformation detail //CheckIP/text() this will open a pane to select the output file:









32. Click on the Eclipse button …









33. Select the Eclipse button:









34. Go to PublicIPMonitor XML folder and select PublicIPOutput.xml and select Open









35. Click OK









36. Double click on org:IPCheckResult



37. Click on Evaluate to test the query:









38. Click OK if you see the above









39. Next Sonic ESB will ask to add Namespaces Click OK









As you can see the Xpath Query was added to extract to IN parameter from message. Click Save at this point.

40. Close PublicIPDBInvocation tab



Adding Database Invocation to Service

We are going to cover the Management Console to create our Database connectivity for Oracle; Sonic uses the Management Console to call the Database Invocation.











1. Click on Container Tab and then the

Icon to open Management console



2. Enter Administrator/Administrator Click OK









3. Management Console is now open









4. Expand Services Folder and click on Database Service Type

5. Click on: dev.DBService and copy the control number from the service:











6. Copy this to a notepad as we will need it to create the new Service









7. Click New and look for the * these are required fields for now we populate temporary values to build the service









8. Click on the eclipse at the right on Control Number:









9. Enter the copied control number









10. Click on Product Information to populate and click OK

11. Enter Service Name as: dev.DBServiceOracle









12. Click on the Eclipse on the Entry Point to create a topic for the service.







13. New ==> Endpoint ==> SonicMQ Endpoint









14. Enter the above for the Entry point and do the same for:

a. dev.oracle.db.service.Entry

b. dev.oracle.db.service.Exit

c. dev.oracle.db.service.Fault

d. dev.oracle.db.service.RME

15. Enter the JDBC Driver:

(mine is 1531 make sure you use 1521)

Because the service uses a different driver than the IDE you will need to ensure you use the sonic.oracle in the Connection Click the right Eclipse button and add the below:

16. Click OK and save

17. Enter the:

18. Click Apply to create service









19. Now we need to add this service to the dev_core









20. Expand Folder ESB Containers ==> dev_ESBCore









21. Click on New and Click on PublicIPProcess and Click OK









22. Click Apply and Select New again and Click on dev.DBServiceOracle and Click Ok and Apply



23. Click on the Top Left Tab Manage:









24. Expand Container and right click dev_ESBCore









25. Operations and Restart

26. Wait till dev_ESBCore goes green and then close management Console









27. Go back to IDE and Click Eclipse









Service Name select the New DB service we created









28. Click OK to activate









29. Click on Process Link (Top Left)





Check Response from Oracle Insert



In order to ensure nothing went wrong with our insert into Oracle we are going to create an XCBR (Routing) to check the response from the Insert into Oracle and act by sending a Fault message to Sonic ESB.

1. Create a New sample XML file called PublicIPSample.xml:









Right Click XML folder and New ==> XML add name and click Finish. Copy below text into the XML file replace all text with below and save. This will be our sample for the XCBR

2. Create a New Folder called: XCBR: Right click the project



3. New Folder name of XCBR

4. Right Click the XCBR folder: New ==> XPath Routing Rules







5. This routing file will read the response back from the database insert and act as defined:







Name: OracleResponseCBR



6. Click Finish







7. Next we need to tell Sonic what rules should apply:









8. Click on Add







9. Click on Xpath Expression and the eclipse to open the Editor:









10. Select the PublicIPSample.xml and click Open









11. Click OK









12. Double Click on

13. Click Evaluate and then OK (if successful)



14. Click Default Destination and click Add







15. Select ENDPOINT









16. Click on eclipse and select dev.Fault

17. Click OK









What we are doing here is to send the failed message to the Topic dev.Fault any other services can subscribe to this Topic and act by sending Email or logging to notify Support or others of the problems with the Oracle Database, they can then follow-up and either fix the Oracle DB or determine the cause. In part three we will use a Topic called dev.oracle.success and subscribe to this topic for the response.

18. To create the topic click the Add Rules Address Section and add the following:



19. Select ENDPOINT and click on the eclipse button Name:*









20. Click on the New Icon









21. Add the following and click OK









22. Click OK again









23. And OK again











24. The routing is now completed

25. Go back to the process and drag the router XCBR file onto the process:

















Our process is now completed. First we make a call-out to the web service then we insert the data into Oracle Database then we check the return message from Oracle to determine if it is success or failure. The routing XCBR will check the return response and if it is > 0 then send the message to the Topic dev.oracle.success otherwise dev.fault will get the message.





Testing the Complete process

In this section we are going to send a test message, this will trigger the process to execute the services. In order to send a message we will use a Scenario created in the IDE.



1. We are going to add a transformation prior to insertion to remove any tags we don’t want. The message returned from the web service adds a tag so we are going to clean the message prior inserting into the oracle database. This will introduce you to using XSLT within Sonic ESB. To begin create a folder called XSLT within the project. Right click the folder and select New ==> XSLT. Add the name as TransformOracle.xsl replace all the text with the below:

What this XSLT will do is remove the inner tag from the message coming back from the Web service. The output will be a clean XML message:

2. Here is the complete service:









3. Run the Scenario created in Part 1









4. Click on Run Button

5. Check Oracle Database and record has been inserted













Congratualtions on completing a complete Sonic ESB service, we have covered Database, web service, transformations and creating database services in the management console. If you have any questions please contact me on this site.