-
30. Re: How to use web service to update/query data
jalen Oct 20, 2010 11:04 PM (in response to jdoyle)Thank you, John.
-
31. Re: How to use web service to update/query data
jalen Oct 21, 2010 1:52 AM (in response to jalen)Since this problem still exists, re-post it.
I have a WS operation named "getSubjectContainer" whose real implementation is to get all rows from a table.
In Teiid virtual model, I write a "SELECT" procedure as following:SELECT
t.*
FROM
(EXEC jalenPocDrWs.invoke(binding => 'SOAP11', action => null, request => '<ns:getSubjectContainer xmlns:ns="http://ws.dr.poc.jalen"/>', endpoint => null)) AS fr, XMLTABLE(XMLNAMESPACES('http://ws.dr.poc.jalen' AS ns, 'http://ws.dr.poc.jalen/xsd' AS ax21), '$d/ns:getSubjectContainerResponse/ns:return/ax21:subjects' PASSING result AS d COLUMNS sub_id integer PATH 'ax21:subId', sub_keyword string PATH 'ax21:subKeyword', sub_type string PATH 'ax21:subType') AS tThe question is this WS operation is to retrieve all rows in table. So even client call "select * from <table> where id = 7", in the WS side, it will retrieve all data in DB and then return one row whose id is 7. It will be a performance issue if there are many rows in the table.
Is there any trick for this case? WS implementation should be care of this logic or it is the procedure's responsibility to judge whether client is retrieving some rows or all rows?
-
32. Re: How to use web service to update/query data
jdoyle Oct 21, 2010 9:40 AM (in response to jalen)If I understand correctly, you're saying that the getSubjectContainer operation does not take any parameters that will allow you to narrow the result?
If that's the case, then there's no alternative for you in how you call the service, but you might be able to improve the performance of your call if you parse the response with XQuery rather than using XMLTABLE. As you say, XMLTABLE will create a table of the full result. A properly written XQuery could filter the XML response down to just the elements where id = 7. The result of that could then be passed to XMLTABLE.
~john
-
33. Re: How to use web service to update/query data
shawkins Oct 21, 2010 10:47 AM (in response to jdoyle)You could create another procedure to create your request document. Then you can use the procedural relational syntax. For example:
SELECT
t.*
FROM
getSubjectContainer as REQUEST, TABLE (EXEC jalenPocDrWs.invoke(binding => 'SOAP11', request =>REQUEST.result)) AS fr, XMLTABLE(XMLNAMESPACES('http://ws.dr.poc.jalen' AS ns, 'http://ws.dr.poc.jalen/xsd' AS ax21), '$d/ns:getSubjectContainerResponse/ns:return/ax21:subjects' PASSING result AS d COLUMNS sub_id integer PATH 'ax21:subId', sub_keyword string PATH 'ax21:subKeyword', sub_type string PATH 'ax21:subType') AS tgetSubjectContainer would be expected to have an input parameter that is mapped to the id column and a single column resultset with the document. However if you take this approach then you would be required to use an equality or other predicate on id to supply a value to the procedure.
-
34. Re: How to use web service to update/query data
jalen Oct 27, 2010 1:20 AM (in response to shawkins)Still a question regarding the WS. How to use Teiid to construct the SOAP HEADER message.
For example:
CREATE VIRTUAL PROCEDURE
BEGIN
SELECT XMLELEMENT(NAME insertSubject, XMLNAMESPACES(DEFAULT 'http://ws.dr.poc.jalen'), XMLELEMENT(NAME subject, XMLELEMENT(NAME subId, JALENPOCDRWSXML.INSERTSUBJECT.CREATE_INSERTSUBJECT.subId), XMLELEMENT(NAME subKeyword, JALENPOCDRWSXML.INSERTSUBJECT.CREATE_INSERTSUBJECT.subKeyword), XMLELEMENT(NAME subType, JALENPOCDRWSXML.INSERTSUBJECT.CREATE_INSERTSUBJECT.subType))) AS xml_out;
ENDThe above procedure can only construct the message in SOAP BODY instead of SOAP HEADER. As you know, some WS need to cache information in SOAP Header. How to construct it in Teiid?
-
35. Re: How to use web service to update/query data
rareddy Oct 27, 2010 10:42 AM (in response to jalen)Jalen,
If you know what XML fragments you need for the SOAP HEADER could you not create them manually using the same "XMLElement" constructs? Like
XMLElement(NAME "soap-header" ...)
Note that when you build the soap-header, you need to switch the WS execution to PAYLOAD mode from MESSAGE. You can find this in the WS translator's properties.
Ramesh..