Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/DBWS/DBWSBasicSQL"
(→EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements) |
(→EclipseLink DBWS Service based on Results Sets from custom SQL SELECT statements) |
||
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== | ||
− | The use-case for this example is the creation of a Web service that exposes the results of executing some custom SQL <tt>SELECT</tt> statements | + | The use-case for this example is the creation of a Web service that exposes the results of executing some custom SQL <tt>SELECT</tt> 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 <tt>SELECT</tt> statements targeted for this service are in the <tt>DBWSBuilder</tt> builder XML file: | The SQL <tt>SELECT</tt> statements targeted for this service are in the <tt>DBWSBuilder</tt> builder XML file: |
Revision as of 15:21, 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:
<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 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> . . .
</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>