Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be 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"

(→‎DBWS Example: Web Service based on a Stored Procedure)
 
(9 intermediate revisions by 2 users 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, a Web Service is automatically generated that exposes the Stored Procedure(s) operation(s).
+
== 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.
  
−
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 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.
+

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.

Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/Examples/DBWS/DBWSBasicStoredProcedure&oldid=222513"

This page was last modified 09:59, 7 October 2010 by Mike Norman. Based on work by Doug Clarke and David Twelves.

Copyright © Eclipse Foundation, Inc. All Rights Reserved.