-
1. Re: Creating UDF for teiid 8.3 Final
shawkins May 29, 2013 8:02 AM (in response to onkar.dhuri)1 of 1 people found this helpfulOnly procedures and not functions are currently shown via Teiid's database metadata.
-
2. Re: Creating UDF for teiid 8.3 Final
onkar.dhuri May 29, 2013 8:12 AM (in response to shawkins)Thanks Steven for quick reply.
Even if I try to call this function using "CALL testFunction('test')", I get following exception
TEIID30357 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30357 testFunction does not exist.
How to call this function using squirrel or any other jdbc client ?
Thanks,
Onkar -
3. Re: Creating UDF for teiid 8.3 Final
shawkins May 29, 2013 8:44 AM (in response to onkar.dhuri)SELECT testFunction('test')
-
4. Re: Creating UDF for teiid 8.3 Final
onkar.dhuri May 29, 2013 11:54 AM (in response to shawkins)Thanks Steven.
My bad; I was calling function incorrectly.
Also, is there any way to return a resultset from this java method ? Can I map virtual procedure to java method ?
Thanks,
Onkar
-
5. Re: Creating UDF for teiid 8.3 Final
shawkins May 29, 2013 2:57 PM (in response to onkar.dhuri)1 of 1 people found this helpful> Also, is there any way to return a resultset from this java method ?
Not currently. Only scalar results are supported by functions and there is not yet z cursor type to represent a result set. That said you can do some workarounds utilizing the XMLTABLE, ARRAYTABLE, and OBJECTTABLE table functions as your function can return xml, an array, or any collection of complex objects respectively and have that result turned into rows.
> Can I map virtual procedure to java method ?
Not currently. https://issues.jboss.org/browse/TEIID-147 requests this, but some of it could be accomplished with allowing a sufficiently complex language to be used with OBJECTTABLE. For now it's expected that you'll write your virtual procedures in Teiid sql.
-
6. Re: Creating UDF for teiid 8.3 Final
shawkins Jun 6, 2013 1:14 PM (in response to shawkins)I should elaborate on the previous answer. One way to get a "result set" out of a udf is if you have a udf (foo) that returns an java.lang.Iterable (which can be memory safe) of array instances. Then you could use:
{code}SELECT AT.* FROM OBJECTTABLE('udf_rows' PASSING foo(parm1) AS udf_rows COLUMNS udf_row OBJECT 'teiid_row') AS OT, ARRAYTABLE(OT.udf_row COLUMNS col1 type1, col2 type2 ...) AS AT{code}
Here the OBJECTTABLE makes each array representing a row available to ARRAYTABLE, which unpacks the array as typed columns. This could be made more consice if OBJECTTABLE by default supported array indexing or if ARRAYTABLE directly handled an Iteratable of arrays - one or both should probably be worked as an enhancement.
Given that our translator api does expose procedures that may have result sets, a likely implemenation path for https://issues.jboss.org/browse/TEIID-147 is to provide a built-in translator type that can be pointed at an annotated class to provide the metadata and implementations for functions/procedures. You can already easily define a custom translator and have it expose procedures, but what is lacking is a simple bridging api to underlying java logic (there is a simple hook for functions via the MetadataFactory.addFunction(String, Method) call, but not a custom translator yet to automate the process). We'll also likely need/want to work the exsting issue to provide a Connection back into Teiid against the current session for use in the java logic to more fully mimic stored procedure handling.
Steve