4 Replies Latest reply on Nov 29, 2016 2:38 PM by rareddy

    pass an array of ids via POST in generated rest service

    szimano

      Hi,

       

      I've been looking around the internet and have found very little information about the automatically generated REST services.

       

      I would like to pass a list of IDs to teiid, to get the corresponding DB entries, but since the list can take thousands of entries I cannot just use GET path params and wanted to send them in the POST body.

       

      Is it possible to somehow extract it in the DDL?

       

      Thanks for the info!

       

      --

      szimano

        • 1. Re: pass an array of ids via POST in generated rest service
          rareddy

          Take look at this unit test for POST based example teiid/IntegrationTestRestWebserviceGeneration.java at master · teiid/teiid · GitHub

           

          Here you see a simple parameter being passed as single post variable in testPostOperation method. Now if there going to be many ids as input, then you need to figure out how you want post that, a csv content, json or xml and appropriately deserialize into ids in the virtual procedure using string function, xml or json functions. You can also define the parameter as blob, clob or xml then you would have to use multipart semantics in calling it, and desialization is still same. The above example has all these, you need to pick and choose what you need for your usecase.

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: pass an array of ids via POST in generated rest service
            szimano

            Hi Ramesh,

             

            Thank you very much for your reply. I've seen this test before, but it's the deserialization that i have problems with

             

            Can you point me to some place where i could transform string/xml/json/anything input body into an array of IDs and then how would i use it in a query similar to

            select * from TABLE t where t.id in (myPassedArray)

            ?

             

            Thanks!

            Tomek

            • 3. Re: pass an array of ids via POST in generated rest service
              rareddy

              Tomek,

               

              Honestly it took little wrangling either to use ARRAYTABLE, XMLTABLE or TEXTTABLE but here is simple one I came up with

               

              <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
              <vdb name="sample" version="1">
                  <property name="{http://teiid.org/rest}auto-generate" value="true"/>
                  <model name="PM1">
                      <source name="text-connector" translator-name="looped" />
                      <metadata type="DDL"><![CDATA[
                              CREATE FOREIGN TABLE G1 (e1 string, e2 integer);
                              CREATE FOREIGN TABLE G2 (e1 string, e2 integer);
                      ]]> </metadata>
                  </model>
                  <model name="View" type ="VIRTUAL">
                      <metadata type="DDL"><![CDATA[
                          SET NAMESPACE 'http://teiid.org/rest' AS REST;
                          CREATE VIRTUAL PROCEDURE g1post(IN p1 string) RETURNS TABLE (xml_out xml) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'POST', "REST:URI" 'g1post')
                          AS
                          BEGIN
                              SELECT XMLELEMENT("g1",XMLAGG(XMLELEMENT("value",XMLFOREST(c.e1 AS "e1",c.e2 AS "e2"))))
                                  FROM G1 as c WHERE c.e2 IN (SELECT convert(t.e, integer) FROM TEXTTABLE(UNESCAPE(p1) COLUMNS e string) AS t);
                          END
                          ]]> </metadata>
                  </model>
              
                  <translator name="looped" type="loopback">
                      <property name="IncrementRows" value="true"/>
                      <property name="RowCount" value="10"/>
                  </translator>
              </vdb>
              

               

              Then I tested with

               

              curl --basic -u user:redhat1! -X POST --data 'p1=1\n2\n3\n4' http://localhost:8080/sample_1/View/g1post
              

               

              that gave me the result

               

              <g1>
                <value>
                  <e1>ABCDEFGHI1</e1>
                  <e2>1</e2>
                </value>
                <value>
                  <e1>ABCDEFGHI2</e1>
                  <e2>2</e2>
                </value>
                <value>
                  <e1>ABCDEFGHI3</e1>
                  <e2>3</e2>
                </value>
                <value>
                  <e1>ABCDEFGHI4</e1>
                  <e2>4</e2>
                </value>
              </g1>
              

               

              So, in this example I am passing a  URLForm text parameter called p1 through HTTP, which represents the input to my procedure called "g1post". Then I used TEXTTABLE to parse the content into rows and used that in IN clause as static sub-query. Now, you can also send text parameter in XML or JSON but you would need to parse those using XML or JSON functions into similar constructs. If you use "p1" type as XML, CLOB or BLOB then this procedure requires "multipart" invocation and limits the input to single parameter, where you can pass in very large document. If they are BLOB or CLOB it could contain text content that you could use TEXTTABLE to parse, if it XML it has be an XML content and can use XMLTABLE for that to convert the documents into tabular format.

               

              HTH

               

              Ramesh..

              1 of 1 people found this helpful
              • 4. Re: pass an array of ids via POST in generated rest service
                rareddy

                I also tweaked my example such that I can pass a XML string (not XML object, that requires multipart) that I can do same as above. i.e. instead of sending CSV, send a XML content

                 

                CREATE VIRTUAL PROCEDURE g1xml(IN p1 string) RETURNS TABLE (xml_out xml) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'POST', "REST:URI" 'g1xml')
                AS
                BEGIN
                  declare integer[] ids = (select x.e from XMLTABLE('/root' PASSING xmlparse(document p1) COLUMNS e integer[]) as x);
                  SELECT XMLELEMENT("g1",XMLAGG(XMLELEMENT("value",XMLFOREST(c.e1 AS "e1",c.e2 AS "e2")))) 
                    FROM G1 as c WHERE c.e2 IN (select cast(y.col as integer) from (exec ARRAYITERATE(ids))y);
                END
                

                 

                Then I executed using

                 

                curl --basic -u user:redhat1! -X POST -d 'p1=<root><e>1</e><e>2</e><e>4</e></root>' http://localhost:8080/sample_1/View/g1xml
                

                 

                Got response of

                <g1>
                  <value>
                  <e1>ABCDEFGHI1</e1>
                  <e2>1</e2>
                  </value>
                  <value>
                  <e1>ABCDEFGHI2</e1>
                  <e2>2</e2>
                  </value>
                  <value>
                  <e1>ABCDEFGHI4</e1>
                  <e2>4</e2>
                  </value>
                </g1>
                

                 

                So it shows how to parse the XML data into array and using that array in the WHERE clause. If you want send JSON data, slap in the JSONTOXML around variable p1, which converts the data to XML then the rest of the process is same. Also, in my example I showed building the XML document, you can also build JSON document using JSON Functions easily. But note that when JSON is being returned, set the return type as clob, and set the "REST:PRODUCES" metadata tag on the procedure to "JSON" so that content-type set correctly on the payload.

                 

                Ramesh..