Usage of global variables

Peter,

I am new to Cloverleaf and am new to TPS processes too.  Here is the script, I am trying to test.  Can you please go through the script and let me know, if I am making a mistake or where it would need correction.

######################################################################################

# Name: tpsCheckDuplicateControlIds

# Purpose: Reads a control ID and checks for duplicate messages

# UPoC type: Read TPS

# Args:  tps keyedlist containing the following keys:

#        MODE: time (“start” or “time”)

#        MSGID: message handle

#        ARGS: user-supplied arguments: NONE

#

# Notes:

#

---

#        Connects to Oracle server database – using Connect3.7 ODBC Driver

#        Reads rows based on the SQL statement and generates a file.

#

######################################################################################

proc tpsCheckDuplicateControlIds {args} {

  global hdbc henv hstmt hstmt2 usr pass dsn DBTABLE

  #=====================================================================

  #                     GLOBALS

  #                    

---

  # henv       DBC environment

  # hdbc       ODBC connection handle

  # hstmt      ODBC statement handle

  # dsn        ODBC Data Set name

  # usr        Database username

  # pass       Database password

  # DBTABLE    Table to read

  #

  #=====================================================================

  keylget args MODE mode

  switch -exact — $mode {

     start {

          echo In the start

return “”

     }

     run {

  echo In the run

      # the following usernames and passwords may not be necessary.  *KIRAN*

           set usr userName

           set pass Password

           set dsn CheckDupMessages  

         

           # Initialize the ODBC call level interface

           package require odbc

           # Initiliaze CSV Parser package

           

       

           echo ”

---

> Start of tpsCheckDuplicateControlIds  <

---

“

       

            keylget args MSGID mh

            set msg [msgget $mh]

            set segid MSH

            set fieldId 9

            # get the field separator from the hl7 message

            set fieldSeparator [crange $msg 3 3]

            set segment [getHL7Segment $msg $segid]

            set msgContrId [getHL7Field $segment $fieldId $fieldSeparator]

echo

---

> Print Message Control ID: $msgContrId

           

   # Initialize ODBC environment

   echo

---

> Initialize ODBC status = [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]

 # Set the most current ODBC version (3.0)

   echo

---

> Set current ODBC version status = [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]

   # Connect to SQL Server

   set err [ODBC_gm_NameSpace::odbc_connect]

   echo ”

---

> Connect Status = $err”

 echo $err

   if [cequal $err SQL_ERROR] {

           # Failed to connect – print error message

           # Bail out and will try again at next interval

           echo ”

---

> SQL Error: Failed to Connect To Database”

           #set err [odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 711 TextLength]

           ODBC_gm_NameSpace::odbc_error_email “Failed to Connect To Database”

     set returnList “{CONTINUE $mh}”

     return $returnList

   }

                                     

                                 

       # set ODBC statement handle

       set err [ODBC_gm_NameSpace::StmtHandle]

                echo After the First IF block

       if {![cequal $err “SQL_SUCCESS”]} {

      # Terminate connection/statement handles

      ODBC_gm_NameSpace::odbc_disconnect hdbc

ODBC_gm_NameSpace::odbc_error_email “Not Connected to the Database”

               set returnList “{CONTINUE $mh}”

         return $returnList

       }

               # Call sp_insert_BrowseTime, pass value of each csvField

               set sqlStmt “exec dbo.CheckDupMessage ‘$msgContrId'”

               # Execute

               set rVal [odbc SQLExecDirect $hstmt $sqlStmt SQL_NTS]

               echo ”

---

> SQLExecDirect: $rVal <

---

“

               

       # Terminate connection/statement handles

       ODBC_gm_NameSpace::odbc_disconnect hstmt

       ODBC_gm_NameSpace::odbc_disconnect hdbc

               if [cequal $rVal “1”] {

      # Insert failed.

      # Bail out and will try again at next read interval

      echo ”

---

> SQL Error: Failed to Insert Record”

      #set err [odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 711 TextLength]

                    #ODBC_gm_NameSpace::odbc_error_email “Failed to Insert Record”

      echo

---

> Error Message $MessageText

               set returnList “{CONTINUE $mh}”

         return $returnList

         }

         

           

    set returnList “{CONTINUE $mh}”

           

          echo ”

---

> tpsCheckDuplicateControlIds “

           

          return $returnList

     

         

     }

     shutdown {

 # Terminate connection/statement handles

           ODBC_gm_NameSpace::odbc_disconnect hdbc

     }

     default {}

  }

}

#===================================

# set up namespace for subfunctions

#===================================

namespace eval ODBC_gm_NameSpace {

 

 

  #=====================================================================

  # Name: odbc_connect

  # Purpose: connects to DB

  # Return: SQL_SUCCESS || SQL_ERROR

  #=====================================================================

  proc odbc_connect {} {

     global hstmt hdbc henv dsn usr pass

     set timeoutC 5

     # Clean any old ones

     catch {odbc SQLDisconnect $hdbc}

     catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}

     # Allocate connection handle

     set err [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]

     #echo odbc_connect: SQLAllocHandle = $err      

     #echo ”

---

> odbc_connect at [clock format [clock seconds] -format “%m/%d/%y %H:%M:%S”] <

---

“

      # Set connection timeout

      set err [odbc SQLSetConnectAttr $hdbc SQL_ATTR_LOGIN_TIMEOUT $timeoutC 5]

      #echo odbc_connect: SQLSetConnectAttr = $err      

     # Make a connection

     #set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]

     set catchVal [catch {odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS}]

     catch {odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS}

     #echo catchVal = $catchVal

     if {$catchVal == 1} {

      set err SQL_ERROR

     }

     # If connection failed… will attempt to connect 3 times (5sec sleep between each retry)

     set retries 3

     

     set sleep_int 5

     while {$retries && [cequal $err SQL_ERROR]} {

         sleep $sleep_int

         # Make a connection

         #set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]

         set catchVal [catch {odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS}]

      catch {odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS}

  if {$catchVal == 1} {

         set err SQL_ERROR

         }

         incr retries -1

     }

     return $err

  }

 

 

   #=====================================================================

  # Name: odbc_disconnect

  # Purpose: connects to DB

  # Arguments: Requires handle type, if called with hstmt terminates

  #            only statement handle and if called with hdbc terminates

  #            both connection and statement handle

  # Return: SQL_SUCCESS || SQL_ERROR

  #=====================================================================

  proc odbc_disconnect {type} {

      set hstmt $type

      if ![cequal $type “hdbc”] {

    # Terminate statement handle

        catch {odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt}

      }

      # Terminate connection/statement handle

      catch {odbc SQLDisconnect $hdbc}

      catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}

      catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}

  }

 

  #=====================================================================

  # Name: StmtHandle

  # Purpose: Statement Handle to process all SQL statements

  # Return: SQL_SUCCESS || SQL_ERROR

  #=====================================================================

  proc StmtHandle {} {

     global hdbc hstmt

     # Cleanup any old handles

     catch {odbc SQLFreeStmt $hstmt SQL_DROP}

     set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]

     #echo StmtHandle: SQLAllocHandle = $err

     

     if { ($err == “SQL_ERROR”) || ($err == “SQL_ERROR_WITH_INFO”) } {

      set err [odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 1024 TextLength]

    echo SqlState = $SqlState NativeError = $NativeError MessageText = $MessageText

         return SQL_ERROR

     }

     

     # Check, if we are still connected….

     set err [odbc SQLGetInfo $hdbc SQL_DATABASE_NAME dbname 100 cnt]

     #echo StmtHandle: SQLGetInfo = $err    

     

     if { ($err == “SQL_ERROR”) || ($err == “SQL_ERROR_WITH_INFO”) } {

      set err [odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 1024 TextLength]

    echo SqlState = $SqlState NativeError = $NativeError MessageText = $MessageText

         return SQL_ERROR

     }

     

     # If not SQL_SUCCESS…make a connection to DB

     if {![cequal $err SQL_SUCCESS]} {

         set err [odbc_connect]

echo return odbc_connect $err

         if {![cequal $err “”]} {

             return $err

             echo return error $err

         }

     }

     # Just in case…cleanup any old handles

     catch {odbc SQLFreeStmt $hstmt SQL_DROP}

     set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]

     if {[cequal $err SQL_ERROR]} {return $err}

     return $err

  }

 

  #=====================================================================

  # Name: odbc_error_email

  # Purpose: Sends out email.

  # Return:

  #=====================================================================

## Add the email code here.

  proc odbc_error_email {err} {

         set errormsg $err

set date [clock format [clock seconds]]

  set subject “devcb1: MU2  to SCM SQL Interface Error”

          set MAIL /usr/sbin/sendmail

          #set fh [open “| $MAIL inteng10@hoaghospital.org” w+]

          set fh [open “| $MAIL ifteam-t@hoaghospital.org” w+]  

          puts $fh “Mime-Version: 1.0”

          puts $fh “Content-type: text/html; charset=”iso-8859-1″”

          puts $fh “From: Integratiion@hoag.org”

          puts $fh “To: Integration@hoag.org”

          puts $fh “Subject: $subjectn”

          puts $fh “nnn”

          puts $fh “n”

          puts $fh “

“

          puts $fh “

“

          puts $fh “

“

          #puts $fh “

Datestamp of Incident:		$date
Environment:		TESTING

“

          #puts $fh “

“

          #puts $fh “

	IBFullPath

“

puts $fh “”

flush $fh

catch { close $fh }

   }

}