#! /usr/bin/tclsh

# Copyright  c 2001  David Muse
# See the file COPYING for more information.

load /usr/lib/sqlrelay/sqlrelay.so sqlrelay

proc checkUndef {value} {

	switch $value "" {
		puts "success "
	} default {
		puts "failure "
		exit
	}
}

proc checkSuccess {value success} {

	if {$value==$success} {
		puts "success "
	} else {
		puts "failure "
		exit
	}
}

# usage...
if {$argc<5} {
	puts "usage: db2.tcl host port socket user password"
	exit
}

# instantiation
set con [sqlrcon -server [lindex $argv 0] -port [lindex $argv 1] -socket [lindex $argv 2] -user [lindex $argv 3] -password [lindex $argv 4] -retrytime 0 -tries 1]
set cur [$con sqlrcur]

# get database type
puts "IDENTIFY: "
checkSuccess [$con identify] "db2"
puts ""

# ping
puts "PING: "
checkSuccess [$con ping] 1
puts ""

# drop existing table
catch {$cur sendQuery "drop table testtable"}

puts "CREATE TEMPTABLE: "
checkSuccess [$cur sendQuery "create table testtable (testsmallint smallint, testint integer, testbigint bigint, testdecimal decimal(10,2), testreal real, testdouble double, testchar char(40), testvarchar varchar(40), testdate date, testtime time, testtimestamp timestamp)"] 1
puts ""

puts "INSERT: "
checkSuccess [$cur sendQuery "insert into testtable values (1,1,1,1.1,1.1,1.1,'testchar1','testvarchar1','01/01/2001','01:00:00',NULL)"] 1
puts ""

puts "BIND BY POSITION: "
$cur prepareQuery "insert into testtable values (?,?,?,?,?,?,?,?,?,?,NULL)"
checkSuccess [$cur countBindVariables] 10
$cur inputBind "1" 2
$cur inputBind "2" 2
$cur inputBind "3" 2
$cur inputBind "4" 2.2 4 2
$cur inputBind "5" 2.2 4 2
$cur inputBind "6" 2.2 4 2
$cur inputBind "7" "testchar2"
$cur inputBind "8" "testvarchar2"
$cur inputBind "9" "01/01/2002"
$cur inputBind "10" "02:00:00"
checkSuccess [$cur executeQuery] 1
$cur clearBinds 
$cur inputBind "1" 3
$cur inputBind "2" 3
$cur inputBind "3" 3
$cur inputBind "4" 3.3 4 2
$cur inputBind "5" 3.3 4 2
$cur inputBind "6" 3.3 4 2
$cur inputBind "7" "testchar3"
$cur inputBind "8" "testvarchar3"
$cur inputBind "9" "01/01/2003"
$cur inputBind "10" "03:00:00"
checkSuccess [$cur executeQuery] 1
puts ""

puts "ARRAY OF BINDS BY POSITION: "
$cur clearBinds 
$cur inputBinds {{"1" 4} {"2" 4} {"3" 4} {"4" 4.4 4 2} {"5" 4.4 4 2} {"6" 4.4 4 2} {"7" "testchar4"} {"8" "testvarchar4"} {"9" "01/01/2004"} {"10" "04:00:00"}}
checkSuccess [$cur executeQuery] 1
puts ""

puts "INSERT: "
checkSuccess [$cur sendQuery "insert into testtable values (5,5,5,5.5,5.5,5.5,'testchar5','testvarchar5','01/01/2005','05:00:00',NULL)"] 1
checkSuccess [$cur sendQuery "insert into testtable values (6,6,6,6.6,6.6,6.6,'testchar6','testvarchar6','01/01/2006','06:00:00',NULL)"] 1
checkSuccess [$cur sendQuery "insert into testtable values (7,7,7,7.7,7.7,7.7,'testchar7','testvarchar7','01/01/2007','07:00:00',NULL)"] 1
checkSuccess [$cur sendQuery "insert into testtable values (8,8,8,8.8,8.8,8.8,'testchar8','testvarchar8','01/01/2008','08:00:00',NULL)"] 1
puts ""

puts "AFFECTED ROWS: "
checkSuccess [$cur affectedRows] 1
puts ""

puts "STORED PROCEDURE: "
catch {$cur sendQuery "drop procedure testproc"}
checkSuccess [$cur sendQuery "create procedure testproc(in invar int, out outvar int) language sql begin set outvar = invar; end"] 1
$cur prepareQuery "call testproc(?,?)"
$cur inputBind "1" 5
$cur defineOutputBind "2" 10
checkSuccess [$cur executeQuery] 1
checkSuccess [$cur getOutputBind "2"] "5"
checkSuccess [$cur sendQuery "drop procedure testproc"] 1
puts ""

puts "SELECT: "
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
puts ""

puts "COLUMN COUNT: "
checkSuccess [$cur colCount] 11
puts ""

puts "COLUMN NAMES: "
checkSuccess [$cur getColumnName 0] "TESTSMALLINT"
checkSuccess [$cur getColumnName 1] "TESTINT"
checkSuccess [$cur getColumnName 2] "TESTBIGINT"
checkSuccess [$cur getColumnName 3] "TESTDECIMAL"
checkSuccess [$cur getColumnName 4] "TESTREAL"
checkSuccess [$cur getColumnName 5] "TESTDOUBLE"
checkSuccess [$cur getColumnName 6] "TESTCHAR"
checkSuccess [$cur getColumnName 7] "TESTVARCHAR"
checkSuccess [$cur getColumnName 8] "TESTDATE"
checkSuccess [$cur getColumnName 9] "TESTTIME"
checkSuccess [$cur getColumnName 10] "TESTTIMESTAMP"
set cols [$cur getColumnNames]
checkSuccess [lindex $cols 0] "TESTSMALLINT"
checkSuccess [lindex $cols 1] "TESTINT"
checkSuccess [lindex $cols 2] "TESTBIGINT"
checkSuccess [lindex $cols 3] "TESTDECIMAL"
checkSuccess [lindex $cols 4] "TESTREAL"
checkSuccess [lindex $cols 5] "TESTDOUBLE"
checkSuccess [lindex $cols 6] "TESTCHAR"
checkSuccess [lindex $cols 7] "TESTVARCHAR"
checkSuccess [lindex $cols 8] "TESTDATE"
checkSuccess [lindex $cols 9] "TESTTIME"
checkSuccess [lindex $cols 10] "TESTTIMESTAMP"
puts ""

puts "COLUMN TYPES: "
checkSuccess [$cur getColumnTypeByIndex 0] "SMALLINT"
checkSuccess [$cur getColumnTypeByName "TESTSMALLINT"] "SMALLINT"
checkSuccess [$cur getColumnTypeByIndex 1] "INTEGER"
checkSuccess [$cur getColumnTypeByName "TESTINT"] "INTEGER"
checkSuccess [$cur getColumnTypeByIndex 2] "BIGINT"
checkSuccess [$cur getColumnTypeByName "TESTBIGINT"] "BIGINT"
checkSuccess [$cur getColumnTypeByIndex 3] "DECIMAL"
checkSuccess [$cur getColumnTypeByName "TESTDECIMAL"] "DECIMAL"
checkSuccess [$cur getColumnTypeByIndex 4] "REAL"
checkSuccess [$cur getColumnTypeByName "TESTREAL"] "REAL"
checkSuccess [$cur getColumnTypeByIndex 5] "DOUBLE"
checkSuccess [$cur getColumnTypeByName "TESTDOUBLE"] "DOUBLE"
checkSuccess [$cur getColumnTypeByIndex 6] "CHAR"
checkSuccess [$cur getColumnTypeByName "TESTCHAR"] "CHAR"
checkSuccess [$cur getColumnTypeByIndex 7] "VARCHAR"
checkSuccess [$cur getColumnTypeByName "TESTVARCHAR"] "VARCHAR"
checkSuccess [$cur getColumnTypeByIndex 8] "DATE"
checkSuccess [$cur getColumnTypeByName "TESTDATE"] "DATE"
checkSuccess [$cur getColumnTypeByIndex 9] "TIME"
checkSuccess [$cur getColumnTypeByName "TESTTIME"] "TIME"
checkSuccess [$cur getColumnTypeByIndex 10] "TIMESTAMP"
checkSuccess [$cur getColumnTypeByName "TESTTIMESTAMP"] "TIMESTAMP"
puts ""

puts "COLUMN LENGTH: "
checkSuccess [$cur getColumnLengthByIndex 0] 2
checkSuccess [$cur getColumnLengthByName "TESTSMALLINT"] 2
checkSuccess [$cur getColumnLengthByIndex 1] 4
checkSuccess [$cur getColumnLengthByName "TESTINT"] 4
checkSuccess [$cur getColumnLengthByIndex 2] 8
checkSuccess [$cur getColumnLengthByName "TESTBIGINT"] 8
checkSuccess [$cur getColumnLengthByIndex 3] 12
checkSuccess [$cur getColumnLengthByName "TESTDECIMAL"] 12
checkSuccess [$cur getColumnLengthByIndex 4] 4
checkSuccess [$cur getColumnLengthByName "TESTREAL"] 4
checkSuccess [$cur getColumnLengthByIndex 5] 8
checkSuccess [$cur getColumnLengthByName "TESTDOUBLE"] 8
checkSuccess [$cur getColumnLengthByIndex 6] 40
checkSuccess [$cur getColumnLengthByName "TESTCHAR"] 40
checkSuccess [$cur getColumnLengthByIndex 7] 40
checkSuccess [$cur getColumnLengthByName "TESTVARCHAR"] 40
checkSuccess [$cur getColumnLengthByIndex 8] 6
checkSuccess [$cur getColumnLengthByName "TESTDATE"] 6
checkSuccess [$cur getColumnLengthByIndex 9] 6
checkSuccess [$cur getColumnLengthByName "TESTTIME"] 6
checkSuccess [$cur getColumnLengthByIndex 10] 16
checkSuccess [$cur getColumnLengthByName "TESTTIMESTAMP"] 16
puts ""

puts "LONGEST COLUMN: "
checkSuccess [$cur getLongestByIndex 0] 1
checkSuccess [$cur getLongestByName "TESTSMALLINT"] 1
checkSuccess [$cur getLongestByIndex 1] 1
checkSuccess [$cur getLongestByName "TESTINT"] 1
checkSuccess [$cur getLongestByIndex 2] 1
checkSuccess [$cur getLongestByName "TESTBIGINT"] 1
checkSuccess [$cur getLongestByIndex 3] 4
checkSuccess [$cur getLongestByName "TESTDECIMAL"] 4
#checkSuccess [$cur getLongestByIndex 4] 3
#checkSuccess [$cur getLongestByName "TESTREAL"] 3
#checkSuccess [$cur getLongestByIndex 5] 3
#checkSuccess [$cur getLongestByName "TESTDOUBLE"] 3
checkSuccess [$cur getLongestByIndex 6] 40
checkSuccess [$cur getLongestByName "TESTCHAR"] 40
checkSuccess [$cur getLongestByIndex 7] 12
checkSuccess [$cur getLongestByName "TESTVARCHAR"] 12
checkSuccess [$cur getLongestByIndex 8] 10
checkSuccess [$cur getLongestByName "TESTDATE"] 10
checkSuccess [$cur getLongestByIndex 9] 8
checkSuccess [$cur getLongestByName "TESTTIME"] 8
puts ""

puts "ROW COUNT: "
checkSuccess [$cur rowCount] 8
puts ""

puts "TOTAL ROWS: "
checkSuccess [$cur totalRows] 0
puts ""

puts "FIRST ROW INDEX: "
checkSuccess [$cur firstRowIndex] 0
puts ""

puts "END OF RESULT SET: "
checkSuccess [$cur endOfResultSet] 1
puts ""

puts "FIELDS BY INDEX: "
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 0 1] "1"
checkSuccess [$cur getFieldByIndex 0 2] "1"
checkSuccess [$cur getFieldByIndex 0 3] "1.10"
#checkSuccess [$cur getFieldByIndex 0 4] "1.1"
#checkSuccess [$cur getFieldByIndex 0 5] "1.1"
checkSuccess [$cur getFieldByIndex 0 6] "testchar1                               "
checkSuccess [$cur getFieldByIndex 0 7] "testvarchar1"
checkSuccess [$cur getFieldByIndex 0 8] "2001-01-01"
checkSuccess [$cur getFieldByIndex 0 9] "01:00:00"
puts ""
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkSuccess [$cur getFieldByIndex 7 1] "8"
checkSuccess [$cur getFieldByIndex 7 2] "8"
checkSuccess [$cur getFieldByIndex 7 3] "8.80"
#checkSuccess [$cur getFieldByIndex 7 4] "8.8"
#checkSuccess [$cur getFieldByIndex 7 5] "8.8"
checkSuccess [$cur getFieldByIndex 7 6] "testchar8                               "
checkSuccess [$cur getFieldByIndex 7 7] "testvarchar8"
checkSuccess [$cur getFieldByIndex 7 8] "2008-01-01"
checkSuccess [$cur getFieldByIndex 7 9] "08:00:00"
puts ""

puts "FIELD LENGTHS BY INDEX: "
checkSuccess [$cur getFieldLengthByIndex 0 0] 1
checkSuccess [$cur getFieldLengthByIndex 0 1] 1
checkSuccess [$cur getFieldLengthByIndex 0 2] 1
checkSuccess [$cur getFieldLengthByIndex 0 3] 4
#checkSuccess [$cur getFieldLengthByIndex 0 4] 3
#checkSuccess [$cur getFieldLengthByIndex 0 5 3
checkSuccess [$cur getFieldLengthByIndex 0 6] 40
checkSuccess [$cur getFieldLengthByIndex 0 7] 12
checkSuccess [$cur getFieldLengthByIndex 0 8] 10
checkSuccess [$cur getFieldLengthByIndex 0 9] 8
puts ""
checkSuccess [$cur getFieldLengthByIndex 7 0] 1
checkSuccess [$cur getFieldLengthByIndex 7 1] 1
checkSuccess [$cur getFieldLengthByIndex 7 2] 1
checkSuccess [$cur getFieldLengthByIndex 7 3] 4
#checkSuccess [$cur getFieldLengthByIndex 7 4] 3
#checkSuccess [$cur getFieldLengthByIndex 7 5] 3
checkSuccess [$cur getFieldLengthByIndex 7 6] 40
checkSuccess [$cur getFieldLengthByIndex 7 7] 12
checkSuccess [$cur getFieldLengthByIndex 7 8] 10
checkSuccess [$cur getFieldLengthByIndex 7 9] 8
puts ""

puts "FIELDS BY NAME: "
checkSuccess [$cur getFieldByName 0 "testsmallint"] "1"
checkSuccess [$cur getFieldByName 0 "testint"] "1"
checkSuccess [$cur getFieldByName 0 "testbigint"] "1"
checkSuccess [$cur getFieldByName 0 "testdecimal"] "1.10"
#checkSuccess [$cur getFieldByName 0 "testreal"] "1.1"
#checkSuccess [$cur getFieldByName 0 "testdouble"] "1.1"
checkSuccess [$cur getFieldByName 0 "testchar"] "testchar1                               "
checkSuccess [$cur getFieldByName 0 "testvarchar"] "testvarchar1"
checkSuccess [$cur getFieldByName 0 "testdate"] "2001-01-01"
checkSuccess [$cur getFieldByName 0 "testtime"] "01:00:00"
puts ""
checkSuccess [$cur getFieldByName 7 "testsmallint"] "8"
checkSuccess [$cur getFieldByName 7 "testint"] "8"
checkSuccess [$cur getFieldByName 7 "testbigint"] "8"
checkSuccess [$cur getFieldByName 7 "testdecimal"] "8.80"
#checkSuccess [$cur getFieldByName 7 "testreal"] "8.8"
#checkSuccess [$cur getFieldByName 7 "testdouble"] "8.8"
checkSuccess [$cur getFieldByName 7 "testchar"] "testchar8                               "
checkSuccess [$cur getFieldByName 7 "testvarchar"] "testvarchar8"
checkSuccess [$cur getFieldByName 7 "testdate"] "2008-01-01"
checkSuccess [$cur getFieldByName 7 "testtime"] "08:00:00"
puts ""

puts "FIELD LENGTHS BY NAME: "
checkSuccess [$cur getFieldLengthByName 0 "testsmallint"] 1
checkSuccess [$cur getFieldLengthByName 0 "testint"] 1
checkSuccess [$cur getFieldLengthByName 0 "testbigint"] 1
checkSuccess [$cur getFieldLengthByName 0 "testdecimal"] 4
#checkSuccess [$cur getFieldLengthByName 0 "testreal"] 3
#checkSuccess [$cur getFieldLengthByName 0 "testdouble"] 3
checkSuccess [$cur getFieldLengthByName 0 "testchar"] 40
checkSuccess [$cur getFieldLengthByName 0 "testvarchar"] 12
checkSuccess [$cur getFieldLengthByName 0 "testdate"] 10
checkSuccess [$cur getFieldLengthByName 0 "testtime"] 8
puts ""
checkSuccess [$cur getFieldLengthByName 7 "testsmallint"] 1
checkSuccess [$cur getFieldLengthByName 7 "testint"] 1
checkSuccess [$cur getFieldLengthByName 7 "testbigint"] 1
checkSuccess [$cur getFieldLengthByName 7 "testdecimal"] 4
#checkSuccess [$cur getFieldLengthByName 7 "testreal"] 3
#checkSuccess [$cur getFieldLengthByName 7 "testdouble"] 3
checkSuccess [$cur getFieldLengthByName 7 "testchar"] 40
checkSuccess [$cur getFieldLengthByName 7 "testvarchar"] 12
checkSuccess [$cur getFieldLengthByName 7 "testdate"] 10
checkSuccess [$cur getFieldLengthByName 7 "testtime"] 8
puts ""

puts "FIELDS BY ARRAY: "
set fields [$cur getRow 0]
checkSuccess [lindex $fields 0] 1
checkSuccess [lindex $fields 1] 1
checkSuccess [lindex $fields 2] 1
checkSuccess [lindex $fields 3] 1.1
checkSuccess [lindex $fields 4] 1.1
checkSuccess [lindex $fields 5] 1.1
checkSuccess [lindex $fields 6] "testchar1                               "
checkSuccess [lindex $fields 7] "testvarchar1"
checkSuccess [lindex $fields 8] "2001-01-01"
checkSuccess [lindex $fields 9] "01:00:00"
puts ""

puts "FIELD LENGTHS BY ARRAY: "
set fieldlens [$cur getRowLengths 0]
checkSuccess [lindex $fieldlens 0] 1
checkSuccess [lindex $fieldlens 1] 1
checkSuccess [lindex $fieldlens 2] 1
checkSuccess [lindex $fieldlens 3] 4
#checkSuccess [lindex $fieldlens 4] 3
#checkSuccess [lindex $fieldlens 5] 3
checkSuccess [lindex $fieldlens 6] 40
checkSuccess [lindex $fieldlens 7] 12
checkSuccess [lindex $fieldlens 8] 10
checkSuccess [lindex $fieldlens 9] 8
puts ""

puts "INDIVIDUAL SUBSTITUTIONS: "
$cur prepareQuery "values (\$(var1),'\$(var2)','\$(var3)')"
$cur substitution "var1" 1
$cur substitution "var2" "hello"
$cur substitution "var3" 10.5556 6 4
checkSuccess [$cur executeQuery] 1
puts ""

puts "FIELDS: "
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 0 1] "hello"
checkSuccess [$cur getFieldByIndex 0 2] "10.5556"
puts ""

puts "ARRAY SUBSTITUTIONS: "
$cur prepareQuery "values (\$(var1),'\$(var2)','\$(var3)')"
$cur substitutions {{"var1" 1} {"var2" "hello"} {"var3" 10.5556 6 4}}
checkSuccess [$cur executeQuery] 1
puts ""

puts "FIELDS: "
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 0 1] "hello"
checkSuccess [$cur getFieldByIndex 0 2] "10.5556"
puts ""

puts "RESULT SET BUFFER SIZE: "
checkSuccess [$cur getResultSetBufferSize] 0
$cur setResultSetBufferSize 2
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
checkSuccess [$cur getResultSetBufferSize] 2
puts ""
checkSuccess [$cur firstRowIndex] 0
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 2
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
puts ""
checkSuccess [$cur firstRowIndex] 2
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 4
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur firstRowIndex] 6
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 8
checkUndef [$cur getFieldByIndex 8 0]
puts ""
checkSuccess [$cur firstRowIndex] 8
checkSuccess [$cur endOfResultSet] 1
checkSuccess [$cur rowCount] 8
puts ""

puts "DONT GET COLUMN INFO: "
$cur dontGetColumnInfo 
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
checkUndef [$cur getColumnName 0]
checkSuccess [$cur getColumnLengthByIndex 0] 0
checkUndef [$cur getColumnTypeByIndex 0]
$cur getColumnInfo 
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
checkSuccess [$cur getColumnName 0] "TESTSMALLINT"
checkSuccess [$cur getColumnLengthByIndex 0] 2
checkSuccess [$cur getColumnTypeByIndex 0] "SMALLINT"
puts ""

puts "SUSPENDED SESSION: "
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
$cur suspendResultSet 
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
puts ""
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
checkSuccess [$cur getFieldByIndex 3 0] "4"
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
$cur suspendResultSet 
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
puts ""
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
checkSuccess [$cur getFieldByIndex 3 0] "4"
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
$cur suspendResultSet 
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
puts ""
checkSuccess [$cur getFieldByIndex 0 0] "1"
checkSuccess [$cur getFieldByIndex 1 0] "2"
checkSuccess [$cur getFieldByIndex 2 0] "3"
checkSuccess [$cur getFieldByIndex 3 0] "4"
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""

puts "SUSPENDED RESULT SET: "
$cur setResultSetBufferSize 2
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
checkSuccess [$cur getFieldByIndex 2 0] "3"
set id [$cur getResultSetId]
$cur suspendResultSet 
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
checkSuccess [$cur resumeResultSet $id] 1
puts ""
checkSuccess [$cur firstRowIndex] 4
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 6
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur firstRowIndex] 6
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 8
checkUndef [$cur getFieldByIndex 8 0]
puts ""
checkSuccess [$cur firstRowIndex] 8
checkSuccess [$cur endOfResultSet] 1
checkSuccess [$cur rowCount] 8
$cur setResultSetBufferSize 0
puts ""

puts "CACHED RESULT SET: "
$cur cacheToFile "cachefile1"
$cur setCacheTtl 200
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
set filename [$cur getCacheFileName]
checkSuccess $filename "cachefile1"
$cur cacheOff 
checkSuccess [$cur openCachedResultSet $filename] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""

puts "COLUMN COUNT FOR CACHED RESULT SET: "
checkSuccess [$cur colCount] 11
puts ""

puts "COLUMN NAMES FOR CACHED RESULT SET: "
checkSuccess [$cur getColumnName 0] "TESTSMALLINT"
checkSuccess [$cur getColumnName 1] "TESTINT"
checkSuccess [$cur getColumnName 2] "TESTBIGINT"
checkSuccess [$cur getColumnName 3] "TESTDECIMAL"
checkSuccess [$cur getColumnName 4] "TESTREAL"
checkSuccess [$cur getColumnName 5] "TESTDOUBLE"
checkSuccess [$cur getColumnName 6] "TESTCHAR"
checkSuccess [$cur getColumnName 7] "TESTVARCHAR"
checkSuccess [$cur getColumnName 8] "TESTDATE"
checkSuccess [$cur getColumnName 9] "TESTTIME"
checkSuccess [$cur getColumnName 10] "TESTTIMESTAMP"
set cols [$cur getColumnNames]
checkSuccess [lindex $cols 0] "TESTSMALLINT"
checkSuccess [lindex $cols 1] "TESTINT"
checkSuccess [lindex $cols 2] "TESTBIGINT"
checkSuccess [lindex $cols 3] "TESTDECIMAL"
checkSuccess [lindex $cols 4] "TESTREAL"
checkSuccess [lindex $cols 5] "TESTDOUBLE"
checkSuccess [lindex $cols 6] "TESTCHAR"
checkSuccess [lindex $cols 7] "TESTVARCHAR"
checkSuccess [lindex $cols 8] "TESTDATE"
checkSuccess [lindex $cols 9] "TESTTIME"
checkSuccess [lindex $cols 10] "TESTTIMESTAMP"
puts ""

puts "CACHED RESULT SET WITH RESULT SET BUFFER SIZE: "
$cur setResultSetBufferSize 2
$cur cacheToFile "cachefile1"
$cur setCacheTtl 200
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
set filename [$cur getCacheFileName]
checkSuccess $filename "cachefile1"
$cur cacheOff 
checkSuccess [$cur openCachedResultSet $filename] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
$cur setResultSetBufferSize 0
puts ""

puts "FROM ONE CACHE FILE TO ANOTHER: "
$cur cacheToFile "cachefile2"
checkSuccess [$cur openCachedResultSet "cachefile1"] 1
$cur cacheOff 
checkSuccess [$cur openCachedResultSet "cachefile2"] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
puts ""

puts "FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: "
$cur setResultSetBufferSize 2
$cur cacheToFile "cachefile2"
checkSuccess [$cur openCachedResultSet "cachefile1"] 1
$cur cacheOff 
checkSuccess [$cur openCachedResultSet "cachefile2"] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
$cur setResultSetBufferSize 0
puts ""

puts "CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: "
$cur setResultSetBufferSize 2
$cur cacheToFile "cachefile1"
$cur setCacheTtl 200
checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 1
checkSuccess [$cur getFieldByIndex 2 0] "3"
set filename [$cur getCacheFileName]
checkSuccess $filename "cachefile1"
set id [$cur getResultSetId]
$cur suspendResultSet 
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
puts ""
checkSuccess [$con resumeSession $port $socket] 1
checkSuccess [$cur resumeCachedResultSet $id $filename] 1
puts ""
checkSuccess [$cur firstRowIndex] 4
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 6
checkSuccess [$cur getFieldByIndex 7 0] "8"
puts ""
checkSuccess [$cur firstRowIndex] 6
checkSuccess [$cur endOfResultSet] 0
checkSuccess [$cur rowCount] 8
checkUndef [$cur getFieldByIndex 8 0]
puts ""
checkSuccess [$cur firstRowIndex] 8
checkSuccess [$cur endOfResultSet] 1
checkSuccess [$cur rowCount] 8
$cur cacheOff 
puts ""
checkSuccess [$cur openCachedResultSet $filename] 1
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkUndef [$cur getFieldByIndex 8 0]
$cur setResultSetBufferSize 0
puts ""

puts "FINISHED SUSPENDED SESSION: "
checkSuccess [$cur sendQuery "select * from testtable order by testint"] 1
checkSuccess [$cur getFieldByIndex 4 0] "5"
checkSuccess [$cur getFieldByIndex 5 0] "6"
checkSuccess [$cur getFieldByIndex 6 0] "7"
checkSuccess [$cur getFieldByIndex 7 0] "8"
set id [$cur getResultSetId]
$cur suspendResultSet 
checkSuccess [$con suspendSession] 1
set port [$con getConnectionPort]
set socket [$con getConnectionSocket]
checkSuccess [$con resumeSession $port $socket] 1
checkSuccess [$cur resumeResultSet $id] 1
checkUndef [$cur getFieldByIndex 4 0]
checkUndef [$cur getFieldByIndex 5 0]
checkUndef [$cur getFieldByIndex 6 0]
checkUndef [$cur getFieldByIndex 7 0]
puts ""

# drop existing table
$con commit 
catch {$cur sendQuery "drop table testtable"}
puts ""

# invalid queries...
puts "INVALID QUERIES: "
catch {checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable order by testsmallint"] 0}
puts ""
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
catch {checkSuccess [$cur sendQuery "insert into testtable values (1,2,3,4)"] 0}
puts ""
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
catch {checkSuccess [$cur sendQuery "create table testtable"] 0}
puts ""