1/* 2 * Copyright (C) 2008 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17package tests.SQLite; 18 19import SQLite.Constants; 20import SQLite.Database; 21import SQLite.Exception; 22import SQLite.Stmt; 23import SQLite.TableResult; 24import dalvik.annotation.BrokenTest; 25import dalvik.annotation.KnownFailure; 26import dalvik.annotation.TestLevel; 27import dalvik.annotation.TestTargetNew; 28import dalvik.annotation.TestTargetClass; 29 30 31import tests.support.DatabaseCreator; 32import tests.support.Support_SQL; 33 34import java.sql.Connection; 35import java.sql.SQLException; 36 37@TestTargetClass(Stmt.class) 38public class StmtTest extends SQLiteTest { 39 40 private static Database db = null; 41 42 private static Stmt st = null; 43 44 private static final String createAllTypes = 45 "create table type (" + 46 47 " BoolVal BOOLEAN," + " IntVal INT," + " LongVal LONG," 48 + " Bint BIGINT," + " Tint TINYINT," + " Sint SMALLINT," 49 + " Mint MEDIUMINT, " + 50 51 " IntegerVal INTEGER, " + " RealVal REAL, " 52 + " DoubleVal DOUBLE, " + " FloatVal FLOAT, " 53 + " DecVal DECIMAL, " + 54 55 " NumVal NUMERIC, " + " charStr CHAR(20), " 56 + " dateVal DATE, " + " timeVal TIME, " + " TS TIMESTAMP, " 57 + 58 59 " DT DATETIME, " + " TBlob TINYBLOB, " + " BlobVal BLOB, " 60 + " MBlob MEDIUMBLOB, " + " LBlob LONGBLOB, " + 61 62 " TText TINYTEXT, " + " TextVal TEXT, " 63 + " MText MEDIUMTEXT, " + " LText LONGTEXT, " + 64 65 " MaxLongVal BIGINT, MinLongVal BIGINT, "+ 66 67 " validURL URL, invalidURL URL "+ 68 69 ");"; 70 71 static final String insertAllTypes = 72 "insert into type (BoolVal, IntVal, LongVal, Bint, Tint, Sint, Mint," 73 + "IntegerVal, RealVal, DoubleVal, FloatVal, DecVal," 74 + "NumVal, charStr, dateVal, timeVal, TS," 75 + "DT, TBlob, BlobVal, MBlob, LBlob," 76 + "TText, TextVal, MText, LText, MaxLongVal, MinLongVal," 77 + " validURL, invalidURL" 78 + ") " 79 + "values (1, -1, 22, 2, 33," 80 + "3, 1, 2, 3.9, 23.2, 33.3, 44," 81 + "5, 'test string', '1799-05-26', '12:35:45', '2007-10-09 14:28:02.0'," 82 + "'1221-09-22 10:11:55', 1, 2, 3, 4," 83 + "'Test text message tiny', 'Test text'," 84 + " 'Test text message medium', 'Test text message long', " 85 + Long.MAX_VALUE+", "+Long.MIN_VALUE+", " 86 + "null, null "+ 87 ");"; 88 89 static final String allTypesTable = "type"; 90 91 public void setUp() throws java.lang.Exception { 92 super.setUp(); 93 Support_SQL.loadDriver(); 94 db = new Database(); 95 db.open(dbFile.getPath(), 0); 96 db.exec(DatabaseCreator.CREATE_TABLE_SIMPLE1, null); 97 DatabaseCreator.fillSimpleTable1(conn); 98 99 } 100 101 public void tearDown() { 102 if (st != null) { 103 try { 104 st.close(); 105 } catch (Exception e) { 106 107 } 108 } 109 try { 110 db.close(); 111 Connection con = Support_SQL.getConnection(); 112 con.close(); 113// dbFile.delete(); 114 } catch (Exception e) { 115 fail("Exception in tearDown: "+e.getMessage()); 116 } catch (SQLException e) { 117 fail("SQLException in tearDown: "+e.getMessage()); 118 } 119 super.tearDown(); 120 } 121 122 /** 123 * @tests {@link Stmt#Stmt()} 124 */ 125 @TestTargetNew( 126 level = TestLevel.COMPLETE, 127 notes = "constructor test", 128 method = "Stmt", 129 args = {} 130 ) 131 public void testStmt() { 132 Stmt st = new Stmt(); 133 assertNotNull(st); 134 try { 135 Stmt actual = db.prepare(""); 136 assertNotNull(st); 137 // no black box test assertEquals(actual.error_code,st.error_code); 138 } catch (Exception e) { 139 fail("Statement setup fails: "+e.getMessage()); 140 e.printStackTrace(); 141 } 142 143 try { 144 st.step(); 145 fail("Cannot execute non prepared Stmt"); 146 } catch (Exception e) { 147 //ok 148 } 149 } 150 151 /** 152 * @tests {@link Stmt#finalize()} 153 */ 154 @TestTargetNew( 155 level = TestLevel.NOT_FEASIBLE, 156 notes = "method test", 157 method = "finalize", 158 args = {} 159 ) 160 public void testFinalize() { 161 162 } 163 164 /** 165 * @tests {@link Stmt#prepare()} 166 */ 167 @TestTargetNew( 168 level = TestLevel.COMPLETE, 169 notes = "method test", 170 method = "prepare", 171 args = {} 172 ) 173 public void testPrepare() { 174 try { 175 st = db.prepare(""); 176 st.prepare(); 177 fail("statement is closed"); 178 } catch (Exception e) { 179 assertEquals("stmt already closed", e.getMessage()); 180 } 181 182 try { 183 st = new Stmt(); 184 st = db.prepare("select * from " + DatabaseCreator.SIMPLE_TABLE1); 185 assertFalse(st.prepare()); 186 st = new Stmt(); 187 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 188 + " values (:one,:two,:three)"); 189 assertFalse(st.prepare()); 190 st = new Stmt(); 191 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 192 + " values (:one,:two,:three)"); 193 st.bind(1, 1); 194 st.bind(2, 10); 195 st.bind(3, 30); 196 assertFalse(st.prepare()); 197 st = db.prepare("select * from " + DatabaseCreator.SIMPLE_TABLE1 198 + "; " + "delete from " + DatabaseCreator.SIMPLE_TABLE1 199 + " where id = 5; " + "insert into " 200 + DatabaseCreator.SIMPLE_TABLE1 + " values(5, 10, 20); " 201 + "select * from " + DatabaseCreator.SIMPLE_TABLE1 + ";"); 202 assertTrue(st.prepare()); 203 assertTrue(st.prepare()); 204 assertTrue(st.prepare()); 205 assertFalse(st.prepare()); 206 } catch (Exception e) { 207 fail("statement should be ready for execution: " 208 + e.getMessage()); 209 e.printStackTrace(); 210 } 211 } 212 213 /** 214 * @tests {@link Stmt#step()} 215 */ 216 @TestTargetNew( 217 level = TestLevel.COMPLETE, 218 notes = "method test", 219 method = "step", 220 args = {} 221 ) 222 public void testStep() { 223 try { 224 st.step(); 225 fail("Exception expected"); 226 } catch (Exception e) { 227 assertEquals("stmt already closed", e.getMessage()); 228 } 229 230 try { 231 st = new Stmt(); 232 st = db.prepare("select name from sqlite_master where type = 'table'"); 233 st.step(); 234 } catch (Exception e) { 235 fail("test fails"); 236 } 237 238 } 239 240 /** 241 * @tests {@link Stmt#close()} 242 */ 243 @TestTargetNew( 244 level = TestLevel.COMPLETE, 245 notes = "method test", 246 method = "close", 247 args = {} 248 ) 249 public void testClose() { 250 try { 251 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 252 + " values (:one,:two,:three)"); 253 st.close(); 254 } catch (Exception e) { 255 fail("Test fails"); 256 e.printStackTrace(); 257 } 258 259 try { 260 st.step(); 261 fail("Test fails"); 262 } catch (Exception e) { 263 assertEquals("stmt already closed", e.getMessage()); 264 } 265 } 266 267 /** 268 * @throws Exception 269 * @tests {@link Stmt#reset()} 270 */ 271 @TestTargetNew( 272 level = TestLevel.COMPLETE, 273 notes = "method test", 274 method = "reset", 275 args = {} 276 ) 277 public void testReset() throws Exception { 278 db.exec("create table TEST (res integer not null)", null); 279 280 st = db.prepare("insert into TEST values (:one);"); 281 st.bind(1, 1); 282 st.step(); 283 284 // verify that parameter is still bound 285 st.reset(); 286 assertEquals(1,st.bind_parameter_count()); 287 st.step(); 288 289 TableResult count = db.get_table("select count(*) from TEST where res=1", null); 290 291 String[] row0 = (String[]) count.rows.elementAt(0); 292 assertEquals(2, Integer.parseInt(row0[0])); 293 } 294 295 /** 296 * @tests {@link Stmt#clear_bindings()} 297 */ 298 @TestTargetNew( 299 level = TestLevel.COMPLETE, 300 notes = "not supported", 301 method = "clear_bindings", 302 args = {} 303 ) 304 public void testClear_bindings() { 305 try { 306 st.clear_bindings(); 307 } catch (Exception e) { 308 assertEquals("unsupported", e.getMessage()); 309 } 310 } 311 312 /** 313 * @tests {@link Stmt#bind(int, int)} 314 */ 315 @TestTargetNew( 316 level = TestLevel.COMPLETE, 317 notes = "method test", 318 method = "bind", 319 args = {int.class, int.class} 320 ) 321 public void testBindIntInt() { 322 try { 323 int input = 0; 324 int maxVal = Integer.MAX_VALUE; 325 int minVal = Integer.MIN_VALUE; 326 327 db.exec("create table TEST (res integer)", null); 328 st = db.prepare("insert into TEST values (:one);"); 329 st.bind(1, input); 330 st.step(); 331 332 st.reset(); 333 st.bind(1,maxVal); 334 st.step(); 335 336 st.reset(); 337 st.bind(1,minVal); 338 st.step(); 339 340 TableResult r = db.get_table("select * from TEST"); 341 342 String[] row0 = (String[]) r.rows.elementAt(0); 343 assertEquals(input,Integer.parseInt(row0[0])); 344 345 String[] row1 = (String[]) r.rows.elementAt(1); 346 assertEquals(maxVal,Integer.parseInt(row1[0])); 347 348 String[] row2 = (String[]) r.rows.elementAt(2); 349 assertEquals(minVal,Integer.parseInt(row2[0])); 350 351 } catch (Exception e) { 352 fail("Error in test setup: "+e.getMessage()); 353 e.printStackTrace(); 354 } 355 356 try { 357 st.close(); 358 st.bind(1,Integer.MIN_VALUE); 359 fail("Exception expected"); 360 } catch (Exception e) { 361 //ok 362 } 363 } 364 365 /** 366 * @tests {@link Stmt#bind(int, long)} 367 */ 368 @TestTargetNew( 369 level = TestLevel.COMPLETE, 370 notes = "method test", 371 method = "bind", 372 args = {int.class, long.class} 373 ) 374 public void testBindIntLong() { 375 try { 376 long input = 0; 377 long maxVal = Long.MAX_VALUE; 378 long minVal = Long.MIN_VALUE; 379 380 db.exec("create table TEST (res long)", null); 381 st = db.prepare("insert into TEST values (:one);"); 382 st.bind(1, input); 383 st.step(); 384 385 st.reset(); 386 st.bind(1,maxVal); 387 st.step(); 388 389 st.reset(); 390 st.bind(1,minVal); 391 st.step(); 392 393 TableResult r = db.get_table("select * from TEST"); 394 395 String[] row0 = (String[]) r.rows.elementAt(0); 396 assertEquals(input,Long.parseLong(row0[0])); 397 398 String[] row1 = (String[]) r.rows.elementAt(1); 399 assertEquals(maxVal,Long.parseLong(row1[0])); 400 401 String[] row2 = (String[]) r.rows.elementAt(2); 402 assertEquals(minVal,Long.parseLong(row2[0])); 403 404 } catch (Exception e) { 405 fail("Error in test setup: "+e.getMessage()); 406 e.printStackTrace(); 407 } 408 409 try { 410 st.close(); 411 st.bind(1,Long.MIN_VALUE); 412 fail("Exception expected"); 413 } catch (Exception e) { 414 //ok 415 } 416 } 417 418 /** 419 * @tests {@link Stmt#bind(int, double)} 420 */ 421 @TestTargetNew( 422 level = TestLevel.COMPLETE, 423 notes = "method test", 424 method = "bind", 425 args = {int.class, double.class} 426 ) 427 public void testBindIntDouble() { 428 try { 429 double input = 0.0; 430 double maxVal = Double.MAX_VALUE; 431 double minVal = Double.MIN_VALUE; 432 double negInf = Double.NEGATIVE_INFINITY; 433 double posInf = Double.POSITIVE_INFINITY; 434 double nan = Double.NaN; 435 436 db.exec("create table TEST (res double)", null); 437 st = db.prepare("insert into TEST values (:one);"); 438 st.bind(1, input); 439 st.step(); 440 441 st.reset(); 442 st.bind(1, maxVal); 443 st.step(); 444 445 st.reset(); 446 st.bind(1, minVal); 447 st.step(); 448 449 st.reset(); 450 st.bind(1, negInf); 451 st.step(); 452 453 st.reset(); 454 st.bind(1, posInf); 455 st.step(); 456 457 st.reset(); 458 st.bind(1, nan); 459 st.step(); 460 461 462 TableResult r = db.get_table("select * from TEST"); 463 464 String[] row0 = (String[]) r.rows.elementAt(0); 465 assertTrue(Double.compare(input, Double.parseDouble(row0[0])) == 0); 466 467 String[] row1 = (String[]) r.rows.elementAt(1); 468 assertFalse(Double.compare(maxVal, Double.parseDouble(row1[0])) == 0); 469 assertTrue(Double.compare(maxVal, Double.parseDouble(row1[0])) < 0); 470 assertTrue(Double.isInfinite(Double.parseDouble(row1[0]))); 471 472 String[] row2 = (String[]) r.rows.elementAt(2); 473 assertTrue(Double.compare(minVal, Double.parseDouble(row2[0])) == 0); 474 475 String[] row3 = (String[]) r.rows.elementAt(3); 476 assertEquals("Double.NEGATIVE_INFINITY SQLite representation", 477 "-Inf", row3[0]); 478 479 String[] row4 = (String[]) r.rows.elementAt(4); 480 assertEquals("Double.POSITIVE_INFINITY SQLite representation", 481 "Inf", row4[0]); 482 483 String[] row5 = (String[]) r.rows.elementAt(4); 484 assertEquals("Double.Nan SQLite representation", "Inf", row5[0]); 485 486 } catch (Exception e) { 487 fail("Error in test setup: " + e.getMessage()); 488 e.printStackTrace(); 489 } 490 491 try { 492 st.close(); 493 st.bind(1,0.0); 494 fail("Exception expected"); 495 } catch (Exception e) { 496 //ok 497 } 498 } 499 500 /** 501 * @tests {@link Stmt#bind(int, byte[])} 502 */ 503 @TestTargetNew( 504 level = TestLevel.COMPLETE, 505 notes = "", 506 method = "bind", 507 args = {int.class, byte[].class} 508 ) 509 public void testBindIntByteArray() { 510 511 String name = "Hello World"; 512 513 try { 514 byte[] b = new byte[name.getBytes().length]; 515 b = name.getBytes(); 516 String stringInHex = ""; 517 518 db.exec(DatabaseCreator.CREATE_TABLE_PARENT, null); 519 st = db.prepare("insert into " + DatabaseCreator.PARENT_TABLE 520 + " values (:one, :two);"); 521 st.bind(1, 2); 522 st.bind(2, b); 523 st.step(); 524 525 //compare what was stored with input based on Hex representation 526 // since type of column is CHAR 527 TableResult r = db.get_table("select * from " 528 + DatabaseCreator.PARENT_TABLE); 529 String[] row = (String[]) r.rows.elementAt(0); 530 531 for (byte aByte : b) { 532 stringInHex += Integer.toHexString(aByte); 533 } 534 stringInHex = "X'" + stringInHex + "'"; 535 assertTrue(stringInHex.equalsIgnoreCase(row[1])); 536 537 } catch (Exception e) { 538 fail("Error in test setup: "+e.getMessage()); 539 e.printStackTrace(); 540 } 541 542 try { 543 st.close(); 544 st.bind(1,name.getBytes()); 545 fail("Exception expected"); 546 } catch (Exception e) { 547 //ok 548 } 549 } 550 551 /** 552 * @tests {@link Stmt#bind(int, String)} 553 */ 554 @TestTargetNew( 555 level = TestLevel.COMPLETE, 556 notes = "method test", 557 method = "bind", 558 args = {int.class, java.lang.String.class} 559 ) 560 public void testBindIntString() { 561 String name = "Hello World"; 562 563 try { 564 565 db.exec(DatabaseCreator.CREATE_TABLE_PARENT, null); 566 st = db.prepare("insert into " + DatabaseCreator.PARENT_TABLE 567 + " values (:one, :two);"); 568 st.bind(1, 2); 569 st.bind(2, name); 570 st.step(); 571 572 TableResult r = db.get_table("select * from " 573 + DatabaseCreator.PARENT_TABLE); 574 String[] row = (String[]) r.rows.elementAt(0); 575 assertEquals(name,row[1]); 576 577 } catch (Exception e) { 578 fail("Error in test setup: "+e.getMessage()); 579 e.printStackTrace(); 580 } 581 582 try { 583 st.close(); 584 st.bind(1,name); 585 fail("Exception expected"); 586 } catch (Exception e) { 587 //ok 588 } 589 } 590 591 /** 592 * @tests {@link Stmt#bind(int)} 593 */ 594 @TestTargetNew( 595 level = TestLevel.COMPLETE, 596 notes = "method test", 597 method = "bind", 598 args = {int.class} 599 ) 600 public void testBindInt() { 601 602 try { 603 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 604 + " values (:one,:two,:three)"); 605 st.bind(4); 606 st.bind(1, 4); 607 st.bind(2, 10); 608 st.bind(3, 30); 609 st.step(); 610 fail("Test failes"); 611 } catch (Exception e) { 612 // What happens if null is bound to non existing variable position 613 assertEquals("parameter position out of bounds" , e.getMessage()); 614 } 615 616 // functional tests 617 618 try { 619 st.reset(); 620 st.bind(1); 621 st.bind(2, 10); 622 st.bind(3, 30); 623 st.step(); 624 fail("Test failes"); 625 } catch (Exception e) { 626 // What happens if null is bound to NON NULL field 627 assertEquals("SQL logic error or missing database", e.getMessage()); 628 } 629 630 try { 631 st.reset(); 632 st.bind(1, 3); 633 st.bind(2); 634 st.bind(3, 30); 635 st.step(); 636 } catch (Exception e) { 637 fail("Error in test setup : " + e.getMessage()); 638 } 639 640 } 641 642 /** 643 * @tests {@link Stmt#bind_zeroblob(int, int)} 644 */ 645 @TestTargetNew( 646 level = TestLevel.NOT_FEASIBLE, 647 notes = "not supported", 648 method = "bind_zeroblob", 649 args = {int.class, int.class} 650 ) 651 public void testBind_zeroblob() { 652 try { 653 st.bind_zeroblob(1, 128); 654 } catch (Exception e) { 655 assertEquals("unsupported", e.getMessage()); 656 } 657 } 658 659 /** 660 * @tests {@link Stmt#bind_parameter_count()} 661 */ 662 @TestTargetNew( 663 level = TestLevel.COMPLETE, 664 notes = "method test", 665 method = "bind_parameter_count", 666 args = {} 667 ) 668 public void testBind_parameter_count() { 669 try { 670 st.bind_parameter_count(); 671 } catch (Exception e) { 672 assertEquals("stmt already closed", e.getMessage()); 673 } 674 675 try { 676 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 677 + " values (:one,:two,:three)"); 678 assertEquals(3, st.bind_parameter_count()); 679 } catch (Exception e) { 680 fail("Error in test setup : " + e.getMessage()); 681 e.printStackTrace(); 682 } 683 684 try { 685 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 686 + " values (?, ?, ?)"); 687 assertEquals(3, st.bind_parameter_count()); 688 } catch (Exception e) { 689 fail("Error in test setup : " + e.getMessage()); 690 e.printStackTrace(); 691 } 692 693 try { 694 st = db.prepare("select * from " + DatabaseCreator.SIMPLE_TABLE1); 695 assertEquals(0, st.bind_parameter_count()); 696 } catch (Exception e) { 697 fail("Error in test setup : " + e.getMessage()); 698 e.printStackTrace(); 699 } 700 701 try { 702 st.close(); 703 st.bind_parameter_count(); 704 fail("Exception expected"); 705 } catch (Exception e) { 706 //ok 707 } 708 709 } 710 711 /** 712 * @tests {@link Stmt#bind_parameter_name(int)} 713 */ 714 @TestTargetNew( 715 level = TestLevel.COMPLETE, 716 notes = "method test", 717 method = "bind_parameter_name", 718 args = {int.class} 719 ) 720 public void testBind_parameter_name() { 721 try { 722 st.bind_parameter_name(1); 723 fail("Exception expected"); 724 } catch (Exception e) { 725 assertEquals("stmt already closed", e.getMessage()); 726 } 727 728 try { 729 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 730 + " values (:one,:two,:three)"); 731 assertEquals(":one", st.bind_parameter_name(1)); 732 assertEquals(":two", st.bind_parameter_name(2)); 733 assertEquals(":three", st.bind_parameter_name(3)); 734 String name = st.bind_parameter_name(4); 735 } catch (Exception e) { 736 assertEquals("parameter position out of bounds",e.getMessage()); 737 } 738 } 739 740 /** 741 * @tests {@link Stmt#bind_parameter_index(String)} 742 */ 743 @TestTargetNew( 744 level = TestLevel.COMPLETE, 745 notes = "method test", 746 method = "bind_parameter_index", 747 args = {java.lang.String.class} 748 ) 749 public void testBind_parameter_index() { 750 751 try { 752 st.bind_parameter_index(""); 753 fail("Exception expected"); 754 } catch (Exception e) { 755 assertEquals("stmt already closed", e.getMessage()); 756 } 757 758 try { 759 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 760 + " values (:one,:two,:three)"); 761 assertEquals(3, st.bind_parameter_index(":three")); 762 } catch (Exception e) { 763 fail("Error in test setup : " + e.getMessage()); 764 e.printStackTrace(); 765 } 766 767 try { 768 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 769 + " values (:one,:two,:three)"); 770 assertEquals(0, st.bind_parameter_index(":t")); 771 } catch (Exception e) { 772 fail("Error in test setup : " + e.getMessage()); 773 e.printStackTrace(); 774 } 775 776 try { 777 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 778 + " values (?, ?, ?)"); 779 assertEquals(0, st.bind_parameter_index("?")); 780 } catch (Exception e) { 781 fail("Error in test setup : " + e.getMessage()); 782 e.printStackTrace(); 783 } 784 785 } 786 787 /** 788 * @throws Exception 789 * @tests {@link Stmt#column_int(int)} 790 */ 791 @TestTargetNew( 792 level = TestLevel.COMPLETE, 793 notes = "method test", 794 method = "column_int", 795 args = {int.class} 796 ) 797 public void testColumn_int() throws Exception { 798 db.exec(createAllTypes, null); 799 db.exec(insertAllTypes, null); 800 801 int columnObjectCastFromLong; 802 Object columnObject = null; 803 int intColumn = 0; 804 String selectStmt = "select * from "+DatabaseCreator.SIMPLE_TABLE1; 805 806 st = db.prepare(selectStmt); 807 st.step(); 808 // select 'speed' value 809 columnObject = st.column(1); 810 intColumn = st.column_int(1); 811 assertNotNull(intColumn); 812 813 assertTrue("Integer".equalsIgnoreCase(st.column_decltype(1))); 814 int stSpeed = Integer.parseInt(columnObject.toString()); 815 assertNotNull(stSpeed); 816 assertEquals( intColumn, stSpeed); 817 assertEquals(10,stSpeed); 818 819 selectStmt = "select TextVal from "+allTypesTable; 820 821 st = db.prepare(selectStmt); 822 st.step(); 823 // select double value 824 try { 825 st.column_int(0); 826 } catch (Exception e) { 827 //ok 828 } 829 } 830 831 /** 832 * @tests {@link Stmt#column_long(int)} 833 */ 834 @TestTargetNew( 835 level = TestLevel.COMPLETE, 836 notes = "method test", 837 method = "column_long", 838 args = {int.class} 839 ) 840 public void testColumn_long() { 841 Object columnObject = null; 842 int columnObjectCastFromLong; 843 long longColumn = 0; 844 try { 845 String selectStmt = "select * from "+DatabaseCreator.SIMPLE_TABLE1; 846 st = db.prepare(selectStmt); 847 st.step(); 848 columnObject = st.column(1); 849 longColumn = st.column_long(1); 850 assertNotNull(longColumn); 851 // column declared as integer 852 assertTrue("Integer".equalsIgnoreCase(st.column_decltype(1))); 853 int stSpeed = Integer.parseInt(columnObject.toString()); 854 assertNotNull(stSpeed); 855 assertEquals( longColumn, stSpeed); 856 } catch (Exception e) { 857 fail("Error in test setup : " + e.getMessage()); 858 e.printStackTrace(); 859 } 860 861 try { 862 st.column_long(4); 863 fail("Exception expected"); 864 } catch (Exception e) { 865 assertEquals( "column out of bounds" , e.getMessage()); 866 } 867 868 try { 869 st.column_long(-1); 870 fail("Exception expected"); 871 } catch (Exception e) { 872 assertEquals( "column out of bounds" , e.getMessage()); 873 } 874 } 875 876 /** 877 * @throws Exception 878 * @tests {@link Stmt#column_double(int)} 879 */ 880 @TestTargetNew( 881 level = TestLevel.COMPLETE, 882 notes = "method test", 883 method = "column_double", 884 args = {int.class} 885 ) 886 public void testColumn_double() throws Exception { 887 db.exec(createAllTypes, null); 888 db.exec(insertAllTypes, null); 889 890 Object columnObject = null; 891 double doubleColumn = 0; 892 double actualVal = 23.2; 893 String selectStmt = "select DoubleVal from "+allTypesTable; 894 895 st = db.prepare(selectStmt); 896 st.step(); 897 // select double value 898 doubleColumn = st.column_double(0); 899 assertNotNull(doubleColumn); 900 901 assertTrue("DOUBLE".equalsIgnoreCase(st.column_decltype(0))); 902 assertNotNull(doubleColumn); 903 assertEquals( actualVal, doubleColumn); 904 905 // Exception test 906 selectStmt = "select dateVal from "+allTypesTable; 907 908 st = db.prepare(selectStmt); 909 st.step(); 910 // select double value 911 try { 912 st.column_double(0); 913 } catch (Exception e) { 914 //ok 915 } 916 917 918 } 919 920 /** 921 * @throws Exception 922 * @tests {@link Stmt#column_bytes(int)} 923 */ 924 @TestTargetNew( 925 level = TestLevel.NOT_FEASIBLE, 926 notes = "not supported", 927 method = "column_bytes", 928 args = {int.class} 929 ) 930 public void testColumn_bytes() throws Exception { 931 932 db.exec("create table B(id integer primary key, val blob)",null); 933 db.exec("insert into B values(1, zeroblob(128))", null); 934 st = db.prepare("select val from B where id = 1"); 935 assertTrue(st.step()); 936 try { 937 st.column_bytes(0); 938 } catch (Exception e) { 939 assertEquals("unsupported", e.getMessage()); 940 } 941 } 942 943 /** 944 * @throws Exception 945 * @tests {@link Stmt#column_string(int)} 946 */ 947 @TestTargetNew( 948 level = TestLevel.COMPLETE, 949 notes = "method test", 950 method = "column_string", 951 args = {int.class} 952 ) 953 public void testColumn_string() throws Exception { 954 db.exec(createAllTypes, null); 955 db.exec(insertAllTypes, null); 956 957 Object columnObject = null; 958 String stringColumn = ""; 959 String actualVal = "test string"; 960 String selectStmt = "select charStr from "+allTypesTable; 961 962 st = db.prepare(selectStmt); 963 st.step(); 964 // select string value 965 stringColumn = st.column_string(0); 966 assertNotNull(stringColumn); 967 968 assertTrue("CHAR(20)".equalsIgnoreCase(st.column_decltype(0))); 969 assertNotNull(stringColumn); 970 assertEquals( actualVal, stringColumn); 971 972 // Exception test 973 selectStmt = "select DoubleVal from "+allTypesTable; 974 975 st = db.prepare(selectStmt); 976 st.step(); 977 // select double value 978 try { 979 st.column_string(0); 980 } catch (Exception e) { 981 //ok 982 } 983 } 984 985 /** 986 * @throws Exception 987 * @tests {@link Stmt#column_type(int)} 988 */ 989 @TestTargetNew( 990 level = TestLevel.SUFFICIENT, 991 notes = "method test", 992 method = "column_type", 993 args = {int.class} 994 ) 995 @KnownFailure("For numeric, float and blob wrong type is returned") 996 public void testColumn_type() throws Exception { 997 db.exec(createAllTypes, null); 998 db.exec(insertAllTypes, null); 999 st = db.prepare("select * from " + allTypesTable); 1000 st.step(); 1001 1002 // Exception test 1003 try { 1004 st.column_type(100); 1005 } catch (Exception e) { 1006 // ok 1007 } 1008 1009 /* 1010 Dictionary 1011 1012 public static final int SQLITE_INTEGER = 1; 1013 public static final int SQLITE_FLOAT = 2; 1014 public static final int SQLITE_BLOB = 4; 1015 public static final int SQLITE_NULL = 5; 1016 public static final int SQLITE3_TEXT = 3; 1017 public static final int SQLITE_NUMERIC = -1; 1018 */ 1019 1020 assertEquals(Constants.SQLITE3_TEXT, st.column_type(23)); // ok TEXT 1021 assertEquals(Constants.SQLITE3_TEXT, st.column_type(13)); // CHAR(20) 1022 1023 assertEquals(Constants.SQLITE_FLOAT, st.column_type(8)); 1024 assertEquals(Constants.SQLITE_FLOAT, st.column_type(9)); 1025 assertEquals(Constants.SQLITE_FLOAT, st.column_type(10)); // FLOAT 1026 1027 for (int i = 0; i < 8; i++) { 1028 assertEquals("Expected Integer at position " + i, 1029 Constants.SQLITE_INTEGER, st.column_type(i)); 1030 } 1031 1032 assertEquals(Constants.SQLITE_NULL, st.column_type(28)); 1033 assertEquals(Constants.SQLITE_NULL, st.column_type(29)); 1034 1035 // Failing tests 1036 assertTrue("NUMERIC".equalsIgnoreCase(st.column_decltype(12))); 1037 assertEquals(Constants.SQLITE_NUMERIC, st.column_type(12)); // NUMERIC 1038 // -> got 1039 // INTEGER 1040 1041 assertTrue("FLOAT".equalsIgnoreCase(st.column_decltype(11))); 1042 assertEquals(Constants.SQLITE_FLOAT, st.column_type(11)); // FLOAT -> 1043 // got INTEGER 1044 assertTrue("BLOB".equalsIgnoreCase(st.column_decltype(19))); 1045 assertEquals(Constants.SQLITE_BLOB, st.column_type(19)); // Blob got 1046 // INTEGER 1047 1048 } 1049 1050 /** 1051 * @throws Exception 1052 * @tests {@link Stmt#column_count() )} 1053 */ 1054 @TestTargetNew( 1055 level = TestLevel.COMPLETE, 1056 notes = "method test", 1057 method = "column_count", 1058 args = {} 1059 ) 1060 @KnownFailure("Wrong value is returned in case of a prepared statment to "+ 1061 "which a '*' bound ") 1062 public void testColumn_count() throws Exception { 1063 1064 String selectStmt = "select * from "+DatabaseCreator.SIMPLE_TABLE1; 1065 st = db.prepare(selectStmt); 1066 1067 assertEquals(3, st.column_count()); 1068 1069 st.step(); 1070 int columnCount = st.column_count(); 1071 assertNotNull(columnCount); 1072 assertEquals( 3, columnCount); 1073 1074 // actual prepared statement 1075 selectStmt = "select ? from "+DatabaseCreator.SIMPLE_TABLE1; 1076 st = db.prepare(selectStmt); 1077 1078 assertEquals(3, st.column_count()); 1079 1080 st.bind(1, "*"); 1081 st.step(); 1082 columnCount = st.column_count(); 1083 assertNotNull(columnCount); 1084 assertEquals( 3, columnCount); 1085 1086 } 1087 1088 /** 1089 * @throws Exception 1090 * @tests {@link Stmt#column(int) )} 1091 */ 1092 @TestTargetNew( 1093 level = TestLevel.COMPLETE, 1094 notes = "method test", 1095 method = "column", 1096 args = {int.class} 1097 ) 1098 public void testColumn() throws Exception { 1099 Object columnObject = null; 1100 int columnObjectCastFromLong; 1101 int intColumn = 0; 1102 try { 1103 String selectStmt = "select * from "+DatabaseCreator.SIMPLE_TABLE1; 1104 TableResult res = db.get_table(selectStmt); 1105 st = db.prepare(selectStmt); 1106 st.step(); 1107 columnObject = st.column(1); 1108 intColumn = st.column_int(1); 1109 assertNotNull(intColumn); 1110 assertTrue("Integer".equalsIgnoreCase(st.column_decltype(1))); 1111 int stSpeed = Integer.parseInt(columnObject.toString()); 1112 assertNotNull(stSpeed); 1113 assertEquals( intColumn, stSpeed); 1114 } catch (Exception e) { 1115 fail("Error in test setup : " + e.getMessage()); 1116 e.printStackTrace(); 1117 } 1118 1119 try { 1120 assertNotNull(columnObject); 1121 int dummy = ((Integer) columnObject).intValue(); 1122 fail("Cast to Integer should fail"); 1123 } catch (ClassCastException e) { 1124 assertEquals("java.lang.Long", e.getMessage()); 1125 } 1126 1127 try { 1128 st.column(4); 1129 fail("Exception expected"); 1130 } catch (Exception e) { 1131 assertEquals( "column out of bounds" , e.getMessage()); 1132 } 1133 1134 try { 1135 st.column(-1); 1136 fail("Exception expected"); 1137 } catch (Exception e) { 1138 assertEquals( "column out of bounds" , e.getMessage()); 1139 } 1140 } 1141 1142 /** 1143 * @tests {@link Stmt#column_table_name(int)} 1144 */ 1145 @TestTargetNew( 1146 level = TestLevel.NOT_FEASIBLE, 1147 notes = "not supported", 1148 method = "column_table_name", 1149 args = {int.class} 1150 ) 1151 public void testColumn_table_name() { 1152 try { 1153 st = db.prepare("select * from " + DatabaseCreator.SIMPLE_TABLE1); 1154 String name = st.column_table_name(1); 1155 fail("Function is now supported."); 1156 } catch (Exception e) { 1157 assertEquals("unsupported", e.getMessage()); 1158 } 1159 } 1160 1161 /** 1162 * @tests {@link Stmt#column_database_name(int)} 1163 */ 1164 @TestTargetNew( 1165 level = TestLevel.NOT_FEASIBLE, 1166 notes = "not supported", 1167 method = "column_database_name", 1168 args = {int.class} 1169 ) 1170 public void testColumn_database_name() { 1171 try { 1172 st = db.prepare("insert into " + DatabaseCreator.SIMPLE_TABLE1 1173 + " values (:one,:two,:three)"); 1174 String name = st.column_database_name(1); 1175 fail("Function is now supported."); 1176 } catch (Exception e) { 1177 assertEquals("unsupported", e.getMessage()); 1178 } 1179 1180 } 1181 1182 /** 1183 * @throws Exception 1184 * @tests {@link Stmt#column_decltype(int)} 1185 */ 1186 @TestTargetNew( 1187 level = TestLevel.SUFFICIENT, 1188 notes = "method test", 1189 method = "column_decltype", 1190 args = {int.class} 1191 ) 1192 public void testColumn_decltype() throws Exception { 1193 db.exec(createAllTypes, null); 1194 db.exec(insertAllTypes, null); 1195 st = db.prepare("select * from " + allTypesTable); 1196 st.step(); 1197 1198 // Exception test 1199 try { 1200 st.column_decltype(100); 1201 } catch (Exception e) { 1202 // ok 1203 } 1204 1205 assertTrue(st.column_decltype(0), "BOOLEAN".equalsIgnoreCase(st 1206 .column_decltype(0))); 1207 assertTrue(st.column_decltype(1), "INT".equalsIgnoreCase(st 1208 .column_decltype(1))); 1209 assertTrue(st.column_decltype(2), "LONG".equalsIgnoreCase(st 1210 .column_decltype(2))); 1211 assertTrue(st.column_decltype(3), "BIGINT".equalsIgnoreCase(st 1212 .column_decltype(3))); 1213 assertTrue(st.column_decltype(4), "TINYINT".equalsIgnoreCase(st 1214 .column_decltype(4))); 1215 assertTrue(st.column_decltype(5), "SMALLINT".equalsIgnoreCase(st 1216 .column_decltype(5))); 1217 assertTrue(st.column_decltype(6), "MEDIUMINT".equalsIgnoreCase(st 1218 .column_decltype(6))); 1219 assertTrue(st.column_decltype(7), "INTEGER".equalsIgnoreCase(st 1220 .column_decltype(7))); 1221 assertTrue(st.column_decltype(8), "REAL".equalsIgnoreCase(st 1222 .column_decltype(8))); 1223 assertTrue(st.column_decltype(9), "DOUBLE".equalsIgnoreCase(st 1224 .column_decltype(9))); 1225 assertTrue(st.column_decltype(10), "FLOAT".equalsIgnoreCase(st 1226 .column_decltype(10))); 1227 assertTrue(st.column_decltype(11), "DECIMAL".equalsIgnoreCase(st 1228 .column_decltype(11))); 1229 assertTrue(st.column_decltype(12), "NUMERIC".equalsIgnoreCase(st 1230 .column_decltype(12))); 1231 assertTrue(st.column_decltype(13), "CHAR(20)".equalsIgnoreCase(st 1232 .column_decltype(13))); 1233 1234 assertTrue(st.column_decltype(19), "BLOB".equalsIgnoreCase(st 1235 .column_decltype(19))); 1236 1237 assertTrue(st.column_decltype(23), "TEXT".equalsIgnoreCase(st 1238 .column_decltype(23))); 1239 assertTrue(st.column_decltype(28), "URL".equalsIgnoreCase(st 1240 .column_decltype(28))); 1241 assertTrue(st.column_decltype(29), "URL".equalsIgnoreCase(st 1242 .column_decltype(29))); 1243 } 1244 1245 /** 1246 * @tests {@link Stmt#column_origin_name(int)} 1247 */ 1248 @TestTargetNew( 1249 level = TestLevel.NOT_FEASIBLE, 1250 notes = "not supported", 1251 method = "column_origin_name", 1252 args = {int.class} 1253 ) 1254 public void testColumn_origin_name() { 1255 try { 1256 st = db.prepare("select * from " + DatabaseCreator.SIMPLE_TABLE1); 1257 String name = st.column_origin_name(1); 1258 fail("Function is now supported."); 1259 } catch (Exception e) { 1260 assertEquals("unsupported", e.getMessage()); 1261 } 1262 } 1263} 1264