-- $Header: whdev/2.0/owb/shiphome/owb/reposasst/upg/bug_7154050.sql /main/1 2010/05/29 08:35:27 qiawu Exp $
-- Update code template for built in db2 and sqlserver task flow
--
-- Note: PLEASE connect as OWBSYS before execute the file.
--
--  Bug 7154050  - The code template for built in db2 and sqlserver taks flow is not updated  
-- 

SET SERVEROUTPUT ON
SET LINESIZE 500
-- ##########################################################################
DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util)
-- ##########################################################################

  SCRIPTID CONSTANT VARCHAR2(40) := 'bug 7154050'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  TRACEON  CONSTANT BOOLEAN      := TRUE; -- Provide function level trace output
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled

  
  
-- **************************************************************************
-- **************************************************************************
--                      Useful functions/procedures
-- **************************************************************************
-- **************************************************************************

-- ==========================================================================
-- put message to output (uses DBMS_OUTPUT)
  PROCEDURE put(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF NOT OUTINITED THEN
      OUTINITED := TRUE;
      DBMS_OUTPUT.ENABLE(200000);
      put('DBMS_OUTPUT Enabled.');
    END IF;
    
    IF (LENGTH(msg) + LENGTH(SCRIPTID) + 2) > 255 THEN
      -- 10.1 database can't handle messages with length > 255
      --DBMS_OUTPUT.put_line('Message length > 255');
      --DBMS_OUTPUT.put_line(SUBSTR(msg, 0, 255));
      -- Break the string up into lines or 255-byte chunks
      DECLARE
        l_msg_line VARCHAR2(255);
        l_eol_index NUMBER;
        l_curr_index NUMBER;
        l_msg_len NUMBER := LENGTH(msg);
        l_loop_index NUMBER := 0;
      BEGIN
        DBMS_OUTPUT.PUT_LINE(SCRIPTID || ':');
        l_eol_index := 1;
        l_curr_index := 1;
        l_loop_index := 0;
        WHILE (l_curr_index < l_msg_len AND l_loop_index < 10) LOOP
          l_eol_index := INSTR(msg, EOL, l_curr_index);
          --DBMS_OUTPUT.put_line('l_eol_index = ' || TO_CHAR(l_eol_index));
          IF (l_eol_index <= 0) OR (l_eol_index - l_curr_index) > 255 THEN
            l_msg_line := SUBSTR(msg, l_curr_index, 255);
            l_curr_index := l_curr_index + 255;
          ELSE 
            l_msg_line := SUBSTR(msg, l_curr_index, l_eol_index - l_curr_index);
            l_curr_index := l_eol_index + 1;
          END IF;
          DBMS_OUTPUT.PUT_LINE(l_msg_line);        
          l_loop_index := l_loop_index + 1;
        END LOOP;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
    ELSE 
      DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg);
    END IF;  

  END;

-- ==========================================================================
-- put message to output controlled by TRACEON variable (uses put)
  PROCEDURE putTrace(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF TRACEON THEN
      put(msg);
    END IF;
  END;  




-- ##########################################################################
-- ##########################################################################
BEGIN --    Outer block - utility functions/variables now defined
-- ##########################################################################
-- ##########################################################################  

  -- =======================================================
  DECLARE -- Inner block for utility function/variable usage
          -- aka 'user' declarations
  -- =======================================================	
	drop_before_template varchar2(500);
        drop_template varchar2(100);
  
  -- =======================================================
  BEGIN -- 
  -- =======================================================	
    ----- update the handler to JYTHON for DROP_BEFORE_CREATE in task flow SQLSERVER_FCT
    put('update the handler to JYTHON for DROP_BEFORE_CREATE in task flow SQLSERVER_FCT');
    update cmptask_r set handler='JYTHON' where elementid in (
        select distinct k.elementid from cmptaskflow_r w, cmptask_r k, cmptaskflowinstalledmodule_r e, cmpwbproject_r t 
        where t.name='PUBLIC_PROJECT' and t.elementid=e.owningproject and e.name='BUILT_IN_CT' and e.elementid=w.owninginstalledmodule 
        and w.name ='SQLSERVER_FCT' and w.elementid=k.firstclassobject and k.name='DROP_BEFORE_CREATE' );
    
    ----- update the template for DROP_BEFORE_CREATE in task flow SQLSERVER_FCT
    put('update the template for DROP_BEFORE_CREATE in task flow SQLSERVER_FCT');
    
    drop_before_template := 'import java.sql as sql
    
ConTrg = snpRef.getJDBCConnection("TGT")
stmtTrg = ConTrg.createStatement()   
readTrg =  stmtTrg.executeQuery("SELECT * FROM sysobjects objT INNER JOIN sysusers userT ON objT.uid=userT.uid WHERE objT.type=''FN'' AND objT.name=''<%=snpRef.getFunction()%>'' AND userT.name=''<%=snpRef.getInfo("DEST_WORK_SCHEMA")%>''")
if readTrg.next():
   stmtTrg.executeUpdate("drop function <%=snpRef.getFunction()%>")';
       
    update cmptaskimplementation_r set main=drop_before_template where elementid in (
        select distinct n.elementid from cmptask_r k, cmptaskimplementation_r n, cmptaskflow_r w, cmptaskflowinstalledmodule_r e, cmpwbproject_r t 
        where t.name='PUBLIC_PROJECT' and t.elementid=e.owningproject and e.name='BUILT_IN_CT' and e.elementid=w.owninginstalledmodule
        and w.name='SQLSERVER_FCT' and w.elementid=k.firstclassobject and k.name='DROP_BEFORE_CREATE' and k.elementid=n.referringtask );
    
    -------- update the handler to JYTHON for DROP_BEFORE_CREATE in task flow DB2_FCT
    put('update the handler to JYTHON for DROP_BEFORE_CREATE in task flow DB2_FCT');
    update cmptask_r set handler='JYTHON' where elementid in (
        select distinct k.elementid from cmptaskflow_r w, cmptask_r k, cmptaskflowinstalledmodule_r e, cmpwbproject_r t 
        where t.name='PUBLIC_PROJECT' and t.elementid=e.owningproject and e.name='BUILT_IN_CT' and e.elementid=w.owninginstalledmodule
        and w.name ='DB2_FCT' and w.elementid=k.firstclassobject and k.name='DROP_BEFORE_CREATE' );
    
    ----- update the template for DROP_BEFORE_CREATE in task flow DB2_FCT
    put('update the template for DROP_BEFORE_CREATE in task flow DB2_FCT');
    
    drop_before_template := 'import java.sql as sql
    
ConTrg = snpRef.getJDBCConnection("TGT")
stmtTrg = ConTrg.createStatement()    
readTrg =  stmtTrg.executeQuery("select * from syscat.functions where funcname=''<%=snpRef.getFunction()%>''")
if readTrg.next():
   stmtTrg.executeUpdate("drop specific function <%=snpRef.getFunctionPropertyWithSpecific()%>")';
       
    update cmptaskimplementation_r set main=drop_before_template where elementid in (
        select distinct n.elementid from cmptask_r k, cmptaskimplementation_r n, cmptaskflow_r w, cmptaskflowinstalledmodule_r e, cmpwbproject_r t 
        where t.name='PUBLIC_PROJECT' and t.elementid=e.owningproject and e.name='BUILT_IN_CT' and e.elementid=w.owninginstalledmodule
        and w.name='DB2_FCT' and w.elementid=k.firstclassobject and k.name='DROP_BEFORE_CREATE' and k.elementid=n.referringtask );
    
    ---- update the template for DROP_FUNCTION in task flow DB2_FCT
    put('update the template for DROP_FUNCTION in task flow DB2_FCT');
    
    drop_template := 'DROP SPECIFIC FUNCTION <%=snpRef.getFunctionPropertyWithSpecific()%>';
    
    update cmptaskimplementation_r set main=drop_template where elementid in (
        select distinct n.elementid from cmptask_r k, cmptaskimplementation_r n, cmptaskflow_r w, cmptaskflowinstalledmodule_r e, cmpwbproject_r t 
        where t.name='PUBLIC_PROJECT' and t.elementid=e.owningproject and e.name='BUILT_IN_CT' and e.elementid=w.owninginstalledmodule
        and w.name='DB2_FCT' and w.elementid=k.firstclassobject and k.name='DROP_FUNCTION' and k.elementid=n.referringtask );

    
    
  -- =======================================================
  END; -- Inner block for utility function/variable usage
  -- =======================================================    
  
-- ##########################################################################
END; -- Outer block for utility functions/variables
-- ##########################################################################
/