7 Replies Latest reply on Jul 3, 2013 3:22 PM by Steven Hawkins

    Virtual Procedure to insert data into table is not working

    Neeta L Newbie

      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

        • 1. Re: Virtual Procedure to insert data into table is not working
          Ramesh Reddy Master

          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
            Neeta L Newbie

            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
              Ramesh Reddy Master

              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
                Neeta L Newbie

                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
                  Ramesh Reddy Master

                  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
                    Neeta L Newbie

                    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
                      Steven Hawkins Master

                      Neeta,

                       

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

                       

                      Steve