How to invoke sql query from OSB - Proxy with transformation

This is in continuation to my last post where I have shown how to make a OSB pass through.

Using Proxy to transform: 

This is more interesting as we have some control over the data.

Lets go back to JDeveloper, where we will create a concrete WSDL file and two XSLT files each for input and output. We can create a dummy BPEL process which will create all these files.

Inside the BPEL process, use the invoke activity to invoke the DB adapter and use transform activity both before invoke and after where we pass input/output to DB adapter. This gives us 2 XSLT files and once we deploy this composite we can download the concerete WSDL. Copy these 3 files to Eclipse OSB project.

Copy the XSLT and WSDL file to OSB project

Now we are ready to work with the proxy service.

Create a new proxy service 'GetEmployeeDetails_Transform_PS', on the General tab, select WSDL Web service and browse for the WSDL we have copied from JDeveloper 'GetEmployeeDetails.wsdl'
and select GetEmployeeDetailsBPEL_pt press OK.

Now go to Message Flow tab, here we will invoke the business service and transform the input and output.

Drag and drop Pipeline pair node in the flow, this explicitly gives us request and response.
Next drag and drop stage in the request pipeline, inside stage drag and drop Service callout.

Service callout is more like a invoke activity.

Click on service callout in the flow, this will open its properties, click on browse for service and look for our business service, this is very much the same as BPEL invoke activity.

Click on OK and select the required operation from drop down in Invoking, also give name for the input and output variable for this invocation.

In BPEL terms the invoke activity is configured, next we need assign/transform before and after the invoke to pass input and receive output.

Drag and drop a Assign activity on the Request action of Service callout.

Click on the assign activity to open its properties.

Now this assign is completely different from BPEL assign activity.
This assign is combination of BPEL Assign and Transform activity.

Our task right now is to use XSLT (Transform)

Here Variable means target variable.

Expression can be either a simple XPath, XSLT, XQuery etc

Now lets click on expression and then select XSLT tab, click on browse to select required XSLT file.

Input document is the variable which we will pass as input XML to XSLT

Click on OK and mention the variable for input XML

We are done with passing the input to call the business service. Next task is to get the output and pass it to OSB response, however we cant use assign activity here.

Reason ? we are passing the response back to the body of the SOAP message, if we use assign, it will try to overwrite the whole body with the XML, we have to only change the data inside the node of body, for this we will use replace activity and we will select the option of replace node contents.

In variable: Target i.e. where will the replace happen, and the XPath tells where exactly on the In variable it will happen, . (dot) in XPath is on the same node.

In the expression go to XSLT resource browse for output XSLT, and the Input document will be $EmployeeDetailsSelect_Output, this is actually the variable which has receive data after invoking the business service (service call out).

We are done lets deploy and test it.

How to invoke sql query from OSB - Pass through

In this post we will look at how to execute a simple select query.

I will break this into two posts when in the first post I will explain how to make a OSB pass through and in the next post I will explain how to use transform inside proxy service or some logic before invoking the business service.

Below is the SQL

Step 1.) Go to JDeveloper and drop a DB adapter on the reference side do the needful to create the sql as below

JDeveloper will create below file which we will copy to OSB project in Eclipse.

Copy the .xsd, .jca, .wsdl, mappings.xml and properties.xml file to eclipse.

Now we will create a business service which will invoke this sql,to do this we will right click on .jca file and give name to business service.

The business service is done, this can be deployed and tested individually, however we can't expose it and use it as a web service, this is like a DB adapter on the composite without any BPEL or Mediator to expose it.

Lets deploy this to OSB server and test it.

So half of our task is completed, now from here we can proceed to create our OSB pass through.

OSB pass through: this is used when we directly expose the business service with the help of proxy service (no transformation).

Right click on business service > Oracle Service Bus > Generate Proxy Service

We are done with the proxy service, deploy and test it.

Helloworld in OSB

'Helloworld' example is very confusing in OSB specially for SOA developers, so lets do it.

For creating this Helloworld web service in OSB we will need XSD and concrete WSDL, we can can get these from JDeveloper.

Step 1.) From JDeveloper I created a simple schema file as below

Step 2.) In JDeveloper create a synchronous BPEL process with input and output using this schema file, deploy this BPEL to server and get the concrete WSDL file from server.

Download this wsdl file, and we are ready to start the development in OSB.

Now lets go to Eclipse OSB IDE.

Step 3.) Create OSB configuration project as shown below

Now create a new OSB project

Now create different folders as below and copy the schema and WSDL files in the respective folders, also remember to change the xsd file location in wsdl file.

Step 4.) To create a Helloworld in BPEL we would have a exposed web service adapter and a BPEL process, however there is no adapter on external reference, so in OSB we wont have any business service, See the screen shot below, this is the first confusion of any SOA developer, better understand this here.

Step 5.) Create a proxy service 'Helloworld_PS' and use the concrete WSDL which we have downloaded from SOA server.

Select WSDL and web service, browse for wsdl file and select port and press OK.

Select Yes for transport configuration changes.

This gives us a synchronous OSB web service, more like a sync BPEL process without any activity.

Step 6.) Now all we have to do is use a assign activity which will concat Hello !! with our input and pass us as output, so simple isn't it ? this is the next confusion and quit a confusion honestly.

Click on flow tab and go to the flow.

From the design pallet > Oracle Service Bus design flow > Nodes > Pipeline Pair drag and drop it after Helloworld_PS.

Pipeline pair separates the input and output flow and makes it easier to customize transformations and other operations on either the request or response side of the node. Right now we only have a entry node and if we test it, it will echo with the input.

In the response pipeline drag and drop a stage and inside it drag and drop a replace activity.

The replace activity helps us to replace contents inside any variable or particular element inside the variable.

In Variable is the target variable
XPath is the precise node of the target variable where we want the replace to happen
Expression is the content you want to be copied (source XML)

Now deploy and test this to be confused again.

The response is what we passed in the request, now why is this happening, OSB is slightly different in behavior from BPEL, in OSB the output variable inside the body is not created if we define it inside the WSDL file, like it happens in BPEL where if we don't pass anything to the output of BPEL still we will get the empty output XML.

This is not working because the OSB is unable to find anything inside body which can be picked by this XPath "./v1:HelloworldRes/v1:response", in BPEL terms selection failure, however OSB does not gives this error and we remain in confusion why is the service echoing.

Typically we use a XSLT where we will create a final output XML which we want to receive from OSB and use a replace activity and copy it to the body.

For this example I am hard coding a output XML

Deploy and test

We can see whatever the XML we hard coded in the expression the same has got replaced in the body and coming up in the response.