9 Replies Latest reply on Nov 27, 2013 10:24 AM by Lucie Fabrikova

    Unable to update, Issues in insert statement

    Sanjay Bhardwaj Newbie

      I am trying to update base table via model view which is join of base table and flat file. While testing VDB, I'm facing some issues

       

      1. Select statement is working fine in VDB but during update an error -> Update is not allowed. A procedure may be needed to handle the update since: the updatable view has no valid target for updates.

      2. Insert Procedure is giving syntax error that "INPUT.Emp_No is specified with an unknown group context"

            -------------------

           Create Virtual Procedure

           Begin

                 Insert into database.hero_salary (Emp_no, Salary) Values (INPUT.Emp_No, INPUT.Emp_Salary);

           End

           -------------------

      I'm using teiid designer 8.2.0. View files are also attached for reference.

       

      Thanks

      Sanjay

        • 1. Re: Unable to update, Issues in insert statement
          Ramesh Reddy Master

          Sanjay,

           

          You need to set the view to be "updatable", there is check box you need to select for updates to enable. Then based on the "select" transformation you provided for the view, it can automatically supply "insert/update/delete" operations in certain situations. As you know in your case the "file" source does not support updates, so you can provide your own "insert/update/delete" procedures for the view (see there are multiple tabs for this in Designer, where you provided the select transformation) using the procedure language using Update Procedures,that does involve the "file" source.

           

          Ramesh..

          • 2. Re: Unable to update, Issues in insert statement
            Steven Hawkins Master

            > Select statement is working fine in VDB but during update an error -> Update is not allowed. A procedure may be needed to handle the update since: the updatable view has no valid target for updates.

             

            No valid targets usually means that the underlying sources are not marked as updatable.

             

            > Then based on the "select" transformation you provided for the view, it can automatically supply "insert/update/delete" operations in certain situations.

             

            Whenever a join is involved we will automatically determine an appropriate update only if the join is key preserving - Updatable Views - Teiid 8.5 (draft) - Project Documentation Editor

             

            > Insert Procedure is giving syntax error that "INPUT.Emp_No is specified with an unknown group context"

             

            The only insert procedure designer should allow is an INSTEAD OF FOR EACH ROW trigger - Update Procedures - Teiid 8.5 (draft) - Project Documentation Editor

             

            It should probably provide you with a better error message to let you know what is expected.

            • 3. Re: Unable to update, Issues in insert statement
              Sanjay Bhardwaj Newbie

              Thanks Ramesh.

               

              The check box "Support Update" is already selected in database view created upon table and the view created over join between file and database view. Still I am getting an error that underlying sources are not marked as updatable.

               

              Is there some other property which needs to be checked?

               

              Regards

              Sanjay

              • 4. Re: Unable to update, Issues in insert statement
                Steven Hawkins Master

                Sanjay

                 

                See my message above.  It may be the case that key preservation is not satisfied.  Ideally there would be a message about that in the Designer.  Can you post your view definition and the DDL of the tables involved to see if Teiid or Designer is not providing you with enough information.

                 

                Steve

                • 5. Re: Unable to update, Issues in insert statement
                  Sanjay Bhardwaj Newbie


                  The view is based on join between file and database (mysql) view. Database view is marked as "Support Update" to allow updates on database.

                  View is also marked as "Support Update". Using VDB, I can update the base table through database view but not through the final join view. Is this the expected behavior?

                   

                  DDL - The database table is having two columns as "Emp_No" and "Salary" with Emp No marked as primary key.

                  File - It has 2 columns as "Emp No" and "Name"

                  View - View is join between above on the basis of Emp No. Update procedure is also added as mentioned below.

                   

                   

                  ------Update Procedure-----

                  FOR EACH ROW

                  BEGIN ATOMIC

                    IF(CHANGING.salary)

                    BEGIN

                    UPDATE ptp.super_hero_salary SET salary = "NEW".salary WHERE FileDBAccessView.ptp.super_hero_salary.emp_no = "OLD".emp_no;

                    END

                  END

                  --------------------------------

                   

                   

                  Regards

                  Sanjay

                  • 6. Re: Unable to update, Issues in insert statement
                    Steven Hawkins Master

                    > I can update the base table through database view but not through the final join view. Is this the expected behavior?

                     

                    It depends upon what you mean.  As long as the Teiid view definition satisfies the requirements of Updatable Views - Teiid 8.5 (draft) - Project Documentation Editor and the update the you are running is valid, then the view should be inherently updatable.  If you were not getting sufficient information in Designer when attempting to use inherent updates, then there may be an issue there.

                     

                    Any view may be made updatable via INSTEAD OF triggers.  There you assume full responsibility for the processing of the update.  Is your statement that the update is not working based upon trying to do an inherent update or with using the INSTEAD OF trigger shown above?

                     

                    Steve

                    • 7. Re: Unable to update, Issues in insert statement
                      Lucie Fabrikova Newbie

                      Do I understand it correctly, that the tab "INSERT", "UPDATE", "DELETE" in teiid designer relational view transformation diagram stands for "CREATE TRIGGER ON name INSTEAD OF INSERT|UPDATE|DELETE AS" and the contents of the tab (ie the query) should have form "FOR EACH ROW block"?

                       

                      Another question: how to switch the TRANSLATE CRITERIA for teiid 8 - ie. how should the following query look like?

                      VARIABLES.ShipId = SELECT SHIPPER_ID FROM PartsOne.SUPPLIER_PARTS WHERE TRANSLATE CRITERIA ON (TwoTransView.UpdateParts.SHIPPER_ID, TwoTransView.UpdateParts.SUPPLIER_ID, TwoTransView.UpdateParts.PART_ID);
                      DELETE FROM PartsOne.SUPPLIER_PARTS WHERE TRANSLATE CRITERIA ON (TwoTransView.UpdateParts.SUPPLIER_ID, TwoTransView.UpdateParts.PART_ID, TwoTransView.UpdateParts.SHIPPER_ID);

                      • 8. Re: Unable to update, Issues in insert statement
                        Steven Hawkins Master

                        Yes, they are for creating the appropriate instead of triggers.

                         

                        > VARIABLES.ShipId = SELECT SHIPPER_ID FROM PartsOne.SUPPLIER_PARTS WHERE TRANSLATE CRITERIA ON (TwoTransView.UpdateParts.SHIPPER_ID, TwoTransView.UpdateParts.SUPPLIER_ID, TwoTransView.UpdateParts.PART_ID);

                         

                        There is no translate criteria concept, rather your insert/update/delete is effectively applied against the view and then you reference new/old/changing variables in your for each row block:

                         

                        for each row

                        begin atomic

                           DELETE FROM PartsOne.SUPPLIER_PARTS WHERE SUPPLIER_ID = old.SUPPLIER_ID;

                        end