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"

Line 1: Line 1:
 
==EclipseLink DBWS Service based on Stored Procedure==
 
==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.
  
−
Given a procedure name, a Web Service is automatically generated that exposes the Stored Procedure(s) operation(s).
+
The following stored procedure will be used for this example:
−
 
+
<source lang="sql" enclose="div">
−
Note: 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.
+
DROP PROCEDURE TESTECHO;
−
 
+
CREATE OR REPLACE PROCEDURE TESTECHO(T IN VARCHAR2, U OUT VARCHAR2) AS
−
'''XSD for Simple XML Format'''
+
BEGIN
−
<div class="pre">
+
   U := CONCAT(T, '-test');
−
  <?xml version="1.0" encoding="UTF-8"?>
+
END;
−
  <xsd:schema
+
</source>
−
  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>
+
−
</div>
+
−
 
+
−
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.
+
−
 
+
−
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.
+

Revision as of 13:41, 3 April 2009

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;
Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/Examples/DBWS/DBWSBasicStoredProcedure&oldid=147930"

Back to the top