-
1. Re: Virtual Procedure to insert data into table is not working
rareddy Jun 25, 2013 8:49 AM (in response to neetacomp)Neeta,
First of all when you are trying to do INSERT and UPDATES, you need to define a View and on the view you can define INSERT/UPDATE/DELETE "instead of triggers" (procedures) along with your SELECT transformation in Teiid Designer. See http://docs.jboss.org/teiid/designer/8.1/user-guide/en-US/html_single/#transformation-editor-section right next to it you will see additional tabs for update, insert and delete.
You can also do what you are doing in the Virtual procedures, but that will not give all the hooks about old and new values of the table you are changing.
As per the error, there is "error log" and "problem log". If there is some thing in the "problem log" those represent directly to your model issues. "Error log" is general eclipse issues, that may have occurred during some task you did, but should not be occurring like an exception. If you want, you can go to your Designer workspace directory and look under ".metadata" directory for a ".log" file for the exception. If that is some issue you can log a JIRA with Teiid Designer.
Ramesh..
-
2. Re: Virtual Procedure to insert data into table is not working
neetacomp Jun 26, 2013 1:18 AM (in response to rareddy)Thanks Ramesh..
Does it mean I can not directly use Insert and update in my virtual procedure?
I will have to create a view ..If yes, it is cumbersome to create view for all my source tables.
Is view same as Base table? I can not see any thing as New->View, when I select my view model.
Please suggest what is the better way to do it.
Thanks,
Neeta
-
3. Re: Virtual Procedure to insert data into table is not working
rareddy Jun 26, 2013 7:47 AM (in response to neetacomp)I did not say that, yes you can still use the INSERT in the virtual procedure, but the any thing you want to do with old values (like comparing) you can not do in this method. Also the execution model would be executing the virtual procedure rather than executing a INSERT on the view for the other one.
If you are creating One to One views, it is pretty easy to create views. In the Designer, choose to create model, then select "relational view model", then choose to transform a existing model. That will create views for all the tables in that model selected.
Yes, Base Table and View or same.
-
4. Re: Virtual Procedure to insert data into table is not working
neetacomp Jun 28, 2013 7:55 AM (in response to rareddy)Thanks Ramesh for all the help.
So as per you suggestion, I have created views as below
In the Designer, choose to create model, then select "relational view model", then choose to transform a existing model. That will create views for all the tables in that model selected.
Now I am following below steps
- Double click tblItems view
- Checked the checkbox 'Supports Update'
- Goto Insert tab
- Unchecked 'Use Default' checkbox
- Creating instead of trigger as below
CREATE TRIGGER ON tblITEMS
INSTEAD OF INSERT
AS
FOR EACH ROW
BEGIN
INSERT INTO SQLServerSourceModel1.tblItems (SQLServerSourceModel1.tblItems.ItemId, SQLServerSourceModel1.tblItems.Part, SQLServerSourceModel1.tblItems.SegCode) VALUES ('Neeta1', 'Part1', 'Seg1');
END
==> But when I save, all the contents goes away except first 3 lines.
Can you please tell me what is wrong in above trigger?
Can I name this trigger? If yes, how?
One more thing, If I dont want to create views, then how can I insert/update/delete data into my table using normal virtual procedure?
Another thing I tried is I created view for my tables (Did not modify any thing in view , kept default Insert, default Update etc)
Now in my virtual procedure, I am trying to insert into view.
But it gives validation error.
Please help to resolve it.
Thanks,
Neeta
-
5. Re: Virtual Procedure to insert data into table is not working
rareddy Jun 28, 2013 9:02 AM (in response to neetacomp)Steps are right. Can not rename the trigger, it is part of view definition. Most of the times these procedures are automatically figured out based on the select transformation, if you have special needs (select transformation is complex) then you can add your.
I do not know about why your entered SQL is disappearing. Designer experts?
what validation error?
-
6. Re: Virtual Procedure to insert data into table is not working
neetacomp Jul 1, 2013 1:07 AM (in response to rareddy)In Eclipse .log file, I see the following validation exception. I am not clear what is it
!ENTRY org.teiid.designer.core 4 0 2013-07-01 10:35:19.030
!MESSAGE An exception was encountered during validation.
!STACK 0
java.lang.NullPointerException
at java.util.AbstractCollection.addAll(AbstractCollection.java:303)
at org.teiid.designer.query.sql.lang.util.CommandHelper.getProjectedSymbols(CommandHelper.java:33)
at org.teiid.designer.transformation.aspects.validation.rules.ProjectSymbolsValidationHelper.validateProjectedSymbols(ProjectSymbolsValidationHelper.java:59)
at org.teiid.designer.transformation.aspects.validation.rules.SqlTransformationMappingRootValidationRule.validateSqlTransformation(SqlTransformationMappingRootValidationRule.java:788)
at org.teiid.designer.transformation.aspects.validation.rules.SqlTransformationMappingRootValidationRule.validate(SqlTransformationMappingRootValidationRule.java:137)
at org.teiid.designer.core.validation.ValidationRuleSetImpl.validate(ValidationRuleSetImpl.java:121)
at org.teiid.designer.core.validation.Validator.validateObject(Validator.java:124)
at org.teiid.designer.core.validation.Validator.validate(Validator.java:76)
at org.teiid.designer.core.builder.XmiResourceValidator.validate(XmiResourceValidator.java:88)
at org.teiid.designer.core.builder.ModelBuildUtil.internalValidateResource(ModelBuildUtil.java:455)
at org.teiid.designer.core.builder.ModelBuildUtil.validateResources(ModelBuildUtil.java:713)
at org.teiid.designer.core.builder.ModelBuildUtil.buildResources(ModelBuildUtil.java:165)
at org.teiid.designer.core.builder.ModelBuilder$2.run(ModelBuilder.java:225)
at org.teiid.designer.core.ModelEditorImpl.executeAsTransaction(ModelEditorImpl.java:568)
at org.teiid.designer.core.ModelEditorImpl.executeAsTransaction(ModelEditorImpl.java:531)
at org.teiid.designer.core.builder.ModelBuilder.performIncrementaBuild(ModelBuilder.java:238)
at org.teiid.designer.core.builder.ModelBuilder.build(ModelBuilder.java:102)
at org.eclipse.core.internal.events.BuildManager$2.run(BuildManager.java:728)
at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)
at org.eclipse.core.internal.events.BuildManager.basicBuild(BuildManager.java:199)
at org.eclipse.core.internal.events.BuildManager.basicBuild(BuildManager.java:239)
at org.eclipse.core.internal.events.BuildManager$1.run(BuildManager.java:292)
at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)
at org.eclipse.core.internal.events.BuildManager.basicBuild(BuildManager.java:295)
at org.eclipse.core.internal.events.BuildManager.basicBuildLoop(BuildManager.java:351)
at org.eclipse.core.internal.events.BuildManager.build(BuildManager.java:374)
at org.eclipse.core.internal.events.AutoBuildJob.doBuild(AutoBuildJob.java:143)
at org.eclipse.core.internal.events.AutoBuildJob.run(AutoBuildJob.java:241)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:53)
Thanks,
Neeta
-
7. Re: Virtual Procedure to insert data into table is not working
shawkins Jul 3, 2013 3:22 PM (in response to neetacomp)Neeta,
Yes that is a bug in Designer, can you log an issue?
Steve