Virtual Procedure to insert data into table is not working

[neetacomp]

I have created a virtual procedure to insert data into a table

CREATE VIRTUAL PROCEDURE

BEGIN

    INSERT INTO SQLServerSourceModel.tblItems (ItemId, Part, SegCode) VALUES ('Item1', 'Part1', 'Seg2');

END

But it gives me error (Problems tab inm Eclipse) " An exception was encounter during validation. Check the log for details"

Where to see the log for details?

Under 'Error Log', it says "An exception was encountered during validation"

But I am not sure what is the error.

If I run the same Insert statement from SQL Scrapbook, it works fine.

Does my virtual procedure always need to return result set?

I am facing the same issue for update query also.

Does virtual procedure support Insert and update queries?

Thanks,

Neeta

[rareddy]

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..

[neetacomp]

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

[rareddy]

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.

[neetacomp]

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

[rareddy]

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?

[neetacomp]

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

[shawkins]

Neeta,

Yes that is a bug in Designer, can you log an issue?

Steve