Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > ProcedureTest


1 /*
2  * Licensed to the Apache Software Foundation (ASF) under one
3  * or more contributor license agreements. See the NOTICE file
4  * distributed with this work for additional information
5  * regarding copyright ownership. The ASF licenses this file
6  * to you under the Apache License, Version 2.0 (the
7  * "License"); you may not use this file except in compliance
8  * with the License. You may obtain a copy of the License at
9  *
10  * http://www.apache.org/licenses/LICENSE-2.0
11  *
12  * Unless required by applicable law or agreed to in writing,
13  * software distributed under the License is distributed on an
14  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15  * KIND, either express or implied. See the License for the
16  * specific language governing permissions and limitations
17  * under the License.
18  */
19
20 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
21
22 import java.sql.CallableStatement ;
23 import java.sql.Connection ;
24 import java.sql.DriverManager ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.Statement ;
29 import junit.extensions.TestSetup;
30 import junit.framework.Test;
31 import junit.framework.TestSuite;
32 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
33 import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
34 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
35 import org.apache.derbyTesting.junit.JDBC;
36
37 /**
38  * Tests of stored procedures.
39  */
40 public class ProcedureTest extends BaseJDBCTestCase {
41
42     /**
43      * Creates a new <code>ProcedureTest</code> instance.
44      *
45      * @param name name of the test
46      */
47     public ProcedureTest(String name) {
48         super(name);
49     }
50
51     // TESTS
52
53     /**
54      * Tests that <code>Statement.executeQuery()</code> fails when no
55      * result sets are returned.
56      * @exception SQLException if a database error occurs
57      */
58     public void testExecuteQueryWithNoDynamicResultSets() throws SQLException {
59         Statement stmt = createStatement();
60         try {
61             stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(0)");
62             fail("executeQuery() didn't fail.");
63         } catch (SQLException sqle) {
64             assertNoResultSetFromExecuteQuery(sqle);
65         }
66         stmt.close();
67     }
68
69     /**
70      * Tests that <code>Statement.executeQuery()</code> succeeds when
71      * one result set is returned from a stored procedure.
72      * @exception SQLException if a database error occurs
73      */
74     public void testExecuteQueryWithOneDynamicResultSet() throws SQLException {
75         Statement stmt = createStatement();
76         ResultSet rs = stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
77         assertNotNull("executeQuery() returned null.", rs);
78         assertTrue("Result set has no data.", rs.next());
79         rs.close();
80         stmt.close();
81     }
82
83     /**
84      * Tests that <code>Statement.executeQuery()</code> fails when
85      * multiple result sets are returned.
86      * @exception SQLException if a database error occurs
87      */
88     public void testExecuteQueryWithMoreThanOneDynamicResultSet()
89         throws SQLException
90     {
91         Statement stmt = createStatement();
92         try {
93             stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(2)");
94             fail("executeQuery() didn't fail.");
95         } catch (SQLException sqle) {
96             assertMultipleResultsFromExecuteQuery(sqle);
97         }
98         stmt.close();
99     }
100
101     /**
102      * Tests that <code>Statement.executeUpdate()</code> succeeds when
103      * no result sets are returned.
104      *
105      * <p>Currently, this test fails with JCC.
106      *
107      * @exception SQLException if a database error occurs
108      */
109     public void xtestExecuteUpdateWithNoDynamicResultSets()
110         throws SQLException
111     {
112         Statement stmt = getConnection().createStatement();
113         int count = stmt.executeUpdate("CALL RETRIEVE_DYNAMIC_RESULTS(0)");
114         assertEquals("Wrong update count.", 0, count);
115         stmt.close();
116     }
117
118     /**
119      * Tests that <code>Statement.executeUpdate()</code> fails when a
120      * result set is returned from a stored procedure.
121      * @exception SQLException if a database error occurs
122      */
123     public void testExecuteUpdateWithOneDynamicResultSet() throws SQLException {
124         Statement stmt = createStatement();
125         try {
126             stmt.executeUpdate("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
127             fail("executeUpdate() didn't fail.");
128         } catch (SQLException sqle) {
129             assertResultsFromExecuteUpdate(sqle);
130         }
131         stmt.close();
132     }
133
134     /**
135      * Tests that <code>PreparedStatement.executeQuery()</code> fails
136      * when no result sets are returned.
137      * @exception SQLException if a database error occurs
138      */
139     public void testExecuteQueryWithNoDynamicResultSets_prepared()
140         throws SQLException
141     {
142         PreparedStatement ps =
143             getConnection().prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
144         ps.setInt(1, 0);
145         try {
146             ps.executeQuery();
147             fail("executeQuery() didn't fail.");
148         } catch (SQLException sqle) {
149             assertNoResultSetFromExecuteQuery(sqle);
150         }
151         ps.close();
152     }
153
154     /**
155      * Tests that <code>PreparedStatement.executeQuery()</code>
156      * succeeds when one result set is returned from a stored
157      * procedure.
158      * @exception SQLException if a database error occurs
159      */
160     public void testExecuteQueryWithOneDynamicResultSet_prepared()
161         throws SQLException
162     {
163         PreparedStatement ps =
164             getConnection().prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
165         ps.setInt(1, 1);
166         ResultSet rs = ps.executeQuery();
167         assertNotNull("executeQuery() returned null.", rs);
168         assertTrue("Result set has no data.", rs.next());
169         rs.close();
170         ps.close();
171     }
172
173     /**
174      * Tests that <code>PreparedStatement.executeQuery()</code> fails
175      * when multiple result sets are returned.
176      * @exception SQLException if a database error occurs
177      */
178     public void testExecuteQueryWithMoreThanOneDynamicResultSet_prepared()
179         throws SQLException
180     {
181         PreparedStatement ps =
182             getConnection().prepareStatement("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     {
204         PreparedStatement ps =
205             getConnection().prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
206         ps.setInt(1, 0);
207         int count = ps.executeUpdate();
208         assertEquals("Wrong update count.", 0, count);
209         ps.close();
210     }
211
212     /**
213      * Tests that <code>PreparedStatement.executeUpdate()</code> fails
214      * when a result set is returned from a stored procedure.
215      *
216      * <p>Currently, this test fails with
217      * JCC. However, the corresponding tests for
218      * <code>Statement</code> and <code>CallableStatement</code>
219      * succeed. Strange...
220      *
221      * @exception SQLException if a database error occurs
222      */
223     public void xtestExecuteUpdateWithOneDynamicResultSet_prepared()
224         throws SQLException
225     {
226         PreparedStatement ps =
227             getConnection().prepareStatement("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
228         ps.setInt(1, 1);
229         try {
230             ps.executeUpdate();
231             fail("executeUpdate() didn't fail.");
232         } catch (SQLException sqle) {
233             assertResultsFromExecuteUpdate(sqle);
234         }
235         ps.close();
236     }
237
238     /**
239      * Tests that <code>CallableStatement.executeQuery()</code> fails
240      * when no result sets are returned.
241      * @exception SQLException if a database error occurs
242      */
243     public void testExecuteQueryWithNoDynamicResultSets_callable()
244         throws SQLException
245     {
246         CallableStatement cs =
247             getConnection().prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
248         cs.setInt(1, 0);
249         try {
250             cs.executeQuery();
251             fail("executeQuery() didn't fail.");
252         } catch (SQLException sqle) {
253             assertNoResultSetFromExecuteQuery(sqle);
254         }
255     }
256
257     /**
258      * Tests that <code>CallableStatement.executeQuery()</code>
259      * succeeds when one result set is returned from a stored
260      * procedure.
261      * @exception SQLException if a database error occurs
262      */
263     public void testExecuteQueryWithOneDynamicResultSet_callable()
264         throws SQLException
265     {
266         CallableStatement cs =
267             getConnection().prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
268         cs.setInt(1, 1);
269         ResultSet rs = cs.executeQuery();
270         assertNotNull("executeQuery() returned null.", rs);
271         assertTrue("Result set has no data.", rs.next());
272         rs.close();
273         cs.close();
274     }
275
276     /**
277      * Tests that <code>CallableStatement.executeQuery()</code> fails
278      * when multiple result sets are returned.
279      * @exception SQLException if a database error occurs
280      */
281     public void testExecuteQueryWithMoreThanOneDynamicResultSet_callable()
282         throws SQLException
283     {
284         CallableStatement cs =
285             getConnection().prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
286         cs.setInt(1, 2);
287         try {
288             cs.executeQuery();
289             fail("executeQuery() didn't fail.");
290         } catch (SQLException sqle) {
291             assertMultipleResultsFromExecuteQuery(sqle);
292         }
293     }
294
295     /**
296      * Tests that <code>CallableStatement.executeUpdate()</code>
297      * succeeds when no result sets are returned.
298      *
299      * <p>Currently, this test fails with JCC.
300      *
301      * @exception SQLException if a database error occurs
302      */
303     public void xtestExecuteUpdateWithNoDynamicResultSets_callable()
304         throws SQLException
305     {
306         CallableStatement cs =
307             getConnection().prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
308         cs.setInt(1, 0);
309         int count = cs.executeUpdate();
310         assertEquals("Wrong update count.", 0, count);
311         cs.close();
312     }
313
314     /**
315      * Tests that <code>CallableStatement.executeUpdate()</code> fails
316      * when a result set is returned from a stored procedure.
317      * @exception SQLException if a database error occurs
318      */
319     public void testExecuteUpdateWithOneDynamicResultSet_callable()
320         throws SQLException
321     {
322         CallableStatement cs =
323             getConnection().prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
324         cs.setInt(1, 1);
325         try {
326             cs.executeUpdate();
327             fail("executeUpdate() didn't fail.");
328         } catch (SQLException sqle) {
329             assertResultsFromExecuteUpdate(sqle);
330         }
331         cs.close();
332     }
333
334     /**
335      * Tests that the effects of executing a stored procedure with
336      * <code>executeQuery()</code> are correctly rolled back when
337      * <code>Connection.rollback()</code> is called.
338      * @exception SQLException if a database error occurs
339      */
340     public void testRollbackStoredProcWithExecuteQuery() throws SQLException {
341
342         Statement stmt = createStatement();
343         ResultSet rs = stmt.executeQuery("CALL PROC_WITH_SIDE_EFFECTS(1)");
344         rs.close();
345         stmt.getConnection().rollback();
346         ResultSet tableRs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
347         // table should be empty after rollback
348 assertFalse("Side effects from stored procedure not rolled back.",
349                     tableRs.next());
350         tableRs.close();
351         stmt.close();
352     }
353
354     /**
355      * Tests that the effects of executing a stored procedure with
356      * <code>executeUpdate()</code> are correctly rolled back when
357      * <code>Connection.rollback()</code> is called.
358      * @exception SQLException if a database error occurs
359      */
360     public void testRollbackStoredProcWithExecuteUpdate() throws SQLException {
361         Statement stmt = createStatement();
362         stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(0)");
363         stmt.getConnection().rollback();
364         ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
365         // table should be empty after rollback
366 assertFalse("Side effects from stored procedure not rolled back.",
367                     rs.next());
368         rs.close();
369         stmt.close();
370     }
371
372     /**
373      * Tests that the effects of executing a stored procedure with
374      * <code>executeQuery()</code> are correctly rolled back when the
375      * query fails because the number of returned result sets is zero.
376      *
377      * <p> This test case fails with JCC.
378      *
379      * @exception SQLException if a database error occurs
380      */
381     public void xtestRollbackStoredProcWhenExecuteQueryReturnsNothing()
382         throws SQLException
383     {
384         Connection conn = getConnection();
385         conn.setAutoCommit(true);
386         Statement stmt = conn.createStatement();
387         try {
388             ResultSet rs = stmt.executeQuery("CALL PROC_WITH_SIDE_EFFECTS(0)");
389             fail("executeQuery() didn't fail.");
390         } catch (SQLException sqle) {
391             assertNoResultSetFromExecuteQuery(sqle);
392         }
393         ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
394         assertFalse("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     {
413         Connection conn = getConnection();
414         conn.setAutoCommit(true);
415         Statement stmt = conn.createStatement();
416         try {
417             ResultSet rs = stmt.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("Side effects from stored procedure not rolled back.",
424                     rs.next());
425         rs.close();
426         stmt.close();
427     }
428
429     /**
430      * Tests that the effects of executing a stored procedure with
431      * <code>executeUpdate()</code> are correctly rolled back when the
432      * query fails because the stored procedure returned a result set.
433      *
434      * <p> This test case fails with JCC.
435      *
436      * @exception SQLException if a database error occurs
437      */
438     public void xtestRollbackStoredProcWhenExecuteUpdateReturnsResults()
439         throws SQLException
440     {
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("Side effects from stored procedure not rolled back.",
452                     rs.next());
453         rs.close();
454         stmt.close();
455     }
456
457     /**
458      * Tests that the effects of executing a stored procedure with
459      * <code>executeQuery()</code> are correctly rolled back when the
460      * query fails because the number of returned result sets is zero.
461      *
462      * <p> This test case fails with JCC.
463      *
464      * @exception SQLException if a database error occurs
465      */
466     public void xtestRollbackStoredProcWhenExecuteQueryReturnsNothing_prepared()
467         throws SQLException
468     {
469         Connection conn = getConnection();
470         conn.setAutoCommit(true);
471         PreparedStatement ps =
472             conn.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("Side effects from stored procedure not rolled back.",
483                     rs.next());
484         rs.close();
485         ps.close();
486         stmt.close();
487     }
488
489     /**
490      * Tests that the effects of executing a stored procedure with
491      * <code>executeQuery()</code> are correctly rolled back when the
492      * query fails because the number of returned result sets is more
493      * than one.
494      *
495      * <p> This test case fails with JCC.
496      *
497      * @exception SQLException if a database error occurs
498      */
499     public void xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch_prepared()
500         throws SQLException
501     {
502         Connection conn = getConnection();
503         conn.setAutoCommit(true);
504         PreparedStatement ps =
505             conn.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("Side effects from stored procedure not rolled back.",
516                     rs.next());
517         rs.close();
518         ps.close();
519         stmt.close();
520     }
521
522     /**
523      * Tests that the effects of executing a stored procedure with
524      * <code>executeUpdate()</code> are correctly rolled back when the
525      * query fails because the stored procedure returned a result set.
526      *
527      * <p> This test case fails with JCC.
528      *
529      * @exception SQLException if a database error occurs
530      */
531     public void
532         xtestRollbackStoredProcWhenExecuteUpdateReturnsResults_prepared()
533         throws SQLException
534     {
535         Connection conn = getConnection();
536         conn.setAutoCommit(true);
537         PreparedStatement ps =
538             conn.prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
539         ps.setInt(1, 1);
540         try {
541             ps.executeUpdate();
542             fail("executeUpdate() didn't fail.");
543         } catch (SQLException sqle) {
544             assertResultsFromExecuteUpdate(sqle);
545         }
546         Statement stmt = conn.createStatement();
547         ResultSet rs = stmt.executeQuery("SELECT * FROM SIMPLE_TABLE");
548         assertFalse("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     {
563         Statement stmt = createStatement();
564         try {
565             ResultSet rs = stmt.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     {
581         Statement stmt = createStatement();
582         stmt.executeUpdate("CALL RETRIEVE_CLOSED_RESULT()");
583         stmt.close();
584     }
585
586     /**
587      * Tests that dynamic result sets from other connections are
588      * ignored when calling <code>executeQuery</code>.
589      * @exception SQLException if a database error occurs
590      */
591     public void testDynamicResultSetsFromOtherConnectionWithExecuteQuery()
592         throws SQLException
593     {
594         PreparedStatement ps =
595             getConnection().prepareStatement("CALL RETRIEVE_EXTERNAL_RESULT(?,?,?)");
596         
597         ps.setString(1, getTestConfiguration().getDatabaseName());
598         ps.setString(2, getTestConfiguration().getUserName());
599         ps.setString(3, getTestConfiguration().getUserPassword());
600         try {
601             ps.executeQuery();
602             fail("executeQuery() didn't fail.");
603         } catch (SQLException sqle) {
604             assertNoResultSetFromExecuteQuery(sqle);
605         }
606         ps.close();
607     }
608
609     /**
610      * Tests that dynamic result sets from other connections are
611      * ignored when calling <code>executeUpdate</code>.
612      * @exception SQLException if a database error occurs
613      */
614     public void testDynamicResultSetsFromOtherConnectionWithExecuteUpdate()
615         throws SQLException
616     {
617         PreparedStatement ps =
618             getConnection().prepareStatement("CALL RETRIEVE_EXTERNAL_RESULT(?,?,?)");
619         
620         ps.setString(1, getTestConfiguration().getDatabaseName());
621         ps.setString(2, getTestConfiguration().getUserName());
622         ps.setString(3, getTestConfiguration().getUserPassword());
623         
624         ps.executeUpdate();
625         ps.close();
626     }
627
628     // UTILITY METHODS
629
630     /**
631      * Raises an exception if the exception is not caused by
632      * <code>executeQuery()</code> returning no result set.
633      *
634      * @param sqle a <code>SQLException</code> value
635      */
636     private void assertNoResultSetFromExecuteQuery(SQLException sqle) {
637         if (usingDerbyNet()) {
638             assertNull("Unexpected SQL state.", sqle.getSQLState());
639         } else {
640             assertSQLState("Unexpected SQL state.", "X0Y78", sqle);
641         }
642     }
643
644     /**
645      * Raises an exception if the exception is not caused by
646      * <code>executeQuery()</code> returning multiple result sets.
647      *
648      * @param sqle a <code>SQLException</code> value
649      */
650     private void assertMultipleResultsFromExecuteQuery(SQLException sqle)
651     {
652         if (usingDerbyNet()) {
653             assertNull("Unexpected SQL state.", sqle.getSQLState());
654         } else {
655             assertSQLState("Unexpected SQL state.", "X0Y78", sqle);
656         }
657     }
658
659     /**
660      * Raises an exception if the exception is not caused by
661      * <code>executeUpdate()</code> returning result sets.
662      *
663      * @param sqle a <code>SQLException</code> value
664      */
665     private void assertResultsFromExecuteUpdate(SQLException sqle) {
666         if (usingDerbyNet()) {
667             assertNull("Unexpected SQL state.", sqle.getSQLState());
668         } else {
669             assertSQLState("Unexpected SQL state.", "X0Y79", sqle);
670         }
671
672     }
673
674     // SETUP
675
676     /**
677      * Creates the test suite and wraps it in a <code>TestSetup</code>
678      * instance which sets up and tears down the test environment.
679      * @return test suite
680      */
681     public static Test suite() {
682         TestSuite suite = new TestSuite();
683         
684         // Need JDBC 2 DriverManager to run these tests
685 if (JDBC.vmSupportsJDBC2()) {
686         
687         suite.addTestSuite(ProcedureTest.class);
688         if (!usingDerbyNet()) {
689             suite.addTest
690                 (new ProcedureTest
691                  ("xtestExecuteUpdateWithNoDynamicResultSets"));
692             suite.addTest
693                 (new ProcedureTest
694                  ("xtestExecuteUpdateWithNoDynamicResultSets_prepared"));
695             suite.addTest
696                 (new ProcedureTest
697                  ("xtestExecuteUpdateWithOneDynamicResultSet_prepared"));
698             suite.addTest
699                 (new ProcedureTest
700                  ("xtestExecuteUpdateWithNoDynamicResultSets_callable"));
701             suite.addTest
702                 (new ProcedureTest
703                  ("xtestRollbackStoredProcWhenExecuteQueryReturnsNothing"));
704             suite.addTest
705                 (new ProcedureTest
706                  ("xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch"));
707             suite.addTest
708                 (new ProcedureTest
709                  ("xtestRollbackStoredProcWhenExecuteUpdateReturnsResults"));
710             suite.addTest
711                 (new ProcedureTest
712                  ("xtestRollbackStoredProcWhenExecuteQueryReturnsNothing" +
713                   "_prepared"));
714             suite.addTest
715                 (new ProcedureTest
716                  ("xtestRollbackStoredProcWhenExecuteQueryReturnsTooMuch" +
717                   "_prepared"));
718             suite.addTest
719                 (new ProcedureTest
720                  ("xtestRollbackStoredProcWhenExecuteUpdateReturnsResults" +
721                   "_prepared"));
722         }
723         }
724         Test test = new BaseJDBCTestSetup(suite) {
725             /**
726              * Creates the tables and the stored procedures used in the test
727              * cases.
728              * @exception SQLException if a database error occurs
729              */
730             protected void setUp() throws SQLException {
731                 Connection c = getConnection();
732                 c.setAutoCommit(false);
733                 Statement s = c.createStatement();
734                 for (int i = 0; i < PROCEDURES.length; i++) {
735                     s.execute(PROCEDURES[i][1]);
736                 }
737                 for (int i = 0; i < TABLES.length; i++) {
738                     s.execute(TABLES[i][1]);
739                 }
740                 s.close();
741                 c.commit();
742                 c.close();
743             }
744             /**
745              * Drops the stored procedures used in the tests.
746              * @exception SQLException if a database error occurs
747              */
748             protected void tearDown() throws Exception {
749                 Connection c = getConnection();
750                 c.setAutoCommit(false);
751                 Statement s = c.createStatement();
752                 for (int i = 0; i < PROCEDURES.length; i++) {
753                     s.execute("DROP PROCEDURE " + PROCEDURES[i][0]);
754                 }
755                 for (int i = 0; i < TABLES.length; i++) {
756                     s.execute("DROP TABLE " + TABLES[i][0]);
757                 }
758                 s.close();
759                 c.commit();
760                 c.close();
761                 
762                 super.tearDown();
763             }
764         };
765         
766         return new CleanDatabaseTestSetup(test);
767     }
768
769     /**
770      * Sets up the connection for a test case and clears all tables
771      * used in the test cases.
772      * @exception SQLException if a database error occurs
773      */
774     public void setUp() throws SQLException {
775         Connection conn = getConnection();
776         conn.setAutoCommit(false);
777         Statement s = conn.createStatement();
778         for (int i = 0; i < TABLES.length; i++) {
779             s.execute("DELETE FROM " + TABLES[i][0]);
780         }
781         s.close();
782         conn.commit();
783     }
784
785     /**
786      * Procedures that should be created before the tests are run and
787      * dropped when the tests have finished. First element in each row
788      * is the name of the procedure, second element is SQL which
789      * creates it.
790      */
791     private static final String [][] PROCEDURES = {
792         { "RETRIEVE_DYNAMIC_RESULTS",
793           "CREATE PROCEDURE RETRIEVE_DYNAMIC_RESULTS(number INT) " +
794           "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
795           ProcedureTest.class.getName() + ".retrieveDynamicResults' " +
796           "DYNAMIC RESULT SETS 4"
797         },
798         { "RETRIEVE_CLOSED_RESULT",
799           "CREATE PROCEDURE RETRIEVE_CLOSED_RESULT() LANGUAGE JAVA " +
800           "PARAMETER STYLE JAVA EXTERNAL NAME '" +
801           ProcedureTest.class.getName() + ".retrieveClosedResult' " +
802           "DYNAMIC RESULT SETS 1"
803         },
804         { "RETRIEVE_EXTERNAL_RESULT",
805           "CREATE PROCEDURE RETRIEVE_EXTERNAL_RESULT(" +
806           "DBNAME VARCHAR(128), DBUSER VARCHAR(128), DBPWD VARCHAR(128)) LANGUAGE JAVA " +
807           "PARAMETER STYLE JAVA EXTERNAL NAME '" +
808           ProcedureTest.class.getName() + ".retrieveExternalResult' " +
809           "DYNAMIC RESULT SETS 1"
810         },
811         { "PROC_WITH_SIDE_EFFECTS",
812           "CREATE PROCEDURE PROC_WITH_SIDE_EFFECTS(ret INT) LANGUAGE JAVA " +
813           "PARAMETER STYLE JAVA EXTERNAL NAME '" +
814           ProcedureTest.class.getName() + ".procWithSideEffects' " +
815           "DYNAMIC RESULT SETS 2"
816         },
817     };
818
819     /**
820      * Tables that should be created before the tests are run and
821      * dropped when the tests have finished. The tables will be
822      * cleared before each test case is run. First element in each row
823      * is the name of the table, second element is the SQL text which
824      * creates it.
825      */
826     private static final String [][] TABLES = {
827         // SIMPLE_TABLE is used by PROC_WITH_SIDE_EFFECTS
828 { "SIMPLE_TABLE", "CREATE TABLE SIMPLE_TABLE (id INT)" },
829     };
830
831     // PROCEDURES
832
833     /**
834      * Stored procedure which returns 0, 1, 2, 3 or 4 <code>ResultSet</code>s.
835      *
836      * @param number the number of <code>ResultSet</code>s to return
837      * @param rs1 first <code>ResultSet</code>
838      * @param rs2 second <code>ResultSet</code>
839      * @param rs3 third <code>ResultSet</code>
840      * @param rs4 fourth <code>ResultSet</code>
841      * @exception SQLException if a database error occurs
842      */
843     public static void retrieveDynamicResults(int number,
844                                               ResultSet [] rs1,
845                                               ResultSet [] rs2,
846                                               ResultSet [] rs3,
847                                               ResultSet [] rs4)
848         throws SQLException
849     {
850         Connection c = DriverManager.getConnection("jdbc:default:connection");
851         if (number > 0) {
852             rs1[0] = c.createStatement().executeQuery("VALUES(1)");
853         }
854         if (number > 1) {
855             rs2[0] = c.createStatement().executeQuery("VALUES(1)");
856         }
857         if (number > 2) {
858             rs3[0] = c.createStatement().executeQuery("VALUES(1)");
859         }
860         if (number > 3) {
861             rs4[0] = c.createStatement().executeQuery("VALUES(1)");
862         }
863         c.close();
864     }
865
866     /**
867      * Stored procedure which produces a closed result set.
868      *
869      * @param closed holder for the closed result set
870      * @exception SQLException if a database error occurs
871      */
872     public static void retrieveClosedResult(ResultSet [] closed)
873         throws SQLException
874     {
875         Connection c = DriverManager.getConnection("jdbc:default:connection");
876         closed[0] = c.createStatement().executeQuery("VALUES(1)");
877         closed[0].close();
878         c.close();
879     }
880
881     /**
882      * Stored procedure which produces a result set in another
883      * connection.
884      *
885      * @param external result set from another connection
886      * @exception SQLException if a database error occurs
887      */
888     public static void retrieveExternalResult(String dbName,
889             String user, String password, ResultSet [] external)
890         throws SQLException
891     {
892         // Use a server-side connection to the same database.
893 String url = "jdbc:derby:" + dbName;
894         
895         Connection conn = DriverManager.getConnection(url, user, password);
896         
897         external[0] =
898             conn.createStatement().executeQuery("VALUES(1)");
899     }
900
901     /**
902      * Stored procedure which inserts a row into SIMPLE_TABLE and
903      * optionally returns result sets.
904      *
905      * @param returnResults if one, return one result set; if greater
906      * than one, return two result sets; otherwise, return no result
907      * set
908      * @param rs1 first result set to return
909      * @param rs2 second result set to return
910      * @exception SQLException if a database error occurs
911      */
912     public static void procWithSideEffects(int returnResults,
913                                            ResultSet [] rs1,
914                                            ResultSet [] rs2)
915         throws SQLException
916     {
917         Connection c = DriverManager.getConnection("jdbc:default:connection");
918         Statement stmt = c.createStatement();
919         stmt.executeUpdate("INSERT INTO SIMPLE_TABLE VALUES (42)");
920         if (returnResults > 0) {
921             rs1[0] = c.createStatement().executeQuery("VALUES(1)");
922         }
923         if (returnResults > 1) {
924             rs2[0] = c.createStatement().executeQuery("VALUES(1)");
925         }
926         c.close();
927     }
928 }
929

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags