Skip to main content

Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/DBWS/DBWSBasicStoredProcedure"

(New page: == DBWS Example: Web Service based on a Stored Procedure== Given a procedure name (patterns supported as 1.2.1.1), a Web Service is automatically generated that exposes the Stored Procedu...)
 
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
== DBWS Example: Web Service based on a Stored Procedure==
+
<css>
 +
  .source-sql {padding:4px;}
 +
  .source-java5 {padding:4px;border:1px solid black;}
 +
  .source-xml {padding:4px;border:1px solid black;}
 +
  .source-text {padding:4px;border:1px solid black;}
 +
</css>
 +
__NOTOC__
  
Given a procedure name (patterns supported as 1.2.1.1), a Web Service is automatically generated that exposes the Stored Procedure(s) operation(s).
+
== EclipseLink DBWS Service based on Stored Procedure ==
For the Oracle DB platform, the catalogName can be used to identify the PL/SQL package in which a Stored Procedure is located.
+
The use-case for this example is the creation of a Web service that exposes a Stored Procedure (or multiple procedures).
 +
From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore,
 +
the Simple XML Format schema is used. The EclipseLink DBWS runtime produces an XML document that is simple and 'human-readable'.
 +
Any combination of IN, OUT and IN OUT arguments are supported; in addition, procedures in packages that are overloaded -
 +
same name, different parameters – are supported.
  
Note: From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore, a simplified 'pseudo-rowset' schema is used.
+
The following stored procedure will be used for this example:
 +
<source lang="sql"  enclose="div">
 +
DROP PROCEDURE TESTECHO;
 +
CREATE OR REPLACE PROCEDURE TESTECHO(T IN VARCHAR2, U OUT VARCHAR2) AS
 +
BEGIN
 +
  U := CONCAT(T, '-test');
 +
END;
 +
</source>
  
'''XSD for Simple XML Format'''
+
The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input.
<div class="pre">
+
<source lang="xml" enclose="div">
  <?xml version="1.0" encoding="UTF-8"?>
+
<?xml version="1.0" encoding="UTF-8"?>
  <xsd:schema
+
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 +
  <properties>
 +
    <property name="projectName">testEcho</property>
 +
    ... database properties
 +
  </properties>
 +
  <procedure
 +
    name="testEcho"
 +
    procedurePattern="TESTECHO"
 +
    isSimpleXMLFormat="true"
 +
  />
 +
</dbws-builder>
 +
</source>
 +
<source lang="text">
 +
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war
 +
</source>
 +
 
 +
where
 +
* <tt>dbws-builder.xml</tt> is the DBWS builder XML file above
 +
* <tt>output_directory</tt> is the output directory for the generated files
 +
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
 +
 
 +
The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file is the schema for the Simple XML format:
 +
<source lang="xml" enclose="div">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<xsd:schema
 
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 
   >
 
   >
   <xsd:complexType name="sxfType">
+
   <xsd:complexType name="simple-xml-format">
 
     <xsd:sequence>
 
     <xsd:sequence>
 
       <xsd:any minOccurs="0"/>
 
       <xsd:any minOccurs="0"/>
 
     </xsd:sequence>
 
     </xsd:sequence>
 
   </xsd:complexType>
 
   </xsd:complexType>
  <xsd:element name="simple-xml-format" type="sxfType"/>
+
</xsd:schema>
  </xsd:schema>
+
</source>
</div>
+
The element tags <tt><b>simple-xml-format</b></tt> and <tt><b>simple-xml</b></tt> can be customized by setting the appropriate properties
 
+
on an <tt>sql</tt> operation.
The EclipseLink DBWS runtime produces an XML document-fragment that is simple and 'human-readable'; however, the document is 'dumb' as it cannot be validated against any XML .xsd schema (elements underneath <srvc:result> are not namespace-qualified).
+
[[Category:EclipseLink/Example/DBWS]]
 
+
Element tag names are direct copies of table's column names
+
 
+
'''Resulting XML Document'''
+
<div class="pre">
+
<env:Envelope
+
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
+
<env:Header/>
+
<env:Body>
+
  <srvc:findAllEmployeesResponse
+
    xmlns:srvc="urn:empService">
+
  <srvc:result>
+
    <simple-xml-format>
+
    <simple-xml>
+
      <EMPNO>7499</EMPNO>
+
      <ENAME>ALLEN</ENAME>
+
      <JOB>SALESMAN</JOB>
+
      <MGR>7698</MGR>
+
      <HIREDATE>1981-02-20T00:00:00.0</HIREDATE>
+
      <SAL>1600</SAL>
+
      <COMM>300</COMM>
+
      <DEPTNO>30</DEPTNO>
+
    </simple-xml>
+
    . . .
+
    <simple-xml>
+
      <EMPNO>7844</EMPNO>
+
      <ENAME>TURNER</ENAME>
+
      <JOB>SALESMAN</JOB>
+
      <MGR>7698</MGR>
+
      <HIREDATE>1981-09-08T00:00:00.0</HIREDATE>
+
      <SAL>1500</SAL>
+
      <COMM>0</COMM>
+
      <DEPTNO>30</DEPTNO>
+
    </simple-xml>
+
    </simple-xml-format>
+
  </srvc:result>
+
  </srvc:findAllEmployeesResponse>
+
</env:Body>
+
</env:Envelope>
+
</div>
+
 
+
NB: The element-tags <simple-xml-format> and <simple-xml> can be customized:
+
 
+
<div class="pre">
+
<env:Envelope
+
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
+
  <env:Header/>
+
  <env:Body>
+
    <srvc:employeesWithCommissionResponse
+
      xmlns:srvc="urn:empService">
+
      <srvc:result>
+
        <employee-list
+
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+
          xsi:type="any">
+
          <employee>
+
            <EMPNO>7499</EMPNO>
+
            <ENAME>ALLEN</ENAME>
+
            <JOB>SALESMAN</JOB>
+
            <MGR>7698</MGR>
+
            <HIREDATE>1981-02-20T00:00:00.0</HIREDATE>
+
            <SAL>1600</SAL>
+
            <COMM>300</COMM>
+
            <DEPTNO>30</DEPTNO>
+
          </employee>
+
          . . .
+
          <employee>
+
            <EMPNO>7844</EMPNO>
+
            <ENAME>TURNER</ENAME>
+
            <JOB>SALESMAN</JOB>
+
            <MGR>7698</MGR>
+
            <HIREDATE>1981-09-08T00:00:00.0</HIREDATE>
+
            <SAL>1500</SAL>
+
            <COMM>0</COMM>
+
            <DEPTNO>30</DEPTNO>
+
          </employee>
+
        </employee-list>
+
      </srvc:result>
+
    </srvc:employeesWithCommissionResponse>
+
  </env:Body>
+
</env:Envelope>
+
</div>
+
 
+
Any combination of IN, OUT and IN OUT arguments are supported; in addition, procedures in packages that are overloaded - same name, different parameters – are supported.
+
 
+
=== Running this DBWS example ===
+
The following steps explain how to use EclipseLink DBWS to build and deploy a Web Service that returns information by returns information by running a Stored Procedure on the database.
+
 
+
'''Prerequisites'''
+
* ANT: 1.7.0 (or higher)
+
* A full Java SE SDK: 1.6.0_05 (or later)
+
* A J2EE container where the resulting web service may be deployed
+
* An EclipseLink 1.1.0 installation
+
 
+
nb. This example refers to Windows cmd shell scripts for simplicity.
+
 
+
'''User defined variables'''
+
There are variables the user must set in order for the examples to function
+
correctly:
+
Environment variables in ${EXAMPLES_ROOT}/eclipselink.dbws.crud/env.{bat|sh}:
+
    ANT_HOME
+
        see prerequistes above
+
    JAVA_HOME
+
        see prerequistes above
+
 
+
Ant properties in ${EXAMPLES_ROOT}/examples/eclipselink.dbws.crud/build.properties:
+
+
 
+
* directory roots
+
    ECLIPSELINK_HOME=
+
        Root of EclipseLink installation directory
+
    WL_HOME=
+
        Root of WebLogic installation directory
+
 
+
* WLS properties
+
    wls_port=7001
+
        default port for WebLogic
+
    wls_hostname=localhost
+
        default hostname for WebLogic
+
 
+
* Database connectivity information
+
 
+
    USERNAME=
+
        database user id
+
    PASSWORD=
+
        database password
+
    CONNECTION_URL=
+
        database url
+
    DATABASE_DRIVER=
+
        JDBC driver classname
+
    DATABASE_PLATFORM=
+
        EclipseLink database platform classname
+
    DATABASE_DRIVER_PATH=
+
        directory containing JDBC driver jar(s)
+
    DATABASE_DRIVER_JARS=
+
        name(s) of JDBC driver jar(s)
+
        if multiple jars are required, write them as a comma-separated list
+
    outputSQL=true
+
        Only modify if user does not have permissions to drop/create tables on
+
        the database
+
 
+
Run the resetDatabase.cmd script to create and populate the table on the database.
+
 
+
'''DBWSBuilder'''
+
 
+
Having set up the runtime environment and prepared the database, the next step
+
is to build the DBWS application. The build.cmd script does this through Ant's
+
java task line in ${EXAMPLES_ROOT}/eclipselink.dbws.storedprocedure/build.xml.
+
 
+
The org.eclipse.persistence.tools.dbws.DBWSBuilder application requires a dbws-builder
+
file, found in the config directory. The Ant script automatically substitutes
+
the required database information and then the DBWSBuilder application is
+
invoked, directing its output to the local staging directory. The resulting files are
+
packaged into a .war file.
+
 
+
The example application can be deployed to the WebLogic 10.3 domain using the console admin tools.
+

Latest revision as of 09:59, 7 October 2010


EclipseLink DBWS Service based on Stored Procedure

The use-case for this example is the creation of a Web service that exposes a Stored Procedure (or multiple procedures). From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore, the Simple XML Format schema is used. The EclipseLink DBWS runtime produces an XML document that is simple and 'human-readable'. Any combination of IN, OUT and IN OUT arguments are supported; in addition, procedures in packages that are overloaded - same name, different parameters – are supported.

The following stored procedure will be used for this example:

DROP PROCEDURE TESTECHO;
CREATE OR REPLACE PROCEDURE TESTECHO(T IN VARCHAR2, U OUT VARCHAR2) AS
BEGIN
  U := CONCAT(T, '-test');
END;

The DBWSBuilder utility requires a DBWS configuration file as input.

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">testEcho</property>
    ... database properties
  </properties>
  <procedure
   name="testEcho"
   procedurePattern="TESTECHO"
   isSimpleXMLFormat="true"
 />
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war

where

  • dbws-builder.xml is the DBWS builder XML file above
  • output_directory is the output directory for the generated files
  • -packageAs specifies the platform on which the web service will be deployed

The generated eclipselink-dbws-schema.xsd file is the schema for the Simple XML format:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 >
  <xsd:complexType name="simple-xml-format">
    <xsd:sequence>
      <xsd:any minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

The element tags simple-xml-format and simple-xml can be customized by setting the appropriate properties on an sql operation.

Back to the top