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

From Eclipsepedia

Jump to: navigation, search
Line 1: Line 1:
 
==EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements==
 
==EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements==
In this case, it is not possible to determine the structure of the returned data from JDBC metadata thus the DBWS simplified-xml-format is used:-
+
In this case, it is not possible to determine the structure of the returned data from JDBC metadata thus the Simple XML Format is used:
 
+
 
<div class="pre">
 
<div class="pre">
 
  <dbws-builder>
 
  <dbws-builder>

Revision as of 11:52, 2 April 2009

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

In this case, it is not possible to determine the structure of the returned data from JDBC metadata thus the Simple XML Format is used:

<dbws-builder>
 . . .
 <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>

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>