TclBlend and JDBC

使用 MonetDB 的 JDBC Driver 進行測試。

參考資料：
[Activetcl] TclBlend - Examples. 

設定 CLASSPATH:
set CLASSPATH=c:\monetdb-jdbc-2.19.jar

#########################################################  
## Source packages. 
#########################################################  
#
package require java

#################################################################
# putsLog with timestamp.
####################################################################
proc putsLog { a } {

    set host [ info host ]

    set compTime [clock format [clock seconds] -format "%Y-%m-%d-%H.%M.%S"]

    puts "\[$host:$compTime\] $a"

}
#######################################
## Proc - jdbcConnect. 
#######################################
proc jdbcConnect { className url username password sqlQuery } {

   putsLog "proc - [info level 0 ]"

   # 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 java.sql.DatabaseMetaData 

   # load database driver .
   java::call Class forName $className

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

   putsLog "transaction isolation level is [ $ConnectionI getTransactionIsolation ]"

   putsLog "#########################################"
   putsLog "### Database connection details"
   putsLog "#########################################"

   # get the database metadata information.
   #Retrieves a DatabaseMetaData object that contains metadata about the database
   #to which this Connection object represents a connection.

   set DatabaseMetaDataI [ $ConnectionI getMetaData ]

   putsLog [ $DatabaseMetaDataI getDatabaseProductName ]
   putsLog [ $DatabaseMetaDataI getDatabaseProductVersion ]
   putsLog "database version [ $DatabaseMetaDataI getDatabaseMajorVersion ]\.[ $DatabaseMetaDataI getDatabaseMinorVersion ]"
   putsLog "driver version   [ $DatabaseMetaDataI getDriverName ] [ $DatabaseMetaDataI getDriverMajorVersion ]\.[ $DatabaseMetaDataI getDriverMinorVersion ]"
   putsLog "jdbc version     [ $DatabaseMetaDataI getJDBCMajorVersion  ]\.[ $DatabaseMetaDataI getJDBCMinorVersion  ]"
   putsLog "connect username [ $DatabaseMetaDataI getUserName ]"
   putsLog "transaction isolation level is [ $ConnectionI getTransactionIsolation ] \n"

   # get a list of table names in database.
   # if there are no tables the results set is empty.  

   set opt1 [java::field ResultSet TYPE_SCROLL_INSENSITIVE]
   set ResultSetI [ $DatabaseMetaDataI getCatalogs ]
   set ResultSetMetaDataI [ $ResultSetI getMetaData ] 
   set columnCount        [ $ResultSetMetaDataI getColumnCount ]

   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.
######################################

putsLog "executing [info script]"

# make script drive independent.

set drive [lindex [file split [info nameofexecutable]] 0 ] 
set reportFile   C:\\reports\\oracleConnect.txt
set reportFileId [ open $reportFile w ] 

set className    {nl.cwi.monetdb.jdbc.MonetDriver}
set url          jdbc:monetdb://localhost:50000/demo   
set username     monetdb
set password     monetdb

set sqlQuery "SELECT id, name FROM tables"  

jdbcConnect $className $url $username $password $sqlQuery

使用 HSQLDB 的 JDBC Driver 進行測試。

設定 CLASSPATH:
set CLASSPATH=c:\hsqldb.jar

#########################################################  
## Source packages. 
#########################################################  
#
package require java

#################################################################
# putsLog with timestamp.
####################################################################
proc putsLog { a } {

    set host [ info host ]

    set compTime [clock format [clock seconds] -format "%Y-%m-%d-%H.%M.%S"]

    puts "\[$host:$compTime\] $a"

}
#######################################
## Proc - jdbcConnect. 
#######################################
proc jdbcConnect { className url username password sqlQuery } {

   putsLog "proc - [info level 0 ]"

   # 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 java.sql.DatabaseMetaData 

   # load database driver .
   java::call Class forName $className

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

   putsLog "transaction isolation level is [ $ConnectionI getTransactionIsolation ]"

   putsLog "#########################################"
   putsLog "### Database connection details"
   putsLog "#########################################"

   # get the database metadata information.
   #Retrieves a DatabaseMetaData object that contains metadata about the database
   #to which this Connection object represents a connection.

   set DatabaseMetaDataI [ $ConnectionI getMetaData ]

   putsLog [ $DatabaseMetaDataI getDatabaseProductName ]
   putsLog [ $DatabaseMetaDataI getDatabaseProductVersion ]
   putsLog "database version [ $DatabaseMetaDataI getDatabaseMajorVersion ]\.[ $DatabaseMetaDataI getDatabaseMinorVersion ]"
   putsLog "driver version   [ $DatabaseMetaDataI getDriverName ] [ $DatabaseMetaDataI getDriverMajorVersion ]\.[ $DatabaseMetaDataI getDriverMinorVersion ]"
   putsLog "jdbc version     [ $DatabaseMetaDataI getJDBCMajorVersion  ]\.[ $DatabaseMetaDataI getJDBCMinorVersion  ]"
   putsLog "connect username [ $DatabaseMetaDataI getUserName ]"
   putsLog "transaction isolation level is [ $ConnectionI getTransactionIsolation ] \n"

   # get a list of table names in database.
   # if there are no tables the results set is empty.  

   set opt1 [java::field ResultSet TYPE_SCROLL_INSENSITIVE]
   set ResultSetI [ $DatabaseMetaDataI getCatalogs ]
   set ResultSetMetaDataI [ $ResultSetI getMetaData ] 
   set columnCount        [ $ResultSetMetaDataI getColumnCount ]

   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.
######################################

putsLog "executing [info script]"

# make script drive independent.

set drive [lindex [file split [info nameofexecutable]] 0 ] 
set reportFile   C:\\reports\\oracleConnect.txt
set reportFileId [ open $reportFile w ] 

set className    {org.hsqldb.jdbc.JDBCDriver}
set url          jdbc:hsqldb:file:testdb 
set username     SA
set password     ""

set sqlQuery "SELECT * FROM INFORMATION_SCHEMA.TABLES"  

jdbcConnect $className $url $username $password $sqlQuery

請參考上一篇文章（再加上上一次 Build TclBlend 的文章），二個都進行 patch 以後，在 8.6.5 看起來還不錯，至少可以正確的執行 MonetDB 與 HSQLDB JDBC driver。