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

Run DDL against a Cloudscape database using batchupdate SQL.

Tcl, 415 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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
@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

}

This script uses the Cloudscape Java API to run DDL against a Cloudscape database using batchupdate SQL.

Requires Tclblend.

Tclblend: http://tcljava.sourceforge.net/docs/website/index.html. Tclblend Windows Build Instructions: http://wiki.tcl.tk/9993

Created by Patrick Finnegan on Wed, 7 Dec 2005 (MIT)
Tcl recipes (162)
Patrick Finnegan's recipes (56)

Required Modules

  • (none specified)

Other Information and Tasks