VDB reuse how to use (other than reading) virtual model?
m.ardito Jan 11, 2013 10:19 AMhi!
i'm trying to learn vdb reuse cababilities in XML vdb through DDL, as explained here and here
i basically referred to this example, shown there:
<
model
visible
=
"true"
type
=
"VIRTUAL"
name
=
"customers"
>
<
metadata
type
=
"DDL"
>
<![CDATA[
CREATE VIEW PARTS (
PART_ID integer PRIMARY KEY,
PART_NAME varchar(255),
PART_COLOR varchar(30),
PART_WEIGHT varchar(255)
) AS
select a.id as PART_ID, a.name as PART_NAME, b.color as PART_COLOR, b.weight as PART_WEIGHT from modelA.part a, modelB.part b where a.id = b.id
]]>
<
metadata
>
</
model
>
as a template for my tests and i succeeded creating a "reuse" vbd like this
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="reuse" version="1">
<import-vdb name="Testmix" version="1" import-data-policies="false"/>
<model visible="true" type="VIRTUAL" name="new-model">
<metadata type = "DDL"><![CDATA[
CREATE VIEW x (
conto varchar,
descrizione varchar
) AS
select cc.conto_pdc, coa.description from TestmixMS.ApiMn.dbo.MA_ChartOfAccounts as coa inner join TestmixMY.conti_contratti as cc on coa.Account = cc.conto_pdc
]]>
</metadata>
</model>
</vdb>
(note: on localhost teeid instance i have a testmix xml vdb with 2 models: testmixMS (sql server) and testmixMY (mysql) which works (now) perfectly both with JDBC and ODBC (squirrel, msaccess, msexcel) which gives me SQL access to objects like
- TestmixMS.ApiMn.dbo.MA_ChartOfAccounts
or
- TestmixMY.conti_contratti)
and it works! i can read the table with SQL and if in the "TestmixMY" source mysql DB table "conti_contratti" i add a new row, the "reuse" virtual model sees it perfectly.
BUT
i am now for the first time facing the concept of: how can i use such a virtual table, other than reading values as if it was a VIEW?
- can i update single field values? if i try to update the "conto" field of an existing record, i get
"Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 Metadata does not allow updates on the group: x
SQLState: 50000
ErrorCode: 30492"
so, is there any other way to create/update/delete records with this kind of setup, or do i need to create a "full" vdb through the designer? or?
you will excuse my newbieness
btw teiid is SO cool... thanks for it!
[edit]
i've just seen that there are options in DDL that allow table/views and also columns to be "updatable", at least, so that could be the way obvioulsy, but apart from the simple example on top of the "vdb reuse" page is there any other slightly more complex example i can follow (the below "read twitter" example is too much for me now, perhaps...) . is there any docs around which explains those values with examples? perhaps i'm askin too much uh?
[/edit]
Marco