001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.triggerStream
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.Connection;
025: import java.sql.ResultSet;
026: import java.sql.Statement;
027: import java.sql.SQLException;
028:
029: import org.apache.derby.tools.JDBCDisplayUtil;
030: import org.apache.derby.tools.ij;
031: import org.apache.derbyTesting.functionTests.util.StreamUtil;
032: import org.apache.derbyTesting.functionTests.util.TestUtil;
033:
034: /*
035: Small trigger stream test. Make sure we can read streams ok from the context
036: of a row or statement trigger.
037: */
038: public class triggerStream {
039:
040: public static void main(String[] args) {
041: try {
042: ij.getPropertyArg(args);
043: Connection conn = ij.startJBMS();
044:
045: //create tables and functions to be used in triggers
046: createTablesAndFunctions(conn);
047:
048: //create triggers for Ascii test
049: createTriggersWithAsciiStream(conn);
050: //test triggers for Ascii stream
051: testTriggersWithAsciiStream(conn);
052:
053: //create triggers for binary test
054: createTriggersWithBinaryStream(conn);
055: //test triggers for binary stream
056: testTriggersWithBinaryStream(conn);
057:
058: conn.close();
059: } catch (SQLException e) {
060: TestUtil.dumpSQLExceptions(e);
061: } catch (Throwable e) {
062: System.out.println("FAIL -- unexpected exception:"
063: + e.toString());
064: }
065: }
066:
067: private static void createTablesAndFunctions(Connection conn)
068: throws SQLException {
069:
070: System.out
071: .println("Start creating tables and functions to be used in triggers ...");
072:
073: Statement stmt = conn.createStatement();
074:
075: stmt
076: .execute("create table x1 (x int, c1 long varchar, y int, slen int)");
077: stmt
078: .execute("create table x2 (x int, c1 long varchar for bit data, y int, slen int)");
079:
080: // getAsciiColumn() method reads in the stream and verifies each byte and prints
081: // out the length of the column
082: stmt
083: .execute("create function getAsciiColumn(whichRS int,colNumber int,value varchar(128))"
084: + " returns int PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME "
085: + "'org.apache.derbyTesting.functionTests.util.StreamUtil.getAsciiColumn'");
086:
087: // getBinaryColumn() method reads in the stream and verifies each byte and prints
088: // out the length of the column
089: stmt
090: .execute("create function getBinaryColumn( whichRS int, colNumber int, value varchar(128))"
091: + " returns int PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME "
092: + "'org.apache.derbyTesting.functionTests.util.StreamUtil.getBinaryColumn'");
093:
094: stmt.close();
095:
096: System.out
097: .println("... done creating tables and functions to be used in triggers.");
098: }
099:
100: private static void createTriggersWithAsciiStream(Connection conn)
101: throws SQLException {
102:
103: System.out
104: .println("Start creating triggers for Ascii stream tests ...");
105:
106: Statement stmt = conn.createStatement();
107:
108: stmt
109: .execute("create trigger t11 NO CASCADE before update of x,y on x1 "
110: + "for each statement mode db2sql values getAsciiColumn( 0, 2, 'a')");
111: stmt
112: .execute("create trigger t12 after update of x,y on x1 for each row"
113: + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
114: stmt
115: .execute("create trigger t13 after insert on x1 for each statement"
116: + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
117: stmt
118: .execute("create trigger t14 NO CASCADE before insert on x1 for each row"
119: + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
120: stmt
121: .execute("create trigger t15 NO CASCADE before delete on x1 "
122: + "for each statement mode db2sql values getAsciiColumn( 0, 2, 'a')");
123: stmt
124: .execute("create trigger t16 after delete on x1 for each row "
125: + "mode db2sql values getAsciiColumn( 0, 2, 'a')");
126:
127: stmt.close();
128:
129: System.out
130: .println("... done creating triggers for Ascii stream tests.");
131: }
132:
133: private static void testTriggersWithAsciiStream(Connection conn)
134: throws Throwable {
135:
136: int count = 1;
137: insertAsciiColumn(count++, conn,
138: "insert into x1 values (1, ?, 1, ?)", 1, "a", 1);
139: insertAsciiColumn(count++, conn,
140: "insert into x1 values (2, ?, 2, ?)", 1, "a", 10);
141: insertAsciiColumn(count++, conn,
142: "insert into x1 values (3, ?, 3, ?)", 1, "a", 100);
143: insertAsciiColumn(count++, conn,
144: "insert into x1 values (4, ?, 4, ?)", 1, "a", 1000);
145: insertAsciiColumn(count++, conn,
146: "insert into x1 values (5, ?, 5, ?)", 1, "a", 5000);
147: insertAsciiColumn(count++, conn,
148: "insert into x1 values (6, ?, 6, ?)", 1, "a", 10000);
149: insertAsciiColumn(count++, conn,
150: "insert into x1 values (7, ?, 7, ?)", 1, "a", 16500);
151: insertAsciiColumn(count++, conn,
152: "insert into x1 values (8, ?, 8, ?)", 1, "a", 32500);
153: insertAsciiColumn(count++, conn,
154: "insert into x1 values (9, ?, 9, ?)", 1, "a", 0);
155: insertAsciiColumn(count++, conn,
156: "insert into x1 values (10, ?, 10, ?)", 1, "a", 666);
157:
158: executeStatement(conn, "update x1 set x = x+1");
159: executeStatement(conn, "update x1 set x = null");
160: executeStatement(conn, "insert into x1 select * from x1");
161: executeStatement(conn, "delete from x1");
162: }
163:
164: private static void createTriggersWithBinaryStream(Connection conn)
165: throws SQLException {
166:
167: System.out
168: .println("Start creating triggers for binary stream tests ...");
169:
170: Statement stmt = conn.createStatement();
171: stmt
172: .execute("create trigger t21 NO CASCADE before update of x,y on x2 "
173: + "for each statement mode db2sql values getBinaryColumn( 0, 2, 'a')");
174: stmt
175: .execute("create trigger t22 after update of x,y on x2 for each row"
176: + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
177: stmt
178: .execute("create trigger t23 after insert on x2 for each statement"
179: + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
180: stmt
181: .execute("create trigger t24 NO CASCADE before insert on x2 for each row"
182: + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
183: stmt
184: .execute("create trigger t25 NO CASCADE before delete on x2 for each statement"
185: + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
186: stmt
187: .execute("create trigger t26 after delete on x2 for each row mode db2sql"
188: + " values getBinaryColumn( 0, 2, 'a')");
189: stmt.close();
190:
191: System.out
192: .println("... done creating triggers for binary stream tests.");
193: }
194:
195: private static void testTriggersWithBinaryStream(Connection conn)
196: throws Throwable {
197:
198: int count = 1;
199: insertBinaryColumn(count++, conn,
200: "insert into x2 values (1, ?, 1, ?)", 1, "a", 1);
201: insertBinaryColumn(count++, conn,
202: "insert into x2 values (2, ?, 2, ?)", 1, "a", 10);
203: insertBinaryColumn(count++, conn,
204: "insert into x2 values (3, ?, 3, ?)", 1, "a", 100);
205: insertBinaryColumn(count++, conn,
206: "insert into x2 values (4, ?, 4, ?)", 1, "a", 1000);
207: insertBinaryColumn(count++, conn,
208: "insert into x2 values (5, ?, 5, ?)", 1, "a", 10000);
209: insertBinaryColumn(count++, conn,
210: "insert into x2 values (6, ?, 6, ?)", 1, "a", 32700);
211: insertBinaryColumn(count++, conn,
212: "insert into x2 values (7, ?, 7, ?)", 1, "a", 32699);
213: insertBinaryColumn(count++, conn,
214: "insert into x2 values (8, ?, 8, ?)", 1, "a", 16384);
215: insertBinaryColumn(count++, conn,
216: "insert into x2 values (9, ?, 9, ?)", 1, "a", 16383);
217: insertBinaryColumn(count++, conn,
218: "insert into x2 values (10, ?, 10, ?)", 1, "a", 0);
219: insertBinaryColumn(count++, conn,
220: "insert into x2 values (11, ?, 11, ?)", 1, "a", 666);
221:
222: executeStatement(conn,
223: "select x, length(c1) from x2 order by 1");
224: executeStatement(conn, "update x2 set x = x+1");
225: executeStatement(conn,
226: "select x, length(c1) from x2 order by 1");
227: executeStatement(conn, "update x2 set x = null");
228: executeStatement(conn,
229: "select x, length(c1) from x2 order by 2");
230: executeStatement(conn, "insert into x2 select * from x2");
231: executeStatement(conn,
232: "select x, length(c1) from x2 order by 2");
233: executeStatement(conn, "delete from x2");
234:
235: }
236:
237: private static void executeStatement(Connection conn, String str)
238: throws SQLException {
239: System.out.println("#### Executing \"" + str + "\"");
240: Statement stmt = conn.createStatement();
241:
242: //Display results for select statements
243: if (str.startsWith("select")) {
244: ResultSet rs = stmt.executeQuery(str);
245: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
246: rs.close();
247: } else
248: stmt.execute(str);
249:
250: stmt.close();
251: }
252:
253: private static void insertAsciiColumn(int count, Connection conn,
254: String stmtText, int colNumber, String value, int length)
255: throws Throwable {
256: System.out.println("Call #" + count + " to insertAsciiColumn");
257: StreamUtil.insertAsciiColumn(conn, stmtText, colNumber, value,
258: length);
259: }
260:
261: private static void insertBinaryColumn(int count, Connection conn,
262: String stmtText, int colNumber, String value, int length)
263: throws Throwable {
264: System.out.println("Call #" + count + " to insertBinaryColumn");
265: StreamUtil.insertBinaryColumn(conn, stmtText, colNumber, value,
266: length);
267: }
268:
269: }
|