5 Replies Latest reply on Oct 20, 2017 8:23 AM by shawkins

    Multiple delete statements Procedure, Output as XML

    rflesken

      Hi all,

       

      I'm looking for a way to create a virtual procedure in teiid which triggers multiple delete statements for 1 input parameter.

      In the output i'd like to have something similar to dbms_output like for example: 1 row deleted, 2 rows deleted etc.

       

      Right now i created a procedure (DeleteCase) with input parameter 'CaseNumber' as String and output xmlResult as XML.

       

      The transformation looks as follows:

       

      BEGIN
      
      delete
      from CDBTables.cdb_product_specifications
      where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber);
      
      delete
      from CDBTables.cdb_g_productdetails
      where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber);
      
      End
      

       

      My Questions:

       

      1) How can i create one output to these statements for 1 procedure run?

      2) How do i transform the output to XML?

       

      Thanks in advance!

      Ruud

        • 1. Re: Multiple delete statements Procedure, Output as XML
          rareddy

          If you are using Designer, add a return to the procedure and have it type as "xml". Then change the procedure like below

           

          BEGIN 
          
          delete 
          from CDBTables.cdb_product_specifications 
          where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber); 
          DECLARE INTEGER product_deleted = VARIABLES.ROWCOUNT
          
          delete 
          from CDBTables.cdb_g_productdetails 
          where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber); 
          DECLARE INTEGER details_deleted = VARIABLES.ROWCOUNT
          
          SELECT XMLELEMENT("deleted",
                    XMLELEMENT("product", product_deleted),
                    XMLELEMENT("details", details_deleted));
          End
          

           

          Note the return is SQLXML object, so viewing the result in tools like SQuirreL will not show up correctly until you convert to string. You can also return the individual updated columns which can be read as resultset.

          • 2. Re: Multiple delete statements Procedure, Output as XML
            rflesken

            Hi Ramesh,

             

            Thanks for your answers, that worked for me.

            I have a follow up question, i can't get it to work using the example. I want to add an exception that catches any SQL errors and shows me some kind of useful error in an XML output.

            I checked out the article on Exception Handling with Teiid  but that didn't help for me yet.. How would i do this? What i want is something like below:

             

             BEGIN   
                  
                delete   
                from CDBTables.cdb_product_specifications   
                where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber);   
                DECLARE INTEGER product_deleted = VARIABLES.ROWCOUNT  
                  
                delete   
                from CDBTables.cdb_g_productdetails   
                where caseid in (select caseid from CDBTables.c
                db_order_request_headers where external_requestid = DeleteCase.CaseNumber);   
                DECLARE INTEGER details_deleted = VARIABLES.ROWCOUNT  
                 
            <exception handler NOK?>
            
              SELECT XMLELEMENT("Error",  
                          XMLELEMENT("Code", SQLSTATE),  
            
            <exception handler OK?>
            
            
            
                SELECT XMLELEMENT("deleted",  
                          XMLELEMENT("product", product_deleted),  
                          XMLELEMENT("details", details_deleted));  
                End  
            
            • 3. Re: Multiple delete statements Procedure, Output as XML
              shawkins

              See Procedure Language · Teiid Documentation

               

              You will want the end of your procedure block to look like:

               

                      SELECT XMLELEMENT("deleted",   
                            XMLELEMENT("product", product_deleted),   
                            XMLELEMENT("details", details_deleted));   
              
                  EXCEPTION e
              
                      SELECT XMLELEMENT("Error",   
                            XMLELEMENT("Code", e.STATE),   
              
                  End  
              
              • 4. Re: Multiple delete statements Procedure, Output as XML
                rflesken

                Hi Steven,

                 

                Will this rollback all statements within the block (there are multiple delete statements on different tables) ?

                • 5. Re: Multiple delete statements Procedure, Output as XML
                  shawkins

                  > Will this rollback all statements within the block (there are multiple delete statements on different tables) ?

                   

                  To utilize a block level transaction you need to use

                   

                  BEGIN ATOMIC

                  ...

                   

                  If there is exception handling for an atomic block and there is an exception the transaction will only be allowed to rollback.  A caveat is that you need all your sources to participate in XA transactions for a rollback to be effective.