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...)
 
Line 104: Line 104:
 
</div>
 
</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.
+
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.
+

Revision as of 12:07, 11 March 2009

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 Procedure(s) operation(s). For the Oracle DB platform, the catalogName can be used to identify the PL/SQL package in which a Stored Procedure is located.

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.

XSD for Simple XML Format 

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

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).

Element tag names are direct copies of table's column names

Resulting XML Document 

<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>

NB: The element-tags <simple-xml-format> and <simple-xml> can be customized: 

<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>

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.

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

Back to the top