8 Replies Latest reply on Jun 23, 2015 4:24 AM by Dharmendra Prasad

    INSTEAD OF UPDATE trigger fetches the complete view.

    Dharmendra Prasad Newbie

      I have a View backed by a REST API which returns a collection.  Here is the view definition

      CREATE VIEW ContactView(
        id varchar(100), type varchar(100), title varchar(100), firstName varchar(100), lastName varchar(100), name varchar(100), emailAddress varchar(100), 
        accountName varchar(100), currentStatus varchar(100), businessPhone varchar(100), province varchar(100), postalCode varchar(100), country varchar(100), 
        subscriptionDate varchar(100), updatedAt varchar(100), depth varchar(100), isBounceback varchar(100), isSubscribed varchar(100), salesPerson varchar(100)
        ) 
              OPTIONS (UPDATABLE TRUE) 
              AS 
              SELECT A.id, A.type, A.title, A.firstName, A.lastName, A.name, A.emailAddress, A.accountName, A.currentStatus, A.businessPhone, 
              A.province, A.postalCode, A.country, A.subscriptionDate, A.updatedAt, A.depth, A.isBounceback, A.isSubscribed, A.salesPerson
        FROM
        (EXEC CustomerSource.invokeHttp('GET', null, 'https://secure.eloqua.com/API/REST/1.0/data/contacts?depth=complete', 'TRUE')) AS f, 
        XMLTABLE('/holder/elements' PASSING JSONTOXML('holder', f.result) 
        COLUMNS 
        id string PATH 'id/text()',
        type string PATH 'type/text()',
        title string PATH 'title/text()',
        firstName string PATH 'firstName/text()',
        lastName string PATH 'type/text()',
        name string PATH 'name/text()',
        emailAddress string PATH 'emailAddress/text()',
        accountName string PATH 'accountName/text()',
        currentStatus string PATH 'currentStatus/text()',
        businessPhone string PATH 'businessPhone/text()',
        province string PATH 'province/text()',
        postalCode string PATH 'postalCode/text()',
        country string PATH 'country/text()',
        subscriptionDate string PATH 'subscriptionDate/text()',
        updatedAt string PATH 'updatedAt/text()',
        depth string PATH 'depth/text()', 
        isBounceback string PATH 'isBounceback/text()', 
        isSubscribed string PATH 'isSubscribed/text()', 
        salesPerson string PATH 'salesPerson/text()'
        ) 
        AS A;
      

       

      I am able to implement a INSTEAD OF INSERT trigger on the view and invoke virtual procedure, which in turn makes a POST call to the REST web service. Here is the trigger and the procedure which is successful.

      CREATE VIRTUAL PROCEDURE CreateContact(
        type varchar, title varchar, firstName varchar, lastName varchar, name varchar, emailAddress varchar, accountName varchar, businessPhone varchar,
        province varchar, postalCode varchar, country varchar, salesPerson varchar
        ) 
        RETURNS (id varchar(25), name varchar(25)) 
        AS
          BEGIN
          DECLARE string url = 'https://secure.eloqua.com/API/REST/1.0/data/contact';  
        DECLARE clob obj = jsonObject(type as type, title as title, firstName as firstName, lastName as lastName, name as name, emailAddress as emailAddress, 
        accountName as accountName, businessPhone as businessPhone, province as province, postalCode as postalCode, 
        country as country, salesPerson as salesPerson);
      
          SELECT B.id as id, B.name as name FROM (call CustomerSource.invokeHttp('POST', obj , url, 'TRUE', '{"Content-Type":["application/json"]}')) AS f, 
        XMLTABLE('/holder' PASSING JSONTOXML('holder', f.result) 
        COLUMNS id string PATH 'id/text()', name string PATH 'name/text()'
        ) AS B;  
         
          END;
           
          CREATE TRIGGER ON ContactView INSTEAD OF INSERT AS
          FOR EACH ROW
          BEGIN ATOMIC
          call CreateContact.CreateContact(
          New.type, New.title, New.firstName, New.lastName, New.name, New.emailAddress, New.accountName, New.businessPhone, New.province, New.postalCode, New.country, New.salesPerson);
          END;  
      

       

      Now, I tried to implement the INSTEAD OF UPDATE trigger on the view using a different virtual procedure, which will make a PUT call to the REST web service. Upon writing this and deploying the VDB, I realized that the UPDATE JDBC calls on the view are actually making a GET call to fetch the records from the REST API. This times out because the API has a lot of data. The below one doesn't work:

       

      CREATE VIRTUAL PROCEDURE UpdateContact(
        id varchar, type varchar, title varchar, firstName varchar, lastName varchar, name varchar, emailAddress varchar, accountName varchar, businessPhone varchar,
        province varchar, postalCode varchar, country varchar, salesPerson varchar
        ) 
        RETURNS (id varchar(25), name varchar(25)) 
        AS
          BEGIN
          DECLARE string url = CONCAT('https://secure.eloqua.com/API/REST/1.0/data/contact/',id);  
        DECLARE clob obj = jsonObject(id as id, type as type, title as title, firstName as firstName, lastName as lastName, name as name, emailAddress as emailAddress, 
        accountName as accountName, businessPhone as businessPhone, province as province, postalCode as postalCode, 
        country as country, salesPerson as salesPerson);
      
          SELECT B.id as id, B.name as name FROM (call CustomerSource.invokeHttp('PUT', obj , url, 'TRUE', '{"Content-Type":["application/json"]}')) AS f, 
        XMLTABLE('/holder' PASSING JSONTOXML('holder', f.result) 
        COLUMNS id string PATH 'id/text()', name string PATH 'name/text()'
        ) AS B;  
         
          END;
           
          CREATE TRIGGER ON ContactView INSTEAD OF UPDATE AS
          FOR EACH ROW
          BEGIN ATOMIC
          call CreateContact.UpdateContact(
          New.id, New.type, New.title, New.firstName, New.lastName, New.name, New.emailAddress, New.accountName, New.businessPhone, New.province, New.postalCode, New.country, New.salesPerson);
          END;  
      

       

       

      Please suggest if I am missing something.

        • 1. Re: INSTEAD OF UPDATE trigger fetches the complete view.
          Steven Hawkins Master

          > Please suggest if I am missing something.

           

          Logically when the instead of update runs it will make available both the old and the new values for each affected row.  To determine the old values it has to query the view - at a minimum we have to know which rows are valid to process the update against.

          • 2. Re: INSTEAD OF UPDATE trigger fetches the complete view.
            Dharmendra Prasad Newbie

            Thank you for your genuine reply Steven.

            I understand why it makes a GET query on the view. I still think that there might be a way in my scenario where I can force it to fetch the record for the affected id and need not require to fetch the complete view.

             

            Please guide me in the right direction if possible. Thanks.

            • 3. Re: INSTEAD OF UPDATE trigger fetches the complete view.
              Steven Hawkins Master

              Unfortunately since the view is written over all of the results from the web service call, there isn't a way for us to know how to call it for a specific subset of ids.

              • 4. Re: INSTEAD OF UPDATE trigger fetches the complete view.
                Dharmendra Prasad Newbie


                Yes it makes a lot of sense what you said.
                So, after lot of Googling and reading, I have found something like below:

                 

                Write a procedure which can take parameters from the where clause and makes appropriate REST call to the web service. Now the SELECT queries will work fine if I invoke them directly on the procedure. My UPDATE and INSERT Triggers suffer.

                 

                Putting it here for you to review. Please enlighten me.

                 

                CREATE VIRTUAL PROCEDURE GetContact (id varchar) 
                  RETURNS (type varchar(100), title varchar(100), firstName varchar(100), lastName varchar(100), name varchar(100), emailAddress varchar(100), 
                  accountName varchar(100), currentStatus varchar(100), businessPhone varchar(100), province varchar(100), postalCode varchar(100), country varchar(100), 
                  subscriptionDate varchar(100), updatedAt varchar(100), depth varchar(100), isBounceback varchar(100), isSubscribed varchar(100), salesPerson varchar(100)) 
                  AS
                     BEGIN
                      DECLARE string url; 
                      DECLARE string rootElement;
                      IF (id > 0)
                      BEGIN
                      url = CONCAT('https://secure.eloqua.com/API/REST/1.0/data/contact/',id);
                      rootElement = '/holder';
                      END
                      ELSE
                      BEGIN
                      url = 'https://secure.eloqua.com/API/REST/1.0/data/contacts?depth=complete';
                      rootElement = '/holder/elements';
                      END
                  SELECT A.type, A.title, A.firstName, A.lastName, A.name, A.emailAddress, A.accountName, A.currentStatus, A.businessPhone, 
                          A.province, A.postalCode, A.country, A.subscriptionDate, A.updatedAt, A.depth, A.isBounceback, A.isSubscribed, A.salesPerson
                  FROM
                  (EXEC CustomerSource.invokeHttp('GET', null, url, 'TRUE')) AS f, 
                  XMLTABLE('/holder' PASSING JSONTOXML('holder', f.result) 
                  COLUMNS 
                  type string PATH 'type/text()',
                  title string PATH 'title/text()',
                  firstName string PATH 'firstName/text()',
                  lastName string PATH 'type/text()',
                  name string PATH 'name/text()',
                  emailAddress string PATH 'emailAddress/text()',
                  accountName string PATH 'accountName/text()',
                  currentStatus string PATH 'currentStatus/text()',
                  businessPhone string PATH 'businessPhone/text()',
                  province string PATH 'province/text()',
                  postalCode string PATH 'postalCode/text()',
                  country string PATH 'country/text()',
                  subscriptionDate string PATH 'subscriptionDate/text()',
                  updatedAt string PATH 'updatedAt/text()',
                  depth string PATH 'depth/text()', 
                  isBounceback string PATH 'isBounceback/text()', 
                  isSubscribed string PATH 'isSubscribed/text()', 
                  salesPerson string PATH 'salesPerson/text()'
                  ) 
                  AS A;
                  END;
                

                 

                 

                I can use the above procedure like "SELECT * FROM GetContact WHERE id = 5" or "SELECT * FROM GetContact

                 

                Now I do not have any view, hence my INSERT and UPDATE TRIGGERS which were working earlier are not working now. I tried to write the triggers on my GetContact procedure, but the procedure was not treated as a table for triggerAction and it threw a NPE.

                 

                So, I am sure I am missing something. I would really appreciate if you can tell me, if I am heading in the right direction.

                • 5. Re: INSTEAD OF UPDATE trigger fetches the complete view.
                  Steven Hawkins Master

                  > I can use the above procedure like "SELECT * FROM GetContact WHERE id = 5" or "SELECT * FROM GetContact

                   

                  You can use the former, but not the latter.

                   

                  > I tried to write the triggers on my GetContact procedure, but the procedure was not treated as a table for triggerAction and it threw a NPE.

                   

                  Can you provide more context here so that we can provide a better exception?

                   

                  > So, I am sure I am missing something. I would really appreciate if you can tell me, if I am heading in the right direction.

                   

                  You can create a view over this procedure and define a trigger, but it will only work for a single id and the id value will be required for select/update.

                  • 6. Re: INSTEAD OF UPDATE trigger fetches the complete view.
                    Dharmendra Prasad Newbie

                    Thanks.

                     

                    Here is the context:


                    "I am trying to use the Eloqua webservice which provides /contacts and /contact{id} API. The second API has GET, PUT, POST and DELETE methods supported. After all the ways I tried in the beginning (already mentioned in the top of this thread) I landed up writing the Procedural Relational Command which I already posted in the thread above.

                     

                    I wrote a trigger INSTEAD OF INSERT on the procedure, so that I can invoke the POST API when the trigger invokes a VIRTUAL PROCEDURE. The below piece of in SQLParseUtil.java throws a NullPointerException at line number 06. This happened because the schema doesn't have a table with the name of the Procedure and hence the table is null.

                     

                     

                    void createDDLTrigger(MetadataFactory schema, AlterTrigger trigger) {
                      GroupSymbol group = trigger.getTarget();
                    
                      Table table = schema.getSchema().getTable(group.getName());
                      if (trigger.getEvent().equals(Table.TriggerEvent.INSERT)) {
                          table.setInsertPlan(trigger.getDefinition().toString());
                      }
                      else if (trigger.getEvent().equals(Table.TriggerEvent.UPDATE)) {
                          table.setUpdatePlan(trigger.getDefinition().toString());
                      }
                      else if (trigger.getEvent().equals(Table.TriggerEvent.DELETE)) {
                      table.setDeletePlan(trigger.getDefinition().toString());
                      }
                      }
                    


                    ". Please let me know if I am not clear in providing enough context.

                     

                     

                    >You can create a view over this procedure and define a trigger, but it will only work for a single id and the id value will be required for select/update. -

                    I understand that I am free to create a View on the procedure and I have my View ready as below, but how do I pass the id parameter to the procedure?

                    CREATE VIEW ContactView(
                      type varchar(100), title varchar(100), firstName varchar(100), lastName varchar(100), name varchar(100), emailAddress varchar(100), 
                      accountName varchar(100), currentStatus varchar(100), businessPhone varchar(100), province varchar(100), postalCode varchar(100), country varchar(100), 
                      subscriptionDate varchar(100), updatedAt varchar(100), depth varchar(100), isBounceback varchar(100), isSubscribed varchar(100), salesPerson varchar(100)
                      ) 
                            OPTIONS (UPDATABLE TRUE) 
                            AS 
                            SELECT type, title, firstName, lastName, name, emailAddress, accountName, currentStatus, businessPhone, 
                              province, postalCode, country, subscriptionDate, updatedAt, depth, isBounceback, isSubscribed, salesPerson FROM GetContact;
                    
                    • 7. Re: INSTEAD OF UPDATE trigger fetches the complete view.
                      Steven Hawkins Master

                      > Please let me know if I am not clear in providing enough context.

                       

                      No that is clear, and was captured/worked as [TEIID-3551] NPE creating trigger on a view that doesn't exist - JBoss Issue Tracker

                       

                      > I understand that I am free to create a View on the procedure and I have my View ready as below, but how do I pass the id parameter to the procedure?

                       

                      You would need to project a column for the id parameter.  It is implicitly projected when using the procedural relational syntax.

                      • 8. Re: INSTEAD OF UPDATE trigger fetches the complete view.
                        Dharmendra Prasad Newbie

                        Finally, I got that working and all your answers helped me move int he right direction. Thank you Steven.