Sunday, June 27, 2010

Exposing database artifacts as Web Services without writing any Java code

With the help of the  EclipseLink DBWS framework it is very simple to generate Web Services based on SQL statements, a table or a PL/SQL Procedure.without knowing or writing any java code. The only thing you need to do is to configure the dbws-builder xml and use this in the DBWS generator. The result of this generator can be a WAR archive, which you can deploy on a WebLogic Server.  Because Eclipselink DBWS is part of EclipseLink you don't need to install any libraries on the WebLogic Server and after deployment you can test these Web Services in the WebLogic Console or add some OWSM or WLS WS Security policies. 

First you need to download EclipseLink and unpack the zip. Next step is to set the JAVA_HOME and provide the JDBC Drivers location. Go to the eclipselink\utils\dbws folder and edit the setenv.cmd or setenv.sh file.

set JAVA_HOME=xxxxx\jdk160_18
set DRIVER_CLASSPATH=xxxxx\wlserver_10.3\server\lib\ojdbc6.jar;C:\oracle\MiddlewareJdev11gR1PS2\wlserver_10.3\server\lib\aqapi.jar
You can replace xxxxx with any 10.3.2 or 10.3.3 Middleware home or use a JDeveloper 11g R1 home.

For this blogpost, I will use the emp and dept table of scott demo schema ( Part of the Oracle Database ) and use a PL/SQL package. Here is the code of the package, this packages contains two functions, the first returns the time in the required format and the second return all the departments in a ref cursor.
create or replace package scott_ws is
  function get_current_time(date_format varchar2) return varchar2;

  function get_all_department return SYS_REFCURSOR; 

end scott_ws;
/
create or replace package body scott_ws is

  function get_current_time(date_format varchar2) return varchar2 is
    v_time varchar2(20);
  begin
    if date_format is null then
      return 'empty format';
    end if;
    select to_char(sysdate, date_format) into v_time from dual;
    return v_time;
  
  exception
    when others then
      return 'error';
  end;

  function get_all_department
  return SYS_REFCURSOR 
  is
   dept_recordset SYS_REFCURSOR; 
  begin
    OPEN dept_recordset FOR SELECT * FROM dept;
    return dept_recordset;
  end get_all_department;

end scott_ws;
/

Next step is to configure the dbws-builder xml file. ( Click here for more information about the dbws-builder xml ) The first properties part is necessary information for the DBWS generator. Off course you need to provide the JDBC Driver details. The dataSource property is for the JNDI name of the JDBC Datasource, this need to be created on the WebLogic Server.
<?xml  version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <properties>
    <property name="projectName">scott</property>
    <property name="username">scott</property>
    <property name="password">tiger</property>
    <property name="url">jdbc:oracle:thin:@localhost:1521:orcl</property>
    <property name="driver">oracle.jdbc.OracleDriver</property>
    <property name="dataSource">jdbc/scottDS</property>
    <property name="targetNamespace">http://www.whitehorses.nl/scott</property>
    <property name="logLevel">finest</property>
    <property name="contextRoot">/MyScottServices</property>
  </properties>

  <table catalogPattern="%" tableNamePattern="EMP">
    <sql name="findEmpByDept2" isCollection="true" returnType="empType">
        <text>
          <![CDATA[select * from EMP where DEPTNO like ?]]>
        </text>
       <binding name="DEPTNO" type="xsd:int"/>
    </sql>
  </table> 

  <table catalogPattern="%" tableNamePattern="DEPT"/>

  <sql name="findEmpByDept" simpleXMLFormatTag="employees" xmlTag="employee">
      <text>
         <![CDATA[select * from EMP where DEPTNO like ?]]>
      </text>
     <binding name="DEPTNO" type="xsd:int"/>
  </sql>

  <procedure catalogPattern="scott_ws" 
              schemaPattern="SCOTT" 
              procedurePattern="get_all_department"
               isCollection="true"
               simpleXMLFormatTag="departments"
               xmlTag="department"/>

  <procedure catalogPattern="scott_ws" 
            schemaPattern="SCOTT" 
            procedurePattern="get_current_time"
             simpleXMLFormatTag="scott_ws" 
             xmlTag="time"/>

</dbws-builder>

To expose a table as Web Service you can use this
<table catalogPattern="%" tableNamePattern="DEPT"/>

You can also use a SQL statement with or without any bind variables. Here is it handy to provide the simpleXMLFormatTag and xmlTag attributes.
<sql name="findEmpByDept" simpleXMLFormatTag="employees" xmlTag="employee">
<text>
<![CDATA[select * from EMP where DEPTNO like ?]]>
</text>
<binding name="DEPTNO" type="xsd:int"/>
</sql>

To expose a function in a package I can use this and because this will return all the departments, the isCollection attribute need to be true and here is it also good, to provide the simpleXMLFormatTag and xmlTag attributes.
<procedure catalogPattern="scott_ws"
schemaPattern="SCOTT"
procedurePattern="get_all_department"
isCollection="true"
simpleXMLFormatTag="departments"
xmlTag="department"/>

The last part of this dbws-builder xml is the emp table with inside a SQL statement. This is handy when you want to re-use the emp complex type. In this case I will also use the empType ( table name + Type ) in the SQL statement ( returnType attribute ).

We are ready to generate some Web Services based on this dbws-builder xml.
Go to the eclipselink\utils\dbws folder and start this in a cmd box.
dbwsbuilder.cmd -builderFile scott_emp.xml -stageDir c:\temp\test -packageAs wls scott.war

this will generate a war which can be deployed on WebLogic. you can also use -packageAs jdev or -packageAs eclipse. Then you can include this code in your JDeveloper or Eclipse project.

Go to the Weblogic Console ( http://xxxx:7001/console ) and create the JDBC Datasource ( jdbc/scottDS ) and target this datasource to the right servers.

Go to deployments and click on install, Select your WAR.
After deployment you can click on the application and open the web service
Click on the Test Client to test your WSDL operations.

The last optional step is to configure the security on these web services.  Go to the Configuration /  WS-Policy Tab and select the endpoint.
You can choose for the WS security policies of OWSM or those of Weblogic. To configure OWSM for this Web Service you can use this blogpost.
That's all

19 comments:

  1. First of all, thanks for an excellent blog.
    Are you aware of this: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10492/xdb_web_services.htm#ADXDB5676
    It's basically a native way of exposing data and procedures without any code. And when coupled with OSB it really makes securely publishing legacy procedures as services a really simple trick.

    ReplyDelete
  2. Hi,

    I know this XDB feature and I am using it for rest web services in combination with OSB.

    +ve of my solution is database independed, no licensing ( don't need to have WLS or Ora DB) and you don't need to know OSB.

    I think Oracle customer will use Soa Suite or OSB with the DB /EJB adapter.

    thanks

    ReplyDelete
  3. Hi Edwin,

    I tried out this and working perfectly...

    Thanks...

    ReplyDelete
  4. Do you know if you can use DBWS as a REST service as well?

    ReplyDelete
  5. Hi,

    I dont thinks so , this is a DB to WS framework.

    But you can check jersey or one of its implementation
    https://jersey.dev.java.net/

    thanks

    ReplyDelete
  6. Could you please specify which version of WebLogic did you use? 10gR3 (10.3.0) does not seem to work....

    ReplyDelete
  7. Hi,

    I think it is 10.3.3 (ps3) or 10.3.2. Do you get a particular error message.

    you can try to download the latest eclipselink and update these jars in weblogic.

    thanks

    ReplyDelete
  8. Yes, it fails with

    [EclipseLink-47000] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DBWSException
    Exception Description: Could not locate file [eclipselink-dbws-sessions.xml]


    No matter what I try. Though I have tried it with 10.3.0 and JBoss 5.1.0 only so far.

    ReplyDelete
  9. Hi,

    can you check the generated war, it should be located in WEB-INF\classes\META-INF

    and add the eclipselink jars to the setDomainenv.bat

    thanks

    ReplyDelete
  10. Hi

    First of you I want to thank you for such a wonderful blog.
    I have tested for a function which is having four input parameters.
    It is working well when all the parameters are provided but fails when
    one parameter which is optional and having type as Number in function
    is not provided and invoked. How can I control the type and number of input parameters to handle this situation.

    For reference below is the xsd generated.









    Can I get more information from some source to fine control all this?
    Thanks for your help.

    Rony

    ReplyDelete
    Replies
    1. Hi,

      I think for optional parameters you should use param nill = true and not < param /> or < param >< /param > , this is not empty and can't converted to a number,

      Hope this helps

      Delete
  11. Hi
    I think I didn't get your point.
    In which file I need to update the nill attribute of the param tag ?

    a> eclipselink-dbws-sessions.xml
    b> eclipselink-dbws.xml
    c> eclipselink-dbws.wsdl
    d> eclipselink-dbws-schema.wsdl

    Can't I generate the same during running the command line utility?
    Thanks for your help.

    Regards,
    Rony

    ReplyDelete
    Replies
    1. Hi,

      I mean in the tool which send the soap request like soapui , when you got an element and this element is optIonal you need to provide it with nill is true instead of removing it .



      Thanks

      Delete
  12. Hi Edwin,

    Thank you. I was using weblogic webservice test client to invoke the web Service.So there is no way the the xsd can be generated with minOccurs,maxOccurs param for a certain parameter?

    Also I faced a problem while executing in different network. At my home network the webservice worked well but it failed at my office network.
    It failed with below message:

    failed due to weblogic.testclient.WsdlParseFailedException: weblogic.wsee.wsdl.WsdlException: Failed to construct WSDL relative URL using rootURL='http://[2002:a990:32f1:0:0:0:a990:32f1]:80/NameGenerator/NameGenerator?WSDL', location='http://[2002:a990:32f1:0:0:0:a990:80/NameGenerator/NameGenerator?xsd=1'. Invalid authority field: [2002:a990:32f1:0:0:0:a990:80

    Do you have any idea why this is happening?

    Best Regards,
    Rony

    ReplyDelete
    Replies
    1. Hi,

      Looks like something goes wrong with ip6 It cannot retrieve the xsd of the wsdl. Check the ip on which wls runs and the nat setting of your router. The xsd import in the wsdl should also use your router address and forward to the wls server. Maybe there is a setting in eclipse link to say what your exposed ip is

      Delete
  13. Hi Edwin...

    I was trying dbws and works perfect for simple pakages and stored procedure
    But now i have an error when a stored procedure has oracle types for variable.

    example ocrya

    ReplyDelete
    Replies
    1. Hi,

      oh oracle types must be mapped to something else ( need a wrapper I think ) and you need oracle toplink for this ( no dbws).
      What is possible, use eclipselink with PLSQL / ORacle object record types, plsql tables etc but not with DBWS, You need to program it.

      like this http://biemond.blogspot.nl/2012/03/oracle-bulk-insert-or-select-from-java.html

      thanks

      Delete
  14. Hi I am new to Eclipse link and trying to deploy it obn Tomcat but its giving error ....Can't it be deployed on any other server except Weblogic
    Waiting for the response....

    ReplyDelete
    Replies
    1. Hi,

      It should also work on other JEE servers, probably you are missing eclipselink jars or a jdbc driver. Can you try Tomcat EE or Glassfish.

      Thanks

      Delete