Jump to: navigation, search

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

(EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements)
Line 22: Line 22:
 
     xmlTag="count-and-max-salary"
 
     xmlTag="count-and-max-salary"
 
     >
 
     >
     <text><![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]> </text>
+
     <text><![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]></text>
 
   </sql>
 
   </sql>
 
</dbws-builder>
 
</dbws-builder>
 
</source>
 
</source>
 
<source lang="text">
 
<source lang="text">
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war
+
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testSql.war
 
</source>
 
</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"
 +
  >
 +
  <xsd:complexType name="simple-xml-format">
 +
    <xsd:sequence>
 +
      <xsd:any minOccurs="0"/>
 +
    </xsd:sequence>
 +
  </xsd:complexType>
 +
</xsd:schema>
 +
</source>
 +
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.
 +
 +
===Generated EclipseLink DBWS service descriptor===
 +
One can see the SQL operation in the generated EclipseLink DBWS service descriptor <tt><b>eclipselink-dbws.xml</b></tt> file:
 +
<source lang="xml" enclose="div">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
</source>
 +
 +
===SOAP Messaging===
  
 
'''SOAP Response '''
 
'''SOAP Response '''

Revision as of 14:26, 3 April 2009

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">testSql</property>
    ... database properties
  </properties>
  <sql
   name="count"
   simpleXMLFormatTag="aggregate-info"
   xmlTag="count"
   >
    <text><![CDATA[select count(*) from EMP]]></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 EMP]]></text>
  </sql>
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testSql.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.

Generated EclipseLink DBWS service descriptor

One can see the SQL operation in the generated EclipseLink DBWS service descriptor eclipselink-dbws.xml file:

<?xml version="1.0" encoding="UTF-8"?>

SOAP Messaging

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>