Sample TCL code to connect to Microsoft SQL Database

           ## Get database server name and database name from user supplied argument keyed list ARGS
           keylget args ARGS.DBSERVER dbserver
           keylget args ARGS.DBNAME   dbname
           keylget args ARGS.DBUSER   dbuser
           keylget args ARGS.DBPWD    dbpwd
           keylget args ARGS.DOCTYPE  docType

           ## Get file name from message metadata
           set userdata [msgmetaget $mh USERDATA]
           keylget userdata OBFILENAME pdfFileName

           #puts “$module $pdfFileName”
           regexp -nocase — {[[:alpha:]]+d+_d+_(d+)} $pdfFileName dummy distItemId
           #puts “$module AFTER $distItemId”

           ## ODBC
           set ERR_CODE 0  ;# set good error code.
           # ———- initialize ODBC environment ———————
           # initialize the ODBC call level interface

           package require odbc
           set rVal [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
           # application is ODBC 3
           # err could be SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, or SQL_ERROR
           #

           if { $rVal != “SQL_ERROR” } {
               set rVal [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
           } else {
               puts stderr “$module SQLAllocHandle failed.”
           }

           # allocate a connection handle
           if { $rVal != “SQL_ERROR” } {
               set rVal [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]
           } else {
               puts stderr “$module SQLSetEnvAttr failed.”
           }
           # ————————————————————————–
           # connect to the data source: data source name: ARGS.DBSERVER
           if { $rVal != “SQL_ERROR” } {
               set rVal [odbc SQLConnect $hdbc $dbserver SQL_NTS $dbuser SQL_NTS $dbpwd SQL_NTS]
               if { $rVal == “SQL_ERROR” } {
                   echo “$module ODBCERROR on SQLConnect”
                   odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 511 TextLength
                   puts stderr “$module ”
               }
           }
           # allocate a statement handle
           set rVal [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]

           if { $rVal == “SQL_ERROR” } {
               puts stderr “$module Could not allocate statement handle”
           }

           set getDataSqlStmt “SELECT ${dbname}..acc_report.report_image, ${dbname}..accession_2.accession_no, ${dbname}..patient_mrn_2.med_re
c_no, ${dbname}..patient_visit.visit_number FROM ${dbname}..dist_item INNER JOIN ${dbname}..acc_report ON ${dbname}..dist_item.acc_report_id =
${dbname}..acc_report.id INNER JOIN ${dbname}..accession_2 ON ${dbname}..acc_report.acc_id = ${dbname}..accession_2.id LEFT OUTER JOIN ${dbname
}..patient_mrn_2 ON ${dbname}..accession_2.patient_mrn_id = ${dbname}..patient_mrn_2.id LEFT OUTER JOIN ${dbname}..patient_visit ON ${dbname}..
accession_2.patient_visit_id = ${dbname}..patient_visit.patient_visit_id WHERE ${dbname}..dist_item.dist_item_id = ‘$distItemId'”

           set rVal [odbc SQLExecDirect $hstmt $getDataSqlStmt SQL_NTS]
           if { $rVal == “SQL_ERROR” } {
               odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength
               puts stderr “$module ”
           }
           set rVal [odbc SQLFetch $hstmt]

           ## reset temp variables
           set i 0
           set sql_report_image {}
           set sql_accession_no {}
           set sql_med_rec_no   {}
           set sql_visit_number {}

           while { $rVal == “SQL_SUCCESS” || $rVal == “SQL_SUCCESS_WITH_INFO” } {
               # get size of the BLOB
               set rVal [odbc SQLGetData $hstmt 1 SQL_C_BINARY sql_report_image 0 rptSize]
               if { $rVal == “SQL_ERROR” } {
                   odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength
                   puts stderr “$module ”
               } else {
                   # if there are more than 0 bytes then attempt to process
                   if { $rptSize > 0 } {
                       odbc SQLGetData $hstmt 1 SQL_C_BINARY sql_report_image $rptSize dummy
                       odbc SQLGetData $hstmt 2 SQL_C_CHAR   sql_accession_no 30 dummy
                       odbc SQLGetData $hstmt 3 SQL_C_CHAR   sql_med_rec_no   30 dummy
                       odbc SQLGetData $hstmt 4 SQL_C_CHAR   sql_visit_number 30 dummy

                       #puts “$module ”
                       if { [catch { set rtfImage [inflate $sql_report_image] } catchRetVal] } {
                           puts stderr $catchRetVal
                       }
                   }
               }
               set rVal [odbc SQLFetch $hstmt]
               incr i

           }

           # ——— clean up —————–
           odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt
           odbc SQLDisconnect $hdbc
           odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
           odbc SQLFreeHandle SQL_HANDLE_ENV $henv
           #–