|
1
25 package testsuite.regression;
26
27 import com.mysql.jdbc.NonRegisteringDriver;
28 import com.mysql.jdbc.NotUpdatable;
29 import com.mysql.jdbc.SQLError;
30
31 import testsuite.BaseTestCase;
32
33 import java.io.Reader  ;
34 import java.math.BigDecimal ;
35
36 import java.sql.Clob ;
37 import java.sql.Connection ;
38 import java.sql.DatabaseMetaData ;
39 import java.sql.PreparedStatement ;
40 import java.sql.ResultSet ;
41 import java.sql.ResultSetMetaData ;
42 import java.sql.SQLException ;
43 import java.sql.Statement ;
44 import java.sql.Timestamp ;
45
46 import java.util.ArrayList ;
47 import java.util.Calendar ;
48 import java.util.GregorianCalendar ;
49 import java.util.List ;
50 import java.util.Properties ;
51
52
57 public class ResultSetRegressionTest extends BaseTestCase {
58
64 public ResultSetRegressionTest(String name) {
65 super(name);
66 }
67
68
73 public static void main(String [] args) {
74 junit.textui.TestRunner.run(ResultSetRegressionTest.class);
75 }
76
77
84 public void testBug2359() throws Exception {
85 try {
86
147 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
148 this.stmt
149 .executeUpdate("CREATE TABLE testBug2359_1 (id INT) TYPE=InnoDB");
150 this.stmt.executeUpdate("INSERT INTO testBug2359_1 VALUES (1)");
151
152 this.pstmt = this.conn
153 .prepareStatement("SELECT max(id) FROM testBug2359_1");
154 this.rs = this.pstmt.executeQuery();
155
156 if (this.rs.next()) {
157 assertTrue(this.rs.getInt(1) != 0);
158 this.rs.close();
159 }
160
161 this.rs.close();
162 } finally {
163 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
164 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
165
166 this.rs.close();
167 this.pstmt.close();
168 }
169 }
170
171
177 public void testBug2623() throws Exception {
178 PreparedStatement pStmt = null;
179
180 try {
181 pStmt = this.conn
182 .prepareStatement("SELECT NOW()",
183 ResultSet.TYPE_SCROLL_SENSITIVE,
184 ResultSet.CONCUR_READ_ONLY);
185
186 this.rs = pStmt.executeQuery();
187
188 this.rs.absolute(1);
189 } finally {
190 if (this.rs != null) {
191 this.rs.close();
192 }
193
194 this.rs = null;
195
196 if (pStmt != null) {
197 pStmt.close();
198 }
199 }
200 }
201
202
209 public void testBug2654() throws Exception {
210 if (false) {
212 try {
213 this.stmt.executeUpdate("DROP TABLE IF EXISTS foo");
214 this.stmt.executeUpdate("DROP TABLE IF EXISTS bar");
215
216 this.stmt.executeUpdate("CREATE TABLE foo ("
217 + " id tinyint(3) default NULL,"
218 + " data varchar(255) default NULL"
219 + ") TYPE=MyISAM DEFAULT CHARSET=latin1");
220 this.stmt
221 .executeUpdate("INSERT INTO foo VALUES (1,'male'),(2,'female')");
222
223 this.stmt.executeUpdate("CREATE TABLE bar ("
224 + "id tinyint(3) unsigned default NULL,"
225 + "data char(3) default '0'"
226 + ") TYPE=MyISAM DEFAULT CHARSET=latin1");
227
228 this.stmt
229 .executeUpdate("INSERT INTO bar VALUES (1,'yes'),(2,'no')");
230
231 String statement = "select foo.id, foo.data, "
232 + "bar.data from foo, bar" + " where "
233 + "foo.id = bar.id order by foo.id";
234
235 String column = "foo.data";
236
237 this.rs = this.stmt.executeQuery(statement);
238
239 ResultSetMetaData rsmd = this.rs.getMetaData();
240 System.out.println(rsmd.getTableName(1));
241 System.out.println(rsmd.getColumnName(1));
242
243 this.rs.next();
244
245 String fooData = this.rs.getString(column);
246 } finally {
247 this.stmt.executeUpdate("DROP TABLE IF EXISTS foo");
248 this.stmt.executeUpdate("DROP TABLE IF EXISTS bar");
249 }
250 }
251 }
252
253
259 public void testClobTruncate() throws Exception {
260 try {
261 this.stmt.executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
262 this.stmt
263 .executeUpdate("CREATE TABLE testClobTruncate (field1 TEXT)");
264 this.stmt
265 .executeUpdate("INSERT INTO testClobTruncate VALUES ('abcdefg')");
266
267 this.rs = this.stmt.executeQuery("SELECT * FROM testClobTruncate");
268 this.rs.next();
269
270 Clob clob = this.rs.getClob(1);
271 clob.truncate(3);
272
273 Reader reader = clob.getCharacterStream();
274 char[] buf = new char[8];
275 int charsRead = reader.read(buf);
276
277 String clobAsString = new String (buf, 0, charsRead);
278
279 assertTrue(clobAsString.equals("abc"));
280 } finally {
281 this.stmt.executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
282 }
283 }
284
285
291 public void testClobberStreamingRS() throws Exception {
292 try {
293 Properties props = new Properties ();
294 props.setProperty("clobberStreamingResults", "true");
295
296 Connection clobberConn = getConnectionWithProps(props);
297
298 Statement clobberStmt = clobberConn.createStatement();
299
300 clobberStmt.executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
301 clobberStmt
302 .executeUpdate("CREATE TABLE StreamingClobber ( DUMMYID "
303 + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
304 clobberStmt
305 .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
306 clobberStmt
307 .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
308 clobberStmt
309 .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
310 clobberStmt
311 .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");
312
313 Statement streamStmt = null;
314
315 try {
316 streamStmt = clobberConn.createStatement(
317 java.sql.ResultSet.TYPE_FORWARD_ONLY,
318 java.sql.ResultSet.CONCUR_READ_ONLY);
319 streamStmt.setFetchSize(Integer.MIN_VALUE);
320
321 this.rs = streamStmt.executeQuery("SELECT DUMMYID, DUMMYNAME "
322 + "FROM StreamingClobber ORDER BY DUMMYID");
323
324 this.rs.next();
325
326 clobberStmt.executeQuery("SHOW VARIABLES");
329 this.rs.close();
330 } finally {
331 if (streamStmt != null) {
332 streamStmt.close();
333 }
334 }
335 } finally {
336 this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
337 }
338 }
339
340
346 public void testEmptyResultSetGet() throws Exception {
347 try {
348 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'foo'");
349 System.out.println(this.rs.getInt(1));
350 } catch (SQLException sqlEx) {
351 assertTrue("Correct exception not thrown",
352 SQLError.SQL_STATE_GENERAL_ERROR
353 .equals(sqlEx.getSQLState()));
354 }
355 }
356
357
364 public void testFixForBug1592() throws Exception {
365 if (versionMeetsMinimum(4, 1)) {
366 Statement updatableStmt = this.conn
367 .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
368 ResultSet.CONCUR_UPDATABLE);
369
370 try {
371 updatableStmt.execute("SELECT * FROM mysql.user");
372
373 this.rs = updatableStmt.getResultSet();
374 } catch (SQLException sqlEx) {
375 String message = sqlEx.getMessage();
376
377 if ((message != null) && (message.indexOf("denied") != -1)) {
378 System.err
379 .println("WARN: Can't complete testFixForBug1592(), access to"
380 + " 'mysql' database not allowed");
381 } else {
382 throw sqlEx;
383 }
384 }
385 }
386 }
387
388
396 public void testFixForBug2006() throws Exception {
397 try {
398 this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
399 this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
400 this.stmt
401 .executeUpdate("CREATE TABLE testFixForBug2006_1 (key_field INT NOT NULL)");
402 this.stmt
403 .executeUpdate("CREATE TABLE testFixForBug2006_2 (key_field INT NULL)");
404 this.stmt
405 .executeUpdate("INSERT INTO testFixForBug2006_1 VALUES (1)");
406
407 this.rs = this.stmt
408 .executeQuery("SELECT testFixForBug2006_1.key_field, testFixForBug2006_2.key_field FROM testFixForBug2006_1 LEFT JOIN testFixForBug2006_2 USING(key_field)");
409
410 ResultSetMetaData rsmd = this.rs.getMetaData();
411
412 assertTrue(rsmd.getColumnName(1).equals(rsmd.getColumnName(2)));
413 assertTrue(rsmd.isNullable(this.rs.findColumn("key_field")) == ResultSetMetaData.columnNoNulls);
414 assertTrue(rsmd.isNullable(2) == ResultSetMetaData.columnNullable);
415 assertTrue(this.rs.next());
416 assertTrue(this.rs.getObject(1) != null);
417 assertTrue(this.rs.getObject(2) == null);
418 } finally {
419 if (this.rs != null) {
420 try {
421 this.rs.close();
422 } catch (SQLException sqlEx) {
423 }
425
426 this.rs = null;
427 }
428
429 this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
430 this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
431 }
432 }
433
434
440 public void testGetLongBug() throws Exception {
441 this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
442 this.stmt
443 .executeUpdate("CREATE TABLE IF NOT EXISTS getLongBug (int_col int, bigint_col bigint)");
444
445 int intVal = 123456;
446 long longVal1 = 123456789012345678L;
447 long longVal2 = -2079305757640172711L;
448 this.stmt.executeUpdate("INSERT INTO getLongBug "
449 + "(int_col, bigint_col) " + "VALUES (" + intVal + ", "
450 + longVal1 + "), " + "(" + intVal + ", " + longVal2 + ")");
451
452 try {
453 this.rs = this.stmt
454 .executeQuery("SELECT int_col, bigint_col FROM getLongBug ORDER BY bigint_col DESC");
455 this.rs.next();
456 assertTrue(
457 "Values not decoded correctly",
458 ((this.rs.getInt(1) == intVal) && (this.rs.getLong(2) == longVal1)));
459 this.rs.next();
460 assertTrue(
461 "Values not decoded correctly",
462 ((this.rs.getInt(1) == intVal) && (this.rs.getLong(2) == longVal2)));
463 } finally {
464 if (this.rs != null) {
465 try {
466 this.rs.close();
467 } catch (Exception ex) {
468 }
470 }
471
472 this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
473 }
474 }
475
476
482 public void testGetTimestampWithDate() throws Exception {
483 try {
484 this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
485 this.stmt.executeUpdate("CREATE TABLE testGetTimestamp (d date)");
486 this.stmt
487 .executeUpdate("INSERT INTO testGetTimestamp values (now())");
488
489 this.rs = this.stmt.executeQuery("SELECT * FROM testGetTimestamp");
490 this.rs.next();
491 System.out.println(this.rs.getTimestamp(1));
492 } finally {
493 this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
494 }
495 }
496
497
504 public void testIsBeforeFirstOnEmpty() throws Exception {
505 try {
506 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
508 assertTrue("Non-empty search should return true", this.rs
509 .isBeforeFirst());
510
511 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'garbage'");
514 assertTrue("Empty search should return false ", !this.rs
515 .isBeforeFirst());
516 } finally {
517 this.rs.close();
518 }
519 }
520
521
528 public void testMetaDataIsWritable() throws Exception {
529 try {
530 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
532
533 ResultSetMetaData rsmd = this.rs.getMetaData();
534
535 int numColumns = rsmd.getColumnCount();
536
537 for (int i = 1; i <= numColumns; i++) {
538 assertTrue("rsmd.isWritable() should != rsmd.isReadOnly()",
539 rsmd.isWritable(i) != rsmd.isReadOnly(i));
540 }
541 } finally {
542 this.rs.close();
543 }
544 }
545
546
552 public void testNextAndPrevious() throws Exception {
553 try {
554 this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
555 this.stmt
556 .executeUpdate("CREATE TABLE testNextAndPrevious (field1 int)");
557 this.stmt
558 .executeUpdate("INSERT INTO testNextAndPrevious VALUES (1)");
559
560 this.rs = this.stmt
561 .executeQuery("SELECT * from testNextAndPrevious");
562
563 System.out.println("Currently at row " + this.rs.getRow());
564 this.rs.next();
565 System.out.println("Value at row " + this.rs.getRow() + " is "
566 + this.rs.getString(1));
567
568 this.rs.previous();
569
570 try {
571 System.out.println("Value at row " + this.rs.getRow() + " is "
572 + this.rs.getString(1));
573 fail("Should not be able to retrieve values with invalid cursor");
574 } catch (SQLException sqlEx) {
575 assertTrue(sqlEx.getMessage().startsWith("Before start"));
576 }
577
578 this.rs.next();
579
580 this.rs.next();
581
582 try {
583 System.out.println("Value at row " + this.rs.getRow() + " is "
584 + this.rs.getString(1));
585 fail("Should not be able to retrieve values with invalid cursor");
586 } catch (SQLException sqlEx) {
587 assertTrue(sqlEx.getMessage().startsWith("After end"));
588 }
589 } finally {
590 this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
591 }
592 }
593
594
601 public void testNotUpdatable() throws Exception {
602 this.rs = null;
603
604 try {
605 String sQuery = "SHOW VARIABLES";
606 this.pstmt = this.conn
607 .prepareStatement(sQuery, ResultSet.TYPE_SCROLL_SENSITIVE,
608 ResultSet.CONCUR_UPDATABLE);
609
610 this.rs = this.pstmt.executeQuery();
611
612 if (this.rs.next()) {
613 this.rs.absolute(1);
614
615 try {
616 this.rs.updateInt(1, 1);
617 } catch (SQLException sqlEx) {
618 assertTrue(sqlEx instanceof NotUpdatable);
619 }
620
621 try {
622 this.rs.updateString(1, "1");
623 } catch (SQLException sqlEx) {
624 assertTrue(sqlEx instanceof NotUpdatable);
625 }
626 }
627 } finally {
628 if (this.pstmt != null) {
629 try {
630 this.pstmt.close();
631 } catch (Exception e) {
632 }
634 }
635 }
636 }
637
638
644 public void testStreamingRegBug() throws Exception {
645 try {
646 this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
647 this.stmt
648 .executeUpdate("CREATE TABLE StreamingRegBug ( DUMMYID "
649 + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
650 this.stmt
651 .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
652 this.stmt
653 .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
654 this.stmt
655 .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
656 this.stmt
657 .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");
658
659 PreparedStatement streamStmt = null;
660
661 try {
662 streamStmt = this.conn.prepareStatement(
663 "SELECT DUMMYID, DUMMYNAME "
664 + "FROM StreamingRegBug ORDER BY DUMMYID",
665 java.sql.ResultSet.TYPE_FORWARD_ONLY,
666 java.sql.ResultSet.CONCUR_READ_ONLY);
667 streamStmt.setFetchSize(Integer.MIN_VALUE);
668
669 this.rs = streamStmt.executeQuery();
670
671 while (this.rs.next()) {
672 this.rs.getString(1);
673 }
674
675 this.rs.close(); } catch (SQLException sqlEx) {
677
678 } finally {
679 if (streamStmt != null) {
680 try {
681 streamStmt.close();
682 } catch (SQLException exWhileClose) {
683 exWhileClose.printStackTrace();
684 }
685 }
686 }
687 } finally {
688 this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
689 }
690 }
691
692
699 public void testUpdatability() throws Exception {
700 this.rs = null;
701
702 this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
703 this.stmt.execute("CREATE TABLE IF NOT EXISTS updatabilityBug ("
704 + " id int(10) unsigned NOT NULL auto_increment,"
705 + " field1 varchar(32) NOT NULL default '',"
706 + " field2 varchar(128) NOT NULL default '',"
707 + " field3 varchar(128) default NULL,"
708 + " field4 varchar(128) default NULL,"
709 + " field5 varchar(64) default NULL,"
710 + " field6 int(10) unsigned default NULL,"
711 + " field7 varchar(64) default NULL," + " PRIMARY KEY (id)"
712 + ") TYPE=InnoDB;");
713 this.stmt.executeUpdate("insert into updatabilityBug (id) values (1)");
714
715 try {
716 String sQuery = " SELECT * FROM updatabilityBug WHERE id = ? ";
717 this.pstmt = this.conn
718 .prepareStatement(sQuery, ResultSet.TYPE_SCROLL_SENSITIVE,
719 ResultSet.CONCUR_UPDATABLE);
720 this.conn.setAutoCommit(false);
721 this.pstmt.setInt(1, 1);
722 this.rs = this.pstmt.executeQuery();
723
724 if (this.rs.next()) {
725 this.rs.absolute(1);
726 this.rs.updateInt("id", 1);
727 this.rs.updateString("field1", "1");
728 this.rs.updateString("field2", "1");
729 this.rs.updateString("field3", "1");
730 this.rs.updateString("field4", "1");
731 this.rs.updateString("field5", "1");
732 this.rs.updateInt("field6", 1);
733 this.rs.updateString("field7", "1");
734 this.rs.updateRow();
735 }
736
737 this.conn.commit();
738 this.conn.setAutoCommit(true);
739 } finally {
740 if (this.pstmt != null) {
741 try {
742 this.pstmt.close();
743 } catch (Exception e) {
744 }
746 }
747
748 this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
749 }
750 }
751
752
758 public void testUpdatabilityAndEscaping() throws Exception {
759 Properties props = new Properties ();
760 props.setProperty("useUnicode", "true");
761 props.setProperty("characterEncoding", "big5");
762
763 Connection updConn = getConnectionWithProps(props);
764 Statement updStmt = updConn.createStatement(
765 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
766
767 try {
768 updStmt
769 .executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
770 updStmt
771 .executeUpdate("CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
772 updStmt
773 .executeUpdate("INSERT INTO testUpdatesWithEscaping VALUES (1, null)");
774
775 String stringToUpdate = "\" \\ '";
776
777 this.rs = updStmt
778 .executeQuery("SELECT * from testUpdatesWithEscaping");
779
780 this.rs.next();
781 this.rs.updateString(2, stringToUpdate);
782 this.rs.updateRow();
783
784 assertTrue(stringToUpdate.equals(this.rs.getString(2)));
785 } finally {
786 updStmt
787 .executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
788 updStmt.close();
789 updConn.close();
790 }
791 }
792
793
800 public void testUpdatabilityWithQuotes() throws Exception {
801 Statement updStmt = null;
802
803 try {
804 this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");
805 this.stmt
806 .executeUpdate("CREATE TABLE testUpdWithQuotes (keyField CHAR(32) PRIMARY KEY NOT NULL, field2 int)");
807
808 PreparedStatement pStmt = this.conn
809 .prepareStatement("INSERT INTO testUpdWithQuotes VALUES (?, ?)");
810 pStmt.setString(1, "Abe's");
811 pStmt.setInt(2, 1);
812 pStmt.executeUpdate();
813
814 updStmt = this.conn
815 .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
816 ResultSet.CONCUR_UPDATABLE);
817
818 this.rs = updStmt.executeQuery("SELECT * FROM testUpdWithQuotes");
819 this.rs.next();
820 this.rs.updateInt(2, 2);
821 this.rs.updateRow();
822 } finally {
823 this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");
824
825 if (this.rs != null) {
826 this.rs.close();
827 }
828
829 this.rs = null;
830
831 if (updStmt != null) {
832 updStmt.close();
833 }
834
835 updStmt = null;
836 }
837 }
838
839
845 public void testUpdateClob() throws Exception {
846 Statement updatableStmt = this.conn.createStatement(
847 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
848
849 try {
850 this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
851 this.stmt
852 .executeUpdate("CREATE TABLE testUpdateClob(intField INT NOT NULL PRIMARY KEY, clobField TEXT)");
853 this.stmt
854 .executeUpdate("INSERT INTO testUpdateClob VALUES (1, 'foo')");
855
856 this.rs = updatableStmt
857 .executeQuery("SELECT intField, clobField FROM testUpdateClob");
858 this.rs.next();
859
860 Clob clob = this.rs.getClob(2);
861
862 clob.setString(1, "bar");
863
864 this.rs.updateClob(2, clob);
865 this.rs.updateRow();
866
867 this.rs.moveToInsertRow();
868
869 clob.setString(1, "baz");
870 this.rs.updateInt(1, 2);
871 this.rs.updateClob(2, clob);
872 this.rs.insertRow();
873
874 clob.setString(1, "bat");
875 this.rs.updateInt(1, 3);
876 this.rs.updateClob(2, clob);
877 this.rs.insertRow();
878
879 this.rs.close();
880
881 this.rs = this.stmt
882 .executeQuery("SELECT intField, clobField FROM testUpdateClob ORDER BY intField");
883
884 this.rs.next();
885 assertTrue((this.rs.getInt(1) == 1)
886 && this.rs.getString(2).equals("bar"));
887
888 this.rs.next();
889 assertTrue((this.rs.getInt(1) == 2)
890 && this.rs.getString(2).equals("baz"));
891
892 this.rs.next();
893 assertTrue((this.rs.getInt(1) == 3)
894 && this.rs.getString(2).equals("bat"));
895 } finally {
896 this.stmt.executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
897 }
898 }
899
900
907 public void testBug4482() throws Exception {
908 this.rs = this.conn.prepareStatement("SELECT 'abcdef'").executeQuery();
909 assertTrue(this.rs.next());
910 assertTrue(this.rs.getObject(1) instanceof String );
911 }
912
913
917 public void testBug4689() throws Exception {
918 try {
919 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4689");
920 this.stmt
921 .executeUpdate("CREATE TABLE testBug4689 (tinyintField tinyint, tinyintFieldNull tinyint, "
922 + "intField int, intFieldNull int, "
923 + "bigintField bigint, bigintFieldNull bigint, "
924 + "shortField smallint, shortFieldNull smallint, "
925 + "doubleField double, doubleFieldNull double)");
926
927 this.stmt.executeUpdate("INSERT INTO testBug4689 VALUES (1, null, "
928 + "1, null, " + "1, null, " + "1, null, " + "1, null)");
929
930 PreparedStatement pStmt = this.conn
931 .prepareStatement("SELECT tinyintField, tinyintFieldNull,"
932 + "intField, intFieldNull, "
933 + "bigintField, bigintFieldNull, "
934 + "shortField, shortFieldNull, "
935 + "doubleField, doubleFieldNull FROM testBug4689");
936 this.rs = pStmt.executeQuery();
937 assertTrue(this.rs.next());
938
939 assertTrue(this.rs.getByte(1) == 1);
940 assertTrue(this.rs.wasNull() == false);
941 assertTrue(this.rs.getByte(2) == 0);
942 assertTrue(this.rs.wasNull() == true);
943
944 assertTrue(this.rs.getInt(3) == 1);
945 assertTrue(this.rs.wasNull() == false);
946 assertTrue(this.rs.getInt(4) == 0);
947 assertTrue(this.rs.wasNull() == true);
948
949 assertTrue(this.rs.getInt(5) == 1);
950 assertTrue(this.rs.wasNull() == false);
951 assertTrue(this.rs.getInt(6) == 0);
952 assertTrue(this.rs.wasNull() == true);
953
954 assertTrue(this.rs.getShort(7) == 1);
955 assertTrue(this.rs.wasNull() == false);
956 assertTrue(this.rs.getShort(8) == 0);
957 assertTrue(this.rs.wasNull() == true);
958
959 assertTrue(this.rs.getDouble(9) == 1);
960 assertTrue(this.rs.wasNull() == false);
961 assertTrue(this.rs.getDouble(10) == 0);
962 assertTrue(this.rs.wasNull() == true);
963 } finally {
964 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4689");
965 }
966 }
967
968
975 public void testBug5032() throws Exception {
976 if (versionMeetsMinimum(4, 1)) {
977 PreparedStatement pStmt = null;
978
979 try {
980 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5032");
981 this.stmt.executeUpdate("CREATE TABLE testBug5032(field1 BIT)");
982 this.stmt.executeUpdate("INSERT INTO testBug5032 VALUES (1)");
983
984 pStmt = this.conn
985 .prepareStatement("SELECT field1 FROM testBug5032");
986 this.rs = pStmt.executeQuery();
987 assertTrue(this.rs.next());
988 assertTrue(this.rs.getObject(1) instanceof Boolean );
989 } finally {
990 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5032");
991
992 if (pStmt != null) {
993 pStmt.close();
994 }
995 }
996 }
997 }
998
999
1008 public void testBug5069() throws Exception {
1009 try {
1010 this.rs = this.stmt.executeQuery("SELECT 1");
1011 this.rs.close();
1012
1013 try {
1014 ResultSetMetaData md = this.rs.getMetaData();
1015 } catch (NullPointerException npEx) {
1016 fail("Should not catch NullPointerException here");
1017 } catch (SQLException sqlEx) {
1018 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1019 .getSQLState()));
1020 }
1021
1022 try {
1023 this.rs.getRow();
1024 } catch (NullPointerException npEx) {
1025 fail("Should not catch NullPointerException here");
1026 } catch (SQLException sqlEx) {
1027 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1028 .getSQLState()));
1029 }
1030
1031 try {
1032 this.rs.getWarnings();
1033 } catch (NullPointerException npEx) {
1034 fail("Should not catch NullPointerException here");
1035 } catch (SQLException sqlEx) {
1036 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1037 .getSQLState()));
1038 }
1039
1040 try {
1041 this.rs.first();
1042 } catch (NullPointerException npEx) {
1043 fail("Should not catch NullPointerException here");
1044 } catch (SQLException sqlEx) {
1045 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1046 .getSQLState()));
1047 }
1048
1049 try {
1050 this.rs.beforeFirst();
1051 } catch (NullPointerException npEx) {
1052 fail("Should not catch NullPointerException here");
1053 } catch (SQLException sqlEx) {
1054 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1055 .getSQLState()));
1056 }
1057
1058 try {
1059 this.rs.last();
1060 } catch (NullPointerException npEx) {
1061 fail("Should not catch NullPointerException here");
1062 } catch (SQLException sqlEx) {
1063 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1064 .getSQLState()));
1065 }
1066
1067 try {
1068 this.rs.afterLast();
1069 } catch (NullPointerException npEx) {
1070 fail("Should not catch NullPointerException here");
1071 } catch (SQLException sqlEx) {
1072 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1073 .getSQLState()));
1074 }
1075
1076 try {
1077 this.rs.relative(0);
1078 } catch (NullPointerException npEx) {
1079 fail("Should not catch NullPointerException here");
1080 } catch (SQLException sqlEx) {
1081 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1082 .getSQLState()));
1083 }
1084
1085 try {
1086 this.rs.next();
1087 } catch (NullPointerException npEx) {
1088 fail("Should not catch NullPointerException here");
1089 } catch (SQLException sqlEx) {
1090 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1091 .getSQLState()));
1092 }
1093
1094 try {
1095 this.rs.previous();
1096 } catch (NullPointerException npEx) {
1097 fail("Should not catch NullPointerException here");
1098 } catch (SQLException sqlEx) {
1099 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1100 .getSQLState()));
1101 }
1102
1103 try {
1104 this.rs.isBeforeFirst();
1105 } catch (NullPointerException npEx) {
1106 fail("Should not catch NullPointerException here");
1107 } catch (SQLException sqlEx) {
1108 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1109 .getSQLState()));
1110 }
1111
1112 try {
1113 this.rs.isFirst();
1114 } catch (NullPointerException npEx) {
1115 fail("Should not catch NullPointerException here");
1116 } catch (SQLException sqlEx) {
1117 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1118 .getSQLState()));
1119 }
1120
1121 try {
1122 this.rs.isAfterLast();
1123 } catch (NullPointerException npEx) {
1124 fail("Should not catch NullPointerException here");
1125 } catch (SQLException sqlEx) {
1126 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1127 .getSQLState()));
1128 }
1129
1130 try {
1131 this.rs.isLast();
1132 } catch (NullPointerException npEx) {
1133 fail("Should not catch NullPointerException here");
1134 } catch (SQLException sqlEx) {
1135 assertTrue(SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
1136 .getSQLState()));
1137 }
1138 } finally {
1139 if (this.rs != null) {
1140 this.rs.close();
1141 this.rs = null;
1142 }
1143 }
1144 }
1145
1146
1155 public void testBug5235() throws Exception {
1156 try {
1157 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
1158 this.stmt.executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
1159 this.stmt
1160 .executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");
1161
1162 Properties props = new Properties ();
1163 props.setProperty("zeroDateTimeBehavior", "convertToNull");
1164
1165 Connection nullConn = getConnectionWithProps(props);
1166
1167 this.rs = nullConn.createStatement().executeQuery(
1168 "SELECT field1 FROM testBug5235");
1169 this.rs.next();
1170 assertTrue(null == this.rs.getObject(1));
1171 } finally {
1172 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
1173 }
1174 }
1175
1176
1183 public void testBug5136() throws Exception {
1184 if (false) {
1185 PreparedStatement toGeom = this.conn
1186 .prepareStatement("select GeomFromText(?)");
1187 PreparedStatement toText = this.conn
1188 .prepareStatement("select AsText(?)");
1189
1190 String inText = "POINT(146.67596278 -36.54368233)";
1191
1192 this.rs = this.stmt.executeQuery("select AsText(GeomFromText('"
1194 + inText + "'))");
1195 this.rs.next();
1196
1197 String outText = this.rs.getString(1);
1198 this.rs.close();
1199 assertTrue(
1200 "Server side only\n In: " + inText + "\nOut: " + outText,
1201 inText.equals(outText));
1202
1203 toGeom.setString(1, inText);
1205 this.rs = toGeom.executeQuery();
1206 this.rs.next();
1207
1208 Object geom = this.rs.getObject(1);
1210 this.rs.close();
1211 toText.setObject(1, geom);
1212 this.rs = toText.executeQuery();
1213 this.rs.next();
1214
1215 outText = this.rs.getString(1);
1217 this.rs.close();
1218 assertTrue("Server to client and back\n In: " + inText + "\nOut: "
1219 + outText, inText.equals(outText));
1220 }
1221 }
1222
1223
1230 public void testBug5664() throws Exception {
1231 try {
1232 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5664");
1233 this.stmt
1234 .executeUpdate("CREATE TABLE testBug5664 (pkfield int PRIMARY KEY NOT NULL, field1 SMALLINT)");
1235 this.stmt.executeUpdate("INSERT INTO testBug5664 VALUES (1, 1)");
1236
1237 Statement updatableStmt = this.conn
1238 .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
1239 ResultSet.CONCUR_UPDATABLE);
1240
1241 this.rs = updatableStmt
1242 .executeQuery("SELECT pkfield, field1 FROM testBug5664");
1243 this.rs.next();
1244 this.rs.moveToInsertRow();
1245 this.rs.updateInt(1, 2);
1246 this.rs.updateByte(2, (byte) 2);
1247 } finally {
1248 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5664");
1249 }
1250 }
1251
1252 public void testBogusTimestampAsString() throws Exception {
1253
1254 this.rs = this.stmt.executeQuery("SELECT '2004-08-13 13:21:17.'");
1255
1256 this.rs.next();
1257
1258 this.rs.getTimestamp(1);
1260
1261 }
1262
1263
1267 public void testBug5717() throws Exception {
1268 try {
1269 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5717");
1270 this.stmt.executeUpdate("CREATE TABLE testBug5717 (field1 DOUBLE)");
1271 this.pstmt = this.conn
1272 .prepareStatement("INSERT INTO testBug5717 VALUES (?)");
1273
1274 try {
1275 this.pstmt.setDouble(1, Double.NEGATIVE_INFINITY);
1276 fail("Exception should've been thrown");
1277 } catch (Exception ex) {
1278 }
1280
1281 try {
1282 this.pstmt.setDouble(1, Double.POSITIVE_INFINITY);
1283 fail("Exception should've been thrown");
1284 } catch (Exception ex) {
1285 }
1287
1288 try {
1289 this.pstmt.setDouble(1, Double.NaN);
1290 fail("Exception should've been thrown");
1291 } catch (Exception ex) {
1292 }
1294 } finally {
1295 if (this.pstmt != null) {
1296 this.pstmt.close();
1297 }
1298
1299 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5717");
1300 }
1301 }
1302
1303
1310 public void testBug6537() throws Exception {
1311 if (versionMeetsMinimum(4, 1, 0)) {
1312 String tableName = "testBug6537";
1313
1314 try {
1315 createTable(
1316 tableName,
1317 "(`id` int(11) NOT NULL default '0',"
1318 + "`value` decimal(10,2) NOT NULL default '0.00', `stringval` varchar(10),"
1319 + "PRIMARY KEY (`id`)"
1320 + ") ENGINE=MyISAM DEFAULT CHARSET=latin1");
1321 this.stmt
1322 .executeUpdate("INSERT INTO "
1323 + tableName
1324 + "(id, value, stringval) VALUES (1, 100.00, '100.00'), (2, 200, '200')");
1325
1326 String sql = "SELECT SUM(value) as total FROM " + tableName
1327 + " WHERE id = ? ";
1328 PreparedStatement pStmt = this.conn.prepareStatement(sql);
1329 pStmt.setInt(1, 1);
1330 this.rs = pStmt.executeQuery();
1331 assertTrue(this.rs.next());
1332
1333 assertTrue("100.00".equals(this.rs.getBigDecimal("total")
1334 .toString()));
1335
1336 sql = "SELECT stringval as total FROM " + tableName
1337 + " WHERE id = ? ";
1338 pStmt = this.conn.prepareStatement(sql);
1339 pStmt.setInt(1, 2);
1340 this.rs = pStmt.executeQuery();
1341 assertTrue(this.rs.next());
1342
1343 assertTrue("200.00".equals(this.rs.getBigDecimal("total", 2)
1344 .toString()));
1345
1346 } finally {
1347 dropTable(tableName);
1348 }
1349 }
1350 }
1351
1352
1359 public void testBug6231() throws Exception {
1360 try {
1361 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
1362 this.stmt.executeUpdate("CREATE TABLE testBug6231 (field1 TIME)");
1363 this.stmt
1364 .executeUpdate("INSERT INTO testBug6231 VALUES ('09:16:00')");
1365
1366 this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug6231");
1367 this.rs.next();
1368 long asMillis = this.rs.getTimestamp(1).getTime();
1369 Calendar cal = Calendar.getInstance();
1370 cal.setTimeInMillis(asMillis);
1371 assertTrue(cal.get(Calendar.HOUR) == 9);
1372 assertTrue(cal.get(Calendar.MINUTE) == 16);
1373 assertTrue(cal.get(Calendar.SECOND) == 0);
1374 } finally {
1375 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
1376 }
1377 }
1378
1379 public void testBug6619() throws Exception {
1380 try {
1381 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
1382 this.stmt.executeUpdate("CREATE TABLE testBug6619 (field1 int)");
1383 this.stmt.executeUpdate("INSERT INTO testBug6619 VALUES (1), (2)");
1384
1385 PreparedStatement pStmt = this.conn
1386 .prepareStatement("SELECT SUM(field1) FROM testBug6619");
1387
1388 this.rs = pStmt.executeQuery();
1389 this.rs.next();
1390 System.out.println(this.rs.getString(1));
1391
1392 } finally {
1393 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
1394 }
1395 }
1396
1397 public void testBug6743() throws Exception {
1398 String katakanaStr = "\u30BD";
1400
1401 Properties props = new Properties ();
1402
1403 props.setProperty("useUnicode", "true");
1404 props.setProperty("characterEncoding", "SJIS");
1405
1406 Connection sjisConn = null;
1407 Statement sjisStmt = null;
1408
1409 try {
1410 sjisConn = getConnectionWithProps(props);
1411 sjisStmt = sjisConn.createStatement(
1412 ResultSet.TYPE_SCROLL_INSENSITIVE,
1413 ResultSet.CONCUR_UPDATABLE);
1414
1415 sjisStmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
1416 StringBuffer queryBuf = new StringBuffer (
1417 "CREATE TABLE testBug6743 (pkField INT NOT NULL PRIMARY KEY, field1 VARCHAR(32)");
1418
1419 if (versionMeetsMinimum(4, 1)) {
1420 queryBuf.append(" CHARACTER SET SJIS");
1421 }
1422
1423 queryBuf.append(")");
1424 sjisStmt.executeUpdate(queryBuf.toString());
1425 sjisStmt.executeUpdate("INSERT INTO testBug6743 VALUES (1, 'abc')");
1426
1427 this.rs = sjisStmt
1428 .executeQuery("SELECT pkField, field1 FROM testBug6743");
1429 this.rs.next();
1430 this.rs.updateString(2, katakanaStr);
1431 this.rs.updateRow();
1432
1433 String retrString = this.rs.getString(2);
1434 assertTrue(katakanaStr.equals(retrString));
1435
1436 this.rs = sjisStmt
1437 .executeQuery("SELECT pkField, field1 FROM testBug6743");
1438 this.rs.next();
1439
1440 retrString = this.rs.getString(2);
1441 assertTrue(katakanaStr.equals(retrString));
1442 } finally {
1443 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
1444
1445 if (sjisStmt != null) {
1446 sjisStmt.close();
1447 }
1448
1449 if (sjisConn != null) {
1450 sjisConn.close();
1451 }
1452 }
1453 }
1454
1455
1462 public void testBug6561() throws Exception {
1463
1464 try {
1465 Properties props = new Properties ();
1466 props.setProperty("zeroDateTimeBehavior", "convertToNull");
1467
1468 Connection zeroConn = getConnectionWithProps(props);
1469
1470 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6561");
1471 this.stmt
1472 .executeUpdate("CREATE TABLE testBug6561 (ofield int, field1 DATE, field2 integer, field3 integer)");
1473 this.stmt
1474 .executeUpdate("INSERT INTO testBug6561 (ofield, field1,field2,field3) VALUES (1, 0,NULL,0)");
1475 this.stmt
1476 .executeUpdate("INSERT INTO testBug6561 (ofield, field1,field2,field3) VALUES (2, '2004-11-20',NULL,0)");
1477
1478 PreparedStatement ps = zeroConn
1479 .prepareStatement("SELECT field1,field2,field3 FROM testBug6561 ORDER BY ofield");
1480 this.rs = ps.executeQuery();
1481
1482 assertTrue(rs.next());
1483 assertTrue(null == rs.getObject("field1"));
1484 assertTrue(null == rs.getObject("field2"));
1485 assertTrue(0 == rs.getInt("field3"));
1486
1487 assertTrue(rs.next());
1488 assertTrue(rs.getString("field1").equals("2004-11-20"));
1489 assertTrue(null == rs.getObject("field2"));
1490 assertTrue(0 == rs.getInt("field3"));
1491
1492 ps.close();
1493 } finally {
1494 this.stmt.executeUpdate("DROP TABLE IF EXISTS test");
1495 }
1496 }
1497
1498 public void testBug7686() throws SQLException {
1499 String tableName = "testBug7686";
1500 createTable(tableName, "(id1 int(10) unsigned NOT NULL,"
1501 + " id2 DATETIME, "
1502 + " field1 varchar(128) NOT NULL default '',"
1503 + " PRIMARY KEY (id1, id2)) TYPE=InnoDB;");
1504
1505 this.stmt.executeUpdate("insert into " + tableName
1506 + " (id1, id2, field1)"
1507 + " values (1, '2005-01-05 13:59:20', 'foo')");
1508
1509 String sQuery = " SELECT * FROM " + tableName
1510 + " WHERE id1 = ? AND id2 = ?";
1511 this.pstmt = this.conn.prepareStatement(sQuery,
1512 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
1513
1514 this.conn.setAutoCommit(false);
1515 this.pstmt.setInt(1, 1);
1516 GregorianCalendar cal = new GregorianCalendar ();
1517 cal.clear();
1518 cal.set(2005, 00, 05, 13, 59, 20);
1519 Timestamp jan5before2pm = new java.sql.Timestamp (cal.getTimeInMillis());
1520 this.pstmt.setTimestamp(2, jan5before2pm);
1521 this.rs = this.pstmt.executeQuery();
1522 assertTrue(this.rs.next());
1523 this.rs.absolute(1);
1524 this.rs.updateString("field1", "bar");
1525 this.rs.updateRow();
1526 this.conn.commit();
1527 this.conn.setAutoCommit(true);
1528 }
1529
1530
1537 public void testBug7715() throws Exception {
1538 PreparedStatement pStmt = null;
1539
1540 try {
1541 this.stmt
1542 .executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
1543 this.stmt
1544 .executeUpdate("CREATE TABLE testConvertedBinaryTimestamp (field1 VARCHAR(32), field2 VARCHAR(32), field3 VARCHAR(32), field4 TIMESTAMP)");
1545 this.stmt
1546 .executeUpdate("INSERT INTO testConvertedBinaryTimestamp VALUES ('abc', 'def', 'ghi', NOW())");
1547
1548 pStmt = this.conn
1549 .prepareStatement(
1550 "SELECT field1, field2, field3, field4 FROM testConvertedBinaryTimestamp",
1551 ResultSet.TYPE_SCROLL_SENSITIVE,
1552 ResultSet.CONCUR_UPDATABLE);
1553
1554 this.rs = pStmt.executeQuery();
1555 assertTrue(this.rs.next());
1556
1557 this.rs.getObject(4); } finally {
1559 this.stmt
1560 .executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
1561 }
1562 }
1563
1564
1571 public void testBug8428() throws Exception {
1572 Connection noSyncConn = null;
1573
1574 try {
1575 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
1576 this.stmt
1577 .executeUpdate("CREATE TABLE testBug8428 (field1 YEAR, field2 DATETIME)");
1578 this.stmt
1579 .executeUpdate("INSERT INTO testBug8428 VALUES ('1999', '2005-02-11 12:54:41')");
1580
1581 Properties props = new Properties ();
1582 props.setProperty("noDatetimeStringSync", "true");
1583 props.setProperty("useUsageAdvisor", "true");
1584 props.setProperty("yearIsDateType", "false");
1586 noSyncConn = getConnectionWithProps(props);
1587
1588 this.rs = noSyncConn.createStatement().executeQuery(
1589 "SELECT field1, field2 FROM testBug8428");
1590 this.rs.next();
1591 assertEquals("1999", this.rs.getString(1));
1592 assertEquals("2005-02-11 12:54:41", this.rs.getString(2));
1593
1594 this.rs = noSyncConn.prepareStatement(
1595 "SELECT field1, field2 FROM testBug8428").executeQuery();
1596 this.rs.next();
1597 assertEquals("1999", this.rs.getString(1));
1598 assertEquals("2005-02-11 12:54:41", this.rs.getString(2));
1599 } finally {
1600 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
1601 }
1602 }
1603
1604
1611 public void testBug8868() throws Exception {
1612 if (versionMeetsMinimum(4, 1)) {
1613 createTable("testBug8868",
1614 "(field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY)");
1615 this.stmt
1616 .executeUpdate("INSERT INTO testBug8868 VALUES (NOW(), 'abcd')");
1617 try {
1618 this.rs = this.stmt
1619 .executeQuery("SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868");
1620 this.rs.next();
1621 assertEquals("java.lang.String", this.rs.getObject(1)
1622 .getClass().getName());
1623 } finally {
1624 if (this.rs != null) {
1625 this.rs.close();
1626 }
1627 }
1628 }
1629 }
1630
1631
1638 public void testBug9098() throws Exception {
1639 if (versionMeetsMinimum(4, 1, 10)) {
1640 Statement updatableStmt = null;
1641
1642 try {
1643 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug9098");
1644 this.stmt
1645 .executeUpdate("CREATE TABLE testBug9098(pkfield INT PRIMARY KEY NOT NULL AUTO_INCREMENT, \n"
1646 + "tsfield TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsfield2 TIMESTAMP NOT NULL DEFAULT '2005-12-25 12:20:52', charfield VARCHAR(4) NOT NULL DEFAULT 'abcd')");
1647 updatableStmt = this.conn.createStatement(
1648 ResultSet.TYPE_SCROLL_INSENSITIVE,
1649 ResultSet.CONCUR_UPDATABLE);
1650 this.rs = updatableStmt
1651 .executeQuery("SELECT pkfield, tsfield, tsfield2, charfield FROM testBug9098");
1652 this.rs.moveToInsertRow();
1653 this.rs.insertRow();
1654
1655 } finally {
1656 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug9098");
1657 }
1658 }
1659 }
1660
1661
1670 public void testBug9236() throws Exception {
1671 if (versionMeetsMinimum(4, 1)) {
1672 try {
1673 createTable(
1674 "testBug9236",
1675 "("
1676 + "field_1 int(18) NOT NULL auto_increment,"
1677 + "field_2 varchar(50) NOT NULL default '',"
1678 + "field_3 varchar(12) default NULL,"
1679 + "field_4 int(18) default NULL,"
1680 + "field_5 int(18) default NULL,"
1681 + "field_6 datetime default NULL,"
1682 + "field_7 varchar(30) default NULL,"
1683 + "field_8 varchar(50) default NULL,"
1684 + "field_9 datetime default NULL,"
1685 + "field_10 int(18) NOT NULL default '0',"
1686 + "field_11 int(18) default NULL,"
1687 + "field_12 datetime NOT NULL default '0000-00-00 00:00:00',"
1688 + "PRIMARY KEY (field_1)," + "KEY (field_4),"
1689 + "KEY (field_2)," + "KEY (field_3),"
1690 + "KEY (field_7,field_1)," + "KEY (field_5),"
1691 + "KEY (field_6,field_10,field_9),"
1692 + "KEY (field_11,field_10),"
1693 + "KEY (field_12,field_10)"
1694 + ") ENGINE=InnoDB DEFAULT CHARSET=latin1");
1695
1696 this.stmt
1697 .executeUpdate("INSERT INTO testBug9236 VALUES "
1698 + "(1,'0',NULL,-1,0,'0000-00-00 00:00:00','123456789','-1','2004-03-13 14:21:38',0,NULL,'2004-03-13 14:21:38'),"
1699 + "(2,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'1',NULL,0,NULL,'2004-07-13 14:29:52'),"
1700 + "(3,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'2',NULL,0,NULL,'2004-07-16 13:20:51'),"
1701 + "(4,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'3','2004-07-16 13:43:39',0,NULL,'2004-07-16 13:22:01'),"
1702 + "(5,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'4','2004-07-16 13:23:48',0,NULL,'2004-07-16 13:23:01'),"
1703 + "(6,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'5',NULL,0,NULL,'2004-07-16 14:41:07'),"
1704 + "(7,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'6',NULL,0,NULL,'2004-07-16 14:41:34'),"
1705 + "(8,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'7',NULL,0,NULL,'2004-07-16 14:41:54'),"
1706 + "(9,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'8',NULL,0,NULL,'2004-07-16 14:42:42'),"
1707 + "(10,'0','PI',1,0,'0000-00-00 00:00:00',NULL,'9',NULL,0,NULL,'2004-07-18 10:51:30'),"
1708 + "(11,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'10','2004-07-23 17:23:06',0,NULL,'2004-07-23 17:18:19'),"
1709 + "(12,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'11','2004-07-23 17:24:45',0,NULL,'2004-07-23 17:23:57'),"
1710 + "(13,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'12','2004-07-23 17:30:51',0,NULL,'2004-07-23 17:30:15'),"
1711 + "(14,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'13','2004-07-26 17:50:19',0,NULL,'2004-07-26 17:49:38'),"
1712 + "(15,'0','FRL',1,0,'0000-00-00 00:00:00',NULL,'1',NULL,0,NULL,'2004-08-19 18:29:18'),"
1713 + "(16,'0','FRL',1,0,'0000-00-00 00:00:00',NULL,'15',NULL,0,NULL,'2005-03-16 12:08:28')");
1714
1715 createTable("testBug9236_1",
1716 "(field1 CHAR(2) CHARACTER SET BINARY)");
1717 this.stmt
1718 .executeUpdate("INSERT INTO testBug9236_1 VALUES ('ab')");
1719 this.rs = this.stmt
1720 .executeQuery("SELECT field1 FROM testBug9236_1");
1721
1722 ResultSetMetaData rsmd = this.rs.getMetaData();
1723 assertEquals("[B", rsmd.getColumnClassName(1));
1724 assertTrue(this.rs.next());
1725 Object asObject = rs.getObject(1);
1726 assertEquals("[B", asObject.getClass().getName());
1727
1728 this.rs = this.stmt
1729 .executeQuery("select DATE_FORMAT(field_12, '%Y-%m-%d') as date, count(*) as count from testBug9236 where field_10 = 0 and field_3 = 'FRL' and field_12 >= '2005-03-02 00:00:00' and field_12 <= '2005-03-17 00:00:00' group by date");
1730 rsmd = this.rs.getMetaData();
1731 assertEquals("java.lang.String", rsmd.getColumnClassName(1));
1732 this.rs.next();
1733 asObject = rs.getObject(1);
1734 assertEquals("java.lang.String", asObject.getClass().getName());
1735
1736 this.rs.close();
1737
1738 createTable("testBug8868_2",
1739 "(field1 CHAR(4) CHARACTER SET BINARY)");
1740 this.stmt
1741 .executeUpdate("INSERT INTO testBug8868_2 VALUES ('abc')");
1742 this.rs = this.stmt
1743 .executeQuery("SELECT field1 FROM testBug8868_2");
1744
1745 rsmd = this.rs.getMetaData();
1746 assertEquals("[B", rsmd.getColumnClassName(1));
1747 this.rs.next();
1748 asObject = rs.getObject(1);
1749 assertEquals("[B", asObject.getClass().getName());
1750 } finally {
1751 if (this.rs != null) {
1752 this.rs.close();
1753 this.rs = null;
1754 }
1755 }
1756 }
1757 }
1758
1759
1767 public void testBug9437() throws Exception {
1768 String tableName = "testBug9437";
1769
1770 if (versionMeetsMinimum(4, 1, 0)) {
1771 try {
1772 createTable(
1773 tableName,
1774 "("
1775 + "languageCode char(2) NOT NULL default '',"
1776 + "countryCode char(2) NOT NULL default '',"
1777 + "supported enum('no','yes') NOT NULL default 'no',"
1778 + "ordering int(11) default NULL,"
1779 + "createDate datetime NOT NULL default '1000-01-01 00:00:03',"
1780 + "modifyDate timestamp NOT NULL default CURRENT_TIMESTAMP on update"
1781 + " CURRENT_TIMESTAMP,"
1782 + "PRIMARY KEY (languageCode,countryCode),"
1783 + "KEY languageCode (languageCode),"
1784 + "KEY countryCode (countryCode),"
1785 + "KEY ordering (ordering),"
1786 + "KEY modifyDate (modifyDate)"
1787 + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
1788
1789 this.stmt.executeUpdate("INSERT INTO " + tableName
1790 + " (languageCode) VALUES ('en')");
1791
1792 String alias = "someLocale";
1793 String sql = "select if ( languageCode = ?, ?, ? ) as " + alias
1794 + " from " + tableName;
1795 this.pstmt = this.conn.prepareStatement(sql);
1796
1797 int count = 1;
1798 this.pstmt.setObject(count++, "en");
1799 this.pstmt.setObject(count++, "en_US");
1800 this.pstmt.setObject(count++, "en_GB");
1801
1802 this.rs = this.pstmt.executeQuery();
1803
1804 assertTrue(this.rs.next());
1805
1806 Object object = this.rs.getObject(alias);
1807
1808 if (object != null) {
1809 assertEquals("java.lang.String", object.getClass()
1810 .getName());
1811 assertEquals("en_US", object.toString());
1812 }
1813
1814 } finally {
1815 if (this.rs != null) {
1816 this.rs.close();
1817 this.rs = null;
1818 }
1819
1820 if (this.pstmt != null) {
1821 this.pstmt.close();
1822 this.pstmt = null;
1823 }
1824 }
1825 }
1826 }
1827
1828 public void testBug9684() throws Exception {
1829 if (versionMeetsMinimum(4, 1, 9)) {
1830 String tableName = "testBug9684";
1831
1832 try {
1833 createTable(tableName,
1834 "(sourceText text character set utf8 collate utf8_bin)");
1835 this.stmt.executeUpdate("INSERT INTO " + tableName
1836 + " VALUES ('abc')");
1837 this.rs = this.stmt.executeQuery("SELECT sourceText FROM "
1838 + tableName);
1839 assertTrue(this.rs.next());
1840 assertEquals("java.lang.String", this.rs.getString(1)
1841 .getClass().getName());
1842 assertEquals("abc", this.rs.getString(1));
1843 } finally {
1844 if (this.rs != null) {
1845 this.rs.close();
1846 this.rs = null;
1847 }
1848 }
1849 }
1850 }
1851
1852
1858 public void testBug10156() throws Exception {
1859 String tableName = "testBug10156";
1860 try {
1861 createTable(tableName, "(field1 smallint(5) unsigned, "
1862 + "field2 tinyint unsigned," + "field3 int unsigned)");
1863 this.stmt.executeUpdate("INSERT INTO " + tableName
1864 + " VALUES (32768, 255, 4294967295)");
1865 this.rs = this.conn.prepareStatement(
1866 "SELECT field1, field2, field3 FROM " + tableName)
1867 .executeQuery();
1868 assertTrue(this.rs.next());
1869 assertEquals(32768, this.rs.getInt(1));
1870 assertEquals(255, this.rs.getInt(2));
1871 assertEquals(4294967295L, this.rs.getLong(3));
1872
1873 assertEquals(String.valueOf(this.rs.getObject(1)), String
1874 .valueOf(this.rs.getInt(1)));
1875 assertEquals(String.valueOf(this.rs.getObject(2)), String
1876 .valueOf(this.rs.getInt(2)));
1877 assertEquals(String.valueOf(this.rs.getObject(3)), String
1878 .valueOf(this.rs.getLong(3)));
1879
1880 } finally {
1881 if (this.rs != null) {
1882 this.rs.close();
1883 this.rs = null;
1884 }
1885 }
1886 }
1887
1888 public void testBug10212() throws Exception {
1889 String tableName = "testBug10212";
1890
1891 try {
1892 createTable(tableName, "(field1 YEAR(4))");
1893 this.stmt.executeUpdate("INSERT INTO " + tableName
1894 + " VALUES (1974)");
1895 this.rs = this.conn.prepareStatement(
1896 "SELECT field1 FROM " + tableName).executeQuery();
1897
1898 ResultSetMetaData rsmd = this.rs.getMetaData();
1899 assertTrue(this.rs.next());
1900 assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
1901 assertEquals("java.sql.Date", this.rs.getObject(1).getClass()
1902 .getName());
1903
1904 this.rs = this.stmt.executeQuery("SELECT field1 FROM " + tableName);
1905
1906 rsmd = this.rs.getMetaData();
1907 assertTrue(this.rs.next());
1908 assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
1909 assertEquals("java.sql.Date", this.rs.getObject(1).getClass()
1910 .getName());
1911 } finally {
1912 if (this.rs != null) {
1913 this.rs.close();
1914 this.rs = null;
1915 }
1916 }
1917 }
1918
1919 public void testNPEWithUsageAdvisor() throws Exception {
1920 Connection advisorConn = null;
1921
1922 try {
1923 Properties props = new Properties ();
1924 props.setProperty("useUsageAdvisor", "true");
1925
1926 advisorConn = getConnectionWithProps(props);
1927 this.pstmt = advisorConn.prepareStatement("SELECT 1");
1928 this.rs = this.pstmt.executeQuery();
1929 this.rs.close();
1930 this.rs = this.pstmt.executeQuery();
1931
1932 } finally {
1933 }
1934 }
1935
1936 public void testAllTypesForNull() throws Exception {
1937 Properties props = new Properties ();
1938 props.setProperty("jdbcCompliantTruncation", "false");
1939 props.setProperty("zeroDateTimeBehavior", "round");
1940 Connection conn2 = getConnectionWithProps(props);
1941 Statement stmt2 = conn2.createStatement();
1942
1943 DatabaseMetaData dbmd = conn.getMetaData();
1944
1945 this.rs = dbmd.getTypeInfo();
1946
1947 boolean firstColumn = true;
1948 int numCols = 1;
1949 StringBuffer createStatement = new StringBuffer (
1950 "CREATE TABLE testAllTypes (");
1951 List wasDatetimeTypeList = new ArrayList ();
1952
1953 while (this.rs.next()) {
1954 String dataType = this.rs.getString("TYPE_NAME").toUpperCase();
1955
1956 boolean wasDateTime = false;
1957
1958 if (dataType.indexOf("DATE") != -1
1959 || dataType.indexOf("TIME") != -1) {
1960 wasDateTime = true;
1961 }
1962
1963 if (!"BOOL".equalsIgnoreCase(dataType)
1964 && !"LONG VARCHAR".equalsIgnoreCase(dataType)
1965 && !"LONG VARBINARY".equalsIgnoreCase(dataType)
1966 && !"ENUM".equalsIgnoreCase(dataType)
1967 && !"SET".equalsIgnoreCase(dataType)) {
1968 wasDatetimeTypeList.add(new Boolean (wasDateTime));
1969 createStatement.append("\n\t");
1970 if (!firstColumn) {
1971 createStatement.append(",");
1972 } else {
1973 firstColumn = false;
1974 }
1975
1976 createStatement.append("field_");
1977 createStatement.append(numCols++);
1978 createStatement.append(" ");
1979
1980 createStatement.append(dataType);
1981
1982 if (dataType.indexOf("CHAR") != -1
1983 || dataType.indexOf("BINARY") != -1
1984 && dataType.indexOf("BLOB") == -1
1985 && dataType.indexOf("TEXT") == -1) {
1986 createStatement.append("(");
1987 createStatement.append(this.rs.getString("PRECISION"));
1988 createStatement.append(")");
1989 }
1990
1991 createStatement.append(" NULL DEFAULT NULL");
1992 }
1993 }
1994
1995 createStatement.append("\n)");
1996
1997 stmt2.executeUpdate("DROP TABLE IF EXISTS testAllTypes");
1998
1999 stmt2.executeUpdate(createStatement.toString());
2000 StringBuffer insertStatement = new StringBuffer (
2001 "INSERT INTO testAllTypes VALUES (NULL");
2002 for (int i = 1; i < numCols - 1; i++) {
2003 insertStatement.append(", NULL");
2004 }
2005 insertStatement.append(")");
2006 stmt2.executeUpdate(insertStatement.toString());
2007
2008 this.rs = stmt2.executeQuery("SELECT * FROM testAllTypes");
2009
2010 testAllFieldsForNull(this.rs);
2011 this.rs.close();
2012
2013 this.rs = this.conn.prepareStatement("SELECT * FROM testAllTypes")
2014 .executeQuery();
2015 testAllFieldsForNull(this.rs);
2016
2017 stmt2.executeUpdate("DELETE FROM testAllTypes");
2018
2019 insertStatement = new StringBuffer ("INSERT INTO testAllTypes VALUES (");
2020
2021 boolean needsNow = ((Boolean ) wasDatetimeTypeList.get(0))
2022 .booleanValue();
2023
2024 if (needsNow) {
2025 insertStatement.append("NOW()");
2026 } else {
2027 insertStatement.append("'0'");
2028 }
2029
2030 for (int i = 1; i < numCols - 1; i++) {
2031 needsNow = ((Boolean ) wasDatetimeTypeList.get(i)).booleanValue();
2032 insertStatement.append(",");
2033 if (needsNow) {
2034 insertStatement.append("NOW()");
2035 } else {
2036 insertStatement.append("'0'");
2037 }
2038 }
2039
2040 insertStatement.append(")");
2041
2042 stmt2.executeUpdate(insertStatement.toString());
2043
2044 this.rs = stmt2.executeQuery("SELECT * FROM testAllTypes");
2045
2046 testAllFieldsForNotNull(this.rs, wasDatetimeTypeList);
2047 this.rs.close();
2048
2049 this.rs = conn2.prepareStatement("SELECT * FROM testAllTypes")
2050 .executeQuery();
2051 testAllFieldsForNotNull(this.rs, wasDatetimeTypeList);
2052 }
2053
2054 private void testAllFieldsForNull(ResultSet rsToTest) throws Exception {
2055 ResultSetMetaData rsmd = rs.getMetaData();
2056 int numCols = rsmd.getColumnCount();
2057
2058 while (rsToTest.next()) {
2059 for (int i = 0; i < numCols - 1; i++) {
2060 if (!"BIT".equalsIgnoreCase(rsmd.getColumnTypeName(i + 1))) {
2061 assertEquals(false, rsToTest.getBoolean(i + 1));
2062 assertTrue(rsToTest.wasNull());
2063
2064 assertEquals(0, rsToTest.getDouble(i + 1), 0 );
2065 assertTrue(rsToTest.wasNull());
2066 assertEquals(0, rsToTest.getFloat(i + 1), 0 );
2067 assertTrue(rsToTest.wasNull());
2068 assertEquals(0, rsToTest.getInt(i + 1));
2069 assertTrue(rsToTest.wasNull());
2070 assertEquals(0, rsToTest.getLong(i + 1));
2071 assertTrue(rsToTest.wasNull());
2072 assertEquals(null, rsToTest.getObject(i + 1));
2073 assertTrue(rsToTest.wasNull());
2074 assertEquals(null, rsToTest.getString(i + 1));
2075 assertTrue(rsToTest.wasNull());
2076 assertEquals(null, rsToTest.getAsciiStream(i + 1));
2077 assertTrue(rsToTest.wasNull());
2078 assertEquals(null, rsToTest.getBigDecimal(i + 1));
2079 assertTrue(rsToTest.wasNull());
2080 assertEquals(null, rsToTest.getBinaryStream(i + 1));
2081 assertTrue(rsToTest.wasNull());
2082 assertEquals(null, rsToTest.getBlob(i + 1));
2083 assertTrue(rsToTest.wasNull());
2084 assertEquals(0, rsToTest.getByte(i + 1));
2085 assertTrue(rsToTest.wasNull());
2086 assertEquals(null, rsToTest.getBytes(i + 1));
2087 assertTrue(rsToTest.wasNull());
2088 assertEquals(null, rsToTest.getCharacterStream(i + 1));
2089 assertTrue(rsToTest.wasNull());
2090 assertEquals(null, rsToTest.getClob(i + 1));
2091 assertTrue(rsToTest.wasNull());
2092 assertEquals(null, rsToTest.getDate(i + 1));
2093 assertTrue(rsToTest.wasNull());
2094 assertEquals(0, rsToTest.getShort(i + 1));
2095 assertTrue(rsToTest.wasNull());
2096 assertEquals(null, rsToTest.getTime(i + 1));
2097 assertTrue(rsToTest.wasNull());
2098 assertEquals(null, rsToTest.getTimestamp(i + 1));
2099 assertTrue(rsToTest.wasNull());
2100 assertEquals(null, rsToTest.getUnicodeStream(i + 1));
2101 assertTrue(rsToTest.wasNull());
2102 assertEquals(null, rsToTest.getURL(i + 1));
2103 assertTrue(rsToTest.wasNull());
2104 }
2105 }
2106 }
2107 }
2108
2109 private void testAllFieldsForNotNull(ResultSet rsToTest,
2110 List wasDatetimeTypeList) throws Exception {
2111 ResultSetMetaData rsmd = rs.getMetaData();
2112 int numCols = rsmd.getColumnCount();
2113
2114 while (rsToTest.next()) {
2115 for (int i = 0; i < numCols - 1; i++) {
2116 boolean wasDatetimeType = ((Boolean ) wasDatetimeTypeList.get(i))
2117 .booleanValue();
2118 if (!"BIT".equalsIgnoreCase(rsmd.getColumnTypeName(i + 1))) {
2119 if (!wasDatetimeType) {
2120
2121 assertEquals(false, rsToTest.getBoolean(i + 1));
2122 assertTrue(!rsToTest.wasNull());
2123
2124 assertEquals(0, rsToTest.getDouble(i + 1), 0 );
2125 assertTrue(!rsToTest.wasNull());
2126 assertEquals(0, rsToTest.getFloat(i + 1), 0 );
2127 assertTrue(!rsToTest.wasNull());
2128 assertEquals(0, rsToTest.getInt(i + 1));
2129 assertTrue(!rsToTest.wasNull());
2130 assertEquals(0, rsToTest.getLong(i + 1));
2131 assertTrue(!rsToTest.wasNull());
2132 assertEquals(0, rsToTest.getByte(i + 1));
2133 assertTrue(!rsToTest.wasNull());
2134 assertEquals(0, rsToTest.getShort(i + 1));
2135 assertTrue(!rsToTest.wasNull());
2136 }
2137
2138 assertNotNull(rsToTest.getObject(i + 1));
2139 assertTrue(!rsToTest.wasNull());
2140 assertNotNull(rsToTest.getString(i + 1));
2141 assertTrue(!rsToTest.wasNull());
2142 assertNotNull(rsToTest.getAsciiStream(i + 1));
2143 assertTrue(!rsToTest.wasNull());
2144
2145 assertNotNull(rsToTest.getBinaryStream(i + 1));
2146 assertTrue(!rsToTest.wasNull());
2147 assertNotNull(rsToTest.getBlob(i + 1));
2148 assertTrue(!rsToTest.wasNull());
2149 assertNotNull(rsToTest.getBytes(i + 1));
2150 assertTrue(!rsToTest.wasNull());
2151 assertNotNull(rsToTest.getCharacterStream(i + 1));
2152 assertTrue(!rsToTest.wasNull());
2153 assertNotNull(rsToTest.getClob(i + 1));
2154 assertTrue(!rsToTest.wasNull());
2155
2156 String columnClassName = rsmd.getColumnClassName(i + 1);
2157
2158 boolean canBeUsedAsDate = !("java.lang.Boolean"
2159 .equals(columnClassName)
2160 || "java.lang.Double".equals(columnClassName)
2161 || "java.lang.Float".equals(columnClassName)
2162 || "java.lang.Real".equals(columnClassName) || "java.math.BigDecimal"
2163 .equals(columnClassName));
2164
2165 if (canBeUsedAsDate) {
2166 assertNotNull(rsToTest.getDate(i + 1));
2167 assertTrue(!rsToTest.wasNull());
2168 assertNotNull(rsToTest.getTime(i + 1));
2169 assertTrue(!rsToTest.wasNull());
2170 assertNotNull(rsToTest.getTimestamp(i + 1));
2171 assertTrue(!rsToTest.wasNull());
2172 }
2173
2174 assertNotNull(rsToTest.getUnicodeStream(i + 1));
2175 assertTrue(!rsToTest.wasNull());
2176
2177 try {
2178 assertNotNull(rsToTest.getURL(i + 1));
2179 } catch (SQLException sqlEx) {
2180 assertTrue(sqlEx.getMessage().indexOf("URL") != -1);
2181 }
2182
2183 assertTrue(!rsToTest.wasNull());
2184 }
2185 }
2186 }
2187 }
2188
2189 public void testNPEWithStatementsAndTime() throws Exception {
2190 try {
2191 this.stmt.executeUpdate("DROP TABLE IF EXISTS testNPETime");
2192 this.stmt
2193 .executeUpdate("CREATE TABLE testNPETime (field1 TIME NULL, field2 DATETIME NULL, field3 DATE NULL)");
2194 this.stmt
2195 .executeUpdate("INSERT INTO testNPETime VALUES (null, null, null)");
2196 this.pstmt = this.conn
2197 .prepareStatement("SELECT field1, field2, field3 FROM testNPETime");
2198 this.rs = this.pstmt.executeQuery();
2199 this.rs.next();
2200
2201 for (int i = 0; i < 3; i++) {
2202 assertEquals(null, this.rs.getTime(i + 1));
2203 assertEquals(true, this.rs.wasNull());
2204 }
2205
2206 for (int i = 0; i < 3; i++) {
2207 assertEquals(null, this.rs.getTimestamp(i + 1));
2208 assertEquals(true, this.rs.wasNull());
2209 }
2210
2211 for (int i = 0; i < 3; i++) {
2212 assertEquals(null, this.rs.getDate(i + 1));
2213 assertEquals(true, this.rs.wasNull());
2214 }
2215 } finally {
2216 this.stmt.executeUpdate("DROP TABLE IF EXISTS testNPETime");
2217 }
2218 }
2219
2220 public void testEmptyStringsWithNumericGetters() throws Exception {
2221 try {
2222 createTable("emptyStringTable", "(field1 char(32))");
2223 this.stmt.executeUpdate("INSERT INTO emptyStringTable VALUES ('')");
2224 this.rs = this.stmt
2225 .executeQuery("SELECT field1 FROM emptyStringTable");
2226 assertTrue(this.rs.next());
2227 createTable("emptyStringTable", "(field1 char(32))");
2228 this.stmt.executeUpdate("INSERT INTO emptyStringTable VALUES ('')");
2229
2230 this.rs = this.stmt
2231 .executeQuery("SELECT field1 FROM emptyStringTable");
2232 assertTrue(this.rs.next());
2233 checkEmptyConvertToZero();
2234
2235 this.rs = this.conn.prepareStatement(
2236 "SELECT field1 FROM emptyStringTable").executeQuery();
2237 assertTrue(this.rs.next());
2238 checkEmptyConvertToZero();
2239
2240 Properties props = new Properties ();
2241 props.setProperty("useFastIntParsing", "false");
2242
2243 Connection noFastIntParseConn = getConnectionWithProps(props);
2244 Statement noFastIntStmt = noFastIntParseConn.createStatement();
2245
2246 this.rs = noFastIntStmt
2247 .executeQuery("SELECT field1 FROM emptyStringTable");
2248 assertTrue(this.rs.next());
2249 checkEmptyConvertToZero();
2250
2251 this.rs = noFastIntParseConn.prepareStatement(
2252 "SELECT field1 FROM emptyStringTable").executeQuery();
2253 assertTrue(this.rs.next());
2254 checkEmptyConvertToZero();
2255
2256
2260 props = new Properties ();
2261 props.setProperty("emptyStringsConvertToZero", "false");
2262
2263 Connection pedanticConn = getConnectionWithProps(props);
2264 Statement pedanticStmt = pedanticConn.createStatement();
2265
2266 this.rs = pedanticStmt
2267 .executeQuery("SELECT field1 FROM emptyStringTable");
2268 assertTrue(this.rs.next());
2269
2270 checkEmptyConvertToZeroException();
2271
2272 this.rs = pedanticConn.prepareStatement(
2273 "SELECT field1 FROM emptyStringTable").executeQuery();
2274 assertTrue(this.rs.next());
2275 checkEmptyConvertToZeroException();
2276
2277 props = new Properties ();
2278 props.setProperty("emptyStringsConvertToZero", "false");
2279 props.setProperty("useFastIntParsing", "false");
2280
2281 pedanticConn = getConnectionWithProps(props);
2282 pedanticStmt = pedanticConn.createStatement();
2283
2284 this.rs = pedanticStmt
2285 .executeQuery("SELECT field1 FROM emptyStringTable");
2286 assertTrue(this.rs.next());
2287
2288 checkEmptyConvertToZeroException();
2289
2290 this.rs = pedanticConn.prepareStatement(
2291 "SELECT field1 FROM emptyStringTable").executeQuery();
2292 assertTrue(this.rs.next());
2293 checkEmptyConvertToZeroException();
2294
2295 } finally {
2296 if (this.rs != null) {
2297 this.rs.close();
2298
2299 this.rs = null;
2300 }
2301 }
2302 }
2303
2304 public void testNegativeOneIsTrue() throws Exception {
2305 if (!versionMeetsMinimum(5, 0, 3)) {
2306 String tableName = "testNegativeOneIsTrue";
2307 Connection tinyInt1IsBitConn = null;
2308
2309 try {
2310 createTable(tableName, "(field1 BIT)");
2311 this.stmt.executeUpdate("INSERT INTO " + tableName
2312 + " VALUES (-1)");
2313
2314 Properties props = new Properties ();
2315 props.setProperty("tinyInt1isBit", "true");
2316 tinyInt1IsBitConn = getConnectionWithProps(props);
2317
2318 this.rs = tinyInt1IsBitConn.createStatement().executeQuery(
2319 "SELECT field1 FROM " + tableName);
2320 assertTrue(this.rs.next());
2321 assertEquals(true, this.rs.getBoolean(1));
2322
2323 this.rs = tinyInt1IsBitConn.prepareStatement(
2324 "SELECT field1 FROM " + tableName).executeQuery();
2325 assertTrue(this.rs.next());
2326 assertEquals(true, this.rs.getBoolean(1));
2327
2328 } finally {
2329 if (tinyInt1IsBitConn != null) {
2330 tinyInt1IsBitConn.close();
2331 }
2332 }
2333 }
2334 }
2335
2336
2339 private void checkEmptyConvertToZero() throws SQLException {
2340 assertEquals(0, this.rs.getByte(1));
2341 assertEquals(0, this.rs.getShort(1));
2342 assertEquals(0, this.rs.getInt(1));
2343 assertEquals(0, this.rs.getLong(1));
2344 assertEquals(0, this.rs.getFloat(1), 0.1);
2345 assertEquals(0, this.rs.getDouble(1), 0.1);
2346 assertEquals(0, this.rs.getBigDecimal(1).intValue());
2347 }
2348
2349
2352 private void checkEmptyConvertToZeroException() {
2353 try {
2354 assertEquals(0, this.rs.getByte(1));
2355 fail("Should've thrown an exception!");
2356 } catch (SQLException sqlEx) {
2357 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2358 sqlEx.getSQLState());
2359 }
2360 try {
2361 assertEquals(0, this.rs.getShort(1));
2362 fail("Should've thrown an exception!");
2363 } catch (SQLException sqlEx) {
2364 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2365 sqlEx.getSQLState());
2366 }
2367 try {
2368 assertEquals(0, this.rs.getInt(1));
2369 fail("Should've thrown an exception!");
2370 } catch (SQLException sqlEx) {
2371 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2372 sqlEx.getSQLState());
2373 }
2374 try {
2375 assertEquals(0, this.rs.getLong(1));
2376 fail("Should've thrown an exception!");
2377 } catch (SQLException sqlEx) {
2378 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2379 sqlEx.getSQLState());
2380 }
2381 try {
2382 assertEquals(0, this.rs.getFloat(1), 0.1);
2383 fail("Should've thrown an exception!");
2384 } catch (SQLException sqlEx) {
2385 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2386 sqlEx.getSQLState());
2387 }
2388 try {
2389 assertEquals(0, this.rs.getDouble(1), 0.1);
2390 fail("Should've thrown an exception!");
2391 } catch (SQLException sqlEx) {
2392 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2393 sqlEx.getSQLState());
2394 }
2395 try {
2396 assertEquals(0, this.rs.getBigDecimal(1).intValue());
2397 fail("Should've thrown an exception!");
2398 } catch (SQLException sqlEx) {
2399 assertEquals(SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
2400 sqlEx.getSQLState());
2401 }
2402 }
2403
2404
2411 public void testBug10485() throws Exception {
2412 String tableName = "testBug10485";
2413
2414 createTable(tableName, "(field1 YEAR(2))");
2415 this.stmt.executeUpdate("INSERT INTO " + tableName + " VALUES ('05')");
2416
2417 this.rs = this.stmt.executeQuery("SELECT field1 FROM " + tableName);
2418 assertTrue(rs.next());
2419 assertEquals("2005-01-01", rs.getString(1));
2420
2421 this.rs = this.conn.prepareStatement("SELECT field1 FROM " + tableName)
2422 .executeQuery();
2423 assertTrue(rs.next());
2424 assertEquals("2005-01-01", rs.getString(1));
2425
2426 Properties props = new Properties ();
2427 props.setProperty("yearIsDateType", "false");
2428
2429 Connection yearShortConn = getConnectionWithProps(props);
2430 this.rs = yearShortConn.createStatement().executeQuery(
2431 "SELECT field1 FROM " + tableName);
2432 assertTrue(rs.next());
2433 assertEquals("05", rs.getString(1));
2434
2435 this.rs = yearShortConn.prepareStatement(
2436 "SELECT field1 FROM " + tableName).executeQuery();
2437 assertTrue(rs.next());
2438 assertEquals("05", rs.getString(1));
2439
2440 if (versionMeetsMinimum(5, 0)) {
2441 try {
2442 this.stmt
2443 .executeUpdate("DROP PROCEDURE IF EXISTS testBug10485");
2444 this.stmt
2445 .executeUpdate("CREATE PROCEDURE testBug10485()\nBEGIN\nSELECT field1 FROM "
2446 + tableName + ";\nEND");
2447
2448 this.rs = this.conn.prepareCall("{CALL testBug10485()}")
2449 .executeQuery();
2450 assertTrue(rs.next());
2451 assertEquals("2005-01-01", rs.getString(1));
2452
2453 this.rs = yearShortConn.prepareCall("{CALL testBug10485()}")
2454 .executeQuery();
2455 assertTrue(rs.next());
2456 assertEquals("05", rs.getString(1));
2457 } finally {
2458 this.stmt
2459 .executeUpdate("DROP PROCEDURE IF EXISTS testBug10485");
2460 }
2461 }
2462 }
2463}
2464 |
Popular Tags |
Java API By Example, From Geeks To Geeks.