Usage of global variables

[kiran tummala]

I am trying to connect to the Sql Server Database using TPS process.  In the process I have copied, it had a list of global variables.

global hdbc henv hstmt

I have tried to echo the variables hdbc, henv and hstmt and it wont print any thing.  Where are these variables declared How do we populate the values for them to be able to connect to the databases.

Please advice.

Topic

[Peter Heggie]

I would avoid using global variables for this purpose, but perhaps I do not understand the purpose.

We execute ODBC calls from tcl procs using a proc dedicated to invoking a specific stored procedure, but which calls a subroutine tcl proc to perform the standard ODBC environment setup calls. This subroutine proc passes back the henv, hdbc and hstmt values in a list.

The subroutine procs are generic and are called by many parent procs which invoke many different stored procedures, so we would not use global variables.

Code:

proc DOH_IMM_odbc_Get_Mrn {debug mid } {

       set module “DOH_IMM_odbc_Get_Mrn”

       package require odbc

       # call generic ODBC proc that creates a connection and returns a statement handle
       set result [ODBC_Generic_Connection]
       set result_list [split “$result” “|”]
       lassign $result_list henv hdbc hstmt rcode rdesc
       if {$rcode ne 0} {set message “8$rdesc”; return “$message”}

       # get database name for current environment
       set database [tbllookup “SQLwrite_args” “db_name”]

       # Setup SP Call variables
       set SQLCmd “exec $database.dbo.prGetAuditImmunizationMRN ?, ?, ?, ?”

       # Prepare SQL statement for execution
       set result [odbc SQLPrepare $hstmt $SQLCmd SQL_NTS]
       if {$debug > 1} {puts “$module SQLPrepare result: $result SQLCmd: $SQLCmd”}
       if {$result ne “SQL_SUCCESS”} {
         ODBC_Generic_Drop_Free $hstmt $hdbc $henv
         set message “8SQLPrepare ODBC result: $result”; return “$message”
       }

       # Bind stored procedure input parameters to script variables      
       set result [odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 15 0 MID 19 NULL]
       set result [odbc SQLBindParameter $hstmt 2 SQL_PARAM_INPUT_OUTPUT SQL_C_CHAR SQL_VARCHAR 15 0 MRN 19 NULL]
       set result [odbc SQLBindParameter $hstmt 3 SQL_PARAM_INPUT_OUTPUT SQL_C_SLONG SQL_INTEGER 5 0 RCode 5 NULL]
       set result [odbc SQLBindParameter $hstmt 4 SQL_PARAM_INPUT_OUTPUT SQL_C_CHAR SQL_VARCHAR 200 0 RDesc 204 NULL]
       if {$debug > 1} {echo “$module SQLBindParameter results: $result”}

       set MID $mid ; set MRN “” ; set RCode “” ; set RDesc “”
       
       # Execute prepared statement        
       set result [odbc SQLExecute $hstmt]
       if {$debug > 1} {echo “$module SP executed – result: $result $RDesc”}
       if {$result ne “SQL_SUCCESS”} {
         set retcode [odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SQLState NativeError MessageText 511 TextLength]
         set result “SQLExecute sqlstate: $SQLState error: $NativeError text: $MessageText”
         ODBC_Generic_Drop_Free $hstmt $hdbc $henv
         set message “8SQLPrepare ODBC result: $result”; return “$message”
       } else {
         if {$debug > 1} {echo “$module query successful”}
       }

       # clean up SQL and exit tcl
       ODBC_Generic_Drop_Free $hstmt $hdbc $henv

       if {$debug > 1} {echo “$module end processing” }    
       return “0$MRN”
}

You can see the two generic routines – ODBC_Generic_Connection and ODBC_Generic_Drop_Free – are invoked to perform most of the ODBC work. The Connection proc will pass back a list containing the ODBC variables needed by the other ODBC statements.

So perhaps this is a different style than what you are using; we reuse the two generic procs, so we cannot use global variables.

Peter Heggie
PeterHeggie@crouse.org

[kiran tummala]

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 }

   }

}

[Author]

Replies