Creating UDF for teiid 8.3 Final

[onkar.dhuri]

Hi,

I am trying to write an user defined function (UDF) as documented here.

I created a dynamic vdb which will contain only an UDF as mentioned below

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="TestVDB" version="1">

    <description>VDB For Testing</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="Example" type="VIRTUAL" visible = "true">

        <metadata type="DDL">

            <![CDATA[

                CREATE VIRTUAL FUNCTION testFunction(inparam varchar) RETURNS varchar OPTIONS (JAVA_CLASS 'com.onkar.temp.Test',  JAVA_METHOD 'doNothing');

            ]]>

         </metadata>

    </model>

</vdb>

I have also copied the jar file containing java class to respective jboss module.

The vdb gets deployed successfully and also is set to active; however I can not find this procedure when I connect to this vdb using squirrel (Please find attached screenshot)

I even tried to create a static vdb using teiid-designer 8.1 with udf support but I still face the same problem (The vdb gets deployed and set to active).

Can you please suggest the changes that are required to call this virtual procedure from any jdbc client.

Thanks,

Onkar

[shawkins]

Only procedures and not functions are currently shown via Teiid's database metadata.

[onkar.dhuri]

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

[shawkins]

SELECT testFunction('test')

[onkar.dhuri]

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

[shawkins]

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

[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