Conversion of JAVA_OBJECT data type when trying to use OBJECTTABLE

[van.halbert]

Teiid is trying to convert the TeamObject in Team (and fails), when really, it just needs to be passed as is, to the OBJECTTABLE call in the View.  What option have I missed setting or how should the dynamic vdb DDL be changed to accomplish this?  

I've tried data types of Object and Java_Object.  Each cause a transformation error similar to this:

08:24:11,108 WARN  [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue12) TEIID30020 Processing exception 'TEIID10076 Invalid conversion from type class java.lang.Object with value '=== Team: Barcelona ===

Players:

- Messi

- Pedro

- Puyol

' to type class org.teiid.core.types.BlobType' for request u0jce3lfiLcP.5.  Exception type org.teiid.core.types.TransformationException thrown from org.teiid.core.types.basic.ObjectToAnyTransform.transformDirect(ObjectToAnyTransform.java:59). Enable more detailed logging to see the entire stacktrace.

The following is the dynamic vdb:

    <model name="Team">

        <source name="objsource" translator-name="infinispan1" />

        <metadata type="DDL"><![CDATA[

            CREATE FOREIGN TABLE Team (TeamObject JAVA_OBJECT);

         ]]> </metadata>

    </model>

    <model name="TeamView" type="VIRTUAL">

         <metadata type="DDL"><![CDATA[

             CREATE VIEW TeamObjects (

                 TeamName varchar(255) PRIMARY KEY

             )

             AS

               SELECT o.TeamName FROM Team, OBJECTTABLE('x' PASSING TeamObject AS x COLUMNS TeamName varchar(255) 'teiid_row.name') as o;

        ]]> </metadata>

    </model>

    <translator name="infinispan1" type="infinispanremote-cache">

        <property name="CacheName" value="teams"/>

        <property name="RootClassName" value="com.jboss.datagrid.hotrod.Team"/>

        <property name="RemoteServerList" value="localhost:11222"/>

    </translator>

[van.halbert]

I found a resolution to the problem,  I needed to perform a subselect for Team in the VIew, as shown below:

    <model name="Team">

        <source name="objsource" translator-name="infinispan1" />

        <metadata type="DDL"><![CDATA[

            CREATE FOREIGN TABLE Team (TeamObject Object);

         ]]> </metadata>

    </model>

    <model name="TeamView" type="VIRTUAL">

         <metadata type="DDL"><![CDATA[

             CREATE VIEW TeamObjects (

                 TeamName varchar(255) PRIMARY KEY

             )

             AS

               SELECT o.TeamName FROM (Select TeamObject from Team) as T, OBJECTTABLE('x' PASSING T.TeamObject AS x COLUMNS TeamName varchar(255) 'teiid_row.name') as o;

        ]]> </metadata>

    </model>

[van.halbert]

And here's how you can chain OBJECTTABLES together to access a more complex object:

         <metadata type="DDL"><![CDATA[

             CREATE VIEW Teams (

                 TeamName varchar(255) PRIMARY KEY

             )

             AS

               SELECT o.TeamName FROM (Select TeamObject from Team) as T, OBJECTTABLE('x' PASSING T.TeamObject AS x COLUMNS TeamName varchar(255) 'teiid_row.name') as o;

             CREATE VIEW Players (

                  TeamName varchar(255) PRIMARY KEY,

                  PlayerName varchar(255)

             )

             AS

             SELECT o.TeamName, y.Name FROM (Select TeamObject from Team) as T, OBJECTTABLE('x' PASSING T.TeamObject AS x COLUMNS TeamName varchar(255) 'teiid_row.name', players object 'teiid_row.players') as o, OBJECTTABLE('m' PASSING o.players as m COLUMNS Name string 'teiid_row.toString') as y;         

        ]]> </metadata>

The Team object contains a List of players names, which are of type String.  The Players view shows how to access the List in Team and return the String names,  Notice using "toString" as the accessor method to get the value.

[shawkins]

This looks good, except toString should be unnecessary if the object is already an instance of String.  I also don't see why you need a subselect fro TeamObject.  The optimizer will simply remove that inline view.

[van.halbert]

You are correct on both accounts.   But I don't recalll why I added the subselect, but it does work without it.  Here's my final -vdb.xml:

    <model name="Team" visible="false">

        <source name="objsource" translator-name="infinispan1" />

        <metadata type="DDL"><![CDATA[

            CREATE FOREIGN TABLE Team (TeamObject Object);

         ]]> </metadata>

    </model>

    <model name="TeamView" type="VIRTUAL">

         <metadata type="DDL"><![CDATA[

             CREATE VIEW Teams (

                 TeamName varchar(255) PRIMARY KEY

             )

             AS

               SELECT o.TeamName FROM Team as T, OBJECTTABLE('x' PASSING T.TeamObject AS x COLUMNS TeamName varchar(255) 'teiid_row.name') as o;

             CREATE VIEW Players (

                  TeamName varchar(255) PRIMARY KEY,

                  PlayerName varchar(255)

             )

             AS

             SELECT o.TeamName, y.Name FROM (Select TeamObject from Team) as T, OBJECTTABLE('x' PASSING T.TeamObject AS x COLUMNS TeamName varchar(255) 'teiid_row.name', players object 'teiid_row.players') as o, OBJECTTABLE('m' PASSING o.players as m COLUMNS Name string 'teiid_row') as y;         

        ]]> </metadata>

    </model>

    <translator name="infinispan1" type="infinispanremote-cache">

        <property name="CacheName" value="teams"/>

        <property name="RootClassName" value="com.jboss.datagrid.hotrod.Team"/>

        <property name="RemoteServerList" value="localhost:11222"/>

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

    </translator>