@echo off
setlocal
set dbDir=D:\CloudscapeDatabases\yourdb
set schema=yourschema
set sqlFile=D:\scripts\cloudscape\SQL\createWASPerformanceTable.sql
echo ###################################################
echo # Run SQL on %computername%
echo ###################################################
set CLASSPATH=%CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derby.jar
set CLASSPATH=%CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derbyclient.jar
set CLASSPATH=%CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derbynet.jar
set CLASSPATH=%CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derbytools.jar
d:\tclBlendSun\bin\jtclsh.bat D:\scripts\TCL\JACL\cloudscape\runBatchSQL.tcl %dbDir% %schema% %sqlFile%
endlocal
====================================================================
#
# Run SQl.
#
####################################################################
# Patrick Finnegan 28/11/2005. V1.
# This script runs multiple sql statements delimited by ";" specified in the input file.
# Comments lines "--" are excluded.
####################################################################
puts "\n **** executing [info script] **** \n"
# make script drive independent.
set drive [lindex [file split [info script]] 0 ]
puts "\n proclib = $drive/scripts/TCL/proclib"
source $drive/scripts/TCL/proclib/checkFile_proc.tcl
source $drive/scripts/TCL/proclib/smtp_proc.tcl
source $drive/scripts/TCL/proclib/reportHeader_proc.tcl
####################################################################
# Connect to database.
####################################################################
proc connectDB { dbDir } {
puts "\n**********"
puts "connectDB"
puts "**********\n"
global env
global null
# load client driver
java::call Class forName org.apache.derby.jdbc.ClientDriver
append url jdbc:derby
append url ":"
append url "//"
append url $::env(COMPUTERNAME)
append url ":"
append url "1527"
append url "/"
append url $dbDir
puts "\n connection URL is: $url\n"
java::try {
set ConnectionI [ java::call DriverManager getConnection $url ]
} catch {SQLException SQLExceptionI } {
catchSqlException $SQLExceptionI
} catch {TclException e } {
puts "TCl Exception during Create Database: $url"
return -code error
}
java::lock $ConnectionI
return $ConnectionI
}
####################################################################
# parse the sql file.
####################################################################
proc parseSql { sql } {
puts "\n**********"
puts "Parse SQL"
puts "**********\n"
global env
global null
# split sql statements on newline eliminating blank lines.
set sqlList [ lsearch -all -inline -exact -not [ split $sql "\n" ] "" ]
# eliminate comment lines.
set statements [ lsearch -all -inline -not -regexp $sqlList "(?ni)^--" ]
# concat the statement list, delimit by ";", eliminate spaces.
set sqlParsed [ split [ eval concat $statements ] ";" ]
set sqlParsed [ lsearch -all -inline -exact -not $sqlParsed "" ]
puts "sqlParsed = $sqlParsed"
return $sqlParsed
}
####################################################################
# Run the sql statements.
####################################################################
proc runSQL { sql ConnectionI schema } {
puts "\n**********"
puts "Run SQL"
puts "**********\n"
global env
global null
# Get Metadata.
set DatabaseMetaDataI [ $ConnectionI getMetaData ]
if { [ $DatabaseMetaDataI supportsBatchUpdates ] } {
puts "SQL Batching is supported"
} else {
puts "SQL Batching is NOT supported"
return -code error
}
# Disable Auto Commit.
$ConnectionI setAutoCommit false
# create statement object
java::try {
set StatementI [ $ConnectionI createStatement ]
} catch {SQLException SQLExceptionI } {
catchSqlException $SQLExceptionI
} catch {TclException e } {
puts "TCl Exception during Create Statement"
return -code error $e
}
# Set Schema.
java::try {
$StatementI executeUpdate "set schema $schema"
} catch {SQLException SQLExceptionI } {
catchSqlException $SQLExceptionI
} catch {TclException e } {
puts "TCl Exception during Execute Statement"
return -code error $e
}
# Clear the statement object.
java::try {
$StatementI clearBatch
} catch {SQLException SQLExceptionI } {
catchSqlException $SQLExceptionI
} catch {TclException e } {
puts "TCl Exception during Execute Statement"
return -code error $e
}
set x 1
foreach i $sql {
puts "\nStatement $x :\n\n$i\n"
$StatementI addBatch $i
incr x
}
java::try {
$StatementI executeBatch
} catch {BatchUpdateException BatchUpdateExceptionI } {
catchBatchUpdateException $BatchUpdateExceptionI
} catch {SQLException SQLExceptionI } {
catchSqlException $SQLExceptionI
} catch {TclException e } {
puts "TCl Exception during Execute Statement"
return -code error $e
}
java::try {
set WarningsI [ $StatementI getWarnings ]
} catch {SQLException SQLExceptionI } {
catchSqlException $SQLExceptionI
} catch {TclException e } {
puts "TCl Exception during Execute Statement"
return -code error $e
}
if { $WarningsI == $null } {
puts "no SQL warnings "
} else {
puts " SQL warnings: [ $Warnings toString ] "
}
# Commit changes.
$ConnectionI commit
}
####################################################################
# proc - batchUpdateException.
####################################################################
proc catchBatchUpdateException { batchUpdateExceptionI } {
global AdminConfig
global AdminControl
global Help
global null
puts "\n**********"
puts "catchBatchUpdateException"
puts "**********\n"
set sqlCode [ $batchUpdateExceptionI toString ]
set sqlMessage [ $batchUpdateExceptionI getMessage ]
set errorCode [ $batchUpdateExceptionI getErrorCode ]
set sqlState [ $batchUpdateExceptionI getSQLState ]
if { $sqlCode != $null } { lappend msgList "sql code is: \t$sqlCode" }
if { $sqlMessage != $null } { lappend msgList "sql message is: \t$sqlMessage" }
if { $errorCode != $null } { lappend msgList "error code is: \t$errorCode" }
if { $sqlState != $null } { lappend msgList "sql state is: \t$sqlState\n" }
set SQLExceptionI [ $batchUpdateExceptionI getNextException ]
while { $SQLExceptionI != $null } {
set sqlCode [ $SQLExceptionI toString ]
set sqlMessage [ $SQLExceptionI getMessage ]
set errorCode [ $SQLExceptionI getErrorCode ]
set sqlState [ $SQLExceptionI getSQLState ]
set nextException [ $SQLExceptionI getNextException ]
if { $sqlCode != $null } { lappend msgList "sql code is: \t$sqlCode" }
if { $sqlMessage != $null } { lappend msgList "sql message is: \t$sqlMessage " }
if { $errorCode != $null } { lappend msgList "error code is: \t$errorCode" }
if { $nextException != $null } { lappend msgList "next exception is: \t$nextException" }
if { $sqlState != $null } { lappend msgList "sql state is: \t$sqlState" }
set SQLExceptionI [ $SQLExceptionI getNextException ]
}
return -code error $msgList
}
####################################################################
# proc - sqlException.
####################################################################
proc catchSqlException { SQLExceptionI } {
global AdminConfig
global AdminControl
global Help
global null
puts "\n**********"
puts "catchSqlException"
puts "**********\n"
set sqlCode [ $SQLExceptionI toString ]
set sqlMessage [ $SQLExceptionI getMessage ]
set errorCode [ $SQLExceptionI getErrorCode ]
set sqlState [ $SQLExceptionI getSQLState ]
if { $sqlCode != $null } { lappend msgList "sql code is: \t$sqlCode" }
if { $sqlMessage != $null } { lappend msgList "sql message is: \t$sqlMessage" }
if { $errorCode != $null } { lappend msgList "error code is: \t$errorCode" }
if { $sqlState != $null } { lappend msgList "sql state is: \t$sqlState\n" }
while { $SQLExceptionI != $null } {
puts "\nget SQL Exception\n"
set sqlCode [ $SQLExceptionI toString ]
set sqlMessage [ $SQLExceptionI getMessage ]
set errorCode [ $SQLExceptionI getErrorCode ]
set sqlState [ $SQLExceptionI getSQLState ]
if { $sqlCode != $null } { lappend msgList "sql code is: \t$sqlCode" }
if { $sqlMessage != $null } { lappend msgList "sql message is: \t$sqlMessage " }
if { $errorCode != $null } { lappend msgList "error code is: \t$errorCode" }
if { $sqlState != $null } { lappend msgList "sql state is: \t$sqlState" }
set SQLExceptionI [ $SQLExceptionI getNextException ]
}
return -code error $msgList
}
####################################################################
# Main Control.
####################################################################
puts "\n argc = $argc \n"
if {$argc < 3 } {
return -code error "\nerror - not enough arguments supplied.\nSupply db directory, schema and sql file."
}
set dbDir [ lindex $argv 0 ]
set schema [ lindex $argv 1 ]
set sqlFile [ lindex $argv 2 ]
set computerName $::env(COMPUTERNAME)
checkFile $dbDir
checkFile $sqlFile
set sql [ read [ open $sqlFile r ] ]
puts "\nSQL:\n$sql\n"
#call java package
package require java
set null [ java::null ]
# import required classes
java::import java.sql.Connection
java::import java.sql.DriverManager
java::import java.sql.SQLWarning
java::import java.sql.Statement
java::import org.apache.derby.jdbc.ClientDriver
puts "\nimported classes are:\n"
foreach i [java::import] {
puts [ format "%-5s %-50s" " " $i ]
}
if { [ catch { connectDB $dbDir } r ] == 0 } {
set ConnectionI $r
lappend msgList "***** Connected to $dbDir *****"
if { [ catch { runSQL [ parseSql $sql ] $ConnectionI $schema } r ] == 0 } {
lappend msgList "***** SQL run successfully *****."
} else {
lappend msgList "***** SQL ERROR *****."
lappend msgList $r
}
} else {
lappend msgList "\n***** Failed to connect to $dbDir *****.\n"
lappend msgList $r
}
foreach i $msgList {
puts $i
}
