-
15. Re: Creating trigger on a table
Vineela,
Teiid 7.7 does not support DDL for creation fo views and tables. I think you are getting confused with Teiid 8.x features. create trigger is allowed in 7.7, but as I said previously it is not persisted and will not survive the restarts of the system. To make view updatable you need to
view.setUpdatable(true);
Ramesh..
-
16. Re: Creating trigger on a table
But if you want to mark a view updatable in VDB how to do we do it?
why am i getting sql parsing erros for my trigger definition? Iam unable to figure out which is causing the trigger statement to fail.
Can you provideme with a trigger example?
-
17. Re: Creating trigger on a table
{code}
view.setUpdatable(true);
{code}
for create trigger
http://docs.jboss.org/teiid/7.7.0.Final/reference/en-US/html_single/#d0e1660
Ramesh..
-
18. Re: Creating trigger on a table
Hi Ramesh,
try
{
if (!this.hasConnectionErr)
{
super.getMetadata(metadataFactory, conn);
Map<String, Schema> schemas = metadataFactory.getMetadataStore()
.getSchemas();
Schema currentschema = metadataFactory.getMetadataStore()
.getSchemas()
.get("DEMO");
if (currentschema != null)
{
Map<String, Table> tables = currentschema.getTables();
List<Table> tablesToAdd = new ArrayList<Table>();
for (String tableName : tables.keySet())
{
Table table = tables.get(tableName);
table.setVirtual(true);
if (table.getName().equals("random_number"))
{
Table table_View = new Table();
table_View.setName(tableName + "_view");
table_View.setSupportsUpdate(true);
tablesToAdd.add(table_View);
table_View.setVirtual(true);
QueryNode node = new QueryNode("select random_number as random_number from demo.random_number");
table_View.setSelectTransformation(node.getQuery());
table_View.setTableType(Type.View);
table_View.setBindings(node.getBindings());
for (final Column column : table.getColumns())
{
table_View.addColumn(column);
}
logger.debug("Successfully created view "
+ table_View.getName());
table_View.setInsertPlan("FOR EACH ROW BEGIN insert into demo.random_test (random_number) values (new.random_number); END");
}
}
for (Table tableToAdd : tablesToAdd)
{
currentschema.addTable(tableToAdd);
}
}
}
} catch (Exception e)
{
logger.warn("Error on getMetaData call", e);
}
Iam creating view for random_number table above under DEMO schema. I see my view is created. But when i execute select random_number from demo.RANDOM_NUMBER_view iam getting the below error. When i debugged it i saw that org.teiid.query.parser.QueryParser.parseCommand(String) the sql thats passed to this is null. Am i doing anything wrong wrt below ?
QueryNode node = new QueryNode("select random_number as random_number from demo.random_number");
table_View.setSelectTransformation(node.getQuery());
[10:34:24.006][info][talledLocalContainer] 13 Aug 2012 10:34:24,006 PDT WARN [PROCESSOR] Processing exception 'Error Code:ERR.015.008.0011 Message:Error Code:ERR.015.008.0011 Message:Error parsing query plan transformation for demo.random_number' for request p7ZnJ+tCVjTB.3. Exception type org.teiid.api.exception.query.QueryPlannerException thrown from org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:133). Enable more detailed logging to see the entire stacktrace.
-
-
-
21. Re: Creating trigger on a table
Hi Ramesh,
On the other note iam trying to create trigger using JDBC. Iam getting the below error. I have provided the table definitions and the code iam using below. what is that iam missing. I have marked "demo_policy_metadata"."demo_policy" as updatable by doing table.setVirtual(true); and table.setSupportsUpdate(true);
30 Aug 2012 16:39:13,670 PDT DEBUG [org.teiid.TXN_LOG] (Worker34_QueryProcessorQueue259) after getOrCreateTransactionContext : Ww05LZoxZpAm NONE ID:NONE
30 Aug 2012 16:39:13,670 PDT DEBUG [org.teiid.BUFFER_MGR] (Worker34_QueryProcessorQueue259) Creating TupleBuffer: 229 [objectName, subType, objectInstance, propertyName, propertyValue] [class java.lang.String, class java.lang.String, class java.lang.String, class java.lang.String, class java.lang.String] of type PROCESSOR
30 Aug 2012 16:39:13,670 PDT DEBUG [org.teiid.PROCESSOR] (Worker33_QueryProcessorQueue258) Request Thread 71pj2DGB7gnD.0 - error occurred
[QueryResolverException]demodata.username is not a valid view.
at org.teiid.query.resolver.command.AlterResolver.resolveCommand(AlterResolver.java:67)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:294)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:147)
at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:291)
at org.teiid.dqp.internal.process.PreparedStatementRequest.resolveCommand(PreparedStatementRequest.java:87)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:392)
at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:138)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:459)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:516)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:276)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:215)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)
30 Aug 2012 16:39:13,671 PDT WARN [org.teiid.PROCESSOR] (Worker33_QueryProcessorQueue258) Processing exception 'demodata.username is not a valid view.' for request 71pj2DGB7gnD.0. Exception type org.teiid.api.exception.query.QueryResolverException thrown from org.teiid.query.resolver.command.AlterResolver.resolveCommand(AlterResolver.java:67). Enable more detailed logging to see the entire stacktrace.
CREATE VIEW "demo_policy_metadata"."demo_policy" (name, PKEY)
WITH CASCADED CHECK OPTION;
CREATE TABLE "H2DS_DEMODATA"."FAKE_RECORDS" (
"NAME" String(128),
"AGE" INTEGER,
"ADMINUSER" String(128),
"ZIP" String(128),
"STARTTIME" Timestamp,
"ENDTIME" Timestamp,
"SYSID" String(30),
"USERID" String(30),
"PHONE" String(30)
);
ublic class DbExecutorTest {
TeiidDataSource teiidDataSource = new TeiidDataSource();;
@Before
public void initServices() throws Exception
{
this.teiidDataSource.setServerName("localhost");
this.teiidDataSource.setPortNumber(31000);
this.teiidDataSource.setDatabaseName("db");
this.teiidDataSource.setUser("admin");
this.teiidDataSource.setPassword("test");
}
@Test
public void createTrigger() throws Exception {
Connection connection = this.teiidDataSource.getConnection();
Statement st = connection.createStatement();
st.execute("CREATE TRIGGER ON demo_policy_metadata.demo_policy INSTEAD OF UPDATE AS FOR EACH ROW BEGIN update H2DS_DEMODATA.FAKE_RECORDS "+
"set NAME=new.Name,AGE=new.pkey; END");
st.close();
connection.close();
}
}
-
22. Re: Creating trigger on a table
The exception is for an alter against demodata.username, but your code shows demo_policy_metadata.demo_policy. What is the discrepancy?
-
23. Re: Creating trigger on a table
I was trying against different views. Sorry about that forhving pasted wrong thing..Just replace demodata.username with demo_policy_metadata.demo_policy.
-
24. Re: Creating trigger on a table
Can you point me to the example which creates trigger using teiid JDBC api.
-
25. Re: Creating trigger on a table
Your syntax is correct otherwise you would have received an earlier exception. If this is pre-8.1 our logic to check if the alter target is valid included checking the schema/model type. So if this is coming in from a translator as a physical model, then you would get this exception. If you try this on 8.1 or latest, it should work.
Steve
-
26. Re: Creating trigger on a table
yes this is against 7.7.1. I can switch to 8.1 now. Is there a way to get it workign against 7.7.1 ?if so can you point me to an example.
-
27. Re: Creating trigger on a table
You don't really need more of an example. You are already correctly issuing an alter statement. The one you have is valid or you can use the Reference if you want a different alter. Alters will work in 7.7.1 but it needs to be issued against a view in a virtual model. That was the trust of https://issues.jboss.org/browse/TEIID-2136 which ensured this scenario would work. In 7.7.1 while you can add views to physical models not all of the downstream logic handles them correctly.
-
28. Re: Creating trigger on a table
ok got it. Will try by setting the model to be virtual . But Steve why is it so difficult to create a trigger? Why it has to be a virtual model or why it has to be a view ?
-
29. Re: Creating trigger on a table
>Will try by setting the model to be virtual
You cannot do that if this is coming from translator supplied metadata. You will need to switch to 8.1 to get this to work the way you expect.
>Why it has to be a virtual model or why it has to be a view ?
The virtual model restriction is a hold over from earlier days when we only had Designer supplied metadata, which was forced to be entirely virtual or entirely physical. That condition was relaxed somewhat with the introduction of dynamic vdbs in 7.x, but was not completely addressed until TEIID-2136. The reason it needs to be a view is also somewhat of a legacy restriction. The logic responsible for matviews/triggers only looks for those entries on views. I think that there is already an issue covering the mat view case. You could add an enhancement request or expand that one for triggers. In either case the workaround is straight-forward, you just add a view that performs "SELECT * from tbl".
Steve