|
1
25 package testsuite.simple;
26
27 import java.sql.CallableStatement  ;
28 import java.sql.Connection ;
29 import java.sql.PreparedStatement ;
30 import java.sql.ResultSet ;
31 import java.sql.SQLException ;
32 import java.sql.Statement ;
33 import java.util.Properties ;
34
35 import testsuite.BaseTestCase;
36
37 import com.mysql.jdbc.NotImplemented;
38 import com.mysql.jdbc.SQLError;
39
40
46 public class StatementsTest extends BaseTestCase {
47 private static final int MAX_COLUMNS_TO_TEST = 40;
48
49 private static final int STEP = 8;
50
51 private static final int MAX_COLUMN_LENGTH = 255;
52
53 private static final int MIN_COLUMN_LENGTH = 10;
54
55
61 public StatementsTest(String name) {
62 super(name);
63 }
64
65
70 public static void main(String [] args) {
71 junit.textui.TestRunner.run(StatementsTest.class);
72 }
73
74
80 public void setUp() throws Exception {
81 super.setUp();
82
83 this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_test");
84
85 this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_batch_test");
86
87 this.stmt
88 .executeUpdate("CREATE TABLE statement_test (id int not null primary key auto_increment, strdata1 varchar(255) not null, strdata2 varchar(255))");
89
90 this.stmt.executeUpdate("CREATE TABLE statement_batch_test "
91 + "(id int not null primary key auto_increment, "
92 + "strdata1 varchar(255) not null, strdata2 varchar(255), "
93 + "UNIQUE INDEX (strdata1))");
94
95 for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
96 this.stmt.executeUpdate("DROP TABLE IF EXISTS statement_col_test_"
97 + i);
98
99 StringBuffer insertBuf = new StringBuffer (
100 "INSERT INTO statement_col_test_");
101 StringBuffer stmtBuf = new StringBuffer (
102 "CREATE TABLE IF NOT EXISTS statement_col_test_");
103 stmtBuf.append(i);
104 insertBuf.append(i);
105 stmtBuf.append(" (");
106 insertBuf.append(" VALUES (");
107
108 boolean firstTime = true;
109
110 for (int j = 0; j < i; j++) {
111 if (!firstTime) {
112 stmtBuf.append(",");
113 insertBuf.append(",");
114 } else {
115 firstTime = false;
116 }
117
118 stmtBuf.append("col_");
119 stmtBuf.append(j);
120 stmtBuf.append(" VARCHAR(");
121 stmtBuf.append(MAX_COLUMN_LENGTH);
122 stmtBuf.append(")");
123 insertBuf.append("'");
124
125 int numChars = 16;
126
127 for (int k = 0; k < numChars; k++) {
128 insertBuf.append("A");
129 }
130
131 insertBuf.append("'");
132 }
133
134 stmtBuf.append(")");
135 insertBuf.append(")");
136 this.stmt.executeUpdate(stmtBuf.toString());
137 this.stmt.executeUpdate(insertBuf.toString());
138 }
139
140 }
146
147
153 public void tearDown() throws Exception {
154 this.stmt.executeUpdate("DROP TABLE statement_test");
155
156 for (int i = 0; i < MAX_COLUMNS_TO_TEST; i += STEP) {
157 StringBuffer stmtBuf = new StringBuffer (
158 "DROP TABLE IF EXISTS statement_col_test_");
159 stmtBuf.append(i);
160 this.stmt.executeUpdate(stmtBuf.toString());
161 }
162
163 try {
164 this.stmt.executeUpdate("DROP TABLE statement_batch_test");
165 } catch (SQLException sqlEx) {
166 ;
167 }
168
169 super.tearDown();
170 }
171
172
178 public void testAccessorsAndMutators() throws SQLException {
179 assertTrue("Connection can not be null, and must be same connection",
180 this.stmt.getConnection() == this.conn);
181
182 Statement accessorStmt = null;
185
186 try {
187 accessorStmt = this.conn.createStatement();
188 accessorStmt.setMaxRows(1);
189 accessorStmt.setMaxRows(0); accessorStmt.setMaxFieldSize(255);
192 assertTrue("Max field size should match what was set", accessorStmt
193 .getMaxFieldSize() == 255);
194
195 try {
196 accessorStmt.setMaxFieldSize(Integer.MAX_VALUE);
197 fail("Should not be able to set max field size > max_packet_size");
198 } catch (SQLException sqlEx) {
199 ;
200 }
201
202 accessorStmt.setCursorName("undef");
203 accessorStmt.setEscapeProcessing(true);
204 accessorStmt.setFetchDirection(java.sql.ResultSet.FETCH_FORWARD);
205
206 int fetchDirection = accessorStmt.getFetchDirection();
207 assertTrue("Set fetch direction != get fetch direction",
208 fetchDirection == java.sql.ResultSet.FETCH_FORWARD);
209
210 try {
211 accessorStmt.setFetchDirection(Integer.MAX_VALUE);
212 fail("Should not be able to set fetch direction to invalid value");
213 } catch (SQLException sqlEx) {
214 ;
215 }
216
217 try {
218 accessorStmt.setMaxRows(50000000 + 10);
219 fail("Should not be able to set max rows > 50000000");
220 } catch (SQLException sqlEx) {
221 ;
222 }
223
224 try {
225 accessorStmt.setMaxRows(Integer.MIN_VALUE);
226 fail("Should not be able to set max rows < 0");
227 } catch (SQLException sqlEx) {
228 ;
229 }
230
231 int fetchSize = this.stmt.getFetchSize();
232
233 try {
234 accessorStmt.setMaxRows(4);
235 accessorStmt.setFetchSize(Integer.MAX_VALUE);
236 fail("Should not be able to set FetchSize > max rows");
237 } catch (SQLException sqlEx) {
238 ;
239 }
240
241 try {
242 accessorStmt.setFetchSize(-2);
243 fail("Should not be able to set FetchSize < 0");
244 } catch (SQLException sqlEx) {
245 ;
246 }
247
248 assertTrue(
249 "Fetch size before invalid setFetchSize() calls should match fetch size now",
250 fetchSize == this.stmt.getFetchSize());
251 } finally {
252 if (accessorStmt != null) {
253 try {
254 accessorStmt.close();
255 } catch (SQLException sqlEx) {
256 ;
257 }
258
259 accessorStmt = null;
260 }
261 }
262 }
263
264
270 public void testAutoIncrement() throws SQLException {
271 try {
272 this.stmt = this.conn.createStatement(
273 java.sql.ResultSet.TYPE_FORWARD_ONLY,
274 java.sql.ResultSet.CONCUR_READ_ONLY);
275 this.stmt.setFetchSize(Integer.MIN_VALUE);
276 this.stmt
277 .executeUpdate("INSERT INTO statement_test (strdata1) values ('blah')");
278
279 int autoIncKeyFromApi = -1;
280 this.rs = this.stmt.getGeneratedKeys();
281
282 if (this.rs.next()) {
283 autoIncKeyFromApi = this.rs.getInt(1);
284 } else {
285 fail("Failed to retrieve AUTO_INCREMENT using Statement.getGeneratedKeys()");
286 }
287
288 this.rs.close();
289
290 int autoIncKeyFromFunc = -1;
291 this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");
292
293 if (this.rs.next()) {
294 autoIncKeyFromFunc = this.rs.getInt(1);
295 } else {
296 fail("Failed to retrieve AUTO_INCREMENT using LAST_INSERT_ID()");
297 }
298
299 if ((autoIncKeyFromApi != -1) && (autoIncKeyFromFunc != -1)) {
300 assertTrue(
301 "Key retrieved from API ("
302 + autoIncKeyFromApi
303 + ") does not match key retrieved from LAST_INSERT_ID() "
304 + autoIncKeyFromFunc + ") function",
305 autoIncKeyFromApi == autoIncKeyFromFunc);
306 } else {
307 fail("AutoIncrement keys were '0'");
308 }
309 } finally {
310 if (this.rs != null) {
311 try {
312 this.rs.close();
313 } catch (Exception ex) {
314 ;
315 }
316 }
317
318 this.rs = null;
319 }
320 }
321
322
328 public void testCallableStatement() throws Exception {
329 if (versionMeetsMinimum(5, 0)) {
330 CallableStatement cStmt = null;
331 String stringVal = "abcdefg";
332 int intVal = 42;
333
334 try {
335 try {
336 this.stmt.executeUpdate("DROP PROCEDURE testCallStmt");
337 } catch (SQLException sqlEx) {
338 if (sqlEx.getMessage().indexOf("does not exist") == -1) {
339 throw sqlEx;
340 }
341 }
342
343 this.stmt.executeUpdate("DROP TABLE IF EXISTS callStmtTbl");
344 this.stmt
345 .executeUpdate("CREATE TABLE callStmtTbl (x CHAR(16), y INT)");
346
347 this.stmt
348 .executeUpdate("CREATE PROCEDURE testCallStmt(n INT, x CHAR(16), y INT)"
349 + " WHILE n DO"
350 + " SET n = n - 1;"
351 + " INSERT INTO callStmtTbl VALUES (x, y);"
352 + " END WHILE;");
353
354 int rowsToCheck = 15;
355
356 cStmt = this.conn.prepareCall("{call testCallStmt(?,?,?)}");
357 cStmt.setInt(1, rowsToCheck);
358 cStmt.setString(2, stringVal);
359 cStmt.setInt(3, intVal);
360 cStmt.execute();
361
362 this.rs = this.stmt.executeQuery("SELECT x,y FROM callStmtTbl");
363
364 int numRows = 0;
365
366 while (this.rs.next()) {
367 assertTrue(this.rs.getString(1).equals(stringVal)
368 && (this.rs.getInt(2) == intVal));
369
370 numRows++;
371 }
372
373 this.rs.close();
374 this.rs = null;
375
376 cStmt.close();
377 cStmt = null;
378
379 System.out.println(rowsToCheck + " rows returned");
380
381 assertTrue(numRows == rowsToCheck);
382 } finally {
383 try {
384 this.stmt.executeUpdate("DROP PROCEDURE testCallStmt");
385 } catch (SQLException sqlEx) {
386 if (sqlEx.getMessage().indexOf("does not exist") == -1) {
387 throw sqlEx;
388 }
389 }
390
391 this.stmt.executeUpdate("DROP TABLE IF EXISTS callStmtTbl");
392
393 if (cStmt != null) {
394 cStmt.close();
395 }
396 }
397 }
398 }
399
400
406 public void testClose() throws SQLException {
407 Statement closeStmt = null;
408 boolean exceptionAfterClosed = false;
409
410 try {
411 closeStmt = this.conn.createStatement();
412 closeStmt.close();
413
414 try {
415 closeStmt.executeQuery("SELECT 1");
416 } catch (SQLException sqlEx) {
417 exceptionAfterClosed = true;
418 }
419 } finally {
420 if (closeStmt != null) {
421 try {
422 closeStmt.close();
423 } catch (SQLException sqlEx) {
424
425 }
426 }
427
428 closeStmt = null;
429 }
430
431 assertTrue(
432 "Operations not allowed on Statement after .close() is called!",
433 exceptionAfterClosed);
434 }
435
436
442 public void testInsert() throws SQLException {
443 try {
444 boolean autoCommit = this.conn.getAutoCommit();
445
446 try {
448 this.conn.setAutoCommit(false);
449 this.stmt.executeUpdate("SELECT * FROM statement_test");
450 } catch (SQLException sqlEx) {
451 assertTrue("Exception thrown for unknown reason", sqlEx
452 .getSQLState().equalsIgnoreCase("01S03"));
453 } finally {
454 this.conn.setAutoCommit(autoCommit);
455 }
456
457 try {
459 this.conn.setAutoCommit(false);
460 this.stmt
461 .executeQuery("UPDATE statement_test SET strdata1='blah' WHERE 1=0");
462 } catch (SQLException sqlEx) {
463 assertTrue("Exception thrown for unknown reason", sqlEx
464 .getSQLState().equalsIgnoreCase(
465 SQLError.SQL_STATE_ILLEGAL_ARGUMENT));
466 } finally {
467 this.conn.setAutoCommit(autoCommit);
468 }
469
470 for (int i = 0; i < 10; i++) {
471 int updateCount = this.stmt
472 .executeUpdate("INSERT INTO statement_test (strdata1,strdata2) values ('abcdefg', 'poi')");
473 assertTrue("Update count must be '1', was '" + updateCount
474 + "'", (updateCount == 1));
475 }
476
477 this.stmt
478 .executeUpdate("INSERT INTO statement_test (strdata1, strdata2) values ('a', 'a'), ('b', 'b'), ('c', 'c')");
479 this.rs = this.stmt.getGeneratedKeys();
480
481 if (this.rs.next()) {
482 this.rs.getInt(1);
483 }
484
485 this.rs.close();
486 this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");
487
488 int updateCountFromServer = 0;
489
490 if (this.rs.next()) {
491 updateCountFromServer = this.rs.getInt(1);
492 }
493
494 System.out.println("Update count from server: "
495 + updateCountFromServer);
496 } finally {
497 if (this.rs != null) {
498 try {
499 this.rs.close();
500 } catch (Exception ex) {
501 ;
502 }
503 }
504
505 this.rs = null;
506 }
507 }
508
509
515 public void testMultiStatements() throws Exception {
516 if (versionMeetsMinimum(4, 1)) {
517 Connection multiStmtConn = null;
518 Statement multiStmt = null;
519
520 try {
521 Properties props = new Properties ();
522 props.setProperty("allowMultiQueries", "true");
523
524 multiStmtConn = getConnectionWithProps(props);
525
526 multiStmt = multiStmtConn.createStatement();
527
528 multiStmt
529 .executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
530 multiStmt
531 .executeUpdate("CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
532 multiStmt
533 .executeUpdate("INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");
534
535 multiStmt
536 .execute("SELECT field1 FROM testMultiStatements WHERE field1='abcd';"
537 + "UPDATE testMultiStatements SET field3=3;"
538 + "SELECT field3 FROM testMultiStatements WHERE field3=3");
539
540 this.rs = multiStmt.getResultSet();
541
542 assertTrue(this.rs.next());
543
544 assertTrue("abcd".equals(this.rs.getString(1)));
545 this.rs.close();
546
547 assertTrue(!multiStmt.getMoreResults());
549
550 assertTrue("Update count was " + multiStmt.getUpdateCount()
551 + ", expected 1", multiStmt.getUpdateCount() == 1);
552
553 assertTrue(multiStmt.getMoreResults());
554
555 this.rs = multiStmt.getResultSet();
556
557 assertTrue(this.rs.next());
558
559 assertTrue(this.rs.getDouble(1) == 3);
560
561 assertTrue(!multiStmt.getMoreResults());
563 assertTrue(multiStmt.getUpdateCount() == -1);
564 } finally {
565 if (multiStmt != null) {
566 multiStmt
567 .executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
568
569 multiStmt.close();
570 }
571
572 if (multiStmtConn != null) {
573 multiStmtConn.close();
574 }
575 }
576 }
577 }
578
579
585 public void testNulls() throws SQLException {
586 try {
587 this.stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");
588 this.stmt
589 .executeUpdate("CREATE TABLE IF NOT EXISTS nullTest (field_1 CHAR(20), rowOrder INT)");
590 this.stmt
591 .executeUpdate("INSERT INTO nullTest VALUES (null, 1), ('', 2)");
592
593 this.rs = this.stmt
594 .executeQuery("SELECT field_1 FROM nullTest ORDER BY rowOrder");
595
596 this.rs.next();
597
598 assertTrue("NULL field not returned as NULL", (this.rs
599 .getString("field_1") == null)
600 && this.rs.wasNull());
601
602 this.rs.next();
603
604 assertTrue("Empty field not returned as \"\"", this.rs.getString(
605 "field_1").equals("")
606 && !this.rs.wasNull());
607
608 this.rs.close();
609 } finally {
610 if (this.rs != null) {
611 try {
612 this.rs.close();
613 } catch (Exception ex) {
614 }
616 }
617
618 this.stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");
619 }
620 }
621
622
628 public void testPreparedStatement() throws SQLException {
629 this.stmt
630 .executeUpdate("INSERT INTO statement_test (id, strdata1,strdata2) values (999,'abcdefg', 'poi')");
631 this.pstmt = this.conn
632 .prepareStatement("UPDATE statement_test SET strdata1=?, strdata2=? where id=999");
633 this.pstmt.setString(1, "iop");
634 this.pstmt.setString(2, "higjklmn");
635
636 int updateCount = this.pstmt.executeUpdate();
638 assertTrue("Update count must be '1', was '" + updateCount + "'",
639 (updateCount == 1));
640
641 this.pstmt.clearParameters();
642
643 this.pstmt.close();
644
645 this.rs = this.stmt
646 .executeQuery("SELECT id, strdata1, strdata2 FROM statement_test");
647
648 assertTrue(this.rs.next());
649 assertTrue(this.rs.getInt(1) == 999);
650 assertTrue("Expected 'iop', received '" + this.rs.getString(2) + "'",
651 "iop".equals(this.rs.getString(2)));
652 assertTrue("Expected 'higjklmn', received '" + this.rs.getString(3)
653 + "'", "higjklmn".equals(this.rs.getString(3)));
654 }
655
656
662 public void testPreparedStatementBatch() throws SQLException {
663 this.pstmt = this.conn.prepareStatement("INSERT INTO "
664 + "statement_batch_test (strdata1, strdata2) VALUES (?,?)");
665
666 for (int i = 0; i < 1000; i++) {
667 this.pstmt.setString(1, "batch_" + i);
668 this.pstmt.setString(2, "batch_" + i);
669 this.pstmt.addBatch();
670 }
671
672 int[] updateCounts = this.pstmt.executeBatch();
673
674 for (int i = 0; i < updateCounts.length; i++) {
675 assertTrue("Update count must be '1', was '" + updateCounts[i]
676 + "'", (updateCounts[i] == 1));
677 }
678 }
679
680
686 public void testSelectColumns() throws SQLException {
687 for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
688 long start = System.currentTimeMillis();
689 this.rs = this.stmt
690 .executeQuery("SELECT * from statement_col_test_" + i);
691
692 if (this.rs.next()) {
693 ;
694 }
695
696 long end = System.currentTimeMillis();
697 System.out.println(i + " columns = " + (end - start) + " ms");
698 }
699 }
700
701
707 public void testStubbed() throws SQLException {
708 try {
709 this.stmt.getResultSetHoldability();
710 } catch (NotImplemented notImplEx) {
711 ;
712 }
713 }
714
715
721 public void testBinaryResultSetNumericTypes() throws Exception {
722
727
728 String unsignedMinimum = "0";
729
730 String tiMinimum = "-128";
731 String tiMaximum = "127";
732 String utiMaximum = "255";
733
734 String siMinimum = "-32768";
735 String siMaximum = "32767";
736 String usiMaximum = "65535";
737
738 String miMinimum = "-8388608";
739 String miMaximum = "8388607";
740 String umiMaximum = "16777215";
741
742 String iMinimum = "-2147483648";
743 String iMaximum = "2147483647";
744 String uiMaximum = "4294967295";
745
746 String biMinimum = "-9223372036854775808";
747 String biMaximum = "9223372036854775807";
748 String ubiMaximum = "18446744073709551615";
749
750 try {
751 this.stmt
752 .executeUpdate("DROP TABLE IF EXISTS testBinaryResultSetNumericTypes");
753 this.stmt
754 .executeUpdate("CREATE TABLE testBinaryResultSetNumericTypes(rowOrder TINYINT, ti TINYINT,"
755 + "uti TINYINT UNSIGNED, si SMALLINT,"
756 + "usi SMALLINT UNSIGNED, mi MEDIUMINT,"
757 + "umi MEDIUMINT UNSIGNED, i INT, ui INT UNSIGNED,"
758 + "bi BIGINT, ubi BIGINT UNSIGNED)");
759 PreparedStatement inserter = this.conn
760 .prepareStatement("INSERT INTO testBinaryResultSetNumericTypes VALUES (?,?,?,?,?,?,?,?,?,?,?)");
761 inserter.setInt(1, 0);
762 inserter.setString(2, tiMinimum);
763 inserter.setString(3, unsignedMinimum);
764 inserter.setString(4, siMinimum);
765 inserter.setString(5, unsignedMinimum);
766 inserter.setString(6, miMinimum);
767 inserter.setString(7, unsignedMinimum);
768 inserter.setString(8, iMinimum);
769 inserter.setString(9, unsignedMinimum);
770 inserter.setString(10, biMinimum);
771 inserter.setString(11, unsignedMinimum);
772 inserter.executeUpdate();
773
774 inserter.setInt(1, 1);
775 inserter.setString(2, tiMaximum);
776 inserter.setString(3, utiMaximum);
777 inserter.setString(4, siMaximum);
778 inserter.setString(5, usiMaximum);
779 inserter.setString(6, miMaximum);
780 inserter.setString(7, umiMaximum);
781 inserter.setString(8, iMaximum);
782 inserter.setString(9, uiMaximum);
783 inserter.setString(10, biMaximum);
784 inserter.setString(11, ubiMaximum);
785 inserter.executeUpdate();
786
787 PreparedStatement selector = this.conn
788 .prepareStatement("SELECT * FROM testBinaryResultSetNumericTypes ORDER by rowOrder ASC");
789 this.rs = selector.executeQuery();
790
791 assertTrue(this.rs.next());
792
793 assertTrue(this.rs.getString(2).equals(tiMinimum));
794 assertTrue(this.rs.getString(3).equals(unsignedMinimum));
795 assertTrue(this.rs.getString(4).equals(siMinimum));
796 assertTrue(this.rs.getString(5).equals(unsignedMinimum));
797 assertTrue(this.rs.getString(6).equals(miMinimum));
798 assertTrue(this.rs.getString(7).equals(unsignedMinimum));
799 assertTrue(this.rs.getString(8).equals(iMinimum));
800 assertTrue(this.rs.getString(9).equals(unsignedMinimum));
801 assertTrue(this.rs.getString(10).equals(biMinimum));
802 assertTrue(this.rs.getString(11).equals(unsignedMinimum));
803
804 assertTrue(this.rs.next());
805
806 assertTrue(this.rs.getString(2) + " != " + tiMaximum, this.rs
807 .getString(2).equals(tiMaximum));
808 assertTrue(this.rs.getString(3) + " != " + utiMaximum, this.rs
809 .getString(3).equals(utiMaximum));
810 assertTrue(this.rs.getString(4) + " != " + siMaximum, this.rs
811 .getString(4).equals(siMaximum));
812 assertTrue(this.rs.getString(5) + " != " + usiMaximum, this.rs
813 .getString(5).equals(usiMaximum));
814 assertTrue(this.rs.getString(6) + " != " + miMaximum, this.rs
815 .getString(6).equals(miMaximum));
816 assertTrue(this.rs.getString(7) + " != " + umiMaximum, this.rs
817 .getString(7).equals(umiMaximum));
818 assertTrue(this.rs.getString(8) + " != " + iMaximum, this.rs
819 .getString(8).equals(iMaximum));
820 assertTrue(this.rs.getString(9) + " != " + uiMaximum, this.rs
821 .getString(9).equals(uiMaximum));
822 assertTrue(this.rs.getString(10) + " != " + biMaximum, this.rs
823 .getString(10).equals(biMaximum));
824 assertTrue(this.rs.getString(11) + " != " + ubiMaximum, this.rs
825 .getString(11).equals(ubiMaximum));
826
827 assertTrue(!this.rs.next());
828 } finally {
829 this.stmt
830 .executeUpdate("DROP TABLE IF EXISTS testBinaryResultSetNumericTypes");
831 }
832 }
833
834 public void testTruncationOnRead() throws Exception {
835 this.rs = this.stmt.executeQuery("SELECT '" + Long.MAX_VALUE + "'");
836 this.rs.next();
837
838 try {
839 this.rs.getByte(1);
840 fail("Should've thrown an out-of-range exception");
841 } catch (SQLException sqlEx) {
842 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
843 .equals(sqlEx.getSQLState()));
844 }
845
846 try {
847 this.rs.getShort(1);
848 fail("Should've thrown an out-of-range exception");
849 } catch (SQLException sqlEx) {
850 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
851 .equals(sqlEx.getSQLState()));
852 }
853
854 try {
855 this.rs.getInt(1);
856 fail("Should've thrown an out-of-range exception");
857 } catch (SQLException sqlEx) {
858 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
859 .equals(sqlEx.getSQLState()));
860 }
861
862 this.rs = this.stmt.executeQuery("SELECT '" + Double.MAX_VALUE + "'");
863
864 this.rs.next();
865
866 try {
867 this.rs.getByte(1);
868 fail("Should've thrown an out-of-range exception");
869 } catch (SQLException sqlEx) {
870 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
871 .equals(sqlEx.getSQLState()));
872 }
873
874 try {
875 this.rs.getShort(1);
876 fail("Should've thrown an out-of-range exception");
877 } catch (SQLException sqlEx) {
878 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
879 .equals(sqlEx.getSQLState()));
880 }
881
882 try {
883 this.rs.getInt(1);
884 fail("Should've thrown an out-of-range exception");
885 } catch (SQLException sqlEx) {
886 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
887 .equals(sqlEx.getSQLState()));
888 }
889
890 try {
891 this.rs.getLong(1);
892 fail("Should've thrown an out-of-range exception");
893 } catch (SQLException sqlEx) {
894 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
895 .equals(sqlEx.getSQLState()));
896 }
897
898 try {
899 this.rs.getLong(1);
900 fail("Should've thrown an out-of-range exception");
901 } catch (SQLException sqlEx) {
902 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
903 .equals(sqlEx.getSQLState()));
904 }
905
906 PreparedStatement pStmt = null;
907
908 System.out
909 .println("Testing prepared statements with binary result sets now");
910
911 try {
912 this.stmt
913 .executeUpdate("DROP TABLE IF EXISTS testTruncationOnRead");
914 this.stmt
915 .executeUpdate("CREATE TABLE testTruncationOnRead(intField INTEGER, bigintField BIGINT, doubleField DOUBLE)");
916 this.stmt.executeUpdate("INSERT INTO testTruncationOnRead VALUES ("
917 + Integer.MAX_VALUE + ", " + Long.MAX_VALUE + ", "
918 + Double.MAX_VALUE + ")");
919 this.stmt.executeUpdate("INSERT INTO testTruncationOnRead VALUES ("
920 + Integer.MIN_VALUE + ", " + Long.MIN_VALUE + ", "
921 + Double.MIN_VALUE + ")");
922
923 pStmt = this.conn
924 .prepareStatement("SELECT intField, bigintField, doubleField FROM testTruncationOnRead ORDER BY intField DESC");
925 this.rs = pStmt.executeQuery();
926
927 this.rs.next();
928
929 try {
930 this.rs.getByte(1);
931 fail("Should've thrown an out-of-range exception");
932 } catch (SQLException sqlEx) {
933 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
934 .equals(sqlEx.getSQLState()));
935 }
936
937 try {
938 this.rs.getInt(2);
939 fail("Should've thrown an out-of-range exception");
940 } catch (SQLException sqlEx) {
941 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
942 .equals(sqlEx.getSQLState()));
943 }
944
945 try {
946 this.rs.getLong(3);
947 fail("Should've thrown an out-of-range exception");
948 } catch (SQLException sqlEx) {
949 assertTrue(SQLError.SQL_STATE_NUMERIC_VALUE_OUT_OF_RANGE
950 .equals(sqlEx.getSQLState()));
951 }
952 } finally {
953 this.stmt
954 .executeUpdate("DROP TABLE IF EXISTS testTruncationOnRead");
955 }
956
957 }
958
959 public void testParsedConversionWarning() throws Exception {
960 if (versionMeetsMinimum(4, 1)) {
961 try {
962 Properties props = new Properties ();
963 props.setProperty("useUsageAdvisor", "true");
964 Connection warnConn = getConnectionWithProps(props);
965
966 this.stmt
967 .executeUpdate("DROP TABLE IF EXISTS testParsedConversionWarning");
968 this.stmt
969 .executeUpdate("CREATE TABLE testParsedConversionWarning(field1 VARCHAR(255))");
970 this.stmt
971 .executeUpdate("INSERT INTO testParsedConversionWarning VALUES ('1.0')");
972
973 PreparedStatement badStmt = warnConn
974 .prepareStatement("SELECT field1 FROM testParsedConversionWarning");
975
976 this.rs = badStmt.executeQuery();
977 assertTrue(this.rs.next());
978 this.rs.getFloat(1);
979 } finally {
980 this.stmt
981 .executeUpdate("DROP TABLE IF EXISTS testParsedConversionWarning");
982 }
983 }
984 }
985
986 public void testHoldingResultSetsOverClose() throws Exception {
987 Properties props = new Properties ();
988 props.setProperty("holdResultsOpenOverStatementClose", "true");
989
990 Connection conn2 = getConnectionWithProps(props);
991
992 Statement stmt2 = null;
993 PreparedStatement pstmt2 = null;
994
995 try {
996 stmt2 = conn2.createStatement();
997
998 this.rs = stmt2.executeQuery("SELECT 1");
999 this.rs.next();
1000 this.rs.getInt(1);
1001 stmt2.close();
1002 this.rs.getInt(1);
1003
1004 stmt2 = conn2.createStatement();
1005 stmt2.execute("SELECT 1");
1006 this.rs = stmt2.getResultSet();
1007 this.rs.next();
1008 this.rs.getInt(1);
1009 stmt2.execute("SELECT 2");
1010 this.rs.getInt(1);
1011
1012 pstmt2 = conn2.prepareStatement("SELECT 1");
1013 this.rs = pstmt2.executeQuery();
1014 this.rs.next();
1015 this.rs.getInt(1);
1016 pstmt2.close();
1017 this.rs.getInt(1);
1018
1019 pstmt2 = conn2.prepareStatement("SELECT 1");
1020 this.rs = pstmt2.executeQuery();
1021 this.rs.next();
1022 this.rs.getInt(1);
1023 pstmt2.executeQuery();
1024 this.rs.getInt(1);
1025 pstmt2.execute();
1026 this.rs.getInt(1);
1027
1028 pstmt2 = ((com.mysql.jdbc.Connection) conn2)
1029 .clientPrepareStatement("SELECT 1");
1030 this.rs = pstmt2.executeQuery();
1031 this.rs.next();
1032 this.rs.getInt(1);
1033 pstmt2.close();
1034 this.rs.getInt(1);
1035
1036 pstmt2 = ((com.mysql.jdbc.Connection) conn2)
1037 .clientPrepareStatement("SELECT 1");
1038 this.rs = pstmt2.executeQuery();
1039 this.rs.next();
1040 this.rs.getInt(1);
1041 pstmt2.executeQuery();
1042 this.rs.getInt(1);
1043 pstmt2.execute();
1044 this.rs.getInt(1);
1045
1046 stmt2 = conn2.createStatement();
1047 this.rs = stmt2.executeQuery("SELECT 1");
1048 this.rs.next();
1049 this.rs.getInt(1);
1050 stmt2.executeQuery("SELECT 2");
1051 this.rs.getInt(1);
1052 this.rs = stmt2.executeQuery("SELECT 1");
1053 this.rs.next();
1054 this.rs.getInt(1);
1055 stmt2.executeUpdate("SET @var=1");
1056 this.rs.getInt(1);
1057 stmt2.execute("SET @var=2");
1058 this.rs.getInt(1);
1059 } finally {
1060 if (stmt2 != null) {
1061 stmt2.close();
1062 }
1063 }
1064 }
1065
1066 public void testEnableStreamingResults() throws Exception {
1067 Statement streamStmt = this.conn.createStatement();
1068 ((com.mysql.jdbc.Statement) streamStmt).enableStreamingResults();
1069 assertEquals(streamStmt.getFetchSize(), Integer.MIN_VALUE);
1070 assertEquals(streamStmt.getResultSetType(), ResultSet.TYPE_FORWARD_ONLY);
1071 }
1072}
1073 |
Popular Tags |
Java API By Example, From Geeks To Geeks.