7 Replies Latest reply on Aug 1, 2013 2:51 PM by katherine

    update statement with WHERE clause is splitted

    katherine Newbie

      Hi,

       

      I have created a view model vdb using Teiid Designer and deployed it to JBOSS server.

      I overwrote the default Update transformation in the view model.

       

      I am testing the Update using Squirrel.

       

      My update statement looks like:

       

      UPDATE well SET comment='test' WHERE well.name = 'test_well'

       

      However, Teiid splitted the update statement into two statements and sent to my translator.

       

      The first statement is:

       

      Select name, comment from well where well.name='test_well'

       

      The second statement is:

       

      Update well SET comment='test'

       

      My translator is expecting one Update statement with the WHERE clause, not two.

       

      What have I done wrong? Why Teiid splits the update statement?

       

      Please help. Thanks.

       

      Katherine

        • 1. Re: update statement with WHERE clause is splitted
          Ramesh Reddy Master

          If this is custom translator, in the ExecutionFactory class, there are various "supports" calls with "criteria" in them, those calls define what kind of capabilities that your translator have. If your translator can support it then, turn it on. If you writing a custom translator to a JDBC source that Teiid does NOT already provide, then try extending the JDBCExecutionFactory class instead of ExecutionFactory as it has most basic functionality already turned on.

           

          Also take look at the https://docs.jboss.org/author/display/TEIID/Translator+Development

           

          Ramesh..

          • 2. Re: update statement with WHERE clause is splitted
            Steven Hawkins Master

            Also when using the non-default view instead of triggers the system does not currently optimize the default processing, which is literally FOR EACH ROW ...  So the first thing that Teiid does is determine the affected row set with a select and then for each row will process the sql block from the trigger.

             

            Steve

            • 3. Re: update statement with WHERE clause is splitted
              katherine Newbie

              Hi Ramesh,

               

              Thank you for your response.

               

              I deployed a dynamic VDB to JBOSS server.

               

              I did the same test with the same update statement. Teiid did not split the statement. It passed the entire update statement with the where clause to my translator.

               

              I do not know why? it is the same translator.

               

              I am new to the teiid world. Thank you.

               

              Katherine

              • 4. Re: update statement with WHERE clause is splitted
                Ramesh Reddy Master

                Post both vdbs may be we can tell, but that is not expected behavior.

                • 5. Re: update statement with WHERE clause is splitted
                  katherine Newbie

                  Hi Steven,

                   

                  Thank you very much for your response.

                   

                  Do you mean if I use the default update transformation in the view model, then Teiid will not split the update statement?

                   

                  I tried to use the default update transformation in the view model. It gave me QueryValidatorException:

                   

                  Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 TEIID30061 Update is not allowed on the view fieldPros.  A procedure may be needed to handle the Update since: The updatable view has no valid target for UPDATEs.

                   

                  My non default update transformation looks something like this:

                   

                   

                   

                   

                   

                   

                  FOR

                   

                  EACH

                  ROW

                  BEGIN

                   

                  ATOMIC

                   

                  IF

                  (CHANGING.well_name)

                   

                  BEGIN

                   

                  UPDATE NativeModel.WELL SET Name = "NEW"

                  .well_name;

                   

                  END

                  END

                   

                  In my translator, when it received the second statement (update statement without where clause), it will update all wells in my native database since it did not know what was the where clause at this point.

                   

                  Do you have any suggestions to fix this problem?  Is there any ways to know what was the where clause at the point of executing the second statement?

                   

                  Thank you.

                   

                  Katherine

                  • 6. Re: update statement with WHERE clause is splitted
                    Steven Hawkins Master

                    > Do you mean if I use the default update transformation in the view model, then Teiid will not split the update statement?

                     

                    In most situations it will not.  However depending upon transformation and other factors it may need to do for each row processing to ensure correctness.

                     

                    > I tried to use the default update transformation in the view model. It gave me QueryValidatorException:

                     

                    What does the view definition look like?  And do the tables involved have primary keys?

                     

                    > My non default update transformation looks something like this:

                     

                    Note that you are telling it to update all rows for each row of the update.  Do you have a primary key?

                     

                    You probably want something more like:

                     

                    UPDATE NativeModel.WELL SET Name = "NEW".well_name WHERE pkCol = "OLD".pkCol;

                     

                    To make the update specific to the affected row.

                     

                    Steve

                    • 7. Re: update statement with WHERE clause is splitted
                      katherine Newbie

                      Hi Steven,

                       

                      Thank you a lot.

                       

                      Your answers are all making a lot of senses to me now. I have updated my procedure with specific where clause. It works.

                       

                      Thank you again for your help.

                       

                      Katherine