-
1. Re: Conversion of JAVA_OBJECT data type when trying to use OBJECTTABLE
van.halbert Sep 19, 2012 10:50 AM (in response to 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>
-
2. Re: Conversion of JAVA_OBJECT data type when trying to use OBJECTTABLE
van.halbert Sep 19, 2012 11:40 AM (in response to 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.
-
3. Re: Conversion of JAVA_OBJECT data type when trying to use OBJECTTABLE
shawkins Sep 20, 2012 8:13 AM (in response to van.halbert)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.
-
4. Re: Conversion of JAVA_OBJECT data type when trying to use OBJECTTABLE
van.halbert Sep 20, 2012 11:32 AM (in response to shawkins)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>