EclipseLink/Examples/DBWS/DBWSBasicSQL

EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements

The use-case for this example is the creation of a Web service that exposes the results of executing some custom SQL SELECT statements - without exposing the actual SQL. As with the Stored Procedure use-case, there is no metadata to determine the structure of the returned data; therefore, the Simple XML Format schema is used.

The SQL SELECT statements targeted for this service are in the DBWSBuilder builder XML file:

<?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>
  <sql
   name="count"
   simpleXMLFormatTag="aggregate-info"
   xmlTag="count"
   >
    <text><![CDATA[select count(*) from DBWS_CUSTOMSQL]]></text>
  </sql>
  <sql
   name="countAndMaxSalary"
   simpleXMLFormatTag="aggregate-info"
   xmlTag="count-and-max-salary"
   >
    <text><![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from DBWS_CUSTOMSQL]]>  </text>
  </sql>
</dbws-builder>

prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war

SOAP Response

<env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Body>
   <srvc:countResponse
     xmlns:srvc="urn:sqlService">
     <srvc:result>
       <aggregate-info
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:type="any">
         <count>
           <COUNT_x0028__x002A__x0029_>14</COUNT_x0028__x002A__x0029_>
         </count>
       </aggregate-info>
   </srvc:result>
 </srvc:countResponse>
 </env:Body>
</env:Envelope>

Note Note the treatment of the resultSet for select count(*). The characters '(', '*' and ')' are not valid for XML element-tags and are replaced by the well-known transformation documented as part of the SQL/X specification (a.k.a. SQL/XML:2003)

Operations based on Stored Procedures or ResultSet's from custom SQL statements return un-structured data. However, it is possible to nest such operations within the context of a Table-based operation. These nested operations can be configured to re-use the schema element type of the parent table and return structured data:

custom SQL as sub-operation of Table-based Web Service

<dbws-builder>
 . . .

<sql name="findEmpByName" isCollection="true" returnType="empType"> <text><![CDATA[select * from EMP where ENAME like ?]]></text> <binding name="ENAME" type="xsd:string"/> </sql>

</dbws-builder>

SOAP Request

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body xmlns:ns1="urn:empService">
   <ns1:findEmpByName>
     <ns1:ENAME>A%</ns1:ENAME>
   </ns1:findEmpByName>
 </soap:Body>
</soap:Envelope>

SOAP Response

<env:Envelope
 xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
 <env:Header/>
 <env:Body>
   <srvc:findEmpByNameResponse
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     xmlns:ns1="urn:emp"
     xmlns:srvc="urn:empService"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <srvc:result>
       <ns1:emp>
           <ns1:empno>7499</ns1:empno>
           <ns1:ename>ALLEN</ns1:ename>
           <ns1:job>SALESMAN</ns1:job>
           <ns1:mgr>7698</ns1:mgr>
           <ns1:hiredate>1981-02-20T00:00:00.0</ns1:hiredate>
           <ns1:sal>1600</ns1:sal>
           <ns1:comm>300</ns1:comm>
           <ns1:deptno>30</ns1:deptno>
       </ns1:emp>
       <ns1:emp>
         <ns1:empno>7876</ns1:empno>
         <ns1:ename>ADAMS</ns1:ename>
         <ns1:job>CLERK</ns1:job>
         <ns1:mgr>7788</ns1:mgr>
         <ns1:hiredate>1987-05-23T00:00:00.0</ns1:hiredate>
         <ns1:sal>1100</ns1:sal>
         <ns1:deptno>20</ns1:deptno>
       </ns1:emp>
     </srvc:result>
   </srvc:findEmpByNameResponse>
 </env:Body>
</env:Envelope>