Polling Using JDBC BC, notify with SMTP BC

JDBC Binding Component can be used by users who has less SQL skills. JDBC Binding Component with the help of JDBC wizard connects to the database and maps the table objects to xml, this would also generate a JDBC wsdl with basic CRUD operations.  With the help of  JDBC  Binding Component user can perform the CRUD and Poll (polling a particular table) operations.

Let's think about a business use case where user would like to notify production manager if any of the purchase order is being raised. This can be achieved easily with the help of Open ESB components (JDBC Binding Component and SMTP Binding Component, from here onwards I would refer Binding Component as BC).

One can poll (or listen to) purchase order line item table with the help of JDBC BC, once the purchase order line item is created then a mail can be sent through SMTP BC.

While polling a particular table there should be some means of logic not to process the records which are processed once. To figure out this JDBC BC has few ways, I am listing here couple of them.
1. Delete from the polled table once it is processed.
2.  Mark a flag column with a flag value to mark it is already processed.

We can take the help for orchestraing this business use case with the help of BPEL. JDBC BC when finds a record on purchase order line item table, will process the record. Then SMTP BC would send a notification through mail to the production manager. (http://blogs.sun.com/vishblogs/entry/using_smtp_bc_to_send  blog by Vishnu Vardhan regarding SMTP BC)

The configuration details can be configured in the bindings section of the WSDL, see below...

Please find the sample projects

1. Bpel Project
http://blogs.sun.com/narayana/resource/SamplePollBpel.zip

2. Composite Application Project
http://blogs.sun.com/narayana/resource/SamplePollJBI.zip

3. Table structure grab (to create the table used here go to netbeans runtime tab, under that databases node, after connecting to the default sample database select Recreate table and point to the below ".grab" file)
http://blogs.sun.com/narayana/resource/PO_LINE_ITEM.zip

Posted at 03:12PM Mar 30, 2007 by pedapudinarayana in Sun  |  Comments[0]

Play with SQL SE

Are you trying to achieve SQL transport in your integration scenario along with other open esb components then you are at the right place. Read on to unveil and to get hands dirty with SQL Service Engine.

What all you need?

a. Netbeans enterprise pack 5.5.1
b. Database specific drivers   (believe me nothing else you require)

Here are the steps to achieve SQL transport

a. Create SQL Project

Create new SQL project by choosing "SQL module" from "Service Oriented Architecture" folder.

b. Create SQL File

Create a SQL file by choosing  "SQL file" from "Service Oriented Architecture" folder, this SQL file embeds the netbeans SQL editor. We can use our sql writing skills and write them in the sql file.

c. Configure and build SQL project

Now we need to configure  "connectivityInfo.xml", if we want to connect to the database using plain driver connection just need to add the url in "database-url value" as shown below. If we are interested in using datasource then we should provide "jndi-name value". SQL Service Engine gives preference to use the datasource.

d. Configure using CASA editor in the Composite Application

Create "Composite Application" Project then add the previously built SQL project to the Composite Application Project. After building the Composite App project now we have to add the soap bindings. (It is because the WSDL generated by SQL project would be having plain bindings rather any protocol specific bindings, and we use CASA editor capabilities to attach protocol specific bindings to it, amazing right)

Edit the Composite Application project to add CASA port to it, drag and drop the SOAP bindings from the CASA editor palette, rebuild the project.  We are done with configurations of SQL project, and our project artifacts are ready for the deployment.

More on CASA look into this blog...

http://blogs.sun.com/joshweb/entry/the_casa_editor_in_100

e.  Deploy with Composite Application

That's it.....!!! We did it. It is so simple isn't it?

Sample Application

Download and extract the files below. Open these projects in NetBeans Enterprise Pack 5.5.1 Beta (find it here http://www.netbeans.info/downloads/index.php?rs=20&p=6 )

Below sample application helps user to create a simple stored procedure which inserts data into employee and department tables using oracle database. Before running the application, run the script to create tables which are required to create the procedure http://blogs.sun.com/narayana/resource/CreateTables.sql. (We can as well create these tables using SQL Service Engine)

1. MySQLApp.zip (SQL project) http://blogs.sun.com/narayana/resource/MySQLApp.zip

2. MySQLJBI.zip (Composite Application project) http://blogs.sun.com/narayana/resource/MySQLJBI.zip

Posted at 05:22PM Mar 14, 2007 by pedapudinarayana in Sun  |  Comments[2]