Tuesday, 14 February 2012

Silenced writeback using webservices - part 2

Happy Valentine's day to all :)

This is the 2nd part of the series in which I am using webservies to writeback. In my previous article, I showed a mechanism to do writeback using a utility of OC4J. That mechanism works well with OC4J and OAS (Oracle Application Server) but for other webservers we need an independent webservice. In this article, I'm creating an independent webservice and deploying it on weblogic
The porcess described in this article differs from the previous one on the following 2 accounts
1.       The method of creation of the webservice
2.       The javascript used to call the webservice. The difference lies in the soap envelope and the end point url
My obiee presentation is also hosted on weblogic.
I will use Jdeveloper to create my webservice. This solution does not work with express edition of the database.
Let’s start digging
1.       Go to weblogic's console and select JDBC under Services. We are creating a database connection here
2.       Give the connection name and the corresponding JNDI for it. A connection with the same name will have to be created in step 10.
3.       Fill in the necessary details and test the configuration
4.       Select the server which you want to associate with the connection
5.       Open Jdeveloper and click on New. Select Generic Application under Applications
6.       Give a name to the project and select Web Services from the project technologies shuttle. Click Finish after this.
7.       Right click the project and select New from the menu
8.       Select Web Services from the categories panel on the left and then select PS/SQL Web Service 
9.       Select JAX-RPC radio button
10.   Create a database connection. A connection with the same name should be created in the webserver as well. Select your package and give some name to the service. This name will be a part of your end point url
11.   Select the functions which should be a part of the webservice
12.   The work is done. Now deploy the created service on weblogic. You should have a configured domain on weblogic for this and a corresponding application server connection should have been created in JDeveloper
13.   After deployment, you should be able to see the following message in deployment log in JDeveloper
14.   Your newly deployed application should be visible under deployments in the domain in which you have deployed the application
15.   You should be able to access the endpoint url now
16.   Click on the Test Page link. We are doing this to get the soap envelope which we can write in the Javascript in OBIEE. Put a string in the param text box and click on the obieewritebackfunct button
17.   You should be able to see the following response. Copy the XML in request detail
18.   Login to OBIEE and put the following XML in it
<script language="Javascript">
function load ()
var xmlhttp = new XMLHttpRequest();
xmlhttp.open("POST", "http://localhost:7001/PLSQLApplication-PLSQLProject-context-root/PLSQLWebServicePort",true);
xmlhttp.setRequestHeader("Content-Type","text/xml; charset=utf-8");
xmlhttp.onreadystatechange=function (){ 
       if (xmlhttp.readyState ==4 && xmlhttp.status == 200){
            var xmlDoc=xmlhttp.responseXML ;
            var Xml2String;
            if (xmlDoc.xml) {
                Xml2String= new XMLSerializer().serializeToString(xmlDoc);
           var msg= "RESPONSE HEADERS *********\n";
           msg+="Response here *******************\n";
           msg+= Xml2String;
           msg+="\nResponse ends here ****************";
            alert( msg );       
var xml = ' <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> <env:Header /> '
 + ' <env:Body> <target:obieewritebackfunct xmlns:target="http://plsqlconn/PLSQLWebService.wsdl"> '
+  ' <param>Test String</param> '
+ ' </target:obieewritebackfunct> '
+ ' </env:Body> '
+ ' </env:Envelope> ' ;
    <title>Web services</title>
      <td> <input value="Click for response" type="button" onclick="Javascript:load();"></td>
19.   Click on save and then click on Click for response button on the dashboard. You should be able to see the following

We used JDeveloper to create a webservice and called it from OBIEE


Anonymous said...

After I click "Test page", it returns "Error 404--Not Found". Do you have any hints of that?

Vishal Pathak said...

It mostly happens when the server is not accessible from your client machine.
Since you are able to access the server so network cannot be a problem
What happens when you click on WSDL page?
Try accessing the same webservice using soapUI

Anonymous said...

When I click on WSDL page, WSDL is shown. I ignored the step of clicking "Test page", all other steps are fine. And, the function is work. Thanks.

However, I would like to pass parameter by using an "Action" in dashboard, do you know how make it done?

Anonymous said...

Hi Vishal,

If my SQL function has two parameters, how do I construct the XML string?

For example, my second parameter called "param2". I have tried to make a xml string, but it doesn't work.


+ ' [param]Test String[/param] '
+ ' [param2]Test String[/param2] '

*Remark: Replace [] with <>

Vishal Pathak said...

Your syntax to create the SOAP envelope is correct. Does your code work with a single arguement? I reckon that the problem is in a different place and not in the number of arguements. What is the error message? Have you also tried using other SOAP clients like soapUI? Do you get the same error in other clients as well? Also consider relooking at your network connectivity and firewall