EclipseLink/UserGuide/DBWS/Creating from a SQL Statement

EclipseLink DBWS

• EclipseLink Destinations:
• Documentation
• FAQ
• Examples
• Development

Creating a DBWS service from SQL Statements

Creating from Results Sets from custom SQL SELECT statements

EclipseLink DBWS can create a Web service that exposes the results of executing custom SQL SELECT statements, without exposing the actual SQL. There is no metadata to determine the structure of the returned data -- the Simple XML Format schema is used.

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

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

Use this command to create the web service:

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

where

• dbws-builder.xml is the DBWS builder XML configuration file, as shown previously
• output_directory is the output directory for the generated files
• -packageAs 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 are customized in the sql operations. For example, <simple-xml-format> = <aggregate-info>, <simple-xml> = <count-and-max-salary>.

Generated EclipseLink DBWS service descriptor

The SQL operations are included in the DBWS service descriptor file (eclipselink-dbws.xml) created by EclipseLink, as well as the settings to alter the default Simple XML Format <element-tag> name.

<?xml version="1.0" encoding="UTF-8"?>
<dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:testSql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <name>testSql</name>
   <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
   <query>
      <name>count</name>
      <result>
         <type>simple-xml-format</type>
         <simple-xml-format>
            <simple-xml-format-tag>aggregate-info</simple-xml-format-tag>
            <simple-xml-tag>count</simple-xml-tag>
         </simple-xml-format>
      </result>
      <sql>
         <![CDATA[select count(*) from EMP]]>
      </sql>
   </query>
   <query>
      <name>countAndMaxSalary</name>
      <result>
         <type>simple-xml-format</type>
         <simple-xml-format>
            <simple-xml-format-tag>aggregate-info</simple-xml-format-tag>
            <simple-xml-tag>count-and-max-salary</simple-xml-tag>
         </simple-xml-format>
      </result>
      <sql>
         <![CDATA[select count(*) as "COUNT", max(SAL) as "MAX-Salary" from EMP]]>
      </sql>
   </query>
</dbws>

SOAP Messaging

The following SOAP Message invokes the <count> operation for the testSql DBWS service:

<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Body>
    <count xmlns="urn:testSqlService" xmlns:urn="urn:testSql"/>
  </env:Body>
</env:Envelope>

returning:

<?xml version="1.0" encoding="utf-16"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP-ENV:Header />
  <SOAP-ENV:Body>
    <srvc:countResponse xmlns:srvc="urn:testSqlService">
      <srvc:result>
        <aggregate-info xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="simple-xml-format">
          <count>
            <COUNT_x0028__x002A__x0029_>14</COUNT_x0028__x002A__x0029_>
          </count>
        </aggregate-info>
      </srvc:result>
    </srvc:countResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Note :You should be aware of the resultSet for select count(*); the characters '(', '*' and ')' are not valid for XML element tags and are replaced by the well-known transformation, which documented as part of the SQL/X specification (SQL/XML:2003).

The above operation returns unstructured data. However, it is possible to nest such operations within the context of a Table-based operation; then, the 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>
  <properties>
    <property name="projectName">empSql</property>
    ... database properties
  </properties>
  <table
   catalogPattern="%"
   tableNamePattern="EMP"
   >
    <sql
     name="findEmpByName"
     isCollection="true"
     returnType="empType"
     >
      <text><![CDATA[select * from EMP where ENAME like ?]]></text>
      <binding name="ENAME" type="xsd:string"/>
    </sql>
  </table>
 </dbws-builder>

The generated EclipseLink DBWS service descriptor eclipselink-dbws.xml file:

<dbws xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="urn:testSql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <name>empSql</name>
   <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
   <update>
      <name>update_empType</name>
      <parameter>
         <name>theInstance</name>
         <type>ns1:empType</type>
      </parameter>
   </update>
   ...
   <query>
      <name>findEmpByName</name>
      <parameter>
         <name>ENAME</name>
         <type>xsd:string</type>
      </parameter>
      <result isCollection="true">
         <type>ns1:empType</type>
      </result>
      <sql>
         <![CDATA[select * from EMP where ENAME like #ENAME]]>
      </sql>
   </query>
</dbws>