Application example

This shows an example TPS script using ODBC to modify data in the database through the system. The code shows two methods of inserting data into tables.

This procedure is configured in the UPoC Protocol Properties dialog box's Read TPS field.

The thread wakes up periodically (the interval in seconds is set in Read Interval). Then, it queries a database table called PATIENTS that is being used to place messages into the system.

This procedure:

  • Reads all of the rows in the PATIENTS table.
  • Generates a message for each row that is retrieved.
  • Translates a numeric field in each retrieved row to a string value by performing a lookup in a second table called DOCTORS.
  • Formats a system message with the queried information and passes a message into the system.
  • Deletes the row from the database.

For this example, start mode code populates the PATIENTS and DOCTORS tables with sample data.

##################################
# Name: odbc1
# Purpose: <description>
# UPoC type: tps
# Args: tps keyedlist containing the following keys:
#            MODE  run mode ("start", "run", "time", or "test")
#            MSGID  message handle
#            ARGS   user-supplied arguments:
#                         <describe user-supplied args here>
#
# Returns: tps disposition list:
#                         <describe dispositions used here>
#
proc odbc1 { args } {
  keylget args MODE mode;
# Fetch mode
  switch -exact -- $mode {
     start {
# Perform special init functions
# A Microsoft SQL Server database user on pilsner named "med_adm" 
# has been created.  His database password is "aspirin".  Database
# "masterpatient" has also been created.  The med_adm user has been 
# given all necessary permissions on "masterpatient".
# Using the following hcitcl script we create a couple of tables in 
# the masterpatient database (named DOCTORS and PATIENTS) and 
# populate them.
# initialize the ODBC call level interface

package require odbc
     odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv

# application is ODBC 3 
     odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0
# allocate a connection handle
     odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc

# connect to the data source: data source name: MSSQLServer
# user name: med_adm  password: aspirin
     odbc SQLConnect $hdbc MSSQLServer SQL_NTS med_adm SQL_NTS \
        aspirin SQL_NTS

# allocate a statement handle
     odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt

# drop the DOCTORS table, if it exists
# the call will return SQL_ERROR if it fails, but no worries
      set drop "DROP TABLE DOCTORS"
      odbc SQLExecDirect $hstmt $drop SQL_NTS

# recreate the table
      set create "CREATE TABLE DOCTORS (ID INT, NAME VARCHAR(32))"
      odbc SQLExecDirect $hstmt $create SQL_NTS

# prepare an insert statement with 2 parameter markers
      set insert "INSERT INTO DOCTORS VALUES (?, ?)"
      odbc SQLPrepare $hstmt $insert SQL_NTS

# bind tcl variables to these two parameters
      odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT SQL_C_SLONG \
      SQL_INTEGER 0 0 id 0 NULL
      odbc SQLBindParameter $hstmt 2 SQL_PARAM_INPUT SQL_C_CHAR \
      SQL_CHAR 32 0 name 32 NULL

# insert several rows into the database,
# repeatedly executing the prepared statement
      set id 100; set name "Frankenstein"; odbc SQLExecute $hstmt
      set id 101; set name "Kildare"; odbc SQLExecute $hstmt
      set id 102; set name "Jekyll"; odbc SQLExecute $hstmt
      set id 103; set name "Kevorkian"; odbc SQLExecute $hstmt
      set id 104; set name "Spock"; odbc SQLExecute $hstmt
      set id 105; set name "Watson"; odbc SQLExecute $hstmt
      set id 106; set name "Ruth"; odbc SQLExecute $hstmt
      unset id name

# drop another existing table
      set drop "DROP TABLE PATIENTS"
      odbc SQLExecDirect $hstmt $drop SQL_NTS

# recreate the table
      set create "CREATE TABLE PATIENTS (ID INT, NAME \
          VARCHAR(32),DRID INT)"
      odbc SQLExecDirect $hstmt $create SQL_NTS

# prepare an insert statement with 3 parameter markers
      set insert "INSERT INTO PATIENTS VALUES (?, ?, ?)"
      odbc SQLPrepare $hstmt $insert SQL_NTS

# we will now show a second method of inserting values
# into a table which only requires a single SQLExecute
# set up 2 Tcl arrays with the input values for the insert
      set id(0) 600; set name(0) "Mantle"; set drid(0) 106;
      set id(1) 601; set name(1) "Dimaggio"; set drid(1) 105;
      set id(2) 602; set name(2) "Ruth"; set drid(2) 104;
      set id(3) 603; set name(3) "Gehrig"; set drid(3) 103;
      set id(4) 604; set name(4) "Stengel"; set drid(4) 102;
      set id(5) 605; set name(5) "Berra"; set drid(5) 101;
      set id(6) 606; set name(6) "Jackson"; set drid(6) 100;

# set things up for multiple parameter values
      odbc SQLSetStmtAttr $hstmt SQL_ATTR_PARAMSET_SIZE 7 0

# perform the bulk insert; 7 rows are inserted with one ODBC call
# the parameter bindings are still in effect
      odbc SQLBindParameter $hstmt 3 SQL_PARAM_INPUT SQL_C_SLONG \
          SQL_INTEGER 0 0 drid 0 NULL
      odbc SQLExecute $hstmt
      unset id name drid
      odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt
      odbc SQLDisconnect $hdbc
      odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
      odbc SQLFreeHandle SQL_HANDLE_ENV $henv
      return
    }

    run {
      # "run" mode always has a MSGID; fetch and process it
    }

    time {
      # Timer-based processing

      odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv
      odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0
# allocate a connection handle
      odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc
      odbc SQLConnect $hdbc MSSQLServer SQL_NTS med_adm \
          SQL_NTS aspirin SQL_NTS
      odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt

# the following code is for the case where incoming messages are 
# being deposited into the PATIENT database which is being 
# periodically queried by Tcl code in a UPOC driver

# a result set cursor defaults to read-only. the following enables #locking for
# safe updates and deletes
      odbc SQLSetStmtAttr $hstmt SQL_ATTR_CONCURRENCY \
          SQL_CONCUR_LOCK 0

# the "FOR UPDATE" clause permits the use of a named cursor
# for positioned result set operations
      set select "SELECT * FROM PATIENTS FOR UPDATE"
      odbc SQLExecDirect $hstmt $select SQL_NTS

# we now have a result set and a default cursor name
# we could name our own cursor with SQLSetCursorName
# but we just use the default
      odbc SQLGetCursorName $hstmt cursorName 20 pcbCursor

# we need a second statement handle to do a positioned delete
# to get rid of the record in the database after we have read it.
      odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt2

# prepare the positioned delete using the default cursor name
# returned from the SQLGetCursorName query.
      set delete "DELETE FROM PATIENTS WHERE CURRENT OF $cursorName"
      odbc SQLPrepare $hstmt2 $delete SQL_NTS

# bind the two columns in the result set from the previous 
# "select *"
      odbc SQLBindCol $hstmt 1 SQL_C_CHAR id 10 pcbValue1
      odbc SQLBindCol $hstmt 2 SQL_C_CHAR name 32 pcbValue2
      odbc SQLBindCol $hstmt 3 SQL_C_CHAR drid 10 pcbValue3

# we need a third statement handle to look up the name of the 
# doctor assigned to the patient
      odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt3
      odbc SQLBindCol $hstmt3 1 SQL_C_CHAR drname 32 pcbValueDr

# now loop over the result set fetching out each row’s values and 
# then deleting the row from the PATIENTS table in the database
      set dispList {}
      set rVal [odbc SQLFetch $hstmt]
      while {$rVal == "SQL_SUCCESS" || $rVal == \
          "SQL_SUCCESS_WITH_INFO"} {
      set select "SELECT NAME FROM DOCTORS WHERE ID = $drid"
      odbc SQLExecDirect $hstmt3 $select SQL_NTS
      odbc SQLFetch $hstmt3
      odbc SQLFreeStmt $hstmt3 SQL_DROP
# positioned delete
      set rVal2 [odbc SQLExecute $hstmt2]
      echo "return code from delete:$rVal2"
      set mh [msgcreate "patient identifier: $id patient name: \
          $name doctor: $drname"]
        lappend dispList "CONTINUE $mh"
        set rVal [odbc SQLFetch $hstmt]
        }

# now we’ve read and deleted all of the records
      odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt
      odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt2
      odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt3
      odbc SQLDisconnect $hdbc
      odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
      odbc SQLFreeHandle SQL_HANDLE_ENV $henv
      return $dispList
      }
     default {
      error "Unknown mode ’$mode’ in odbc1"
      }
    }
  }