#! /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 -nonewline "success "
	} default {
		puts "failure "
		exit
	}
}

proc checkSuccess {value success} {

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

# usage...
if {$argc<5} {
	puts "usage: oracle8i.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] "oracle8"
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 (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long, testclob clob, testblob blob)"] 1
puts ""

puts "INSERT: "
checkSuccess [$cur sendQuery "insert into testtable values (1,'testchar1','testvarchar1','01-JAN-2001','testlong1','testclob1',empty_blob())"] 1
puts ""

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

puts "BIND BY POSITION: "
$cur prepareQuery "insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)"
checkSuccess [$cur countBindVariables] 7
$cur inputBind "1" 2
$cur inputBind "2" "testchar2"
$cur inputBind "3" "testvarchar2"
$cur inputBind "4" "01-JAN-2002"
$cur inputBind "5" "testlong2"
$cur inputBindClob "6" "testclob2" 9
$cur inputBindBlob "7" "testblob2" 9
checkSuccess [$cur executeQuery] 1
$cur clearBinds 
$cur inputBind "1" 3
$cur inputBind "2" "testchar3"
$cur inputBind "3" "testvarchar3"
$cur inputBind "4" "01-JAN-2003"
$cur inputBind "5" "testlong3"
$cur inputBindClob "6" "testclob3" 9
$cur inputBindBlob "7" "testblob3" 9
checkSuccess [$cur executeQuery] 1
puts ""

puts "ARRAY OF BINDS BY POSITION: "
$cur clearBinds 
$cur inputBinds {{"1" 4} {"2" "testchar4"} {"3" "testvarchar4"} {"4" "01-JAN-2004"} {"5" "testlong4"}}
$cur inputBindClob "6" "testclob4" 9
$cur inputBindBlob "7" "testblob4" 9
checkSuccess [$cur executeQuery] 1
puts ""

puts "BIND BY NAME: "
$cur prepareQuery "insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)"
$cur inputBind "var1" 5
$cur inputBind "var2" "testchar5"
$cur inputBind "var3" "testvarchar5"
$cur inputBind "var4" "01-JAN-2005"
$cur inputBind "var5" "testlong5"
$cur inputBindClob "var6" "testclob5" 9
$cur inputBindBlob "var7" "testblob5" 9
checkSuccess [$cur executeQuery] 1
$cur clearBinds 
$cur inputBind "var1" 6
$cur inputBind "var2" "testchar6"
$cur inputBind "var3" "testvarchar6"
$cur inputBind "var4" "01-JAN-2006"
$cur inputBind "var5" "testlong6"
$cur inputBindClob "var6" "testclob6" 9
$cur inputBindBlob "var7" "testblob6" 9
checkSuccess [$cur executeQuery] 1
puts ""

puts "ARRAY OF BINDS BY NAME: "
$cur clearBinds 
$cur inputBinds {{"var1" 7} {"var2" "testchar7"} {"var3" "testvarchar7"} {"var4" "01-JAN-2007"} {"var5" "testlong7"}}
$cur inputBindClob "6" "testclob7" 9
$cur inputBindBlob "7" "testblob7" 9
checkSuccess [$cur executeQuery] 1
puts ""

puts "BIND BY NAME WITH VALIDATION: "
$cur clearBinds 
$cur inputBind "var1" 8
$cur inputBind "var2" "testchar8"
$cur inputBind "var3" "testvarchar8"
$cur inputBind "var4" "01-JAN-2008"
$cur inputBind "var5" "testlong8"
$cur inputBindClob "var6" "testclob8" 9
$cur inputBindBlob "var7" "testblob8" 9
$cur inputBind "var9" "junkvalue"
$cur validateBinds 
checkSuccess [$cur executeQuery] 1
puts ""

puts "OUTPUT BIND BY NAME: "
$cur prepareQuery "begin  :numvar:=1;  :stringvar:='hello';  :floatvar:=2.5;  end;"
$cur defineOutputBindInteger "numvar"
$cur defineOutputBindString "stringvar" 10
$cur defineOutputBindDouble "floatvar"
checkSuccess [$cur executeQuery] 1
set numvar [$cur getOutputBindInteger "numvar"]
set stringvar [$cur getOutputBindString "stringvar"]
set floatvar [$cur getOutputBindDouble "floatvar"]
checkSuccess $numvar 1
checkSuccess $stringvar "hello"
checkSuccess $floatvar 2.5
puts ""

puts "OUTPUT BIND BY NAME: "
$cur clearBinds 
$cur defineOutputBindInteger "1"
$cur defineOutputBindString "2" 10
$cur defineOutputBindDouble "3"
checkSuccess [$cur executeQuery] 1
set numvar [$cur getOutputBindInteger "1"]
set stringvar [$cur getOutputBindString "2"]
set floatvar [$cur getOutputBindDouble "3"]
checkSuccess $numvar 1
checkSuccess $stringvar "hello"
checkSuccess $floatvar 2.5
puts ""

puts "OUTPUT BIND BY NAME WITH VALIDATION: "
$cur clearBinds 
$cur defineOutputBindInteger "numvar"
$cur defineOutputBindString "stringvar" 10
$cur defineOutputBindDouble "floatvar"
$cur defineOutputBindString "dummyvar" 10
$cur validateBinds 
checkSuccess [$cur executeQuery] 1
set numvar [$cur getOutputBindInteger "numvar"]
set stringvar [$cur getOutputBindString "stringvar"]
set floatvar [$cur getOutputBindDouble "floatvar"]
checkSuccess $numvar 1
checkSuccess $stringvar "hello"
checkSuccess $floatvar 2.5
puts ""

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

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

puts "COLUMN NAMES: "
checkSuccess [$cur getColumnName 0] "TESTNUMBER"
checkSuccess [$cur getColumnName 1] "TESTCHAR"
checkSuccess [$cur getColumnName 2] "TESTVARCHAR"
checkSuccess [$cur getColumnName 3] "TESTDATE"
checkSuccess [$cur getColumnName 4] "TESTLONG"
set cols [$cur getColumnNames]
checkSuccess [lindex $cols 0] "TESTNUMBER"
checkSuccess [lindex $cols 1] "TESTCHAR"
checkSuccess [lindex $cols 2] "TESTVARCHAR"
checkSuccess [lindex $cols 3] "TESTDATE"
checkSuccess [lindex $cols 4] "TESTLONG"
puts ""

puts "COLUMN TYPES: "
checkSuccess [$cur getColumnTypeByIndex 0] "NUMBER"
checkSuccess [$cur getColumnTypeByName "testnumber"] "NUMBER"
checkSuccess [$cur getColumnTypeByIndex 1] "CHAR"
checkSuccess [$cur getColumnTypeByName "testchar"] "CHAR"
checkSuccess [$cur getColumnTypeByIndex 2] "VARCHAR2"
checkSuccess [$cur getColumnTypeByName "testvarchar"] "VARCHAR2"
checkSuccess [$cur getColumnTypeByIndex 3] "DATE"
checkSuccess [$cur getColumnTypeByName "testdate"] "DATE"
checkSuccess [$cur getColumnTypeByIndex 4] "LONG"
checkSuccess [$cur getColumnTypeByName "testlong"] "LONG"
puts ""

puts "COLUMN LENGTH: "
checkSuccess [$cur getColumnLengthByIndex 0] 22
checkSuccess [$cur getColumnLengthByName "testnumber"] 22
checkSuccess [$cur getColumnLengthByIndex 1] 40
checkSuccess [$cur getColumnLengthByName "testchar"] 40
checkSuccess [$cur getColumnLengthByIndex 2] 40
checkSuccess [$cur getColumnLengthByName "testvarchar"] 40
checkSuccess [$cur getColumnLengthByIndex 3] 7
checkSuccess [$cur getColumnLengthByName "testdate"] 7
checkSuccess [$cur getColumnLengthByIndex 4] 0
checkSuccess [$cur getColumnLengthByName "testlong"] 0
puts ""

puts "LONGEST COLUMN: "
checkSuccess [$cur getLongestByIndex 0] 1
checkSuccess [$cur getLongestByName "testnumber"] 1
checkSuccess [$cur getLongestByIndex 1] 40
checkSuccess [$cur getLongestByName "testchar"] 40
checkSuccess [$cur getLongestByIndex 2] 12
checkSuccess [$cur getLongestByName "testvarchar"] 12
checkSuccess [$cur getLongestByIndex 3] 9
checkSuccess [$cur getLongestByName "testdate"] 9
checkSuccess [$cur getLongestByIndex 4] 9
checkSuccess [$cur getLongestByName "testlong"] 9
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] "testchar1                               "
checkSuccess [$cur getFieldByIndex 0 2] "testvarchar1"
checkSuccess [$cur getFieldByIndex 0 3] "01-JAN-01"
checkSuccess [$cur getFieldByIndex 0 4] "testlong1"
checkSuccess [$cur getFieldByIndex 0 5] "testclob1"
checkSuccess [$cur getFieldByIndex 0 6] ""
puts ""
checkSuccess [$cur getFieldByIndex 7 0] "8"
checkSuccess [$cur getFieldByIndex 7 1] "testchar8                               "
checkSuccess [$cur getFieldByIndex 7 2] "testvarchar8"
checkSuccess [$cur getFieldByIndex 7 3] "01-JAN-08"
checkSuccess [$cur getFieldByIndex 7 4] "testlong8"
checkSuccess [$cur getFieldByIndex 7 5] "testclob8"
checkSuccess [$cur getFieldByIndex 7 6] "testblob8"
puts ""

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

puts "FIELDS BY NAME: "
checkSuccess [$cur getFieldByName 0 "testnumber"] "1"
checkSuccess [$cur getFieldByName 0 "testchar"] "testchar1                               "
checkSuccess [$cur getFieldByName 0 "testvarchar"] "testvarchar1"
checkSuccess [$cur getFieldByName 0 "testdate"] "01-JAN-01"
checkSuccess [$cur getFieldByName 0 "testlong"] "testlong1"
checkSuccess [$cur getFieldByName 0 "testclob"] "testclob1"
checkSuccess [$cur getFieldByName 0 "testblob"] ""
puts ""
checkSuccess [$cur getFieldByName 7 "testnumber"] "8"
checkSuccess [$cur getFieldByName 7 "testchar"] "testchar8                               "
checkSuccess [$cur getFieldByName 7 "testvarchar"] "testvarchar8"
checkSuccess [$cur getFieldByName 7 "testdate"] "01-JAN-08"
checkSuccess [$cur getFieldByName 7 "testlong"] "testlong8"
checkSuccess [$cur getFieldByName 7 "testclob"] "testclob8"
checkSuccess [$cur getFieldByName 7 "testblob"] "testblob8"
puts ""

puts "FIELD LENGTHS BY NAME: "
checkSuccess [$cur getFieldLengthByName 0 "testnumber"] 1
checkSuccess [$cur getFieldLengthByName 0 "testchar"] 40
checkSuccess [$cur getFieldLengthByName 0 "testvarchar"] 12
checkSuccess [$cur getFieldLengthByName 0 "testdate"] 9
checkSuccess [$cur getFieldLengthByName 0 "testlong"] 9
checkSuccess [$cur getFieldLengthByName 0 "testclob"] 9
checkSuccess [$cur getFieldLengthByName 0 "testblob"] 0
puts ""
checkSuccess [$cur getFieldLengthByName 7 "testnumber"] 1
checkSuccess [$cur getFieldLengthByName 7 "testchar"] 40
checkSuccess [$cur getFieldLengthByName 7 "testvarchar"] 12
checkSuccess [$cur getFieldLengthByName 7 "testdate"] 9
checkSuccess [$cur getFieldLengthByName 7 "testlong"] 9
checkSuccess [$cur getFieldLengthByName 7 "testclob"] 9
checkSuccess [$cur getFieldLengthByName 7 "testblob"] 9
puts ""

puts "FIELDS BY ARRAY: "
set fields [$cur getRow 0]
checkSuccess [lindex $fields 0] 1
checkSuccess [lindex $fields 1] "testchar1                               "
checkSuccess [lindex $fields 2] "testvarchar1"
checkSuccess [lindex $fields 3] "01-JAN-01"
checkSuccess [lindex $fields 4] "testlong1"
checkSuccess [lindex $fields 5] "testclob1"
checkSuccess [lindex $fields 6] ""
puts ""

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

puts "INDIVIDUAL SUBSTITUTIONS: "
$cur prepareQuery "select \$(var1),'\$(var2)',\$(var3) from dual"
$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 "OUTPUT BIND: "
$cur prepareQuery "begin  :var1:='hello';  end;"
$cur defineOutputBindString "var1" 10
checkSuccess [$cur executeQuery] 1
checkSuccess [$cur getOutputBindString "var1"] "hello"
puts ""

puts "ARRAY SUBSTITUTIONS: "
$cur prepareQuery "select \$(var1),'\$(var2)',\$(var3) from dual"
$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 testnumber"] 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 testnumber"] 1
checkUndef [$cur getColumnName 0]
checkSuccess [$cur getColumnLengthByIndex 0] 0
checkUndef [$cur getColumnTypeByIndex 0]
$cur getColumnInfo 
checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 1
checkSuccess [$cur getColumnName 0] "TESTNUMBER"
checkSuccess [$cur getColumnLengthByIndex 0] 22
checkSuccess [$cur getColumnTypeByIndex 0] "NUMBER"
puts ""

puts "SUSPENDED SESSION: "
checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 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 testnumber"] 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 testnumber"] 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 testnumber"] 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 testnumber"] 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] 7
puts ""

puts "COLUMN NAMES FOR CACHED RESULT SET: "
checkSuccess [$cur getColumnName 0] "TESTNUMBER"
checkSuccess [$cur getColumnName 1] "TESTCHAR"
checkSuccess [$cur getColumnName 2] "TESTVARCHAR"
checkSuccess [$cur getColumnName 3] "TESTDATE"
checkSuccess [$cur getColumnName 4] "TESTLONG"
checkSuccess [$cur getColumnName 5] "TESTCLOB"
checkSuccess [$cur getColumnName 6] "TESTBLOB"
set cols [$cur getColumnNames]
checkSuccess [lindex $cols 0] "TESTNUMBER"
checkSuccess [lindex $cols 1] "TESTCHAR"
checkSuccess [lindex $cols 2] "TESTVARCHAR"
checkSuccess [lindex $cols 3] "TESTDATE"
checkSuccess [lindex $cols 4] "TESTLONG"
checkSuccess [lindex $cols 5] "TESTCLOB"
checkSuccess [lindex $cols 6] "TESTBLOB"
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 testnumber"] 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 testnumber"] 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 "COMMIT AND ROLLBACK: "
set secondcon [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 secondcur [$secondcon sqlrcur]
checkSuccess [$secondcur sendQuery "select count(*) from testtable"] 1
checkSuccess [$secondcur getFieldByIndex 0 0] "0"
checkSuccess [$con commit] 1
checkSuccess [$secondcur sendQuery "select count(*) from testtable"] 1
checkSuccess [$secondcur getFieldByIndex 0 0] "8"
checkSuccess [$con autoCommit 1] 1
checkSuccess [$cur sendQuery "insert into testtable values (10,'testchar10','testvarchar10','01-JAN-2010','testlong10','testclob10',empty_blob())"] 1
checkSuccess [$secondcur sendQuery "select count(*) from testtable"] 1
checkSuccess [$secondcur getFieldByIndex 0 0] "9"
checkSuccess [$con autoCommit 0] 1
puts ""

puts "CLOB AND BLOB OUTPUT BIND: "
catch {$cur sendQuery "drop table testtable1"}
checkSuccess [$cur sendQuery "create table testtable1 (testclob clob, testblob blob)"] 1
$cur prepareQuery "insert into testtable1 values ('hello',:var1)"
$cur inputBindBlob "var1" "hello" 5
checkSuccess [$cur executeQuery] 1
$cur prepareQuery "begin  select testclob into :clobvar from testtable1;  select testblob into :blobvar from testtable1;  end;"
$cur defineOutputBindClob "clobvar"
$cur defineOutputBindBlob "blobvar"
checkSuccess [$cur executeQuery] 1
set clobvar [$cur getOutputBindClob "clobvar"]
set clobvarlength [$cur getOutputBindLength "clobvar"]
set blobvar [$cur getOutputBindBlob "blobvar"]
set blobvarlength [$cur getOutputBindLength "blobvar"]
checkSuccess $clobvar "hello"
checkSuccess $clobvarlength 5
checkSuccess $blobvar "hello"
checkSuccess $blobvarlength 5
catch {$cur sendQuery "drop table testtable1"}
puts ""

puts "NULL AND EMPTY CLOBS AND CLOBS: "
catch {$cur sendQuery "create table testtable1 (testclob1 clob, testclob2 clob, testblob1 blob, testblob2 blob)"}
$cur prepareQuery "insert into testtable1 values (:var1,:var2,:var3,:var4)"
$cur inputBindClob "var1" "" 0
$cur inputBindClob "var2" "" 0
$cur inputBindBlob "var3" "" 0
$cur inputBindBlob "var4" "" 0
checkSuccess [$cur executeQuery] 1
catch {$cur sendQuery "select * from testtable1"}
checkSuccess [$cur getFieldByIndex 0 0] ""
checkSuccess [$cur getFieldByIndex 0 1] ""
checkSuccess [$cur getFieldByIndex 0 2] ""
checkSuccess [$cur getFieldByIndex 0 3] ""
catch {$cur sendQuery "drop table testtable1"}
puts ""

puts "CURSOR BINDS: "
checkSuccess [$cur sendQuery "create or replace package types as type cursorType is ref cursor;  end;"] 1
checkSuccess [$cur sendQuery "create or replace function sp_testtable return types.cursortype as l_cursor   types.cursorType;  begin  open l_cursor for select * from testtable;  return l_cursor;  end;"] 1
$cur prepareQuery "begin  :curs:=sp_testtable;  end;"
$cur defineOutputBindCursor "curs"
checkSuccess [$cur executeQuery] 1
set bindcur [$cur getOutputBindCursor "curs"]
checkSuccess [$bindcur fetchFromBindCursor] 1
checkSuccess [$bindcur getFieldByIndex 0 0] "1"
checkSuccess [$bindcur getFieldByIndex 1 0] "2"
checkSuccess [$bindcur getFieldByIndex 2 0] "3"
checkSuccess [$bindcur getFieldByIndex 3 0] "4"
checkSuccess [$bindcur getFieldByIndex 4 0] "5"
checkSuccess [$bindcur getFieldByIndex 5 0] "6"
checkSuccess [$bindcur getFieldByIndex 6 0] "7"
checkSuccess [$bindcur getFieldByIndex 7 0] "8"
puts ""


puts "LONG CLOB: "
catch {$cur sendQuery "drop table testtable2"}
catch {$cur sendQuery "create table testtable2 (testclob clob)"}
$cur prepareQuery "insert into testtable2 values (:clobval)"
set clobvallist {}
for {set i 0} {$i<[expr 8*1]} {incr i} {
	lappend clobvallist "C"
}
set clobval [join $clobvallist ""]
$cur inputBindClob "clobval" $clobval [expr 8*1]
checkSuccess [$cur executeQuery] 1
catch {$cur sendQuery "select testclob from testtable2"}
checkSuccess $clobval [$cur getFieldByName 0 "testclob"]
$cur prepareQuery "begin  select testclob into :clobbindval from testtable2;  end;"
$cur defineOutputBindClob "clobbindval"
checkSuccess [$cur executeQuery] 1
set clobbindvar [$cur getOutputBindClob "clobbindval"]
checkSuccess [$cur getOutputBindLength "clobbindval"] [expr 8*1]
checkSuccess $clobval $clobbindvar
catch {$cur sendQuery "delete from testtable2"}
puts ""
$cur prepareQuery "insert into testtable2 values (:clobval)"
set clobvallist {}
for {set i 0} {$i<[expr 8*1]} {incr i} {
	lappend clobvallist "C"
}
set clobval [join $clobvallist ""]
$cur inputBindClob "clobval" $clobval [expr 8*1]
checkSuccess [$cur executeQuery] 1
catch {$cur sendQuery "select testclob from testtable2"}
checkSuccess $clobval [$cur getFieldByName 0 "testclob"]
$cur prepareQuery "begin  select testclob into :clobbindval from testtable2;  end;"
$cur defineOutputBindClob "clobbindval"
checkSuccess [$cur executeQuery] 1
set clobbindvar [$cur getOutputBindClob "clobbindval"]
checkSuccess [$cur getOutputBindLength "clobbindval"] [expr 8*1]
checkSuccess $clobval $clobbindvar
catch {$cur sendQuery "drop table testtable2"}
puts ""

puts "LONG OUTPUT BIND"
catch {$cur sendQuery "drop table testtable2"}
catch {$cur sendQuery "create table testtable2 (testval varchar2(4000))"}
set testval ""
$cur prepareQuery "insert into testtable2 values (:testval)"
for {set i 0} {$i<2000} {incr i} {
	set testval [concat "$testval" "C"]
}
$cur inputBind "testval" $testval
checkSuccess [$cur executeQuery] 1
catch {$cur sendQuery "select testval from testtable2"}
checkSuccess $testval [$cur getFieldByName 0 "testval"]
set query "begin  :bindval:='$testval';  end;"
$cur prepareQuery $query
$cur defineOutputBindString "bindval" 4000
checkSuccess [$cur executeQuery] 1
set bindval [$cur getOutputBindString "bindval"]
checkSuccess [$cur getOutputBindLength "bindval"] 3999
checkSuccess $bindval $testval
catch {$cur sendQuery "drop table testtable2"}
puts ""

puts "NEGATIVE INPUT BIND"
catch {$cur sendQuery "create table testtable2 (testval number)"}
$cur prepareQuery "insert into testtable2 values (:testval)"
$cur inputBind "testval" -1
checkSuccess [$cur executeQuery] 1
catch {$cur sendQuery "select testval from testtable2"}
checkSuccess [$cur getFieldByName 0 "testval"] "-1"
catch {$cur sendQuery "drop table testtable2"}
puts ""

puts "FINISHED SUSPENDED SESSION: "
checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 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
catch {$cur sendQuery "drop table testtable"}

# invalid queries...
puts "INVALID QUERIES: "
catch {checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 0}
catch {checkSuccess [$cur sendQuery "select * from testtable order by testnumber"] 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 ""