001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.userDefMethods
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.*;
025: import java.util.Vector;
026:
027: //This class defines miscelanious test java methods to be called from sql.
028: //These are not generic methods, typically used by a particular tests.
029: public class userDefMethods {
030:
031: //method that does a delete of rows on table t1 based on values from triggers.
032: public static void deleteFromATable() throws SQLException {
033: Connection con = DriverManager
034: .getConnection("jdbc:default:connection");
035: PreparedStatement statement = null;
036: String delStr = null;
037: Statement s = con.createStatement();
038: ResultSet rs = s
039: .executeQuery("SELECT c1 from new org.apache.derby.catalog.TriggerOldTransitionRows() AS EQ");
040: Vector keys = new Vector();
041: while (rs.next()) {
042: keys.addElement(new Long(rs.getLong(1)));
043: }
044: rs.close();
045:
046: statement = con
047: .prepareStatement("delete from t1 where c1 = ?");
048: for (int i = 0; i < keys.size(); i++) {
049: long key = ((Long) keys.elementAt(i)).longValue();
050: statement.setLong(1, key);
051: statement.executeUpdate();
052: }
053: statement.close();
054: }
055:
056: public static void deleteFromParent() throws SQLException {
057: Connection con = DriverManager
058: .getConnection("jdbc:default:connection");
059: String sqlstmt;
060: Statement stmt = con.createStatement();
061: sqlstmt = "SELECT a FROM new org.apache.derby.catalog.TriggerOldTransitionRows() AS EQ";
062: ResultSet rs = stmt.executeQuery(sqlstmt);
063: sqlstmt = "delete from parent where a = ? ";
064: PreparedStatement pstmt = con.prepareStatement(sqlstmt);
065: while (rs.next()) {
066: long value = rs.getLong(1);
067: if (value == 1 || value == 3)
068: value = 4;
069: else
070: value = 5;
071: pstmt.setLong(1, value);
072: pstmt.executeUpdate();
073: }
074: rs.close();
075: stmt.close();
076: pstmt.close();
077: }
078:
079: /* ****
080: * Derby-388: When a set of inserts & updates is performed on a table
081: * and each update fires a trigger that in turn performs other updates,
082: * Derby will sometimes try to recompile the trigger in the middle
083: * of the update process and will throw an NPE when doing so.
084: */
085: public static void derby388() throws SQLException {
086: System.out.println("Running DERBY-388 Test.");
087: Connection conn = DriverManager
088: .getConnection("jdbc:default:connection");
089: boolean needCommit = !conn.getAutoCommit();
090: Statement s = conn.createStatement();
091:
092: // Create our objects.
093: s.execute("CREATE TABLE D388_T1 (ID INT)");
094: s.execute("CREATE TABLE D388_T2 (ID_2 INT)");
095: s
096: .execute("CREATE TRIGGER D388_TRIG1 AFTER UPDATE OF ID ON D388_T1"
097: + " REFERENCING NEW AS N_ROW FOR EACH ROW MODE DB2SQL"
098: + " UPDATE D388_T2"
099: + " SET ID_2 = "
100: + " CASE WHEN (N_ROW.ID <= 0) THEN N_ROW.ID"
101: + " ELSE 6 END " + " WHERE N_ROW.ID < ID_2");
102:
103: if (needCommit)
104: conn.commit();
105:
106: // Statement to insert into D388_T1.
107: PreparedStatement ps1 = conn
108: .prepareStatement("INSERT INTO D388_T1 VALUES (?)");
109:
110: // Statement to insert into D388_T2.
111: PreparedStatement ps2 = conn
112: .prepareStatement("INSERT INTO D388_T2(ID_2) VALUES (?)");
113:
114: // Statement that will cause the trigger to fire.
115: Statement st = conn.createStatement();
116: for (int i = 0; i < 20; i++) {
117:
118: for (int id = 0; id < 10; id++) {
119:
120: ps2.setInt(1, id);
121: ps2.executeUpdate();
122: ps1.setInt(1, 2 * id);
123: ps1.executeUpdate();
124:
125: if (needCommit)
126: conn.commit();
127:
128: }
129:
130: // Execute an update, which will fire the trigger.
131: // Note that having the update here is important
132: // for the reproduction. If we try to remove the
133: // outer loop and just insert lots of rows followed
134: // by a single UPDATE, the problem won't reproduce.
135: st.execute("UPDATE D388_T1 SET ID=5");
136: if (needCommit)
137: conn.commit();
138:
139: }
140:
141: // Clean up.
142: s.execute("DROP TABLE D388_T1");
143: s.execute("DROP TABLE D388_T2");
144:
145: if (needCommit)
146: conn.commit();
147:
148: st.close();
149: ps1.close();
150: ps2.close();
151:
152: System.out.println("DERBY-388 Test Passed.");
153: }
154:
155: }
|