Welcome, guest | Sign In | My Account | Store | Cart

This script uses pure java type 4 drivers to connect to a remote DB2 database, execute a select query and display the results.

Tcl, 135 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
###########################################################
# Connect to DB2 using Type 4 Java driver.
###########################################################

puts "\nexecuting [info script]\n"

# make script drive independent.

set drive [lindex [file split [info nameofexecutable]] 0 ] 

puts "\nproclib = $drive/scripts/TCL/proclib"

########################################################  
# Source packages. 
########################################################  

package require java

######################################
# Proc - check mq listener. 
######################################
proc db2Connect { serverName databaseName portNumber driverType username password sqlQuery } {

   puts "\ndb2Connect \n"

   # import required classes 
   java::import java.sql.Connection
   java::import java.sql.DriverManager
   java::import java.sql.ResultSet
   java::import java.sql.SQLWarning
   java::import java.sql.Statement
   java::import java.sql.ResultSetMetaData 
   java::import com.ibm.db2.jcc.DB2Driver
   java::import com.ibm.db2.jcc.DB2Driver

   # load db2 driver .
   java::call Class forName com.ibm.db2.jcc.DB2Driver

   # set the connection url.

   append url jdbc:db2://
   append url $serverName
   append url :
   append url $portNumber
   append url /
   append url $databaseName
   append url :
   append url user\=$username
   append url \;
   append url password\=$password
   append url \;

   puts "connection URL is:  $url\n"   
   
   set ConnectionI [ java::call DriverManager getConnection $url ] 

   puts "transaction isolation level is [ $ConnectionI getTransactionIsolation ] \n"

   puts "Create query\n" 

   set opt1 [java::field ResultSet TYPE_SCROLL_INSENSITIVE]
   set opt2 [java::field ResultSet CONCUR_READ_ONLY ]

   set StatementI [ $ConnectionI createStatement $opt1 $opt2 ]
   
   $StatementI execute $sqlQuery   

   set ResultSetI         [ $StatementI getResultSet ]  

   puts "get a list of return columns\n" 

   set ResultSetMetaDataI [ $ResultSetI getMetaData ] 
  
   set columnCount        [ $ResultSetMetaDataI getColumnCount ]

   set i 1

   while { $i <= $columnCount } {

       set columnName [ $ResultSetMetaDataI getColumnName $i ]
      
       lappend columnList $columnName

       incr i 
   }    

   unset i

   puts "loop over the results set and print column name, column value.\n" 

   while { [ $ResultSetI next ] == 1 } {

       foreach i $columnList {

	  puts [ format "%-5s %-30s %-s" " " "$i" "[ $ResultSetI getString $i ]" ]

       } 

       puts [ format "\n%-5s \n" [ string repeat "#" 50] ]

   }    

   puts "Close Connections\n" 

   $ResultSetI   close
   $ConnectionI  close

}
######################################
# Main Control.
######################################

# build tcl classpath

append x $drive/IBM/SQLLIB/java/db2jcc.jar;
append x $drive/IBM/SQLLIB/java/db2jcc_license_cu.jar;
append x $drive/IBM/SQLLIB/java/db2jcc_license_cisuz.jar;

set env(TCL_CLASSPATH) $x

puts "\nTCL_CLASSPATH = [ array get env TCL_CLASSPATH ]\n"

set reportFile   $drive/reports/notify/mqConnect.txt
set reportFileId [ open $reportFile w ] 

set serverName   yourserver
set databaseName yourDatabase
set portNumber   50000
set driverType   4
set username     yourUserid
set password     yourPassword

set sqlQuery "select * from abcd.customer_table where char(customer) like '12345%'"  

db2Connect $serverName $databaseName $portNumber $driverType $username $password $sqlQuery 

Type 4 java drivers allow Tcl Apps to connect to most enterprise databases without the need for Tcl specific ODBC packages.

Created by Patrick Finnegan on Mon, 17 Oct 2005 (MIT)
Tcl recipes (162)
Patrick Finnegan's recipes (56)

Required Modules

  • (none specified)

Other Information and Tasks