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"
| 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 | + | 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> . . .
</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>