-
1. Re: pass an array of ids via POST in generated rest service
rareddy Nov 26, 2016 1:28 PM (in response to szimano)1 of 1 people found this helpfulTake 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..
-
2. Re: pass an array of ids via POST in generated rest service
szimano Nov 28, 2016 8:04 AM (in response to rareddy)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 Nov 28, 2016 2:29 PM (in response to szimano)1 of 1 people found this helpfulTomek,
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..
-
4. Re: pass an array of ids via POST in generated rest service
rareddy Nov 29, 2016 2:38 PM (in response to 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..