openESB Database BC and Intalio

Last week I needed to create a web service to access a MySQL database within Intalio.  I had used openESB's LDAP BC previously so I had some insight into what was required.  This blog will attempt to consolidate the pieces of information I had to find from different resources on the Internet to configure the JNDI resource and database pools within GlassFish Server so my composite application could connect to MySQL DB.  I hope this helps others.

First make sure GlassFish Server is running.  Then follow these steps:

1. Log in to the GlassFish administration console at: http://%7bserver%7d:4848/login.jsf
2. Click on 'Resources' in left pane
3. Click on 'JDBC'
4. Click on 'Connection Pools'
5. In main window add a new Connection  Pool by clicking 'New' and enter in your pool settings
6. Click next and configure any additional settings, then click finish - You now have a Connection Pool
7. Click 'Ping' to test your Connection  Pool.  If Ping fails, the next tip may resolve your issue

**** If Ping Fails ****
When I was creating my Connection Pool I was not successfully connecting.  I stumbled across the following tip which hoepfully will save you time.  This is what you need to do:

1. Make sure the MySQL Driver (mysql-connector-{version}.jar) is located in your GlassFish installation domains/domain1/lib/ext directory
2. Restart Server
3. Try pinging again

NOTE:  I initially read that the MySQL jar file had to be in GlassFishESBv21/glassfish/lib when setting up JDBS resources so in my installation the jar file is there as well.

Next you will create a JDBC Resource

1. Click on 'JDBC Resources' in left pane
2. In main window add a new JDBC resource by clicking 'New'
3. Enter your JDBC Resource JNDI Name - JDBC/'something'
4. Select the Connection Pool you created in previous steps
5. Click OK

You now have your JDBC resource configured within GlassFish which can now be used within openESB Database BC.

Start NetBeans IDE

First we need to make sure you have added your Database Application within GlassFish

1. In left hand pane select Services
2. Under Databases if your Database Application is not listed, right click and choose 'New Connection'
3. Enter in your settings for your Database Application Connection

Next we can create a new project using openESB Database BC

1. Click File -New Project -SOA- BPEL Module
2. Give your BPEL Module a name and click finish
3. Right click on Process Files within that BPEL Module and select New - WSDL
4. Enter a File Name
5. Select Concrete WSDL
6. In Binding select 'DATABASE'
7. In Type select the appropriate value - I used 'Prepared Statement'
8. In URl, select the conenction you created above
9. Type in your prepared statement and enter any parameters
10. In the JNDI Name, type in the JDBC resource you created in the beginning of these instructions.

That is it.  Create another WSDL (database trigger - make sure you select Concrete WSDL and choose SOAP -Document Literal to be used within Intalio) which will send in a SOAP message to invoke this Database service.  Add thsese two WSDLs to a BPEL Process within your Project.  Then add jar files from this project into a composite application, CLEAN and Build, Deploy and then Test.

I then took the database trigger WSDL and the associated schema and placed them within Intalio.  Dropped the operation within Intalio Diagram, wired up the variables in the data mapper and deployed successfully.

If you need more assistance, contact me at http://btmsoftwaresolutions.com/