Title: Connect to Oracle DB.
Submitter: Patrick Finnegan (other recipes)
Last Updated: 2007/01/30
Version no: 1.0
Category: TclBlend

Description:

Quick connection to Oracle Database.

Source: Text Source

###########################################################
# Connect to Oracle using Type 4 Java driver.
###########################################################

#########################################################  
## 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 - oracleConnect. 
#######################################
proc oracleConnect { serverName databaseName portNumber driverType 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 
   java::import oracle.jdbc.OracleDatabaseMetaData

   # load database driver .
   java::call Class forName oracle.jdbc.OracleDriver 

   # set the connection url.


   append url jdbc:oracle:thin
   append url :
   append url $username
   append url /
   append url $password
   append url "@"
   append url $serverName
   append url :
   append url $portNumber
   append url :
   append url $databaseName

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

   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 ]

   putsLog "Column Count is $columnCount"

   set i 1

   while { $i <= $columnCount } {

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

       incr i 
   }    

   unset i

   putsLog $columnList 

   $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 serverName   xxxx
set databaseName xxxx
set portNumber   1234
set driverType   4
set username     xxxx
set password     xxxx

set sqlQuery "select * from aaaa.bbbb where char(customer) like '287554%'"  

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

The license for this recipe is available here.

Discussion: