1 2 3 Previous Next 35 Replies Latest reply on Oct 27, 2010 10:42 AM by rareddy Go to original post
      • 30. Re: How to use web service to update/query data
        jalen

        Thank you, John.

        • 31. Re: How to use web service to update/query data
          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 t

           

          The 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

            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

              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 t

               

              getSubjectContainer 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

                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;
                END

                 

                 

                The 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

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

                  1 2 3 Previous Next