001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one
003: * or more contributor license agreements. See the NOTICE file
004: * distributed with this work for additional information
005: * regarding copyright ownership. The ASF licenses this file
006: * to you under the Apache License, Version 2.0 (the
007: * "License"); you may not use this file except in compliance
008: * with the License. You may obtain a copy of the License at
009: *
010: * http://www.apache.org/licenses/LICENSE-2.0
011: *
012: * Unless required by applicable law or agreed to in writing,
013: * software distributed under the License is distributed on an
014: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
015: * KIND, either express or implied. See the License for the
016: * specific language governing permissions and limitations
017: * under the License.
018: */
019:
020: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
021:
022: import java.sql.CallableStatement;
023: import java.sql.Connection;
024: import java.sql.DriverManager;
025: import java.sql.PreparedStatement;
026: import java.sql.ResultSet;
027: import java.sql.SQLException;
028: import java.sql.Statement;
029: import junit.extensions.TestSetup;
030: import junit.framework.Test;
031: import junit.framework.TestSuite;
032: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
033: import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
034: import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
035: import org.apache.derbyTesting.junit.JDBC;
036:
037: /**
038: * Tests of stored procedures.
039: */
040: public class ProcedureTest extends BaseJDBCTestCase {
041:
042: /**
043: * Creates a new <code>ProcedureTest</code> instance.
044: *
045: * @param name name of the test
046: */
047: public ProcedureTest(String name) {
048: super (name);
049: }
050:
051: // TESTS
052:
053: /**
054: * Tests that <code>Statement.executeQuery()</code> fails when no
055: * result sets are returned.
056: * @exception SQLException if a database error occurs
057: */
058: public void testExecuteQueryWithNoDynamicResultSets()
059: throws SQLException {
060: Statement stmt = createStatement();
061: try {
062: stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(0)");
063: fail("executeQuery() didn't fail.");
064: } catch (SQLException sqle) {
065: assertNoResultSetFromExecuteQuery(sqle);
066: }
067: stmt.close();
068: }
069:
070: /**
071: * Tests that <code>Statement.executeQuery()</code> succeeds when
072: * one result set is returned from a stored procedure.
073: * @exception SQLException if a database error occurs
074: */
075: public void testExecuteQueryWithOneDynamicResultSet()
076: throws SQLException {
077: Statement stmt = createStatement();
078: ResultSet rs = stmt
079: .executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
080: assertNotNull("executeQuery() returned null.", rs);
081: assertTrue("Result set has no data.", rs.next());
082: rs.close();
083: stmt.close();
084: }
085:
086: /**
087: * Tests that <code>Statement.executeQuery()</code> fails when
088: * multiple result sets are returned.
089: * @exception SQLException if a database error occurs
090: */
091: public void testExecuteQueryWithMoreThanOneDynamicResultSet()
092: throws SQLException {
093: Statement stmt = createStatement();
094: try {
095: stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(2)");
096: fail("executeQuery() didn't fail.");
097: } catch (SQLException sqle) {
098: assertMultipleResultsFromExecuteQuery(sqle);
099: }
100: stmt.close();
101: }
102:
103: /**
104: * Tests that <code>Statement.executeUpdate()</code> succeeds when
105: * no result sets are returned.
106: *
107: * <p>Currently, this test fails with JCC.
108: *
109: * @exception SQLException if a database error occurs
110: */
111: public void xtestExecuteUpdateWithNoDynamicResultSets()
112: throws SQLException {
113: Statement stmt = getConnection().createStatement();
114: int count = stmt
115: .executeUpdate("CALL RETRIEVE_DYNAMIC_RESULTS(0)");
116: assertEquals("Wrong update count.", 0, count);
117: stmt.close();
118: }
119:
120: /**
121: * Tests that <code>Statement.executeUpdate()</code> fails when a
122: * result set is returned from a stored procedure.
123: * @exception SQLException if a database error occurs
124: */
125: public void testExecuteUpdateWithOneDynamicResultSet()
126: throws SQLException {
127: Statement stmt = createStatement();
128: try {
129: stmt.executeUpdate("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
130: fail("executeUpdate() didn't fail.");
131: } catch (SQLException sqle) {
132: assertResultsFromExecuteUpdate(sqle);
133: }
134: stmt.close();
135: }
136:
137: /**
138: * Tests that <code>PreparedStatement.executeQuery()</code> fails
139: * when no result sets are returned.
140: * @exception SQLException if a database error occurs
141: */
142: public void testExecuteQueryWithNoDynamicResultSets_prepared()
143: throws SQLException {
144: PreparedStatement ps = getConnection().prepareStatement(
145: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
146: ps.setInt(1, 0);
147: try {
148: ps.executeQuery();
149: fail("executeQuery() didn't fail.");
150: } catch (SQLException sqle) {
151: assertNoResultSetFromExecuteQuery(sqle);
152: }
153: ps.close();
154: }
155:
156: /**
157: * Tests that <code>PreparedStatement.executeQuery()</code>
158: * succeeds when one result set is returned from a stored
159: * procedure.
160: * @exception SQLException if a database error occurs
161: */
162: public void testExecuteQueryWithOneDynamicResultSet_prepared()
163: throws SQLException {
164: PreparedStatement ps = getConnection().prepareStatement(
165: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
166: ps.setInt(1, 1);
167: ResultSet rs = ps.executeQuery();
168: assertNotNull("executeQuery() returned null.", rs);
169: assertTrue("Result set has no data.", rs.next());
170: rs.close();
171: ps.close();
172: }
173:
174: /**
175: * Tests that <code>PreparedStatement.executeQuery()</code> fails
176: * when multiple result sets are returned.
177: * @exception SQLException if a database error occurs
178: */
179: public void testExecuteQueryWithMoreThanOneDynamicResultSet_prepared()
180: throws SQLException {
181: PreparedStatement ps = getConnection().prepareStatement(
182: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
183: ps.setInt(1, 2);
184: try {
185: ps.executeQuery();
186: fail("executeQuery() didn't fail.");
187: } catch (SQLException sqle) {
188: assertMultipleResultsFromExecuteQuery(sqle);
189: }
190: ps.close();
191: }
192:
193: /**
194: * Tests that <code>PreparedStatement.executeUpdate()</code>
195: * succeeds when no result sets are returned.
196: *
197: * <p>Currently, this test fails with JCC.
198: *
199: * @exception SQLException if a database error occurs
200: */
201: public void xtestExecuteUpdateWithNoDynamicResultSets_prepared()
202: throws SQLException {
203: PreparedStatement ps = getConnection().prepareStatement(
204: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
205: ps.setInt(1, 0);
206: int count = ps.executeUpdate();
207: assertEquals("Wrong update count.", 0, count);
208: ps.close();
209: }
210:
211: /**
212: * Tests that <code>PreparedStatement.executeUpdate()</code> fails
213: * when a result set is returned from a stored procedure.
214: *
215: * <p>Currently, this test fails with
216: * JCC. However, the corresponding tests for
217: * <code>Statement</code> and <code>CallableStatement</code>
218: * succeed. Strange...
219: *
220: * @exception SQLException if a database error occurs
221: */
222: public void xtestExecuteUpdateWithOneDynamicResultSet_prepared()
223: throws SQLException {
224: PreparedStatement ps = getConnection().prepareStatement(
225: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
226: ps.setInt(1, 1);
227: try {
228: ps.executeUpdate();
229: fail("executeUpdate() didn't fail.");
230: } catch (SQLException sqle) {
231: assertResultsFromExecuteUpdate(sqle);
232: }
233: ps.close();
234: }
235:
236: /**
237: * Tests that <code>CallableStatement.executeQuery()</code> fails
238: * when no result sets are returned.
239: * @exception SQLException if a database error occurs
240: */
241: public void testExecuteQueryWithNoDynamicResultSets_callable()
242: throws SQLException {
243: CallableStatement cs = getConnection().prepareCall(
244: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
245: cs.setInt(1, 0);
246: try {
247: cs.executeQuery();
248: fail("executeQuery() didn't fail.");
249: } catch (SQLException sqle) {
250: assertNoResultSetFromExecuteQuery(sqle);
251: }
252: }
253:
254: /**
255: * Tests that <code>CallableStatement.executeQuery()</code>
256: * succeeds when one result set is returned from a stored
257: * procedure.
258: * @exception SQLException if a database error occurs
259: */
260: public void testExecuteQueryWithOneDynamicResultSet_callable()
261: throws SQLException {
262: CallableStatement cs = getConnection().prepareCall(
263: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
264: cs.setInt(1, 1);
265: ResultSet rs = cs.executeQuery();
266: assertNotNull("executeQuery() returned null.", rs);
267: assertTrue("Result set has no data.", rs.next());
268: rs.close();
269: cs.close();
270: }
271:
272: /**
273: * Tests that <code>CallableStatement.executeQuery()</code> fails
274: * when multiple result sets are returned.
275: * @exception SQLException if a database error occurs
276: */
277: public void testExecuteQueryWithMoreThanOneDynamicResultSet_callable()
278: throws SQLException {
279: CallableStatement cs = getConnection().prepareCall(
280: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
281: cs.setInt(1, 2);
282: try {
283: cs.executeQuery();
284: fail("executeQuery() didn't fail.");
285: } catch (SQLException sqle) {
286: assertMultipleResultsFromExecuteQuery(sqle);
287: }
288: }
289:
290: /**
291: * Tests that <code>CallableStatement.executeUpdate()</code>
292: * succeeds when no result sets are returned.
293: *
294: * <p>Currently, this test fails with JCC.
295: *
296: * @exception SQLException if a database error occurs
297: */
298: public void xtestExecuteUpdateWithNoDynamicResultSets_callable()
299: throws SQLException {
300: CallableStatement cs = getConnection().prepareCall(
301: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
302: cs.setInt(1, 0);
303: int count = cs.executeUpdate();
304: assertEquals("Wrong update count.", 0, count);
305: cs.close();
306: }
307:
308: /**
309: * Tests that <code>CallableStatement.executeUpdate()</code> fails
310: * when a result set is returned from a stored procedure.
311: * @exception SQLException if a database error occurs
312: */
313: public void testExecuteUpdateWithOneDynamicResultSet_callable()
314: throws SQLException {
315: CallableStatement cs = getConnection().prepareCall(
316: "CALL RETRIEVE_DYNAMIC_RESULTS(?)");
317: cs.setInt(1, 1);
318: try {
319: cs.executeUpdate();
320: fail("executeUpdate() didn't fail.");
321: } catch (SQLException sqle) {
322: assertResultsFromExecuteUpdate(sqle);
323: }
324: cs.close();
325: }
326:
327: /**
328: * Tests that the effects of executing a stored procedure with
329: * <code>executeQuery()</code> are correctly rolled back when
330: * <code>Connection.rollback()</code> is called.
331: * @exception SQLException if a database error occurs
332: */
333: public void testRollbackStoredProcWithExecuteQuery()
334: throws SQLException {
335:
336: Statement stmt = createStatement();
337: ResultSet rs = stmt
338: .executeQuery("CALL PROC_WITH_SIDE_EFFECTS(1)");
339: rs.close();
340: stmt.getConnection().rollback();
341: ResultSet tableRs = stmt
342: .executeQuery("SELECT * FROM SIMPLE_TABLE");
343: // table should be empty after rollback
344: assertFalse(
345: "Side effects from stored procedure not rolled back.",
346: tableRs.next());
347: tableRs.close();
348: stmt.close();
349: }
350:
351: /**
352: * Tests that the effects of executing a stored procedure with
353: * <code>executeUpdate()</code> are correctly rolled back when
354: * <code>Connection.rollback()</code> is called.
355: * @exception SQLException if a database error occurs
356: */
357: public void testRollbackStoredProcWithExecuteUpdate()
358: throws SQLException {
359: Statement stmt = createStatement();
360: stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(0)");
361: stmt.getConnection().rollback();
362: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
363: // table should be empty after rollback
364: assertFalse(
365: "Side effects from stored procedure not rolled back.",
366: rs.next());
367: rs.close();
368: stmt.close();
369: }
370:
371: /**
372: * Tests that the effects of executing a stored procedure with
373: * <code>executeQuery()</code> are correctly rolled back when the
374: * query fails because the number of returned result sets is zero.
375: *
376: * <p> This test case fails with JCC.
377: *
378: * @exception SQLException if a database error occurs
379: */
380: public void xtestRollbackStoredProcWhenExecuteQueryReturnsNothing()
381: throws SQLException {
382: Connection conn = getConnection();
383: conn.setAutoCommit(true);
384: Statement stmt = conn.createStatement();
385: try {
386: ResultSet rs = stmt
387: .executeQuery("CALL PROC_WITH_SIDE_EFFECTS(0)");
388: fail("executeQuery() didn't fail.");
389: } catch (SQLException sqle) {
390: assertNoResultSetFromExecuteQuery(sqle);
391: }
392: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
393: assertFalse(
394: "Side effects from stored procedure not rolled back.",
395: rs.next());
396: rs.close();
397: stmt.close();
398: }
399:
400: /**
401: * Tests that the effects of executing a stored procedure with
402: * <code>executeQuery()</code> are correctly rolled back when the
403: * query fails because the number of returned result sets is more
404: * than one.
405: *
406: * <p> This test case fails with JCC.
407: *
408: * @exception SQLException if a database error occurs
409: */
410: public void xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch()
411: throws SQLException {
412: Connection conn = getConnection();
413: conn.setAutoCommit(true);
414: Statement stmt = conn.createStatement();
415: try {
416: ResultSet rs = stmt
417: .executeQuery("CALL PROC_WITH_SIDE_EFFECTS(2)");
418: fail("executeQuery() didn't fail.");
419: } catch (SQLException sqle) {
420: assertMultipleResultsFromExecuteQuery(sqle);
421: }
422: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
423: assertFalse(
424: "Side effects from stored procedure not rolled back.",
425: rs.next());
426: rs.close();
427: stmt.close();
428: }
429:
430: /**
431: * Tests that the effects of executing a stored procedure with
432: * <code>executeUpdate()</code> are correctly rolled back when the
433: * query fails because the stored procedure returned a result set.
434: *
435: * <p> This test case fails with JCC.
436: *
437: * @exception SQLException if a database error occurs
438: */
439: public void xtestRollbackStoredProcWhenExecuteUpdateReturnsResults()
440: throws SQLException {
441: Connection conn = getConnection();
442: conn.setAutoCommit(true);
443: Statement stmt = conn.createStatement();
444: try {
445: stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(1)");
446: fail("executeUpdate() didn't fail.");
447: } catch (SQLException sqle) {
448: assertResultsFromExecuteUpdate(sqle);
449: }
450: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
451: assertFalse(
452: "Side effects from stored procedure not rolled back.",
453: rs.next());
454: rs.close();
455: stmt.close();
456: }
457:
458: /**
459: * Tests that the effects of executing a stored procedure with
460: * <code>executeQuery()</code> are correctly rolled back when the
461: * query fails because the number of returned result sets is zero.
462: *
463: * <p> This test case fails with JCC.
464: *
465: * @exception SQLException if a database error occurs
466: */
467: public void xtestRollbackStoredProcWhenExecuteQueryReturnsNothing_prepared()
468: throws SQLException {
469: Connection conn = getConnection();
470: conn.setAutoCommit(true);
471: PreparedStatement ps = conn
472: .prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
473: ps.setInt(1, 0);
474: try {
475: ResultSet rs = ps.executeQuery();
476: fail("executeQuery() didn't fail.");
477: } catch (SQLException sqle) {
478: assertNoResultSetFromExecuteQuery(sqle);
479: }
480: Statement stmt = conn.createStatement();
481: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
482: assertFalse(
483: "Side effects from stored procedure not rolled back.",
484: rs.next());
485: rs.close();
486: ps.close();
487: stmt.close();
488: }
489:
490: /**
491: * Tests that the effects of executing a stored procedure with
492: * <code>executeQuery()</code> are correctly rolled back when the
493: * query fails because the number of returned result sets is more
494: * than one.
495: *
496: * <p> This test case fails with JCC.
497: *
498: * @exception SQLException if a database error occurs
499: */
500: public void xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch_prepared()
501: throws SQLException {
502: Connection conn = getConnection();
503: conn.setAutoCommit(true);
504: PreparedStatement ps = conn
505: .prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
506: ps.setInt(1, 2);
507: try {
508: ResultSet rs = ps.executeQuery();
509: fail("executeQuery() didn't fail.");
510: } catch (SQLException sqle) {
511: assertMultipleResultsFromExecuteQuery(sqle);
512: }
513: Statement stmt = conn.createStatement();
514: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
515: assertFalse(
516: "Side effects from stored procedure not rolled back.",
517: rs.next());
518: rs.close();
519: ps.close();
520: stmt.close();
521: }
522:
523: /**
524: * Tests that the effects of executing a stored procedure with
525: * <code>executeUpdate()</code> are correctly rolled back when the
526: * query fails because the stored procedure returned a result set.
527: *
528: * <p> This test case fails with JCC.
529: *
530: * @exception SQLException if a database error occurs
531: */
532: public void xtestRollbackStoredProcWhenExecuteUpdateReturnsResults_prepared()
533: throws SQLException {
534: Connection conn = getConnection();
535: conn.setAutoCommit(true);
536: PreparedStatement ps = conn
537: .prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
538: ps.setInt(1, 1);
539: try {
540: ps.executeUpdate();
541: fail("executeUpdate() didn't fail.");
542: } catch (SQLException sqle) {
543: assertResultsFromExecuteUpdate(sqle);
544: }
545: Statement stmt = conn.createStatement();
546: ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
547: assertFalse(
548: "Side effects from stored procedure not rolled back.",
549: rs.next());
550: rs.close();
551: ps.close();
552: stmt.close();
553: }
554:
555: /**
556: * Tests that closed result sets are not returned when calling
557: * <code>executeQuery()</code>.
558: * @exception SQLException if a database error occurs
559: */
560: public void testClosedDynamicResultSetsFromExecuteQuery()
561: throws SQLException {
562: Statement stmt = createStatement();
563: try {
564: ResultSet rs = stmt
565: .executeQuery("CALL RETRIEVE_CLOSED_RESULT()");
566: fail("executeQuery() didn't fail.");
567: } catch (SQLException sqle) {
568: assertNoResultSetFromExecuteQuery(sqle);
569: }
570: stmt.close();
571: }
572:
573: /**
574: * Tests that closed result sets are ignored when calling
575: * <code>executeUpdate()</code>.
576: * @exception SQLException if a database error occurs
577: */
578: public void testClosedDynamicResultSetsFromExecuteUpdate()
579: throws SQLException {
580: Statement stmt = createStatement();
581: stmt.executeUpdate("CALL RETRIEVE_CLOSED_RESULT()");
582: stmt.close();
583: }
584:
585: /**
586: * Tests that dynamic result sets from other connections are
587: * ignored when calling <code>executeQuery</code>.
588: * @exception SQLException if a database error occurs
589: */
590: public void testDynamicResultSetsFromOtherConnectionWithExecuteQuery()
591: throws SQLException {
592: PreparedStatement ps = getConnection().prepareStatement(
593: "CALL RETRIEVE_EXTERNAL_RESULT(?,?,?)");
594:
595: ps.setString(1, getTestConfiguration().getDatabaseName());
596: ps.setString(2, getTestConfiguration().getUserName());
597: ps.setString(3, getTestConfiguration().getUserPassword());
598: try {
599: ps.executeQuery();
600: fail("executeQuery() didn't fail.");
601: } catch (SQLException sqle) {
602: assertNoResultSetFromExecuteQuery(sqle);
603: }
604: ps.close();
605: }
606:
607: /**
608: * Tests that dynamic result sets from other connections are
609: * ignored when calling <code>executeUpdate</code>.
610: * @exception SQLException if a database error occurs
611: */
612: public void testDynamicResultSetsFromOtherConnectionWithExecuteUpdate()
613: throws SQLException {
614: PreparedStatement ps = getConnection().prepareStatement(
615: "CALL RETRIEVE_EXTERNAL_RESULT(?,?,?)");
616:
617: ps.setString(1, getTestConfiguration().getDatabaseName());
618: ps.setString(2, getTestConfiguration().getUserName());
619: ps.setString(3, getTestConfiguration().getUserPassword());
620:
621: ps.executeUpdate();
622: ps.close();
623: }
624:
625: // UTILITY METHODS
626:
627: /**
628: * Raises an exception if the exception is not caused by
629: * <code>executeQuery()</code> returning no result set.
630: *
631: * @param sqle a <code>SQLException</code> value
632: */
633: private void assertNoResultSetFromExecuteQuery(SQLException sqle) {
634: if (usingDerbyNet()) {
635: assertNull("Unexpected SQL state.", sqle.getSQLState());
636: } else {
637: assertSQLState("Unexpected SQL state.", "X0Y78", sqle);
638: }
639: }
640:
641: /**
642: * Raises an exception if the exception is not caused by
643: * <code>executeQuery()</code> returning multiple result sets.
644: *
645: * @param sqle a <code>SQLException</code> value
646: */
647: private void assertMultipleResultsFromExecuteQuery(SQLException sqle) {
648: if (usingDerbyNet()) {
649: assertNull("Unexpected SQL state.", sqle.getSQLState());
650: } else {
651: assertSQLState("Unexpected SQL state.", "X0Y78", sqle);
652: }
653: }
654:
655: /**
656: * Raises an exception if the exception is not caused by
657: * <code>executeUpdate()</code> returning result sets.
658: *
659: * @param sqle a <code>SQLException</code> value
660: */
661: private void assertResultsFromExecuteUpdate(SQLException sqle) {
662: if (usingDerbyNet()) {
663: assertNull("Unexpected SQL state.", sqle.getSQLState());
664: } else {
665: assertSQLState("Unexpected SQL state.", "X0Y79", sqle);
666: }
667:
668: }
669:
670: // SETUP
671:
672: /**
673: * Creates the test suite and wraps it in a <code>TestSetup</code>
674: * instance which sets up and tears down the test environment.
675: * @return test suite
676: */
677: public static Test suite() {
678: TestSuite suite = new TestSuite();
679:
680: // Need JDBC 2 DriverManager to run these tests
681: if (JDBC.vmSupportsJDBC2()) {
682:
683: suite.addTestSuite(ProcedureTest.class);
684: if (!usingDerbyNet()) {
685: suite.addTest(new ProcedureTest(
686: "xtestExecuteUpdateWithNoDynamicResultSets"));
687: suite
688: .addTest(new ProcedureTest(
689: "xtestExecuteUpdateWithNoDynamicResultSets_prepared"));
690: suite
691: .addTest(new ProcedureTest(
692: "xtestExecuteUpdateWithOneDynamicResultSet_prepared"));
693: suite
694: .addTest(new ProcedureTest(
695: "xtestExecuteUpdateWithNoDynamicResultSets_callable"));
696: suite
697: .addTest(new ProcedureTest(
698: "xtestRollbackStoredProcWhenExecuteQueryReturnsNothing"));
699: suite
700: .addTest(new ProcedureTest(
701: "xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch"));
702: suite
703: .addTest(new ProcedureTest(
704: "xtestRollbackStoredProcWhenExecuteUpdateReturnsResults"));
705: suite.addTest(new ProcedureTest(
706: "xtestRollbackStoredProcWhenExecuteQueryReturnsNothing"
707: + "_prepared"));
708: suite.addTest(new ProcedureTest(
709: "xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch"
710: + "_prepared"));
711: suite.addTest(new ProcedureTest(
712: "xtestRollbackStoredProcWhenExecuteUpdateReturnsResults"
713: + "_prepared"));
714: }
715: }
716: Test test = new BaseJDBCTestSetup(suite) {
717: /**
718: * Creates the tables and the stored procedures used in the test
719: * cases.
720: * @exception SQLException if a database error occurs
721: */
722: protected void setUp() throws SQLException {
723: Connection c = getConnection();
724: c.setAutoCommit(false);
725: Statement s = c.createStatement();
726: for (int i = 0; i < PROCEDURES.length; i++) {
727: s.execute(PROCEDURES[i][1]);
728: }
729: for (int i = 0; i < TABLES.length; i++) {
730: s.execute(TABLES[i][1]);
731: }
732: s.close();
733: c.commit();
734: c.close();
735: }
736:
737: /**
738: * Drops the stored procedures used in the tests.
739: * @exception SQLException if a database error occurs
740: */
741: protected void tearDown() throws Exception {
742: Connection c = getConnection();
743: c.setAutoCommit(false);
744: Statement s = c.createStatement();
745: for (int i = 0; i < PROCEDURES.length; i++) {
746: s.execute("DROP PROCEDURE " + PROCEDURES[i][0]);
747: }
748: for (int i = 0; i < TABLES.length; i++) {
749: s.execute("DROP TABLE " + TABLES[i][0]);
750: }
751: s.close();
752: c.commit();
753: c.close();
754:
755: super .tearDown();
756: }
757: };
758:
759: return new CleanDatabaseTestSetup(test);
760: }
761:
762: /**
763: * Sets up the connection for a test case and clears all tables
764: * used in the test cases.
765: * @exception SQLException if a database error occurs
766: */
767: public void setUp() throws SQLException {
768: Connection conn = getConnection();
769: conn.setAutoCommit(false);
770: Statement s = conn.createStatement();
771: for (int i = 0; i < TABLES.length; i++) {
772: s.execute("DELETE FROM " + TABLES[i][0]);
773: }
774: s.close();
775: conn.commit();
776: }
777:
778: /**
779: * Procedures that should be created before the tests are run and
780: * dropped when the tests have finished. First element in each row
781: * is the name of the procedure, second element is SQL which
782: * creates it.
783: */
784: private static final String[][] PROCEDURES = {
785: {
786: "RETRIEVE_DYNAMIC_RESULTS",
787: "CREATE PROCEDURE RETRIEVE_DYNAMIC_RESULTS(number INT) "
788: + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
789: + ProcedureTest.class.getName()
790: + ".retrieveDynamicResults' "
791: + "DYNAMIC RESULT SETS 4" },
792: {
793: "RETRIEVE_CLOSED_RESULT",
794: "CREATE PROCEDURE RETRIEVE_CLOSED_RESULT() LANGUAGE JAVA "
795: + "PARAMETER STYLE JAVA EXTERNAL NAME '"
796: + ProcedureTest.class.getName()
797: + ".retrieveClosedResult' "
798: + "DYNAMIC RESULT SETS 1" },
799: {
800: "RETRIEVE_EXTERNAL_RESULT",
801: "CREATE PROCEDURE RETRIEVE_EXTERNAL_RESULT("
802: + "DBNAME VARCHAR(128), DBUSER VARCHAR(128), DBPWD VARCHAR(128)) LANGUAGE JAVA "
803: + "PARAMETER STYLE JAVA EXTERNAL NAME '"
804: + ProcedureTest.class.getName()
805: + ".retrieveExternalResult' "
806: + "DYNAMIC RESULT SETS 1" },
807: {
808: "PROC_WITH_SIDE_EFFECTS",
809: "CREATE PROCEDURE PROC_WITH_SIDE_EFFECTS(ret INT) LANGUAGE JAVA "
810: + "PARAMETER STYLE JAVA EXTERNAL NAME '"
811: + ProcedureTest.class.getName()
812: + ".procWithSideEffects' "
813: + "DYNAMIC RESULT SETS 2" }, };
814:
815: /**
816: * Tables that should be created before the tests are run and
817: * dropped when the tests have finished. The tables will be
818: * cleared before each test case is run. First element in each row
819: * is the name of the table, second element is the SQL text which
820: * creates it.
821: */
822: private static final String[][] TABLES = {
823: // SIMPLE_TABLE is used by PROC_WITH_SIDE_EFFECTS
824: { "SIMPLE_TABLE", "CREATE TABLE SIMPLE_TABLE (id INT)" }, };
825:
826: // PROCEDURES
827:
828: /**
829: * Stored procedure which returns 0, 1, 2, 3 or 4 <code>ResultSet</code>s.
830: *
831: * @param number the number of <code>ResultSet</code>s to return
832: * @param rs1 first <code>ResultSet</code>
833: * @param rs2 second <code>ResultSet</code>
834: * @param rs3 third <code>ResultSet</code>
835: * @param rs4 fourth <code>ResultSet</code>
836: * @exception SQLException if a database error occurs
837: */
838: public static void retrieveDynamicResults(int number,
839: ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3,
840: ResultSet[] rs4) throws SQLException {
841: Connection c = DriverManager
842: .getConnection("jdbc:default:connection");
843: if (number > 0) {
844: rs1[0] = c.createStatement().executeQuery("VALUES(1)");
845: }
846: if (number > 1) {
847: rs2[0] = c.createStatement().executeQuery("VALUES(1)");
848: }
849: if (number > 2) {
850: rs3[0] = c.createStatement().executeQuery("VALUES(1)");
851: }
852: if (number > 3) {
853: rs4[0] = c.createStatement().executeQuery("VALUES(1)");
854: }
855: c.close();
856: }
857:
858: /**
859: * Stored procedure which produces a closed result set.
860: *
861: * @param closed holder for the closed result set
862: * @exception SQLException if a database error occurs
863: */
864: public static void retrieveClosedResult(ResultSet[] closed)
865: throws SQLException {
866: Connection c = DriverManager
867: .getConnection("jdbc:default:connection");
868: closed[0] = c.createStatement().executeQuery("VALUES(1)");
869: closed[0].close();
870: c.close();
871: }
872:
873: /**
874: * Stored procedure which produces a result set in another
875: * connection.
876: *
877: * @param external result set from another connection
878: * @exception SQLException if a database error occurs
879: */
880: public static void retrieveExternalResult(String dbName,
881: String user, String password, ResultSet[] external)
882: throws SQLException {
883: // Use a server-side connection to the same database.
884: String url = "jdbc:derby:" + dbName;
885:
886: Connection conn = DriverManager.getConnection(url, user,
887: password);
888:
889: external[0] = conn.createStatement().executeQuery("VALUES(1)");
890: }
891:
892: /**
893: * Stored procedure which inserts a row into SIMPLE_TABLE and
894: * optionally returns result sets.
895: *
896: * @param returnResults if one, return one result set; if greater
897: * than one, return two result sets; otherwise, return no result
898: * set
899: * @param rs1 first result set to return
900: * @param rs2 second result set to return
901: * @exception SQLException if a database error occurs
902: */
903: public static void procWithSideEffects(int returnResults,
904: ResultSet[] rs1, ResultSet[] rs2) throws SQLException {
905: Connection c = DriverManager
906: .getConnection("jdbc:default:connection");
907: Statement stmt = c.createStatement();
908: stmt.executeUpdate("INSERT INTO SIMPLE_TABLE VALUES (42)");
909: if (returnResults > 0) {
910: rs1[0] = c.createStatement().executeQuery("VALUES(1)");
911: }
912: if (returnResults > 1) {
913: rs2[0] = c.createStatement().executeQuery("VALUES(1)");
914: }
915: c.close();
916: }
917: }
|