From VisualWorks®, 7.2 of 03.11.2003г. on 18.03.2004г. at 13:48:30.000 (Dialog confirm: 'You are filing-in a Parcel source file!\\While this is possible it will not have\the same effect as loading the parcel.\None of the Parcel''s prerequisites will\be loaded and none of its load actions\will be performed.\\Are you sure you want to file-in?' withCRs) ifFalse: [self error: 'Parcel file-in abandoned. Choose terminate or close.'] <class> <name>DB2EXDITest</name> <environment>Smalltalk</environment> <super>Core.Object</super> <private>false</private> none id name <imports></imports> <category>Database-DB2-Tests</category> <attributes> <package>DB2EXDITest</package> </attributes> </class> <name>AnswerLobAs</name> <environment>DB2EXDITest</environment> <private>false</private> <constant>false</constant> <category>As yet unclassified</category> <initializer>true</initializer> <attributes> <package>DB2EXDITest</package> </attributes> <name>OutMedian</name> <environment>DB2EXDITest</environment> <private>false</private> <constant>false</constant> <category>As yet unclassified</category> <attributes> <package>DB2EXDITest</package> </attributes> <name>Stress</name> <environment>DB2EXDITest</environment> <private>false</private> <constant>false</constant> <category>As yet unclassified</category> <initializer>100</initializer> <attributes> <package>DB2EXDITest</package> </attributes> <name>IsThreaded</name> <environment>DB2EXDITest</environment> <private>false</private> <constant>false</constant> <category>As yet unclassified</category> <initializer>false</initializer> <attributes> <package>DB2EXDITest</package> </attributes> <methods> DB2EXDITest <category>comparing</category>
= anOther ^id hash + name hash
</methods> <methods> DB2EXDITest <category>accessing</category>
id ^id
id: aValue id := aValue
name ^name
name: aValue name := aValue
</methods> <methods> DB2EXDITest <category>initialize-release</category>
id: anInteger name: aString id:=anInteger. name:=aString. ^self
</methods> <methods> DB2EXDITest class <category>test-calls</category>
callClobExtract: c | s expr err | expr:='CALL CLOB_EXTRACT( :empNumber, :deptInfo, :outRc)'. s:=c getSession. s prepareCall: expr; bindVariable: #empNumber value: '000140' kind: #in; bindVariable: #deptInfo value: (String new: 1000) kind: #out; bindVariable: #outRc value: 0 kind: #out; execute; answer. "Check errors" (err:=s bindVariable: #outRc) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully with:' ; cr; show: (s bindVariable: #deptInfo) ] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString].
callInOutParameter: c median: median | s expr err | expr:='CALL INOUT_PARAM (:median, :err, :errString)'. s:=c getSession. Transcript cr; show: 'Call stored procedure ', expr. s prepareCall: expr; bindVariable: #median value: median kind: #inout; bindVariable: #err value: 0 kind: #out; bindVariable: #errString value: (String new: 50) kind: #out; execute; answer. OutMedian := s bindVariable: #median. (err:=s bindVariable: #err) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully, median salary = ', OutMedian printString] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString,' error string = ', (s bindVariable: #errString) displayString]
callInParameter: c | s expr err | expr:='CALL IN_PARAMS (?,?,?,?,?,?)'. s:=c getSession. s prepareCall: expr; bindVariable: 1 value: 15001d kind: #in; bindVariable: 2 value: 20002d ; " parameter type = #in by default" bindVariable: 3 value: 25003d ; bindVariable: 4 value: 'E11' ; bindVariable: 5 value: 0 kind: #out; "result code" bindVariable: 6 value: (String new: 50) kind: #out; "message" execute; answer. (err:=s bindVariable: 5) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully'] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString,' error string = ', (s bindVariable: 6) displayString].
callOneResultSet: c |s expr a err | expr:='CALL ONE_RESULT_SET( :inSalary, :outRc)'. s:=c getSession. s prepareCall: expr; blockFactor: 30; bindVariable: #inSalary value: 14000.0d kind: #in; bindVariable: #outRc value: 0 kind: #out; execute. "Check errors" (err:=s bindVariable: #outRc) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully'] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString]. "Get result" [(a:=s answer) == #noMoreAnswers] whileFalse: [Transcript cr; show: 'Result set: '; show: (a upToEnd) printString ]. Transcript cr.
callOutLanguage: c | s expr | expr:='CALL OUT_LANGUAGE (?)'. s:=c getSession. Transcript cr; show: 'Call stored procedure OUT_LANGUAGE'. s prepareCall: expr; bindVariable: 1 value: (String new: 10) kind: #out; execute; answer. Transcript cr; show: 'Stored procedures are implemented in language ', (s bindVariable: 1) printString. ^s bindVariable: 1
callOutParameter: c | s expr err | expr:='CALL OUT_PARAM (:median, :err, :errString)'. s:=c getSession. Transcript cr; show: 'Call stored procedure ', expr. s prepareCall: expr; bindVariable: #median value: 0d kind: #out; bindVariable: #err value: 0 kind: #out; bindVariable: #errString value: (String new: 50) kind: #out; execute; answer. OutMedian := s bindVariable: #median. (err:=s bindVariable: #err) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully, median salary = ', OutMedian printString] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString,' error string = ', (s bindVariable: #errString) displayString]
callTwoResultSets: c |s expr a err | expr:='CALL TWO_RESULT_SETS( :inSalary, :outRc)'. s:=c getSession. s prepareCall: expr; deferCursorClosing; "defer cursor closing for procedures, answering multiply answer sets" blockFactor: 30; bindVariable: #inSalary value: 14000.0d kind: #in; bindVariable: #outRc value: 0 kind: #out; execute. "Check errors" (err:=s bindVariable: #outRc) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully'] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString]. "Get result" [(a:=s answer) == #noMoreAnswers] whileFalse: [Transcript cr; show: 'Result set: '; show: (a upToEnd) printString ]. Transcript cr.
testCallInOutParameter: median "self testCall6" | c s expr | expr:='CALL INOUT_PARAM( :medSalary, :outRc, :outMsg)'. c:=self testConnection. s:=c getSession. s prepareCall: expr; bindVariable: #medSalary value: 17654.5d kind: #inout; bindVariable: #outRc value: 0 kind: #out; bindVariable: #outMsg value: (String new: 50) kind: #out; execute; answer. "Check errors" (s bindVariable: #outRc ) ~= 0 ifTrue: [((s bindVariable: #outMsg), ' ', (s bindVariable: #outRc ) ) inspect]. "Get result" (s bindVariable: #medSalary ) inspect. c disconnect.
testCallInParameter: c | s expr err | expr:='CALL IN_PARAMS (?,?,?,?,?,?)'. s:=c getSession. s prepareCall: expr; bindVariable: 1 value: 15001 kind: #in; bindVariable: 2 value: 20002 ; " parameter type = #in by default" bindVariable: 3 value: 25003 ; bindVariable: 4 value: 'E11' ; bindVariable: 5 value: 0 kind: #out; "result code" bindVariable: 6 value: (String new: 50) kind: #out; "message" execute; answer. (err:=s bindVariable: 5) == 0 ifTrue: [Transcript cr; show: expr, ' completed successfully'] ifFalse: [Transcript cr; show: expr, ' failed with SQLCODE=', err printString,' error string = ', (s bindVariable: 6) displayString]. c disconnect.
testCallReadMe "To run these call samples you should build and to register stored procedures. These samples tested with C and JAVA stored procedures, to build java stored procedures see comments below: Extracted from SpClient.java comments: // To run this sample, perform the following steps: // (1) create and populate the SAMPLE database (db2sampl) // (2) compile Spserver (javac Spserver.java) // (3) copy Spserver.class to the sqllib/function directory // (4) register the stored procedures using the Spcreate.db2 CLP script: // (i) db2 connect to sample // (ii) db2 -td@ -vf Spcreate.db2 // NOTES: (1) The jdk11_path database manager configuration parameter must // be set on the server // (2) The CLASSPATH and shared library path environment variables // must be set, as for any JDBC application "
testSPCall "self testSPCall" | c | c:= self testConnection. c begin. "autocommit off" self callOutLanguage: c; callOutParameter: c; callInParameter: c; callInOutParameter: c median: OutMedian; callInOutParameter: c median: 99999.99d; "will be fail" callOneResultSet: c; callTwoResultSets: c; callClobExtract: c ; yourself. c rollback.
</methods> <methods> DB2EXDITest class <category>test-datatypes</category>
deleteTestFiles: values values do: [:a | | f | (f:=('test', a key , '.test') asFilename) exists ifTrue: [f delete] ]. #('Apple.data' 'Orange.data' 'Cherry.data' 'testMerge.data' 'testFile.data') do: [:a | | f | (f:=a asFilename) exists ifTrue: [f delete ]] .
testAllFields "Test all database types" "self testAllFields" Transcript cr; show: 'Begin fields test - '. self testInteger; testBigInt; testDecimal; testDecimal2; testFloat; testDouble; testDatalink; testDate; testTime; testTimestamp; testChar; testChar2; testCharB; testCharB2; "------------------------ DB2/UDB 7.1 FP2 edition" testCLOB; testBLOB; testCLOBLocators; testBLOBLocators; "------------------------ DB2/UDB 7.1 fixpak level < 2 edition DB2 UDB v7.1 FP1 client contains a bug at blocked fetch LOB locators and LOB values ! Don't set blockFactor more than 1 for querys with LOB fields ! You can use server DB2 v7.1 with clients v6.1 without these problems." testCLOB_v7FP1; testBLOB_v7FP1; testCLOBLocatorsFP1; testBLOBLocatorsFP1; "-----------------------" testCLOBFileRef; yourself. Transcript cr;cr; show: 'End fields test'.
testBigInt "self testBigInt" | field values | field := 'bigint'. values := (1000000001 to: 1000000100 ) asArray. "must be LargeInteger instances !" values at: 100 put: 9223372036854775807 ; "largest" at: 99 put: -9223372036854775808; "smallest" at: 97 put: nil. self testDatabaseField: field values: values.
testBigInt2 "self testBigInt2" | field values | field := 'bigint'. values := (1 to: 100 ) asArray. "must be LargeInteger instances !" values at: 2 put: 9223372036854775807 ; "largest" "will be rebind from Int to BigInt " at: 99 put: -1; "with input array binding, we must use values of same type (Integer in this case)" at: 100 put: -9223372036854775808. self testDatabaseField: field values: values.
testBLOB "self testBLOB" | field values | field := 'blob(5000)'. values := (1 to: 100 ) collect: [:x | (ByteArray new: x*50 withAll: x) readStream ] . values at: 100 put: (ByteArray new: 5000 withAll: 255) readStream; "largest" at: 99 put: #[] readStream; "smallest" "at: 97 put: nil;" at: 2 put: (ByteArray new: 400 withAll: 2) readStream. "test rebind: " "self testDatabaseLob: field values: values" "doesn't work with V7 FP1 client" self testDatabaseLobNoBlock: field values: values
testBLOBFileRef "self testBLOBFileRef" | field values f s | values:=OrderedCollection new: 100. ((Collection withAllSubclasses collect: [:c | c name -> c comment]) "pair class->comment" select: [:a | a value size >0]) do: [:a | f:=('test', a key , '.test') asFilename. [(s:=f writeStream) nextPutAll: (a value) ] ensure: [ s close]. values add: a key -> (DB2LOBFileReference forBLOB: f asString)]. field := 'blob(20K)'. self testDatabaseLobFileRef: field values: values
testBLOBLocators "self testBLOBLocators" | field values | field := 'blob(5000)'. values := (1 to: 100 ) collect: [:x | (ByteArray new: x*50 withAll: x) readStream ] . values at: 100 put: (ByteArray new: 5000 withAll: 255) readStream; "largest" at: 99 put: #[] readStream; "smallest" "at: 97 put: nil;" at: 2 put: (ByteArray new: 400 withAll: 2) readStream. "test rebind: " self testDatabaseLocators: field values: values. "doesn't work with V7 FP1 client" "self testDatabaseLocatorsNoBlock: field values: values."
testBLOBLocatorsFP1 "self testBLOBLocatorsFP1" | field values | field := 'blob(5000)'. values := (1 to: 100 ) collect: [:x | (ByteArray new: x*50 withAll: x) readStream ] . values at: 100 put: (ByteArray new: 5000 withAll: 255) readStream; "largest" at: 99 put: #[] readStream; "smallest" "at: 97 put: nil;" at: 2 put: (ByteArray new: 400 withAll: 2) readStream. "test rebind: " "self testDatabaseLocators: field values: values." "doesn't work with V7 FP1 client" self testDatabaseLocatorsNoBlock: field values: values.
testBLOB_v7FP1 "self testBLOB_v7FP1" "DB2/UDB v7 with fixpak level < 2 have bug in client part: blocking fetch for LOB and LOB locators doesn't work" | field values | field := 'blob(5000)'. values := (1 to: 100 ) collect: [:x | (ByteArray new: x*50 withAll: x) readStream ] . values at: 100 put: (ByteArray new: 5000 withAll: 255) readStream; "largest" at: 99 put: #[] readStream; "smallest" at: 2 put: (ByteArray new: 400 withAll: 2) readStream. "test rebind: " "self testDatabaseLob: field values: values" "doesn't work with V7 FP1 client" self testDatabaseLobNoBlock: field values: values
testChar "self testChar" | field values string | field := 'varchar(254)'. string := self testStringLiteral. values := (1 to: 100) collect: [:x | String new: x * 2 withAll: (string at: x)]. values at: 100 put: (String new: 254 withAll: (Character value: 126)); at: 97 put: nil; at: 99 put: String new. "largest" "smallest" self testDatabaseString: field values: values
testChar2 "self testChar2" | field values string | field := 'varchar(3996)'. " integer field size 4 + varchar size 3996+4+1 = 4005 max row size for tablespace with 4K pages" string := self testStringLiteral. values := (1 to: 100 ) collect: [:x | (String new: x*39 withAll: (string at: x)) ] . values at: 100 put: (String new: 3996 withAll: (Character value: 126)); "largest" at: 99 put: (String new); "smallest" at: 97 put: nil; at: 2 put: (String new: 400 withAll: (Character value: 65)). "test rebind: " self testDatabaseString: field values: values
testCharB "self testCharB" | field values | field := 'varchar(254) for bit data'. values := (1 to: 100 ) collect: [:x | ByteArray new: x*2 withAll: x ] . values at: 100 put: (ByteArray new: 254 withAll: 255); "largest" at: 97 put: nil; at: 99 put: #[0]. "smallest" self testDatabaseString: field values: values
testCharB2 "self testCharB2" | field values | field := 'varchar(3996) for bit data'. "see testChar2 method comment" values := (1 to: 100 ) collect: [:x | ByteArray new: x*39 withAll: x ] . values at: 100 put: (ByteArray new: 3996 withAll: 255); "largest" at: 99 put: #[0]; "smallest" at: 97 put: nil; at: 2 put: (ByteArray new: 400 withAll: 2). "test rebind: " self testDatabaseString: field values: values
testCLOB "self testCLOB" | field values string | field := 'clob(5000)'. string := self testStringLiteral. values := (1 to: 100 ) collect: [:x | (String new: x*50 withAll: (string at: x)) readStream ] . values at: 100 put: (String new: 5000 withAll: (Character value: 126)) readStream; "largest" at: 99 put: (String new) readStream; "smallest" at: 2 put: (String new: 400 withAll: (Character value: 34)) readStream. "test rebind: " self testDatabaseLob: field values: values "doesn't work with V7 FP1 client" "self testDatabaseLobNoBlock: field values: values."
testCLOBFileRef "self testCLOBFileRef" | field values f s | values:=OrderedCollection new: 100. ((Collection withAllSubclasses collect: [:c | c name -> c comment]) "pair class->comment" select: [:a | a value size >0]) do: [:a | f:=('test', a key , '.test') asFilename. [(s:=f writeStream) nextPutAll: (a value) ] ensure: [ s close]. values add: a key -> (DB2LOBFileReference forCLOB: f asString)]. field := 'clob(20K)'. self testDatabaseLobFileRef: field values: values. "writing" self testDatabaseLobFileRef2: field values: values. "reading" self deleteTestFiles: values.
testCLOBLocators "self testCLOBLocators" | field values string | field := 'clob(5000)'. string := self testStringLiteral. values := (1 to: 100) collect: [:x | (String new: x * 50 withAll: (string at: x)) readStream]. values at: 100 put: (String new: 5000 withAll: (Character value: 126)) readStream; at: 99 put: String new readStream; at: 2 put: (String new: 400 withAll: (Character value: 34)) readStream. "largest" "smallest" "test rebind: " self testDatabaseLocators: field values: values "doesn't work with V7 FP1 client" "self testDatabaseLocatorsNoBlock: field values: values."
testCLOBLocatorsFP1 "self testCLOBLocatorsFP1" | field values string | field := 'clob(5000)'. string := self testStringLiteral. values := (1 to: 100 ) collect: [:x | (String new: x*50 withAll: (string at: x)) readStream ] . values at: 100 put: (String new: 5000 withAll: (Character value: 126)) readStream; "largest" at: 99 put: (String new) readStream; "smallest" at: 2 put: (String new: 400 withAll: (Character value: 34)) readStream. "test rebind: " "self testDatabaseLocators: field values: values" "doesn't work with V7 FP1 client" self testDatabaseLocatorsNoBlock: field values: values.
testCLOB_v7FP1 "self testCLOB_v7FP1" | field values string | field := 'clob(5000)'. string := self testStringLiteral. values := (1 to: 100 ) collect: [:x | (String new: x*50 withAll: (string at: x)) readStream ] . values at: 100 put: (String new: 5000 withAll: (Character value: 126)) readStream; "largest" at: 99 put: (String new) readStream; "smallest" at: 2 put: (String new: 400 withAll: (Character value: 34)) readStream. "test rebind: " self testDatabaseLobNoBlock: field values: values "doesn't work with V7 FP1 client"
testDatabaseField: field values: values "Test database type" | s c entry a result cursor s2 | c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (testField <1s>)' expandMacrosWith: field) ; execute; answer. "now insert values" s prepare: 'insert into tableTestField(testField) values (?)'. "(1) basic insert" entry:= Array with: values first. s bindInput: entry; execute; answer. "(2) repeat basic insert" entry:= Array with: (values at: 2). s bindInput: entry; execute; answer. "(3) array insert" entry:= Array with: (values copyFrom: 3 to: 20). s bindInputArrayByColumns: entry; execute; answer. "(4) repeat with increased array " entry:= Array with: (values copyFrom: 21 to: 90). s bindInputArrayByColumns: entry; execute; answer. "(5) repeat with decreased array " entry:= Array with: (values copyFrom: 91 to: 99). s bindInputArrayByColumns: entry; execute; answer. "(6) and finally basic insert again" entry:= Array with: values last. s bindInput: entry; execute; answer. "Check values" a:=s prepare: 'select * from tableTestField' ; blockFactor: 20; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | row first]. values = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Ok, now try positioned update " "Assume: rows order persist for following two SELECT queries (generally it not so)" c begin. a:=s prepare: 'select * from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. cursor := s cursorName. "only system-generated cursor names will appear with error messages" entry:=Array with: (values at: 5). s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: entry. a next; next; next. s2 execute ; answer. "update third row" c commit. "Check values" a:=s prepare: 'select * from tableTestField' ; execute; answer. (a next; next; next) first = entry first ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Parametrized select query" entry := Array with: (Array with: (values at: 10) with: (values at: 20)) "first row of parameters" with: (Array with: (values at: 30) with: (values at: 40)) with: (Array with: (values at: 50) with: (values at: 60)). "last row of parameters" s prepare: 'select * from tableTestField where testField between ? and ? '. s blockFactor: 7. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 33 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDatabaseLob: field values: values "Test database type" | s c entry a result cursor s2 | c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (id integer not null, testField <1s>) ' expandMacrosWith: field) ; execute; answer. "now insert values" s prepare: 'insert into tableTestField(id , testField) values (?, ?)'. "(1) basic insert" entry:= Array with: 1 with: values first. s bindInput: entry; execute; answer. "(2) repeat basic insert" entry:= Array with: 2 with: (values at: 2). s bindInput: entry; execute; answer. "(3) array insert" entry:= Array with: (3 to: 20) asArray with: (values copyFrom: 3 to: 20). s bindInputArrayByColumns: entry; execute; answer. "(4) repeat with increased array " entry:= Array with: (21 to: 90) asArray with: (values copyFrom: 21 to: 90). s bindInputArrayByColumns: entry; execute; answer. "(5) repeat with decreased array " entry:= Array with: (91 to: 99) asArray with: (values copyFrom: 91 to: 99). s bindInputArrayByColumns: entry; execute; answer. "(6) and finally basic insert again" entry:= Array with: 100 with: values last. s bindInput: entry; execute; answer. "Check values" a:=s prepare: 'select testField from tableTestField order by id'; blockFactor: 20; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | row first]. (values collect: [:x | x contents]) = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Ok, now try positioned update " c begin. a:=s prepare: 'select testField from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. cursor := s cursorName. entry:=Array with: (values at: 5). s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: entry. a next; next; next. s2 execute ; answer. "update third row" c commit. "Check values" a:=s prepare: 'select testField from tableTestField order by id' ; execute; answer. (a next; next; next) first = entry first contents ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Parametrized select query" entry := Array with: (Array with: 10 with: 20) "first row of parameters" with: (Array with: 30 with: 40) with: (Array with: 50 with: 60). "last row of parameters" s prepare: 'select testField from tableTestField where id between ? and ? '. s blockFactor: 7. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 33 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDatabaseLobFileRef2: field values: values "Test database type" | s c entry fr a fileNames counter | c:=self testConnection. [s := c getSession. "Select LOB value into file" fr := (DB2LOBFileReference for: 'testFile.data') overwriteFile. entry := #('Collection'). a:=s prepare: 'select testField from tableTestField where id = ? '; answerLOBAsFileRef: fr; bindInput: entry; execute; answer. a upToEnd. "Merge all contents" fr := (DB2LOBFileReference for: 'testMerge.data') appendToFile. a:=s prepare: 'select testField from tableTestField order by id'; blockFactor: 10; answerLOBAsFileRef: fr; bindInput: entry; execute; answer. a upToEnd. "Using compute: " fileNames := #('Apple.data' 'Orange.data' 'Cherry.data'). counter:=0. fr := (DB2LOBFileReference for: 'test.data') overwriteFile; "block argument - previous filename, block answer - new filename" compute: [:old | fileNames at: (counter:=counter+1) ]. a:=s prepare: 'select testField from tableTestField order by id '; answerLOBAsFileRef: fr; blockFactor: 1; bindInput: entry; execute; answer. a next;next;next. s cancel. ] ensure: [c disconnect]. Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDatabaseLobFileRef: field values: values "Test database type" | s c entry sz | sz:=values size. c:=self testConnection. c class trace: 'File Reference insert test'. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (id varchar(200) not null, testField <1s>) ' expandMacrosWith: field) ; execute; answer. "now insert values" s prepare: 'insert into tableTestField(id , testField) values (?, ?)'. "(1) basic insert" entry:= Array with: values first key with: values first value. s bindInput: entry; execute; answer. "(2) repeat basic insert" entry:= Array with: (values at: 2) key with: (values at: 2) value. s bindInput: entry; execute; answer. "(3) array insert" entry:= Array with: ((values copyFrom: 3 to: sz-1 ) collect: [:ax | ax key]) asArray with: ((values copyFrom: 3 to: sz-1 ) collect: [:ax | ax value]) asArray. s bindInputArrayByColumns: entry; execute; answer. "(4) and finally basic insert again" entry:= Array with: (values at: sz) key with: (values at: sz) value. s bindInput: entry; execute; answer. ] ensure: [c disconnect]. Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDatabaseLobNoBlock: field values: values "Test database type" | s c entry a result cursor s2 | c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (id integer not null, testField <1s>) ' expandMacrosWith: field) ; execute; answer. "now insert values" s prepare: 'insert into tableTestField(id , testField) values (?, ?)'. "(1) basic insert" entry:= Array with: 1 with: values first. s bindInput: entry; execute; answer. "(2) repeat basic insert" entry:= Array with: 2 with: (values at: 2). s bindInput: entry; execute; answer. "(3) array insert" entry:= Array with: (3 to: 20) asArray with: (values copyFrom: 3 to: 20). s bindInputArrayByColumns: entry; execute; answer. "(4) repeat with increased array " entry:= Array with: (21 to: 90) asArray with: (values copyFrom: 21 to: 90). s bindInputArrayByColumns: entry; execute; answer. "(5) repeat with decreased array " entry:= Array with: (91 to: 99) asArray with: (values copyFrom: 91 to: 99). s bindInputArrayByColumns: entry; execute; answer. "(6) and finally basic insert again" entry:= Array with: 100 with: values last. s bindInput: entry; execute; answer. "Check values" a:=s prepare: 'select testField from tableTestField order by id'; blockFactor: 1; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | row first]. (values collect: [:x | x contents]) = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Ok, now try positioned update " c begin. a:=s prepare: 'select testField from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. cursor := s cursorName. entry:=Array with: (values at: 5). s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: entry. a next; next; next. s2 execute ; answer. "update third row" c commit. "Check values" a:=s prepare: 'select testField from tableTestField order by id' ; execute; answer. (a next; next; next) first = entry first contents ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Parametrized select query" entry := Array with: (Array with: 10 with: 20) "first row of parameters" with: (Array with: 30 with: 40) with: (Array with: 50 with: 60). "last row of parameters" s prepare: 'select testField from tableTestField where id between ? and ? '. s blockFactor: 1. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 33 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDatabaseLocators: field values: values "Test database type" | s c entry a result cursor s2 | c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (id integer not null, testField <1s>) ' expandMacrosWith: field) ; execute; answer. "now insert values" c begin. s prepare: 'insert into tableTestField(id , testField) values (?, ?)'. values keysAndValuesDo: [:i :x | entry:= Array with: i with: x. s bindInput: entry; execute; answer]. c commit. "Check values" c begin. "LOB locators life-time limited current transaction only" a:=s prepare: 'select testField from tableTestField order by id'; answerLOBAsLocators; "get locators instead values" blockFactor: 20; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | | loc len | loc:=row first. len:=c getLOBLength: loc. c getLOBSubString: loc from: 1 length: len asLocator: false ]. (values collect: [:x | x contents]) = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. c commit. "Ok, now try positioned update " c begin. a:=s prepare: 'select testField from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. entry:=( a next; next; next) first. "get LOB locator from third row" a next; next. "move to row 5" cursor := s cursorName. s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: (Array with: entry). s2 execute ; answer. "update fifth row" c commit. "Well, now we copied LOB value from third to fifth row without transfer LOB value to client." "Parametrized select query" entry := Array with: (Array with: 10 with: 20) "first row of parameters" with: (Array with: 30 with: 40) with: (Array with: 50 with: 60). "last row of parameters" s prepare: 'select testField from tableTestField where id between ? and ? '. s blockFactor: 7. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 33 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> (locators) passed' expandMacrosWith: field). ^self
testDatabaseLocatorsNoBlock: field values: values "Test database type" | s c entry a result cursor s2 | c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (id integer not null, testField <1s>) ' expandMacrosWith: field) ; execute; answer. "now insert values" c begin. s prepare: 'insert into tableTestField(id , testField) values (?, ?)'. values keysAndValuesDo: [:i :x | entry:= Array with: i with: x. s bindInput: entry; execute; answer]. c commit. "Check values" c begin. "LOB locators life-time limited current transaction only" a:=s prepare: 'select testField from tableTestField order by id'; answerLOBAsLocators; "get locators instead values" blockFactor: 1; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | | loc len | loc:=row first. len:=c getLOBLength: loc. c getLOBSubString: loc from: 1 length: len asLocator: false ]. (values collect: [:x | x contents]) = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. c commit. "Ok, now try positioned update " c begin. a:=s prepare: 'select testField from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. entry:=( a next; next; next) first. "get LOB locator from third row" a next; next. "move to row 5" cursor := s cursorName. s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: (Array with: entry). s2 execute ; answer. "update fifth row" c commit. "Well, now we copied LOB value from third to fifth row without transfer LOB value to client." "Parametrized select query" entry := Array with: (Array with: 10 with: 20) "first row of parameters" with: (Array with: 30 with: 40) with: (Array with: 50 with: 60). "last row of parameters" s prepare: 'select testField from tableTestField where id between ? and ? '. s blockFactor: 1. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 33 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> (locators) passed' expandMacrosWith: field). ^self
testDatabaseString: field values: values "Test database type" | s c entry a result cursor s2 | c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (id integer not null, testField <1s>) ' expandMacrosWith: field) ; execute; answer. "now insert values" s prepare: 'insert into tableTestField(id , testField) values (?, ?)'. "(1) basic insert" entry:= Array with: 1 with: values first. s bindInput: entry; execute; answer. "(2) repeat basic insert" entry:= Array with: 2 with: (values at: 2). s bindInput: entry; execute; answer. "(3) array insert" entry:= Array with: (3 to: 20) asArray with: (values copyFrom: 3 to: 20). s bindInputArrayByColumns: entry; execute; answer. "(4) repeat with increased array " entry:= Array with: (21 to: 90) asArray with: (values copyFrom: 21 to: 90). s bindInputArrayByColumns: entry; execute; answer. "(5) repeat with decreased array " entry:= Array with: (91 to: 99) asArray with: (values copyFrom: 91 to: 99). s bindInputArrayByColumns: entry; execute; answer. "(6) and finally basic insert again" entry:= Array with: 100 with: values last. s bindInput: entry; execute; answer. "Check values" a:=s prepare: 'select testField from tableTestField order by id'; blockFactor: 20; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | row first]. values = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Ok, now try positioned update " c begin. a:=s prepare: 'select testField from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. cursor := s cursorName. entry:=Array with: (values at: 5). s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: entry. a next; next; next. s2 execute ; answer. "update third row" c commit. "Check values" a:=s prepare: 'select testField from tableTestField order by id' ; execute; answer. (a next; next; next) first = entry first ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Parametrized select query" entry := Array with: (Array with: 10 with: 20) "first row of parameters" with: (Array with: 30 with: 40) with: (Array with: 50 with: 60). "last row of parameters" s prepare: 'select testField from tableTestField where id between ? and ? '. s blockFactor: 7. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 33 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDatalink "self testDatalink" | field values s c entry a result cursor s2 | field := 'DATALINK LINKTYPE URL NO LINK CONTROL '. values := (1 to: 100 ) collect: [:x | DB2DataLink new scheme: 'http'; server: 'www.cincom.com'; pathOnly: ('/smalltalk/test_<1p>.jpg' expandMacrosWith: x) ; comment: 'My Comment']. values at: 100 put: ((values at: 100) comment: 'Largest'); "largest" at: 99 put: ((values at: 99) comment: nil); "smallest" at: 97 put: nil. "copied from testDatabaseField:values:" c:=self testConnection. [s := c getSession. [s prepare: 'drop table tableTestField'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: ('create table tableTestField (testField <1s>)' expandMacrosWith: field) ; execute; answer. "now insert values" s prepare: 'insert into tableTestField(testField) values (?)'. "(1) basic insert" entry:= Array with: values first. s bindInput: entry; execute; answer. "(2) repeat basic insert" entry:= Array with: (values at: 2). s bindInput: entry; execute; answer. "(3) array insert" entry:= Array with: (values copyFrom: 3 to: 20). s bindInputArrayByColumns: entry; execute; answer. "(4) repeat with increased array " entry:= Array with: (values copyFrom: 21 to: 90). s bindInputArrayByColumns: entry; execute; answer. "(5) repeat with decreased array " entry:= Array with: (values copyFrom: 91 to: 99). s bindInputArrayByColumns: entry; execute; answer. "(6) and finally basic insert again" entry:= Array with: values last. s bindInput: entry; execute; answer. "Check values" a:=s prepare: 'select * from tableTestField' ; blockFactor: 20; " 20 rows per fetch " execute; answer. result := a upToEnd collect: [:row | row first]. "Test getDLAttribute:" entry:=c getDLAttribute: #SQL_ATTR_DATALINK_COMMENT for: result first. entry = values first comment ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. values = result ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Ok, now try positioned update " c begin. a:=s prepare: 'select * from tableTestField for update' ; blockFactor: 1; "positioned update work only with blockFactor = 1" execute; answer. cursor := s cursorName. entry:=Array with: (values at: 5). s2:=c getSession prepare: 'update tableTestField set testField=? where current of ', cursor. s2 bindInput: entry. a next; next; next. s2 execute ; answer. "update third row" c commit. "Check values" a:=s prepare: 'select * from tableTestField' ; execute; answer. (a next; next; next) first = entry first ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. "Parametrized select query" entry := Array with: (Array with: (values at: 10) with: (values at: 20)) "first row of parameters" with: (Array with: (values at: 30) with: (values at: 40)) with: (Array with: (values at: 50) with: (values at: 60)). "last row of parameters" s prepare: 'select * from tableTestField where dlurlpath(testField) between dlurlpath(cast(? as datalink) ) and dlurlpath(cast(? as datalink)) '. s blockFactor: 7. s bindInputArray: entry ; execute. result:=OrderedCollection new. "We must get 3 result sets" [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 37 ifFalse: [^self error: ('Test for <1s> failed' expandMacrosWith: field)]. ] ensure: [c disconnect] . Transcript cr; show: ('Test for <1s> passed' expandMacrosWith: field). ^self
testDate "self testDate" | field values | field := 'date'. values := (1 to: 100 ) collect: [:x | Date newDay: x year: 2000 ]. values at: 100 put: (Date newDay: 365 year: 9999) ; "largest" at: 97 put: nil; at: 99 put: (Date newDay: 1 year: 100). "smallest - DB2 manual say 1-01-0001, but VW dates before 1 January 100 translate to dd-mm-20yy " self testDatabaseField: field values: values
testDecimal "self testDecimal" | field values | field := 'decimal(31,4)'. values := (1 to: 100 ) collect: [:x | x asFixedPoint: 4]. values at: 100 put: 123456789012345678901234567.8901s; "largest" at: 97 put: nil; at: 99 put: -123456789012345678901234567.8901s. "smallest" self testDatabaseField: field values: values
testDecimal2 "self testDecimal2" | field values | field := 'decimal(5,2)'. values := (1 to: 100 ) collect: [:x | x asFixedPoint: 2]. values at: 100 put: 123.45s; "largest" at: 97 put: nil; at: 99 put: -123.45s. "smallest" self testDatabaseField: field values: values
testDouble "self testDouble" | field values | field := 'double'. values := (1 to: 100 ) collect: [:x | x asDouble]. values at: 100 put: 1.79769d308 ; "largest" at: 99 put: -1.79769d308 ; "smallest" at: 98 put: -2.225d-307 ; "largest negative" at: 97 put: 2.225d-307; "smallest positive" at: 96 put: nil. self testDatabaseField: field values: values
testFloat "self testFloat" | field values | field := 'float'. values := (1 to: 100 ) collect: [:x | x asFloat]. values at: 100 put: 3.402e38 ; "largest" at: 99 put: -3.402e38 ; "smallest" at: 98 put: -1.175e-37 ; "largest negative" at: 97 put: 1.175e-37; "smallest positive" at: 96 put: nil. self testDatabaseField: field values: values
testInteger "self testInteger" | field values | field := 'integer'. values := (1 to: 100 ) asArray. values at: 100 put: 2147483647; "largest" at: 99 put: -2147483648; "smallest" at: 97 put: nil. self testDatabaseField: field values: values
testTime "self testTime" | field values | field := 'time'. values := (1 to: 100 ) collect: [:x | Time fromSeconds: x ]. values at: 100 put: (Time readFrom: '23:59:59' readStream) ; "largest - in DB2 24:00:00" at: 97 put: nil; at: 99 put: (Time readFrom: '00:00:00' readStream) . "smallest " self testDatabaseField: field values: values
testTimestamp "self testTimestamp" | field values | field := 'timestamp'. values := (1 to: 100 ) collect: [:x | Timestamp fromSeconds: x ]. values at: 100 put: (Timestamp readFrom: '12/31/9999 23:59:59' readStream) ; "largest - in DB2 9999-12-31 24:00:00.000000" at: 97 put: nil; at: 99 put: (Timestamp readFrom: '1/1/100 00:00:00' readStream ). "smallest " self testDatabaseField: field values: values
</methods> <methods> DB2EXDITest class <category>instance creation</category>
id: anInteger name: aString ^self new id: anInteger name: aString
</methods> <methods> DB2EXDITest class <category>services</category>
initializeTestDatabase "self initializeTestDatabase" | connection | IsThreaded:=false. connection := self testConnection. self initializeTestSchemaUsing: connection. self initializeTestDataUsing: connection. connection disconnect. ^self
initializeTestDataUsing: aConnection "^self I populate the test database via aConnection." | session | session := aConnection getSession. session prepare: 'INSERT INTO table1 (name) VALUES (''First''),(''Second''),(''Third''), (''Fourth''),(''Fifth''),(''Sixth''), (''Seventh''),(''Eighth''),(''Ninth''), (''Tenth''),(''Eleventh''),(''Twelfth'')' ; execute; answer. session prepare: 'INSERT INTO table2 (id, name) VALUES (1,''One''), (2,''Two''), (3,''Three''), (4,''Four''), (5,''Five''), (6,''Six''), (7,''Seven''), (8,''Eight''), (9,''Nine''), (10,''Ten''), (11,''Eleven''), (12,''Twelve'')' ; execute; answer. session prepare: 'insert into tableAll( varcharField , varcharBitField , charField , charBitField , longvarcharField , longvarcharBitField, integerField , smallintField , bigintField , decimalField , decimalField2 , floatField , doubleField , dateField , timeField , timestampField , blobField , clobField , datalinkField ) values ( ''Text'', X''01020304'', ''Char'', X''05060708'', ''Long var char '', ''1234567890'', 17000000, 123, -12345890123456789, 1234.56, 123456456789012345678901234.1234, 123.4E-1, 123456.789E+1, ''2000-09-27'', ''14:53:42'', ''2000-09-25 14:54:55'', BLOB(''BLOB''), ''CLOB one more'', dlvalue(''http://www.cincom.com/smalltalk/test_1.jpg'', ''URL'', ''Comment of datalink'') )'; execute; answer.
initializeTestSchemaUsing: aConnection "Initialize the test schema using aConnection." | session | session := aConnection getSession. [session prepare: 'drop table table1'; execute; answer] on: aConnection class externalDatabaseErrorSignal do: [:e|]. [session prepare: 'drop table table2'; execute; answer] on: aConnection class externalDatabaseErrorSignal do: [:e|]. [session prepare: 'drop table tableAll'; execute; answer] on: aConnection class externalDatabaseErrorSignal do: [:e|]. session prepare: 'create table table1 (name varchar(30))'; execute; answer. session prepare: 'create table table2 (id integer, name varchar(30))'; execute; answer. "all supported data types" session prepare: 'create table tableAll ( varcharField varchar(4), varcharBitField varchar(300) for bit data, charField char(4), charBitField char(4) for bit data, longvarcharField long varchar, longvarcharBitField long varchar for bit data, integerField integer, smallintField smallint, bigintField bigint, decimalField decimal(6,2), decimalField2 decimal(31,4), floatField float, doubleField double, dateField date, timeField time, timestampField timestamp, blobField blob(5000), clobField clob(3000), datalinkField datalink linktype URL no link control )'; execute; answer. ^self
setStress: aValue Stress:=aValue
testConnection | connection | connection := (IsThreaded ifTrue: [#{DB2ThreadedConnection}] ifFalse: [#{DB2Connection}]) value new. connection username: self testUsernameString; password: self testPasswordString; environment: self testEnvironmentString. connection connect. ^connection
testEnvironmentString "Specify database name" ^'sample'
testPasswordString "Specify password or leave empty string for local instance of DB2" ^''
testStringLiteral ^'0123456789abcdefghijklmnopqrstubwxyzABCDEFGHIJKLMNOPQRSTUBWXYZabcdefghijklmnopqrstubwxyzABCDEFGHIJKL'
testUsernameString "Specify username or leave empty string for local instance of DB2" ^''
</methods> <methods> DB2EXDITest class <category>test-parameters</category>
paramTest01 "self paramTest01" | c s expr entry a result | expr:='select id,name from table2 where id between ? and ? '. c:= self testConnection. [s:=c getSession. c begin. "simple binding" a:=s prepare: expr; bindInput: #(8 9); execute; answer. a upToEnd size = 2 ifFalse: [^self error: ('paramTest01 failed' )]. "parameter array binding (by column)" s bindInputArrayByColumns: #(#(1 2) #(3 5)); execute. result :=OrderedCollection new. [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. "we are must be get two result sets: BETWEEN 1 AND 3 and BETWEEN 2 AND 5" result size = 7 ifFalse: [^self error: ('paramTest01 failed' )]. "parameter array binding (by row)" s bindInputArray: #(#(1 5 ) #(1 3 ) #(5 9)); execute. result :=OrderedCollection new. [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. "we are must be get three result sets: BETWEEN 1 AND 5 and BETWEEN 1 AND 3 and BETWEEN 5 AND 9" result size = 13 ifFalse: [^self error: ('paramTest01 failed' )]. expr:='select id,name from table2 where id < ? '. entry:= Array with: (self new id: 1) with: (self new id: 2) with: (self new id: 3) with: (self new id: 4). a:=s prepare: expr; bindInputArray: entry; execute. result :=OrderedCollection new. [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. result size = 6 ifFalse: [^self error: ('paramTest01 failed' )]. c rollback] ensure: [c disconnect]. Transcript cr; show: ('paramTest01 passed' ).
paramTest02 "self paramTest02" | c s entry a result | c:= self testConnection. [s:=c getSession. c begin. "simple binding" a:=s prepare: 'insert into table2 values(?, ?)'; bindInput: #(100 'a String'); execute. "parameter array binding (by column)" s bindInputArrayByColumns: #(#(101 102 103) #('Red ' 'red' 'roses' )); execute. "parameter array binding (by row)" s bindInputArray: #(#(110 'Velvet' ) #(111 'Green' )); execute. entry:= Array with: (self id: 200 name: 'Broadsword') with: (self id: 201 name: 'and' ) with: (self id: 202 name: 'the') with: (self id: 203 name: 'Beast'). s bindInputArray: entry; execute; "Finally - send answer" answer. a:=s prepare: 'select * from table2 where id>=100' ;execute; answer. result:=a upToEnd. result size = 10 ifFalse: [^self error: ('paramTest02 failed' )]. c rollback] ensure: [c disconnect]. Transcript cr; show: ('paramTest02 passed' ).
testAllParameters self paramTest01; paramTest02; testRebind1; testRebind2; testRebind3; testRebind4; testRebind5
testRebind1 "self testRebind1" | c s | c:=self testConnection. [s:=c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: 'create table testRebind (testField varchar(50))' ; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: #('String') ; execute; bindInput: #(123) ; "Integer" execute; bindInput: #(123456789012345); "LargeInteger" execute; bindInput: (Array with: Date today); execute; bindInput: #( 1.234d23) ; "Double" execute; bindInput:(Array with: Time now); execute; bindInput:#(nil); execute; bindInput:(Array with: Timestamp now); execute; bindInput: #('Very Long String :-)') ; execute; answer. ] ensure: [c disconnect]. Transcript cr; show: ('testRebind1 passed' ).
testRebind2 "self testRebind2" | c s | c:=self testConnection. [s:=c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: 'create table testRebind (testField double)' ; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: #(123) ; execute; bindInput: #(123456789012345); execute; bindInput:#(nil); execute; bindInput: #( 1.234d23) ; execute; bindInput: #(123e13) ; execute; bindInput: #(123.456s3) ; execute; answer. ] ensure: [c disconnect]. Transcript cr; show: ('testRebind2 passed' ).
testRebind3 "self testRebind3" | c s | c:=self testConnection. [s:=c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: 'create table testRebind (testField timestamp)' ; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: (Array with: Time now); execute; bindInput: (Array with: Date today) ; execute; bindInput: (Array with: Timestamp now) ; execute; answer. ] ensure: [c disconnect]. Transcript cr; show: ('testRebind3 passed' ).
testRebind4 "self testRebind4" | c s | c:=self testConnection. [s:=c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: 'create table testRebind (testField varchar(50))' ; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: #(#('String')) ; execute; bindInput: #(#(123)) ; "Integer" execute; bindInput: #(#(123456789012345)); "LargeInteger" execute; bindInput: (Array with: (Array with: Date today)); execute; bindInput: #( #(1.234d23)) ; "Double" execute; bindInput: (Array with: (Array with: Time now)); execute; bindInput:#(#(nil)); execute; bindInput:(Array with: (Array with: Timestamp now)); execute; bindInput: #(#('Very Long String :-)')) ; execute; answer. ] ensure: [c disconnect]. Transcript cr; show: ('testRebind4 passed' ).
testRebind5 "self testRebind5" | c s | c := self testConnection. [| r | s := c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e | ]. s prepare: 'create table testRebind (testField double)'; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: #(#(nil nil)); execute; execute; bindInput: #(#(nil 1.234d23)); execute; bindInput: #(#(nil nil)); execute; bindInput: #(#(789d2 nil)); execute; answer. "Double" r := s prepare: 'select * from testRebind where testField is not Null'; execute; answer. r upToEnd size = 2 ifFalse: [^self error: 'testRebind5 failed !']] ensure: [c disconnect]. Transcript cr; show: 'testRebind5 passed'
testRebindDecimalCharBug "Decimal / Char incompatibility (bug ?) Code below will be failed " "self testRebindDecimalCharBug" | c s | c:=self testConnection. [s:=c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: 'create table testRebind (testField varchar(50))' ; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: #( 1.234s3) ; execute; answer. ] ensure: [c disconnect]
testRebindFloatCharBug "Float / Char incompatibility (bug ?) Code below will be failed " "self testRebindFloatCharBug" | c s | c:=self testConnection. [s:=c getSession. [s prepare: 'drop table testRebind'; execute; answer] on: c class externalDatabaseErrorSignal do: [:e|]. s prepare: 'create table testRebind (testField varchar(50))' ; execute; answer. s prepare: 'insert into testRebind values(?)'. s bindInput: #( 1.234e2) ; execute; answer. ] ensure: [c disconnect]
</methods> <methods> DB2EXDITest class <category>testing</category>
runAllTests "self runAllTests" IsThreaded := false. Transcript cr; show: 'Begin EXDI tests'. self initializeTestDatabase; testAllFields; testAllQueries; testAllParameters; yourself. "testSPCall;" Transcript cr; show: 'End EXDI tests'
</methods> <methods> DB2EXDITest class <category>test-threaded</category>
runAllTestsThreaded "self runAllTestsThreaded" Transcript cr; show: 'Begin EXDI Threaded tests'. self initializeTestDatabase. [IsThreaded:=true. self testAllFields; testAllQueries; testAllParameters; "testSPCall;" yourself. Transcript cr; show: 'End EXDI Threaded tests'] ensure: [IsThreaded:=false].
testThreaded01 "self testThreaded01" | aBlock b1 b2 b3 sem | sem := Semaphore forMutualExclusion. aBlock := [:tableName | | conn sess ansStrm | conn := (#{DB2ThreadedConnection} valueOrDo: [self error: 'DB2ThreadedEXDI not in image']) new. conn username: self testUsernameString; password: self testPasswordString; environment: self testEnvironmentString. conn connect. sess := conn getSession. sess prepare: 'select * from ' , tableName , ' fetch first 20 rows only'. sess execute. ansStrm := sess answer. ansStrm == #noMoreAnswers ifFalse: [[ansStrm atEnd] whileFalse: [| row | row := ansStrm next. (Delay forMilliseconds: 10) wait. "On AMD 700 too fast" sem critical: [Transcript show: tableName , ': '. Transcript show: row printString; cr]]]. conn disconnect]. b1 := aBlock newProcessWithArguments: #('table1'). b2 := aBlock newProcessWithArguments: #('table2'). b3 := aBlock newProcessWithArguments: #('TABLEALL'). b1 priority: 30. b2 priority: 30. b3 priority: 30. b1 resume. b2 resume. b3 resume
testThreaded02 "Cancel long-running query" "self testThreaded02" | aBlock b1 sem conn sess ansStrm| sem:= Semaphore forMutualExclusion. conn := (#{DB2ThreadedConnection} valueOrDo: [^nil]) new. conn username: self testUsernameString; password: self testPasswordString; environment: self testEnvironmentString. conn connect. aBlock := [ |row | sess := conn getSession. sess prepare: 'with r(num) as (values 1 union all select num+1 from r where num<1000000) select count(*) from r '. [sess execute. ansStrm := sess answer. row := ansStrm next] on: conn class unableToFetchDataSignal do: [:e | Transcript cr; show: 'Execution cancelled ', e messageText ; cr ]. sem critical: [Transcript show: 'The End - '; show: row printString; cr]. conn disconnect]. b1 := aBlock newProcess. b1 priority: 30. b1 resume. "Wait some seconds" (Delay forSeconds: 3) wait. "Terminate now" sess cancelExecution.
</methods> <methods> DB2EXDITest class <category>test-simple</category>
test01 "Connect to a DB2 database and then disconnect." "self test01" | connection | connection := DB2Connection new. connection username: self testUsernameString; password: self testPasswordString; environment: self testEnvironmentString. connection connect. connection disconnect. ^self
test02 "Use the default test connection and run simple query." "self test02" | connection session | connection := self testConnection. session := connection getSession. session prepare: 'values(1 + 3)'. session execute. session answer. connection disconnect. ^self
test03 "Repeatedly run query" "self test04" | connection session | connection := self testConnection. session := connection getSession. session prepare: 'values(5 + 6)'. session execute. session answer upToEnd. "one more" session execute. session answer. connection disconnect. ^self
test04 "Repeatedly run query" "self test04" | connection session | connection := self testConnection. session := connection getSession. session prepare: 'values(5 + 6)'. session execute. session answer upToEnd. "one more" session prepare: 'values(7 + 8)'. session execute. session answer. connection disconnect. ^self
test05 "Test variables in queries." "self test05" | connection session | connection := self testConnection. session := connection getSession. [session prepare: 'drop table test06'; execute; answer] on: connection class externalDatabaseErrorSignal do: [:e | ]. session prepare: 'create table test06 (id integer, name varchar(20))'. session execute ; answer. session prepare: 'INSERT INTO test06 (id, name) VALUES(?, ?)'. #(#(1 'Apple') #(2 'Orange') #(3 'Cherry')) do: [:entry | session bindInput: entry; execute; answer]. session prepare: 'drop table exdiTest06'; execute; answer. connection disconnect. ^self
test06 "Test variables in queries." "self test06" | connection session | connection := self testConnection. session := connection getSession. [session prepare: 'drop table test06'; execute; answer] on: connection class externalDatabaseErrorSignal do: [:e | ]. session prepare: 'create table test06 (id integer, name varchar(20))'. session execute ; answer. session prepare: 'INSERT INTO test06 (id, name) VALUES(?, ?)'. #(#(1 'Apple') #(2 'Orange') #(3 'Cherry')) do: [:entry | session bindInput: entry; execute; answer]. session prepare: 'drop table test06'; execute; answer. connection disconnect. ^self
</methods> <methods> DB2EXDITest class <category>test-query</category>
testAllQueries self testFetch01; testFetch02
testFetch01 "Currently you must send 'prepare:' to session if blockFactor value is changed, later this restriction will be removed" "self testFetch01" | c s expr a result testName | testName:='testFetch01'. expr:='select id,name from table2 where id between ? and ? '. c:= self testConnection. [s:=c getSession. c begin. "simple fetch" a:=s prepare: expr; bindInput: #(8 9); execute; answer. a upToEnd size = 2 ifFalse: [^self error: (testName, ' failed' )]. "simple fetch (no result set)" a:=s prepare: expr; bindInput: #(-1 -2); execute; answer. a upToEnd size = 0 ifFalse: [^self error: (testName, ' failed' )]. "use blockFactor" a:=s prepare: expr; blockFactor: 2; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "increase blockFactor" a:=s prepare: expr; blockFactor: 20; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "decrease blockFactor" a:=s prepare: expr; blockFactor: 10; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "parameter array binding (by column)" s prepare: expr; bindInputArrayByColumns: #(#(1 2) #(3 5)); blockFactor: 5; execute. result :=OrderedCollection new. [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. "we are must be get two result sets: BETWEEN 1 AND 3 and BETWEEN 2 AND 5" result size = 7 ifFalse: [^self error: (testName, ' failed' )]. c rollback] ensure: [c disconnect]. Transcript cr; show: (testName, ' passed' ).
testFetch02 "Currently you must send 'prepare:' to session if blockFactor value is changed, later this restriction will be removed" "Use deferCursorClosing" "self testFetch02" | c s expr a result testName | testName:='testFetch02'. expr:='select id,name from table2 where id between ? and ? '. c:= self testConnection. [s:=c getSession. s deferCursorClosing. c begin. "simple fetch" a:=s prepare: expr; bindInput: #(8 9); execute; answer. a upToEnd size = 2 ifFalse: [^self error: (testName, ' failed' )]. "simple fetch (no result set)" a:=s prepare: expr; bindInput: #(-1 -2); execute; answer. a upToEnd size = 0 ifFalse: [^self error: (testName, ' failed' )]. "use blockFactor" a:=s prepare: expr; blockFactor: 2; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "increase blockFactor" a:=s prepare: expr; blockFactor: 20; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "decrease blockFactor" a:=s prepare: expr; blockFactor: 10; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "parameter array binding (by column)" s prepare: expr; bindInputArrayByColumns: #(#(1 2) #(3 5)); blockFactor: 5; execute. result :=OrderedCollection new. [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. "we are must be get two result sets: BETWEEN 1 AND 3 and BETWEEN 2 AND 5" result size = 7 ifFalse: [^self error: (testName, ' failed' )]. c rollback] ensure: [c disconnect]. Transcript cr; show: (testName, ' passed' ).
</methods> <methods> DB2EXDITest class <category>test-stress</category>
testAllStress "self testAllStress" self setStress: 100. self "testIntegerStress;" testFetchStress; testParamStress.
testDatalinkStress "Memory leak detection :)" "self testDatalinkStress" | c | c:=self testConnection. 1 to: Stress do: [:i | ObjectMemory quickGC. Transcript cr; show: 'Step ', i displayString. self testDatalink]. c disconnect
testFetchStress "Memory leak detection :)" "self testFetchStress" | c | c:=self testConnection. 1 to: Stress do: [:i | ObjectMemory quickGC. Transcript cr; show: 'Step ', i displayString. self testFetch01; testFetchStress02: c.]. c disconnect
testFetchStress02: c "Currently you must send 'prepare:' to session if blockFactor value is changed, later this restriction will be removed" "Use deferCursorClosing" "self testFetch02" | s expr a result testName | testName:='testFetch02'. expr:='select id,name from table2 where id between ? and ? '. [s:=c getSession. s deferCursorClosing. c begin. "simple fetch" a:=s prepare: expr; bindInput: #(8 9); execute; answer. a upToEnd size = 2 ifFalse: [^self error: (testName, ' failed' )]. "simple fetch (no result set)" a:=s prepare: expr; bindInput: #(-1 -2); execute; answer. a upToEnd size = 0 ifFalse: [^self error: (testName, ' failed' )]. "use blockFactor" a:=s prepare: expr; blockFactor: 2; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "increase blockFactor" a:=s prepare: expr; blockFactor: 20; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "decrease blockFactor" a:=s prepare: expr; blockFactor: 10; bindInput: #(1 10); execute; answer. a upToEnd size = 10 ifFalse: [^self error: (testName, ' failed' )]. "parameter array binding (by column)" s prepare: expr; bindInputArrayByColumns: #(#(1 2) #(3 5)); blockFactor: 5; execute. result :=OrderedCollection new. [(a := s answer) == #noMoreAnswers] whileFalse: [ a == #noAnswerStream ifFalse: [result addAll:a upToEnd]]. "we are must be get two result sets: BETWEEN 1 AND 3 and BETWEEN 2 AND 5" result size = 7 ifFalse: [^self error: (testName, ' failed' )]. c rollback] ifCurtailed: [c disconnect]. Transcript cr; show: (testName, ' passed' ).
testIntegerStress "Memory leak detection :)" "self testIntegerStress" | c | c:=self testConnection. 1 to: Stress do: [:i | ObjectMemory quickGC. Transcript cr; show: 'Step ', i displayString. self testInteger]. c disconnect
testParamStress "Memory leak detection :)" "self testParamStress" | c | c:=self testConnection. 1 to: Stress do: [:i | ObjectMemory quickGC. Transcript cr; show: 'Step ', i displayString. self paramTest01; paramTest02.]. c disconnect
</methods> <methods> DB2EXDITest class <category>test-blob</category>
testBlob01 "Simply fetch LOB values" "self testBlob01" | connection session a r | connection := self testConnection. session := connection getSession. a := session prepare: 'select clobField, blobField from tableAll fetch first row only '; execute ; answer. r := a upToEnd first. (r first = 'CLOB one more' and: [r last = #[66 76 79 66] ]) ifFalse: [self error: 'Test failed']. connection disconnect
testBlob02 "Simply insert LOB values" "self testBlob02" | connection session | connection := self testConnection. session := connection getSession. connection begin. [session prepare: 'drop table blobTable'; execute; answer] on: connection class externalDatabaseErrorSignal do: [:e|]. session prepare: 'create table blobTable (blobField varchar(32) for bit data)'; execute; answer. session prepare: 'insert into blobTable(blobField) values(?) '; bindInput: #( #[1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9] ); execute ; answer. connection rollback. connection disconnect
testBlob03 "Need SAMPLE database" "self testBlob03 " | c s a loc len pos res | c:=self testConnection. s:=c getSession. c begin. a:=s prepare: 'select resume from avb.emp_resume fetch first 3 rows only'; answerLOBAsLocators; execute; answer. [a atEnd] whileFalse: [loc:= a next first. len:=c getLOBLength: loc. pos:=c getLOBPosition: loc search: 'Interests' from: 1. pos > 0 ifTrue: [res := (c getLOBLength: (c getLOBSubString: loc from: pos length: len-pos asLocator: true )). Transcript cr; show: res printString ]]. c rollback. c disconnect
testBlob04 "Need SAMPLE database" "self testBlob04" | c s a loc len pos res | c:=self testConnection. s:=c getSession. c begin. a:=s prepare: 'select resume from avb.emp_resume fetch first 3 rows only'; answerLOBAsLocators; execute; answer. [a atEnd] whileFalse: [loc:= a next first. len:=c getLOBLength: loc. pos:=c getLOBPosition: loc search: 'Interests' from: 1. pos > 0 ifTrue: [res:=(c getLOBSubString: loc from: pos length: len-pos asLocator: false ). Transcript cr; show: res] ]. c rollback. c disconnect
</methods> "Imported Classes:" self error: 'Attempting to file-in parcel imports. Choose terminate or close' <class> <name>Object</name> <environment>Core</environment> <super></super> <private>false</private> none <imports></imports> <category>Kernel-Objects</category> <attributes> <package>Kernel-Objects</package> </attributes> </class>