1 // Copyright (c) 1999-2018 David Muse 2 // See the file COPYING for more information. 3 4 import com.firstworks.sqlrelay.SQLRConnection; 5 import com.firstworks.sqlrelay.SQLRCursor; 6 7 8 class krb { 9 checkSuccess(String value, String success, int length)10 private static void checkSuccess(String value, String success, int length) { 11 12 if (success==null) { 13 if (value==null) { 14 System.out.print("success "); 15 return; 16 } else { 17 System.out.print(value+"!="+success+" "); 18 System.out.print("failure "); 19 System.exit(1); 20 } 21 } 22 23 if (value.regionMatches(0,success,0,length)) { 24 System.out.print("success "); 25 } else { 26 System.out.print(value+"!="+success+" "); 27 System.out.print("failure "); 28 System.exit(1); 29 } 30 } 31 checkSuccess(String value, String success)32 private static void checkSuccess(String value, String success) { 33 34 if (success==null) { 35 if (value==null) { 36 System.out.print("success "); 37 return; 38 } else { 39 System.out.print(value+"!="+success+" "); 40 System.out.print("failure "); 41 System.exit(1); 42 } 43 } 44 45 if (value.equals(success)) { 46 System.out.print("success "); 47 } else { 48 System.out.print(value+"!="+success+" "); 49 System.out.print("failure "); 50 System.exit(1); 51 } 52 } 53 checkSuccess(byte[] value, String success, int length)54 private static void checkSuccess(byte[] value, String success, int length) { 55 56 if (success==null) { 57 if (value==null) { 58 System.out.print("success "); 59 return; 60 } else { 61 System.out.print("failure "); 62 63 64 System.exit(1); 65 } 66 } 67 68 byte[] successvalue=success.getBytes(); 69 70 for (int index=0; index<length; index++) { 71 if (value[index]!=successvalue[index]) { 72 System.out.println("failure "); 73 System.exit(1); 74 } 75 } 76 77 System.out.println("success "); 78 } 79 checkSuccess(long value, int success)80 private static void checkSuccess(long value, int success) { 81 82 if (value==success) { 83 System.out.println("success "); 84 } else { 85 System.out.println("failure "); 86 87 88 System.exit(1); 89 } 90 } 91 checkSuccess(double value, double success)92 private static void checkSuccess(double value, double success) { 93 94 if (value==success) { 95 System.out.println("success "); 96 } else { 97 System.out.println("failure "); 98 99 100 System.exit(1); 101 } 102 } 103 checkSuccess(boolean value, int success)104 private static void checkSuccess(boolean value, int success) { 105 106 if (((value)?1:0)==success) { 107 System.out.println("success "); 108 } else { 109 System.out.println("failure "); 110 111 112 System.exit(1); 113 } 114 } 115 main(String[] args)116 public static void main(String[] args) { 117 118 String dbtype; 119 String[] bindvars={"1","2","3","4","5"}; 120 String[] bindvals={"4","testchar4","testvarchar4","01-JAN-2004","testlong4"}; 121 String[] subvars={"var1","var2","var3"}; 122 String[] subvalstrings={"hi","hello","bye"}; 123 long[] subvallongs={1,2,3}; 124 double[] subvaldoubles={10.55,10.556,10.5556}; 125 int[] precs={4,5,6}; 126 int[] scales={2,3,4}; 127 String clobvar; 128 long clobvarlength; 129 byte[] blobvar; 130 long blobvarlength; 131 long numvar; 132 String stringvar; 133 double floatvar; 134 String[] cols; 135 String[] fields; 136 short port; 137 String socket; 138 short id; 139 String filename; 140 String[] arraybindvars={"var1","var2","var3","var4","var5"}; 141 String[] arraybindvals={"7","testchar7","testvarchar7","01-JAN-2007","testlong7"}; 142 long[] fieldlens; 143 144 145 String cert="/usr/local/firstworks/etc/client.pem"; 146 String ca="/usr/local/firstworks/etc/ca.pem"; 147 148 149 // instantiation 150 SQLRConnection con=new SQLRConnection("sqlrelay", 151 (short)9000, 152 "/tmp/test.socket", 153 null,null,0,1); 154 SQLRCursor cur=new SQLRCursor(con); 155 con.enableKerberos(null,null,null); 156 157 // get database type 158 System.out.println("IDENTIFY: "); 159 checkSuccess(con.identify(),"oracle"); 160 System.out.println(); 161 162 // ping 163 System.out.println("PING: "); 164 checkSuccess(con.ping(),1); 165 System.out.println(); 166 167 System.out.println("BIND VALIDATION: "); 168 cur.sendQuery("drop table testtable1"); 169 cur.sendQuery("create table testtable1 (col1 varchar2(20), col2 varchar2(20), col3 varchar2(20))"); 170 cur.prepareQuery("insert into testtable1 values ($(var1),$(var2),$(var3))"); 171 cur.inputBind("var1",1); 172 cur.inputBind("var2",2); 173 cur.inputBind("var3",3); 174 cur.substitution("var1",":var1"); 175 checkSuccess(cur.validBind("var1"),1); 176 checkSuccess(cur.validBind("var2"),0); 177 checkSuccess(cur.validBind("var3"),0); 178 checkSuccess(cur.validBind("var4"),0); 179 System.out.println(); 180 cur.substitution("var2",":var2"); 181 checkSuccess(cur.validBind("var1"),1); 182 checkSuccess(cur.validBind("var2"),1); 183 checkSuccess(cur.validBind("var3"),0); 184 checkSuccess(cur.validBind("var4"),0); 185 System.out.println(); 186 cur.substitution("var3",":var3"); 187 checkSuccess(cur.validBind("var1"),1); 188 checkSuccess(cur.validBind("var2"),1); 189 checkSuccess(cur.validBind("var3"),1); 190 checkSuccess(cur.validBind("var4"),0); 191 checkSuccess(cur.executeQuery(),1); 192 cur.sendQuery("drop table testtable1"); 193 System.out.println(); 194 195 // drop existing table 196 cur.sendQuery("drop table testtable"); 197 198 System.out.println("CREATE TEMPTABLE: "); 199 checkSuccess(cur.sendQuery("create table testtable (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long, testclob clob, testblob blob)"),1); 200 System.out.println(); 201 202 System.out.println("INSERT: "); 203 checkSuccess(cur.sendQuery("insert into testtable values (1,'testchar1','testvarchar1','01-JAN-2001','testlong1','testclob1',empty_blob())"),1); 204 System.out.println(); 205 206 System.out.println("AFFECTED ROWS: "); 207 checkSuccess(cur.affectedRows(),1); 208 System.out.println(); 209 210 System.out.println("BIND BY POSITION: "); 211 cur.prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)"); 212 checkSuccess(cur.countBindVariables(),7); 213 cur.inputBind("1",2); 214 cur.inputBind("2","testchar2"); 215 cur.inputBind("3","testvarchar2"); 216 cur.inputBind("4","01-JAN-2002"); 217 cur.inputBind("5","testlong2"); 218 cur.inputBindClob("6","testclob2",9); 219 cur.inputBindBlob("7",(new String("testblob2")).getBytes(),9); 220 checkSuccess(cur.executeQuery(),1); 221 cur.clearBinds(); 222 cur.inputBind("1",3); 223 cur.inputBind("2","testchar3"); 224 cur.inputBind("3","testvarchar3"); 225 cur.inputBind("4","01-JAN-2003"); 226 cur.inputBind("5","testlong3"); 227 cur.inputBindClob("6","testclob3",9); 228 cur.inputBindBlob("7",(new String("testblob3")).getBytes(),9); 229 checkSuccess(cur.executeQuery(),1); 230 System.out.println(); 231 232 System.out.println("ARRAY OF BINDS BY POSITION: "); 233 cur.clearBinds(); 234 cur.inputBinds(bindvars,bindvals); 235 cur.inputBindClob("var6","testclob4",9); 236 cur.inputBindBlob("var7", 237 (new String("testblob4")).getBytes(),9); 238 checkSuccess(cur.executeQuery(),1); 239 System.out.println(); 240 241 System.out.println("BIND BY NAME: "); 242 cur.prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)"); 243 cur.inputBind("var1",5); 244 cur.inputBind("var2","testchar5"); 245 cur.inputBind("var3","testvarchar5"); 246 cur.inputBind("var4","01-JAN-2005"); 247 cur.inputBind("var5","testlong5"); 248 cur.inputBindClob("var6","testclob5",9); 249 cur.inputBindBlob("var7", 250 (new String("testblob5")).getBytes(),9); 251 checkSuccess(cur.executeQuery(),1); 252 cur.clearBinds(); 253 cur.inputBind("var1",6); 254 cur.inputBind("var2","testchar6"); 255 cur.inputBind("var3","testvarchar6"); 256 cur.inputBind("var4","01-JAN-2006"); 257 cur.inputBind("var5","testlong6"); 258 cur.inputBindClob("var6","testclob6",9); 259 cur.inputBindBlob("var7", 260 (new String("testblob6")).getBytes(),9); 261 checkSuccess(cur.executeQuery(),1); 262 System.out.println(); 263 264 System.out.println("ARRAY OF BINDS BY NAME: "); 265 cur.clearBinds(); 266 cur.inputBinds(arraybindvars,arraybindvals); 267 cur.inputBindClob("var6","testclob7",9); 268 cur.inputBindBlob("var7", 269 (new String("testblob7")).getBytes(),9); 270 checkSuccess(cur.executeQuery(),1); 271 System.out.println(); 272 273 System.out.println("BIND BY NAME WITH VALIDATION: "); 274 cur.clearBinds(); 275 cur.inputBind("var1",8); 276 cur.inputBind("var2","testchar8"); 277 cur.inputBind("var3","testvarchar8"); 278 cur.inputBind("var4","01-JAN-2008"); 279 cur.inputBind("var5","testlong8"); 280 cur.inputBindClob("var6","testclob8",9); 281 cur.inputBindBlob("var7", 282 (new String("testblob8")).getBytes(),9); 283 cur.inputBind("var9","junkvalue"); 284 cur.validateBinds(); 285 checkSuccess(cur.executeQuery(),1); 286 System.out.println(); 287 288 System.out.println("OUTPUT BIND BY NAME: "); 289 cur.prepareQuery("begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; end;"); 290 cur.defineOutputBindInteger("numvar"); 291 cur.defineOutputBindString("stringvar",10); 292 cur.defineOutputBindDouble("floatvar"); 293 checkSuccess(cur.executeQuery(),1); 294 numvar=cur.getOutputBindInteger("numvar"); 295 stringvar=cur.getOutputBindString("stringvar"); 296 floatvar=cur.getOutputBindDouble("floatvar"); 297 checkSuccess(numvar,1); 298 checkSuccess(stringvar,"hello"); 299 checkSuccess(floatvar,2.5); 300 System.out.println(); 301 302 System.out.println("OUTPUT BIND BY POSITION: "); 303 cur.clearBinds(); 304 cur.defineOutputBindInteger("1"); 305 cur.defineOutputBindString("2",10); 306 cur.defineOutputBindDouble("3"); 307 checkSuccess(cur.executeQuery(),1); 308 numvar=cur.getOutputBindInteger("1"); 309 stringvar=cur.getOutputBindString("2"); 310 floatvar=cur.getOutputBindDouble("3"); 311 checkSuccess(numvar,1); 312 checkSuccess(stringvar,"hello"); 313 checkSuccess(floatvar,2.5); 314 System.out.println(); 315 316 System.out.println("OUTPUT BIND BY NAME WITH VALIDATION: "); 317 cur.clearBinds(); 318 cur.defineOutputBindInteger("numvar"); 319 cur.defineOutputBindString("stringvar",10); 320 cur.defineOutputBindDouble("floatvar"); 321 cur.defineOutputBindString("dummyvar",10); 322 cur.validateBinds(); 323 checkSuccess(cur.executeQuery(),1); 324 numvar=cur.getOutputBindInteger("numvar"); 325 stringvar=cur.getOutputBindString("stringvar"); 326 floatvar=cur.getOutputBindDouble("floatvar"); 327 checkSuccess(numvar,1); 328 checkSuccess(stringvar,"hello"); 329 checkSuccess(floatvar,2.5); 330 System.out.println(); 331 332 System.out.println("SELECT: "); 333 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 334 System.out.println(); 335 336 System.out.println("COLUMN COUNT: "); 337 checkSuccess(cur.colCount(),7); 338 System.out.println(); 339 340 System.out.println("COLUMN NAMES: "); 341 checkSuccess(cur.getColumnName(0),"TESTNUMBER"); 342 checkSuccess(cur.getColumnName(1),"TESTCHAR"); 343 checkSuccess(cur.getColumnName(2),"TESTVARCHAR"); 344 checkSuccess(cur.getColumnName(3),"TESTDATE"); 345 checkSuccess(cur.getColumnName(4),"TESTLONG"); 346 checkSuccess(cur.getColumnName(5),"TESTCLOB"); 347 checkSuccess(cur.getColumnName(6),"TESTBLOB"); 348 cols=cur.getColumnNames(); 349 checkSuccess(cols[0],"TESTNUMBER"); 350 checkSuccess(cols[1],"TESTCHAR"); 351 checkSuccess(cols[2],"TESTVARCHAR"); 352 checkSuccess(cols[3],"TESTDATE"); 353 checkSuccess(cols[4],"TESTLONG"); 354 checkSuccess(cols[5],"TESTCLOB"); 355 checkSuccess(cols[6],"TESTBLOB"); 356 System.out.println(); 357 358 System.out.println("COLUMN TYPES: "); 359 checkSuccess(cur.getColumnType(0),"NUMBER"); 360 checkSuccess(cur.getColumnType("TESTNUMBER"),"NUMBER"); 361 checkSuccess(cur.getColumnType(1),"CHAR"); 362 checkSuccess(cur.getColumnType("TESTCHAR"),"CHAR"); 363 checkSuccess(cur.getColumnType(2),"VARCHAR2"); 364 checkSuccess(cur.getColumnType("TESTVARCHAR"),"VARCHAR2"); 365 checkSuccess(cur.getColumnType(3),"DATE"); 366 checkSuccess(cur.getColumnType("TESTDATE"),"DATE"); 367 checkSuccess(cur.getColumnType(4),"LONG"); 368 checkSuccess(cur.getColumnType("TESTLONG"),"LONG"); 369 checkSuccess(cur.getColumnType(5),"CLOB"); 370 checkSuccess(cur.getColumnType("TESTCLOB"),"CLOB"); 371 checkSuccess(cur.getColumnType(6),"BLOB"); 372 checkSuccess(cur.getColumnType("TESTBLOB"),"BLOB"); 373 System.out.println(); 374 375 System.out.println("COLUMN LENGTH: "); 376 checkSuccess(cur.getColumnLength(0),22); 377 checkSuccess(cur.getColumnLength("TESTNUMBER"),22); 378 checkSuccess(cur.getColumnLength(1),40); 379 checkSuccess(cur.getColumnLength("TESTCHAR"),40); 380 checkSuccess(cur.getColumnLength(2),40); 381 checkSuccess(cur.getColumnLength("TESTVARCHAR"),40); 382 checkSuccess(cur.getColumnLength(3),7); 383 checkSuccess(cur.getColumnLength("TESTDATE"),7); 384 checkSuccess(cur.getColumnLength(4),0); 385 checkSuccess(cur.getColumnLength("TESTLONG"),0); 386 checkSuccess(cur.getColumnLength(5),0); 387 checkSuccess(cur.getColumnLength("TESTCLOB"),0); 388 checkSuccess(cur.getColumnLength(6),0); 389 checkSuccess(cur.getColumnLength("TESTBLOB"),0); 390 System.out.println(); 391 392 System.out.println("LONGEST COLUMN: "); 393 checkSuccess(cur.getLongest(0),1); 394 checkSuccess(cur.getLongest("TESTNUMBER"),1); 395 checkSuccess(cur.getLongest(1),40); 396 checkSuccess(cur.getLongest("TESTCHAR"),40); 397 checkSuccess(cur.getLongest(2),12); 398 checkSuccess(cur.getLongest("TESTVARCHAR"),12); 399 checkSuccess(cur.getLongest(3),9); 400 checkSuccess(cur.getLongest("TESTDATE"),9); 401 checkSuccess(cur.getLongest(4),9); 402 checkSuccess(cur.getLongest("TESTLONG"),9); 403 checkSuccess(cur.getLongest(5),9); 404 checkSuccess(cur.getLongest("TESTCLOB"),9); 405 checkSuccess(cur.getLongest(6),9); 406 checkSuccess(cur.getLongest("TESTBLOB"),9); 407 System.out.println(); 408 409 System.out.println("ROW COUNT: "); 410 checkSuccess(cur.rowCount(),8); 411 System.out.println(); 412 413 System.out.println("TOTAL ROWS: "); 414 checkSuccess(cur.totalRows(),0); 415 System.out.println(); 416 417 System.out.println("FIRST ROW INDEX: "); 418 checkSuccess(cur.firstRowIndex(),0); 419 System.out.println(); 420 421 System.out.println("END OF RESULT SET: "); 422 checkSuccess(cur.endOfResultSet(),1); 423 System.out.println(); 424 425 System.out.println("FIELDS BY INDEX: "); 426 checkSuccess(cur.getField(0,0),"1"); 427 checkSuccess(cur.getField(0,1),"testchar1 "); 428 checkSuccess(cur.getField(0,2),"testvarchar1"); 429 checkSuccess(cur.getField(0,3),"01-JAN-01"); 430 checkSuccess(cur.getField(0,4),"testlong1"); 431 checkSuccess(cur.getField(0,5),"testclob1"); 432 checkSuccess(cur.getField(0,6),""); 433 System.out.println(); 434 checkSuccess(cur.getField(7,0),"8"); 435 checkSuccess(cur.getField(7,1),"testchar8 "); 436 checkSuccess(cur.getField(7,2),"testvarchar8"); 437 checkSuccess(cur.getField(7,3),"01-JAN-08"); 438 checkSuccess(cur.getField(7,4),"testlong8"); 439 checkSuccess(cur.getField(7,5),"testclob8"); 440 checkSuccess(cur.getField(7,6),"testblob8"); 441 System.out.println(); 442 443 System.out.println("FIELD LENGTHS BY INDEX: "); 444 checkSuccess(cur.getFieldLength(0,0),1); 445 checkSuccess(cur.getFieldLength(0,1),40); 446 checkSuccess(cur.getFieldLength(0,2),12); 447 checkSuccess(cur.getFieldLength(0,3),9); 448 checkSuccess(cur.getFieldLength(0,4),9); 449 checkSuccess(cur.getFieldLength(0,5),9); 450 checkSuccess(cur.getFieldLength(0,6),0); 451 System.out.println(); 452 checkSuccess(cur.getFieldLength(7,0),1); 453 checkSuccess(cur.getFieldLength(7,1),40); 454 checkSuccess(cur.getFieldLength(7,2),12); 455 checkSuccess(cur.getFieldLength(7,3),9); 456 checkSuccess(cur.getFieldLength(7,4),9); 457 checkSuccess(cur.getFieldLength(7,5),9); 458 checkSuccess(cur.getFieldLength(7,6),9); 459 System.out.println(); 460 461 System.out.println("FIELDS BY NAME: "); 462 checkSuccess(cur.getField(0,"TESTNUMBER"),"1"); 463 checkSuccess(cur.getField(0,"TESTCHAR"),"testchar1 "); 464 checkSuccess(cur.getField(0,"TESTVARCHAR"),"testvarchar1"); 465 checkSuccess(cur.getField(0,"TESTDATE"),"01-JAN-01"); 466 checkSuccess(cur.getField(0,"TESTLONG"),"testlong1"); 467 checkSuccess(cur.getField(0,"TESTCLOB"),"testclob1"); 468 checkSuccess(cur.getField(0,"TESTBLOB"),""); 469 System.out.println(); 470 checkSuccess(cur.getField(7,"TESTNUMBER"),"8"); 471 checkSuccess(cur.getField(7,"TESTCHAR"),"testchar8 "); 472 checkSuccess(cur.getField(7,"TESTVARCHAR"),"testvarchar8"); 473 checkSuccess(cur.getField(7,"TESTDATE"),"01-JAN-08"); 474 checkSuccess(cur.getField(7,"TESTLONG"),"testlong8"); 475 checkSuccess(cur.getField(7,"TESTCLOB"),"testclob8"); 476 checkSuccess(cur.getField(7,"TESTBLOB"),"testblob8"); 477 System.out.println(); 478 479 System.out.println("FIELD LENGTHS BY NAME: "); 480 checkSuccess(cur.getFieldLength(0,"TESTNUMBER"),1); 481 checkSuccess(cur.getFieldLength(0,"TESTCHAR"),40); 482 checkSuccess(cur.getFieldLength(0,"TESTVARCHAR"),12); 483 checkSuccess(cur.getFieldLength(0,"TESTDATE"),9); 484 checkSuccess(cur.getFieldLength(0,"TESTLONG"),9); 485 checkSuccess(cur.getFieldLength(0,"TESTCLOB"),9); 486 checkSuccess(cur.getFieldLength(0,"TESTBLOB"),0); 487 System.out.println(); 488 checkSuccess(cur.getFieldLength(7,"TESTNUMBER"),1); 489 checkSuccess(cur.getFieldLength(7,"TESTCHAR"),40); 490 checkSuccess(cur.getFieldLength(7,"TESTVARCHAR"),12); 491 checkSuccess(cur.getFieldLength(7,"TESTDATE"),9); 492 checkSuccess(cur.getFieldLength(7,"TESTLONG"),9); 493 checkSuccess(cur.getFieldLength(7,"TESTCLOB"),9); 494 checkSuccess(cur.getFieldLength(7,"TESTBLOB"),9); 495 System.out.println(); 496 497 System.out.println("FIELDS BY ARRAY: "); 498 fields=cur.getRow(0); 499 checkSuccess(fields[0],"1"); 500 checkSuccess(fields[1],"testchar1 "); 501 checkSuccess(fields[2],"testvarchar1"); 502 checkSuccess(fields[3],"01-JAN-01"); 503 checkSuccess(fields[4],"testlong1"); 504 checkSuccess(fields[5],"testclob1"); 505 checkSuccess(fields[6],""); 506 System.out.println(); 507 508 System.out.println("FIELD LENGTHS BY ARRAY: "); 509 fieldlens=cur.getRowLengths(0); 510 checkSuccess(fieldlens[0],1); 511 checkSuccess(fieldlens[1],40); 512 checkSuccess(fieldlens[2],12); 513 checkSuccess(fieldlens[3],9); 514 checkSuccess(fieldlens[4],9); 515 checkSuccess(fieldlens[5],9); 516 checkSuccess(fieldlens[6],0); 517 System.out.println(); 518 519 System.out.println("INDIVIDUAL SUBSTITUTIONS: "); 520 cur.prepareQuery("select $(var1),'$(var2)',$(var3) from dual"); 521 cur.substitution("var1",1); 522 cur.substitution("var2","hello"); 523 cur.substitution("var3",10.5556,6,4); 524 checkSuccess(cur.executeQuery(),1); 525 System.out.println(); 526 527 System.out.println("FIELDS: "); 528 checkSuccess(cur.getField(0,0),"1"); 529 checkSuccess(cur.getField(0,1),"hello"); 530 checkSuccess(cur.getField(0,2),"10.5556"); 531 System.out.println(); 532 533 System.out.println("OUTPUT BIND: "); 534 cur.prepareQuery("begin :var1:='hello'; end;"); 535 cur.defineOutputBindString("var1",10); 536 checkSuccess(cur.executeQuery(),1); 537 checkSuccess(cur.getOutputBindString("var1"),"hello"); 538 System.out.println(); 539 540 System.out.println("ARRAY SUBSTITUTIONS: "); 541 cur.prepareQuery("select $(var1),$(var2),$(var3) from dual"); 542 cur.substitutions(subvars,subvallongs); 543 checkSuccess(cur.executeQuery(),1); 544 System.out.println(); 545 546 System.out.println("FIELDS: "); 547 checkSuccess(cur.getField(0,0),"1"); 548 checkSuccess(cur.getField(0,1),"2"); 549 checkSuccess(cur.getField(0,2),"3"); 550 System.out.println(); 551 552 System.out.println("ARRAY SUBSTITUTIONS: "); 553 cur.prepareQuery("select '$(var1)','$(var2)','$(var3)' from dual"); 554 cur.substitutions(subvars,subvalstrings); 555 checkSuccess(cur.executeQuery(),1); 556 System.out.println(); 557 558 System.out.println("FIELDS: "); 559 checkSuccess(cur.getField(0,0),"hi"); 560 checkSuccess(cur.getField(0,1),"hello"); 561 checkSuccess(cur.getField(0,2),"bye"); 562 System.out.println(); 563 564 System.out.println("ARRAY SUBSTITUTIONS: "); 565 cur.prepareQuery("select $(var1),$(var2),$(var3) from dual"); 566 cur.substitutions(subvars,subvaldoubles,precs,scales); 567 checkSuccess(cur.executeQuery(),1); 568 System.out.println(); 569 570 System.out.println("FIELDS: "); 571 checkSuccess(cur.getField(0,0),"10.55"); 572 checkSuccess(cur.getField(0,1),"10.556"); 573 checkSuccess(cur.getField(0,2),"10.5556"); 574 System.out.println(); 575 576 System.out.println("nullS as Nulls: "); 577 cur.getNullsAsNulls(); 578 checkSuccess(cur.sendQuery("select null,1,null from dual"),1); 579 checkSuccess(cur.getField(0,0),null); 580 checkSuccess(cur.getField(0,1),"1"); 581 checkSuccess(cur.getField(0,2),null); 582 cur.getNullsAsEmptyStrings(); 583 checkSuccess(cur.sendQuery("select null,1,null from dual"),1); 584 checkSuccess(cur.getField(0,0),""); 585 checkSuccess(cur.getField(0,1),"1"); 586 checkSuccess(cur.getField(0,2),""); 587 cur.getNullsAsNulls(); 588 System.out.println(); 589 590 System.out.println("RESULT SET BUFFER SIZE: "); 591 checkSuccess(cur.getResultSetBufferSize(),0); 592 cur.setResultSetBufferSize(2); 593 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 594 checkSuccess(cur.getResultSetBufferSize(),2); 595 System.out.println(); 596 checkSuccess(cur.firstRowIndex(),0); 597 checkSuccess(cur.endOfResultSet(),0); 598 checkSuccess(cur.rowCount(),2); 599 checkSuccess(cur.getField(0,0),"1"); 600 checkSuccess(cur.getField(1,0),"2"); 601 checkSuccess(cur.getField(2,0),"3"); 602 System.out.println(); 603 checkSuccess(cur.firstRowIndex(),2); 604 checkSuccess(cur.endOfResultSet(),0); 605 checkSuccess(cur.rowCount(),4); 606 checkSuccess(cur.getField(6,0),"7"); 607 checkSuccess(cur.getField(7,0),"8"); 608 System.out.println(); 609 checkSuccess(cur.firstRowIndex(),6); 610 checkSuccess(cur.endOfResultSet(),0); 611 checkSuccess(cur.rowCount(),8); 612 checkSuccess(cur.getField(8,0),null); 613 System.out.println(); 614 checkSuccess(cur.firstRowIndex(),8); 615 checkSuccess(cur.endOfResultSet(),1); 616 checkSuccess(cur.rowCount(),8); 617 System.out.println(); 618 619 System.out.println("DONT GET COLUMN INFO: "); 620 cur.dontGetColumnInfo(); 621 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 622 checkSuccess(cur.getColumnName(0),null); 623 checkSuccess(cur.getColumnLength(0),0); 624 checkSuccess(cur.getColumnType(0),null); 625 cur.getColumnInfo(); 626 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 627 checkSuccess(cur.getColumnName(0),"TESTNUMBER"); 628 checkSuccess(cur.getColumnLength(0),22); 629 checkSuccess(cur.getColumnType(0),"NUMBER"); 630 System.out.println(); 631 632 System.out.println("SUSPENDED SESSION: "); 633 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 634 cur.suspendResultSet(); 635 checkSuccess(con.suspendSession(),1); 636 port=con.getConnectionPort(); 637 socket=con.getConnectionSocket(); 638 checkSuccess(con.resumeSession(port,socket),1); 639 System.out.println(); 640 checkSuccess(cur.getField(0,0),"1"); 641 checkSuccess(cur.getField(1,0),"2"); 642 checkSuccess(cur.getField(2,0),"3"); 643 checkSuccess(cur.getField(3,0),"4"); 644 checkSuccess(cur.getField(4,0),"5"); 645 checkSuccess(cur.getField(5,0),"6"); 646 checkSuccess(cur.getField(6,0),"7"); 647 checkSuccess(cur.getField(7,0),"8"); 648 System.out.println(); 649 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 650 cur.suspendResultSet(); 651 checkSuccess(con.suspendSession(),1); 652 port=con.getConnectionPort(); 653 socket=con.getConnectionSocket(); 654 checkSuccess(con.resumeSession(port,socket),1); 655 System.out.println(); 656 checkSuccess(cur.getField(0,0),"1"); 657 checkSuccess(cur.getField(1,0),"2"); 658 checkSuccess(cur.getField(2,0),"3"); 659 checkSuccess(cur.getField(3,0),"4"); 660 checkSuccess(cur.getField(4,0),"5"); 661 checkSuccess(cur.getField(5,0),"6"); 662 checkSuccess(cur.getField(6,0),"7"); 663 checkSuccess(cur.getField(7,0),"8"); 664 System.out.println(); 665 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 666 cur.suspendResultSet(); 667 checkSuccess(con.suspendSession(),1); 668 port=con.getConnectionPort(); 669 socket=con.getConnectionSocket(); 670 checkSuccess(con.resumeSession(port,socket),1); 671 System.out.println(); 672 checkSuccess(cur.getField(0,0),"1"); 673 checkSuccess(cur.getField(1,0),"2"); 674 checkSuccess(cur.getField(2,0),"3"); 675 checkSuccess(cur.getField(3,0),"4"); 676 checkSuccess(cur.getField(4,0),"5"); 677 checkSuccess(cur.getField(5,0),"6"); 678 checkSuccess(cur.getField(6,0),"7"); 679 checkSuccess(cur.getField(7,0),"8"); 680 System.out.println(); 681 682 System.out.println("SUSPENDED RESULT SET: "); 683 cur.setResultSetBufferSize(2); 684 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 685 checkSuccess(cur.getField(2,0),"3"); 686 id=cur.getResultSetId(); 687 cur.suspendResultSet(); 688 checkSuccess(con.suspendSession(),1); 689 port=con.getConnectionPort(); 690 socket=con.getConnectionSocket(); 691 checkSuccess(con.resumeSession(port,socket),1); 692 checkSuccess(cur.resumeResultSet(id),1); 693 System.out.println(); 694 checkSuccess(cur.firstRowIndex(),4); 695 checkSuccess(cur.endOfResultSet(),0); 696 checkSuccess(cur.rowCount(),6); 697 checkSuccess(cur.getField(7,0),"8"); 698 System.out.println(); 699 checkSuccess(cur.firstRowIndex(),6); 700 checkSuccess(cur.endOfResultSet(),0); 701 checkSuccess(cur.rowCount(),8); 702 checkSuccess(cur.getField(8,0),null); 703 System.out.println(); 704 checkSuccess(cur.firstRowIndex(),8); 705 checkSuccess(cur.endOfResultSet(),1); 706 checkSuccess(cur.rowCount(),8); 707 cur.setResultSetBufferSize(0); 708 System.out.println(); 709 710 System.out.println("CACHED RESULT SET: "); 711 cur.cacheToFile("cachefile1"); 712 cur.setCacheTtl(200); 713 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 714 filename=cur.getCacheFileName(); 715 checkSuccess(filename,"cachefile1"); 716 cur.cacheOff(); 717 checkSuccess(cur.openCachedResultSet(filename),1); 718 checkSuccess(cur.getField(7,0),"8"); 719 System.out.println(); 720 721 System.out.println("COLUMN COUNT FOR CACHED RESULT SET: "); 722 checkSuccess(cur.colCount(),7); 723 System.out.println(); 724 725 System.out.println("COLUMN NAMES FOR CACHED RESULT SET: "); 726 checkSuccess(cur.getColumnName(0),"TESTNUMBER"); 727 checkSuccess(cur.getColumnName(1),"TESTCHAR"); 728 checkSuccess(cur.getColumnName(2),"TESTVARCHAR"); 729 checkSuccess(cur.getColumnName(3),"TESTDATE"); 730 checkSuccess(cur.getColumnName(4),"TESTLONG"); 731 checkSuccess(cur.getColumnName(5),"TESTCLOB"); 732 checkSuccess(cur.getColumnName(6),"TESTBLOB"); 733 cols=cur.getColumnNames(); 734 checkSuccess(cols[0],"TESTNUMBER"); 735 checkSuccess(cols[1],"TESTCHAR"); 736 checkSuccess(cols[2],"TESTVARCHAR"); 737 checkSuccess(cols[3],"TESTDATE"); 738 checkSuccess(cols[4],"TESTLONG"); 739 checkSuccess(cols[5],"TESTCLOB"); 740 checkSuccess(cols[6],"TESTBLOB"); 741 System.out.println(); 742 743 System.out.println("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: "); 744 cur.setResultSetBufferSize(2); 745 cur.cacheToFile("cachefile1"); 746 cur.setCacheTtl(200); 747 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 748 filename=cur.getCacheFileName(); 749 checkSuccess(filename,"cachefile1"); 750 cur.cacheOff(); 751 checkSuccess(cur.openCachedResultSet(filename),1); 752 checkSuccess(cur.getField(7,0),"8"); 753 checkSuccess(cur.getField(8,0),null); 754 cur.setResultSetBufferSize(0); 755 System.out.println(); 756 757 System.out.println("FROM ONE CACHE FILE TO ANOTHER: "); 758 cur.cacheToFile("cachefile2"); 759 checkSuccess(cur.openCachedResultSet("cachefile1"),1); 760 cur.cacheOff(); 761 checkSuccess(cur.openCachedResultSet("cachefile2"),1); 762 checkSuccess(cur.getField(7,0),"8"); 763 checkSuccess(cur.getField(8,0),null); 764 System.out.println(); 765 766 System.out.println("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: "); 767 cur.setResultSetBufferSize(2); 768 cur.cacheToFile("cachefile2"); 769 checkSuccess(cur.openCachedResultSet("cachefile1"),1); 770 cur.cacheOff(); 771 checkSuccess(cur.openCachedResultSet("cachefile2"),1); 772 checkSuccess(cur.getField(7,0),"8"); 773 checkSuccess(cur.getField(8,0),null); 774 cur.setResultSetBufferSize(0); 775 System.out.println(); 776 777 System.out.println("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: "); 778 cur.setResultSetBufferSize(2); 779 cur.cacheToFile("cachefile1"); 780 cur.setCacheTtl(200); 781 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 782 checkSuccess(cur.getField(2,0),"3"); 783 filename=cur.getCacheFileName(); 784 checkSuccess(filename,"cachefile1"); 785 id=cur.getResultSetId(); 786 cur.suspendResultSet(); 787 checkSuccess(con.suspendSession(),1); 788 port=con.getConnectionPort(); 789 socket=con.getConnectionSocket(); 790 System.out.println(); 791 checkSuccess(con.resumeSession(port,socket),1); 792 checkSuccess(cur.resumeCachedResultSet(id,filename),1); 793 System.out.println(); 794 checkSuccess(cur.firstRowIndex(),4); 795 checkSuccess(cur.endOfResultSet(),0); 796 checkSuccess(cur.rowCount(),6); 797 checkSuccess(cur.getField(7,0),"8"); 798 System.out.println(); 799 checkSuccess(cur.firstRowIndex(),6); 800 checkSuccess(cur.endOfResultSet(),0); 801 checkSuccess(cur.rowCount(),8); 802 checkSuccess(cur.getField(8,0),null); 803 System.out.println(); 804 checkSuccess(cur.firstRowIndex(),8); 805 checkSuccess(cur.endOfResultSet(),1); 806 checkSuccess(cur.rowCount(),8); 807 cur.cacheOff(); 808 System.out.println(); 809 checkSuccess(cur.openCachedResultSet(filename),1); 810 checkSuccess(cur.getField(7,0),"8"); 811 checkSuccess(cur.getField(8,0),null); 812 cur.setResultSetBufferSize(0); 813 System.out.println(); 814 815 System.out.println("COMMIT AND ROLLBACK: "); 816 SQLRConnection secondcon=new SQLRConnection("sqlrelay", 817 (short)9000, 818 "/tmp/test.socket", 819 null,null,0,1); 820 SQLRCursor secondcur=new SQLRCursor(secondcon); 821 secondcon.enableKerberos(null,null,null); 822 checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1); 823 checkSuccess(secondcur.getField(0,0),"0"); 824 checkSuccess(con.commit(),1); 825 checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1); 826 checkSuccess(secondcur.getField(0,0),"8"); 827 checkSuccess(con.autoCommitOn(),1); 828 checkSuccess(cur.sendQuery("insert into testtable values (10,'testchar10','testvarchar10','01-JAN-2010','testlong10','testclob10',empty_blob())"),1); 829 checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1); 830 checkSuccess(secondcur.getField(0,0),"9"); 831 checkSuccess(con.autoCommitOff(),1); 832 System.out.println(); 833 834 835 System.out.println("CLOB AND BLOB OUTPUT BIND:"); 836 cur.sendQuery("drop table testtable1"); 837 checkSuccess(cur.sendQuery("create table testtable1 (testclob clob, testblob blob)"),1); 838 cur.prepareQuery("insert into testtable1 values ('hello',:var1)"); 839 cur.inputBindBlob("var1",(new String("hello")).getBytes(),5); 840 checkSuccess(cur.executeQuery(),1); 841 cur.prepareQuery("begin select testclob into :clobvar from testtable1; select testblob into :blobvar from testtable1; end;"); 842 cur.defineOutputBindClob("clobvar"); 843 cur.defineOutputBindBlob("blobvar"); 844 checkSuccess(cur.executeQuery(),1); 845 clobvar=cur.getOutputBindClob("clobvar"); 846 clobvarlength=cur.getOutputBindLength("clobvar"); 847 blobvar=cur.getOutputBindBlob("blobvar"); 848 blobvarlength=cur.getOutputBindLength("blobvar"); 849 checkSuccess(clobvar,"hello",5); 850 checkSuccess(clobvarlength,5); 851 checkSuccess(blobvar,"hello",5); 852 checkSuccess(blobvarlength,5); 853 cur.sendQuery("drop table testtable1"); 854 System.out.println(); 855 856 System.out.println("NULL AND EMPTY CLOBS AND CLOBS:"); 857 cur.getNullsAsNulls(); 858 cur.sendQuery("create table testtable1 (testclob1 clob, testclob2 clob, testblob1 blob, testblob2 blob)"); 859 cur.prepareQuery("insert into testtable1 values (:var1,:var2,:var3,:var4)"); 860 cur.inputBindClob("var1","",0); 861 cur.inputBindClob("var2",null,0); 862 cur.inputBindBlob("var3",(new String("")).getBytes(),0); 863 cur.inputBindBlob("var4",null,0); 864 checkSuccess(cur.executeQuery(),1); 865 cur.sendQuery("select * from testtable1"); 866 checkSuccess(cur.getField(0,0),null); 867 checkSuccess(cur.getField(0,1),null); 868 checkSuccess(cur.getField(0,2),null); 869 checkSuccess(cur.getField(0,3),null); 870 cur.sendQuery("drop table testtable1"); 871 System.out.println(); 872 873 System.out.println("CURSOR BINDS:"); 874 checkSuccess(cur.sendQuery("create or replace package types as type cursorType is ref cursor; end;"),1); 875 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); 876 cur.prepareQuery("begin :curs:=sp_testtable; end;"); 877 cur.defineOutputBindCursor("curs"); 878 checkSuccess(cur.executeQuery(),1); 879 SQLRCursor bindcur=cur.getOutputBindCursor("curs"); 880 checkSuccess(bindcur.fetchFromBindCursor(),1); 881 checkSuccess(bindcur.getField(0,0),"1"); 882 checkSuccess(bindcur.getField(1,0),"2"); 883 checkSuccess(bindcur.getField(2,0),"3"); 884 checkSuccess(bindcur.getField(3,0),"4"); 885 checkSuccess(bindcur.getField(4,0),"5"); 886 checkSuccess(bindcur.getField(5,0),"6"); 887 checkSuccess(bindcur.getField(6,0),"7"); 888 checkSuccess(bindcur.getField(7,0),"8"); 889 System.out.println(); 890 891 System.out.println("LONG CLOB:"); 892 cur.sendQuery("drop table testtable2"); 893 cur.sendQuery("create table testtable2 (testclob clob)"); 894 cur.prepareQuery("insert into testtable2 values (:clobval)"); 895 StringBuffer clobval=new StringBuffer(); 896 for (int i=0; i<8*1024; i++) { 897 clobval.append('C'); 898 } 899 cur.inputBindClob("clobval",clobval.toString(),8*1024); 900 checkSuccess(cur.executeQuery(),1); 901 cur.sendQuery("select testclob from testtable2"); 902 checkSuccess(clobval.toString(),cur.getField(0,"TESTCLOB")); 903 cur.prepareQuery("begin select testclob into :clobbindval from testtable2; end;"); 904 cur.defineOutputBindClob("clobbindval"); 905 checkSuccess(cur.executeQuery(),1); 906 String clobbindvar=cur.getOutputBindClob("clobbindval"); 907 checkSuccess(cur.getOutputBindLength("clobbindval"),8*1024); 908 checkSuccess(clobval.toString(),clobbindvar); 909 cur.sendQuery("drop table testtable2"); 910 System.out.println(); 911 912 913 System.out.println("LONG OUTPUT BIND"); 914 cur.sendQuery("drop table testtable2"); 915 cur.sendQuery("create table testtable2 (testval varchar2(4000))"); 916 cur.prepareQuery("insert into testtable2 values (:testval)"); 917 StringBuffer testval=new StringBuffer(); 918 for (int i=0; i<4000; i++) { 919 testval.append('C'); 920 } 921 cur.inputBind("testval",testval.toString()); 922 checkSuccess(cur.executeQuery(),1); 923 cur.sendQuery("select testval from testtable2"); 924 checkSuccess(testval.toString(),cur.getField(0,"TESTVAL")); 925 StringBuffer query=new StringBuffer(); 926 query.append("begin :bindval:='"); 927 query.append(testval.toString()); 928 query.append("'; end;"); 929 cur.prepareQuery(query.toString()); 930 cur.defineOutputBindString("bindval",4000); 931 checkSuccess(cur.executeQuery(),1); 932 checkSuccess(cur.getOutputBindLength("bindval"),4000); 933 checkSuccess(cur.getOutputBindString("bindval"),testval.toString()); 934 cur.sendQuery("drop table testtable2"); 935 System.out.println(); 936 937 System.out.println("NEGATIVE INPUT BIND"); 938 cur.sendQuery("create table testtable2 (testval number)"); 939 cur.prepareQuery("insert into testtable2 values (:testval)"); 940 cur.inputBind("testval",-1); 941 checkSuccess(cur.executeQuery(),1); 942 cur.sendQuery("select testval from testtable2"); 943 checkSuccess(cur.getField(0,"TESTVAL"),"-1"); 944 cur.sendQuery("drop table testtable2"); 945 System.out.println(); 946 947 System.out.println("FINISHED SUSPENDED SESSION: "); 948 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),1); 949 checkSuccess(cur.getField(4,0),"5"); 950 checkSuccess(cur.getField(5,0),"6"); 951 checkSuccess(cur.getField(6,0),"7"); 952 checkSuccess(cur.getField(7,0),"8"); 953 id=cur.getResultSetId(); 954 cur.suspendResultSet(); 955 checkSuccess(con.suspendSession(),1); 956 port=con.getConnectionPort(); 957 socket=con.getConnectionSocket(); 958 checkSuccess(con.resumeSession(port,socket),1); 959 checkSuccess(cur.resumeResultSet(id),1); 960 checkSuccess(cur.getField(4,0),null); 961 checkSuccess(cur.getField(5,0),null); 962 checkSuccess(cur.getField(6,0),null); 963 checkSuccess(cur.getField(7,0),null); 964 System.out.println(); 965 966 // drop existing table 967 cur.sendQuery("drop table testtable"); 968 969 // invalid queries... 970 System.out.println("INVALID QUERIES: "); 971 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),0); 972 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),0); 973 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),0); 974 checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),0); 975 System.out.println(); 976 checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 977 checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 978 checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 979 checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 980 System.out.println(); 981 checkSuccess(cur.sendQuery("create table testtable"),0); 982 checkSuccess(cur.sendQuery("create table testtable"),0); 983 checkSuccess(cur.sendQuery("create table testtable"),0); 984 checkSuccess(cur.sendQuery("create table testtable"),0); 985 System.out.println(); 986 987 System.exit(0); 988 } 989 } 990