how to load complex object (from a cache store) in a view created in dynamic vdb?

[rupeshsingh01]

Hi all,

I am using embedded teiid and creating view as below in dynamic vdb using object datatype for a physical data source (a object cache store)

CREATE VIEW TansactionSchemaView (

transaction object,

transactionClassificationCollection object

)

AS

select * from physicalModel.TransactionSchema;

The TransactionSchema object has object references as properties. e.g. a Transaction object and Collection<TransactionClassification> etc.

The teiid runtime is able to fetch transactionSchema objects. I am able to see TransactionSchemaView  through a JDBC client but only metadata.No content is available. so unable to fire any query on this view.

So how can we use object datatype and see content in this kind of views?

One-to-one relation of objects can be stored by collating all properties of contained object with the properties of container object in a single view. However one-to-many object relations  is showing difficulty. I can introduce three views

1.for container object (TransactionView) 2. for contained type of collection (ClassificationView) and 3. mapping view  (TransactionClassificationView) having transactionId and classificationId columns. But unable to load three views from a  same query on TransactionSchema (select * from physicalModel.TransactionSchema).

Please help.

Regards

[shawkins]

> The teiid runtime is able to fetch transactionSchema objects. I am able to see TransactionSchemaView  through a JDBC client but only metadata.No content is available. so unable to fire any query on this view.

What is your source translator?

> So how can we use object datatype and see content in this kind of views?

You can use UDFs or OBJECTTABLE OBJECTTABLE - Teiid 9.0 (draft) - Project Documentation Editor to extract whatever columns you need from the object values.  There is logic built-in for this with the object/cache translators as well.

[rupeshsingh01]

Thanks Steven.

I am using a custom translator.

[rareddy]

Rupesh,

If you are using the custom translator, you can expose source tables with objects and then use the OBJECTTABLE as Steve mentions to extract the data. But if you want your translator to show matadata and show full relational schema, then you have write translator to expose that.

Did you take look at "Object" translator Teiid has? does it not satisfy you needs? it sounds like that would be a perfect match.

Ramesh..