1 Reply Latest reply on Aug 24, 2017 9:28 AM by Ramesh Reddy

    Multiple delete statements Procedure, Output as XML

    Ruud Fleskens Newbie

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

          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.