KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > userDefMethods


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.userDefMethods
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23 import java.sql.*;
24 import java.util.Vector JavaDoc;
25
26
27 //This class defines miscelanious test java methods to be called from sql.
28 //These are not generic methods, typically used by a particular tests.
29 public class userDefMethods
30 {
31  
32     //method that does a delete of rows on table t1 based on values from triggers.
33 public static void deleteFromATable() throws SQLException
34     {
35         Connection con = DriverManager.getConnection("jdbc:default:connection");
36         PreparedStatement statement = null;
37         String JavaDoc delStr = null;
38         Statement s = con.createStatement();
39         ResultSet rs = s.executeQuery("SELECT c1 from new org.apache.derby.catalog.TriggerOldTransitionRows() AS EQ");
40         Vector JavaDoc keys = new Vector JavaDoc();
41         while(rs.next()){
42             keys.addElement(new Long JavaDoc(rs.getLong(1)));
43         }
44         rs.close();
45
46         statement =
47         con.prepareStatement("delete from t1 where c1 = ?");
48         for(int i = 0; i < keys.size() ; i++){
49            long key = ((Long JavaDoc)keys.elementAt(i)).longValue();
50            statement.setLong(1, key);
51            statement.executeUpdate();
52         }
53         statement.close();
54     }
55
56
57     public static void deleteFromParent() throws SQLException
58     {
59         Connection con = DriverManager.getConnection("jdbc:default:connection");
60         String JavaDoc sqlstmt;
61         Statement stmt = con.createStatement();
62         sqlstmt = "SELECT a FROM new org.apache.derby.catalog.TriggerOldTransitionRows() AS EQ";
63         ResultSet rs = stmt.executeQuery(sqlstmt);
64         sqlstmt = "delete from parent where a = ? ";
65         PreparedStatement pstmt = con.prepareStatement(sqlstmt);
66         while(rs.next()){
67             long value = rs.getLong(1);
68             if(value == 1 || value == 3)
69                 value = 4;
70             else
71                 value = 5;
72             pstmt.setLong(1,value);
73             pstmt.executeUpdate();
74         }
75         rs.close();
76         stmt.close();
77         pstmt.close();
78     }
79
80     /* ****
81      * Derby-388: When a set of inserts & updates is performed on a table
82      * and each update fires a trigger that in turn performs other updates,
83      * Derby will sometimes try to recompile the trigger in the middle
84      * of the update process and will throw an NPE when doing so.
85      */
86     public static void derby388() throws SQLException
87     {
88         System.out.println("Running DERBY-388 Test.");
89         Connection conn = DriverManager.getConnection("jdbc:default:connection");
90         boolean needCommit = !conn.getAutoCommit();
91         Statement s = conn.createStatement();
92
93         // Create our objects.
94 s.execute("CREATE TABLE D388_T1 (ID INT)");
95         s.execute("CREATE TABLE D388_T2 (ID_2 INT)");
96         s.execute(
97             "CREATE TRIGGER D388_TRIG1 AFTER UPDATE OF ID ON D388_T1" +
98             " REFERENCING NEW AS N_ROW FOR EACH ROW MODE DB2SQL" +
99             " UPDATE D388_T2" +
100             " SET ID_2 = " +
101             " CASE WHEN (N_ROW.ID <= 0) THEN N_ROW.ID" +
102             " ELSE 6 END " +
103             " WHERE N_ROW.ID < ID_2"
104         );
105
106         if (needCommit)
107             conn.commit();
108
109         // Statement to insert into D388_T1.
110 PreparedStatement ps1 = conn.prepareStatement(
111             "INSERT INTO D388_T1 VALUES (?)");
112
113         // Statement to insert into D388_T2.
114 PreparedStatement ps2 = conn.prepareStatement(
115             "INSERT INTO D388_T2(ID_2) VALUES (?)");
116
117         // Statement that will cause the trigger to fire.
118 Statement st = conn.createStatement();
119         for (int i = 0; i < 20; i++) {
120
121             for (int id = 0; id < 10; id++) {
122
123                 ps2.setInt(1, id);
124                 ps2.executeUpdate();
125                 ps1.setInt(1, 2*id);
126                 ps1.executeUpdate();
127
128                 if (needCommit)
129                     conn.commit();
130
131             }
132
133             // Execute an update, which will fire the trigger.
134 // Note that having the update here is important
135 // for the reproduction. If we try to remove the
136 // outer loop and just insert lots of rows followed
137 // by a single UPDATE, the problem won't reproduce.
138 st.execute("UPDATE D388_T1 SET ID=5");
139             if (needCommit)
140                 conn.commit();
141                 
142         }
143
144         // Clean up.
145 s.execute("DROP TABLE D388_T1");
146         s.execute("DROP TABLE D388_T2");
147
148         if (needCommit)
149             conn.commit();
150                 
151         st.close();
152         ps1.close();
153         ps2.close();
154
155         System.out.println("DERBY-388 Test Passed.");
156     }
157
158 }
159
Popular Tags