
CREATE OR REPLACE Function utility.LOG_MSG (
	IN p_log_yn CHAR DEFAULT 'N', 
	IN p_component text DEFAULT null, 
	IN p_function text DEFAULT null, 
	IN p_note  text DEFAULT null, 
	IN p_sql text DEFAULT null)
RETURNS integer AS $$
-- Write messages to the audit table and write out a message to DBMS_OUTPUT
DECLARE 
   v_time TIMESTAMP;
   v_msg  varchar(4000);
   v_tab  varchar(50) := 'UTILITY.BPC_AUDIT';
   v_sql  varchar(4000);
   v_auditlevel char(3) := 'LOG';
BEGIN
   if (UPPER(p_log_yn) = 'Y') then
      v_time := now();
      
      INSERT INTO UTILITY.BPC_AUDIT (COMPONENT, ACTIVITY, AUDIT_LEVEL, AUDIT_TIME, NOTE, SQL)
         VALUES (p_component, p_function, v_auditlevel, v_time, p_note, p_sql);

      -- NOTE: If we can get dynamically built queries to execute, then we can add a v_tabname
      -- parameter to the function call, allowing users to distribute their log and error 
      -- messages to specific tables (all w/ the same attributes). This would make it easier
      -- for individuals to debug their code, since they could truncate their own log tables.
      -- If we can get this to work, then we can replace the INSERT statement above,
      -- both here and in function LOG_ERROR.
      v_sql := 'INSERT INTO ' || v_tab || ' (COMPONENT, ACTIVITY, AUDIT_LEVEL, AUDIT_TIME, NOTE'
	|| ', SQL) VALUES ('
	|| CHR(39) || p_component || CHR(39) || ','
	|| CHR(39) || p_function || CHR(39) || ','
	|| CHR(39) || v_auditlevel || CHR(39) || ','
	|| CHR(39) || v_time || CHR(39) || ','
	|| CHR(39) || p_note || CHR(39) || ','
	|| CHR(39) || p_sql || CHR(39) || ')';
      --EXECUTE IMMEDIATE v_sql;
      RAISE INFO '%', v_sql;

      -- Try EXECUTE IMMEDIATE by itself
      -- EXEC SQL EXECUTE IMMEDIATE :v_sql;		-- by itself, I get syntax error on EXEC
      
      -- EXECUTE IMMEDIATE v_sql;		

      -- Try simple PREPARED STMT	
      /*		
      v_sql := "INSERT INTO " || v_tab 
	|| " (COMPONENT, ACTIVITY, AUDIT_LEVEL, AUDIT_TIME, NOTE, SQL)"
	|| " VALUES (?, ?, ?, ?, ?, ?)";
      EXEC SQL EXECUTE :v_sql USING p_component, p_function, v_auditlevel, v_time, p_note, p_sql;
      */


      -- Another PREPARED STMT attempt
      PREPARE myinsert7 (text, text, text, timestamp, text, text) AS
         INSERT INTO UTILITY.BPC_AUDIT (COMPONENT, ACTIVITY, AUDIT_LEVEL, NOTE, SQL)
         VALUES ($1, $2, $3, $4, $5, $6);
      EXECUTE myinsert7 (p_component, p_function, v_auditlevel, v_time, p_note, p_sql);
      EXECUTE DEALLOCATE myinsert7;


      /*	
      -- Try a PREPARED STMT
      EXEC SQL BEGIN DECLARE SECTION;
      v_sql := "INSERT INTO " || v_tab 
	|| " (COMPONENT, ACTIVITY, AUDIT_LEVEL, AUDIT_TIME, NOTE, SQL)"
	|| " VALUES (?, ?, ?, ?, ?, ?)";
      EXEC SQL END DECLARE SECTION;
      EXEC SQL PREPARE mystmt FROM :v_sql;
      EXEC SQL EXECUTE IMMEDIATE :mystmt USING p_component, p_function, v_auditlevel, v_time, p_note, p_sql;
      EXEC SQL DEALLOCATE PREPARE mystmt; 
      */
      	
      -- Also write a msg to DBMS_OUTPUT
      v_msg := p_component || ': ' || p_note || CHR(10) || '==> SQL: ' || p_sql;
      RAISE INFO '%', v_msg;
   end if;
   RETURN 0;
END;
$$ LANGUAGE plpgsql;

-------------------------------------------------------------

CREATE OR REPLACE Function utility.LOG_ERROR (
	IN p_log_yn CHAR DEFAULT 'N',
	IN p_component text DEFAULT null, 
	IN p_function text DEFAULT null, 
	IN p_note text DEFAULT null, 
	IN p_sql text DEFAULT null)
RETURNS integer AS $$
-- Write error codes and messages to the audit table 
-- and write out a message to DBMS_OUTPUT
DECLARE 
   v_time      TIMESTAMP;
   v_err_msg   VARCHAR(4000) := '';
   v_sqlcode   INTEGER;
   v_sqlerrmc  VARCHAR(100);
   v_sqlstate CHAR(5);
   v_auditlevel char(5) := 'ERROR';
BEGIN
   --IF SQLSTATE = '00000' THEN
	--RAISE NOTICE '%', SQLCODE;
   --END IF;
   if (UPPER(p_log_yn) = 'Y') then
      v_time := now();
      -- NOTE: Thus far, I have not been able to capture the values of SQLCODE,
      -- SQLSTATUS, or SQLERRM.SQLERRMC (all are attributes of the automatically
      -- created SQLCA object.
      --v_sqlerrmc := SUBSTRING(sqlca.sqlerrm.sqlerrmc, 1, 100);
      --v_sqlcode := sqlca.sqlcode;
      --v_sqlstate := sqlca.sqlstate;
      --GET DIAGNOSTICS v_sqlcode = SQLCODE;
      INSERT INTO utility.BPC_AUDIT (
         COMPONENT, ACTIVITY, AUDIT_LEVEL, AUDIT_TIME, NOTE, SQL, 
	 ERR_SQLCODE, ERR_SQLERRMC, ERR_SQLSTATE)
      VALUES (p_component, p_function, v_auditlevel, v_time, p_note, p_sql, 
         v_sqlcode, v_sqlerrmc, v_sqlstate);

      -- Also write a msg to DBMS_OUTPUT
      v_err_msg := 'ERROR in ' || p_component || ': ' || p_note
         || CHR(10) || '==>' || v_sqlcode || ': ' || v_sqlerrmc 
         || CHR(10) || '==> SQL: ' || p_sql;
      RAISE DEBUG '%', v_err_msg;
   end if;
   RETURN 0;
END;
$$ LANGUAGE plpgsql;