|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.Connection  ;
25 import java.sql.ResultSet ;
26 import java.sql.Statement ;
27 import java.sql.SQLException ;
28
29 import org.apache.derby.tools.JDBCDisplayUtil;
30 import org.apache.derby.tools.ij;
31 import org.apache.derbyTesting.functionTests.util.StreamUtil;
32 import org.apache.derbyTesting.functionTests.util.TestUtil;
33
34
38 public class triggerStream {
39
40 public static void main(String [] args) {
41 try{
42 ij.getPropertyArg(args);
43 Connection conn = ij.startJBMS();
44
45 createTablesAndFunctions(conn);
47
48 createTriggersWithAsciiStream(conn);
50 testTriggersWithAsciiStream(conn);
52
53 createTriggersWithBinaryStream(conn);
55 testTriggersWithBinaryStream(conn);
57
58 conn.close();
59 } catch (SQLException e) {
60 TestUtil.dumpSQLExceptions(e);
61 } catch (Throwable e) {
62 System.out.println("FAIL -- unexpected exception:" + e.toString());
63 }
64 }
65
66 private static void createTablesAndFunctions(Connection conn) throws SQLException {
67
68 System.out.println("Start creating tables and functions to be used in triggers ...");
69
70 Statement stmt = conn.createStatement();
71
72 stmt.execute("create table x1 (x int, c1 long varchar, y int, slen int)");
73 stmt.execute("create table x2 (x int, c1 long varchar for bit data, y int, slen int)");
74
75 stmt.execute("create function getAsciiColumn(whichRS int,colNumber int,value varchar(128))"
78 + " returns int PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME "
79 +"'org.apache.derbyTesting.functionTests.util.StreamUtil.getAsciiColumn'");
80
81 stmt.execute("create function getBinaryColumn( whichRS int, colNumber int, value varchar(128))"
84 + " returns int PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME "
85 + "'org.apache.derbyTesting.functionTests.util.StreamUtil.getBinaryColumn'");
86
87 stmt.close();
88
89 System.out.println("... done creating tables and functions to be used in triggers.");
90 }
91
92 private static void createTriggersWithAsciiStream(Connection conn) throws SQLException {
93
94 System.out.println("Start creating triggers for Ascii stream tests ...");
95
96 Statement stmt = conn.createStatement();
97
98 stmt.execute("create trigger t11 NO CASCADE before update of x,y on x1 "
99 + "for each statement mode db2sql values getAsciiColumn( 0, 2, 'a')");
100 stmt.execute("create trigger t12 after update of x,y on x1 for each row"
101 + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
102 stmt.execute("create trigger t13 after insert on x1 for each statement"
103 + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
104 stmt.execute("create trigger t14 NO CASCADE before insert on x1 for each row"
105 + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
106 stmt.execute("create trigger t15 NO CASCADE before delete on x1 "
107 + "for each statement mode db2sql values getAsciiColumn( 0, 2, 'a')");
108 stmt.execute("create trigger t16 after delete on x1 for each row "
109 + "mode db2sql values getAsciiColumn( 0, 2, 'a')");
110
111 stmt.close();
112
113 System.out.println("... done creating triggers for Ascii stream tests.");
114 }
115
116 private static void testTriggersWithAsciiStream(Connection conn) throws Throwable {
117
118 int count = 1;
119 insertAsciiColumn(count++,conn,"insert into x1 values (1, ?, 1, ?)", 1, "a", 1);
120 insertAsciiColumn(count++,conn,"insert into x1 values (2, ?, 2, ?)", 1, "a", 10);
121 insertAsciiColumn(count++,conn,"insert into x1 values (3, ?, 3, ?)", 1, "a", 100);
122 insertAsciiColumn(count++,conn,"insert into x1 values (4, ?, 4, ?)", 1, "a", 1000);
123 insertAsciiColumn(count++,conn,"insert into x1 values (5, ?, 5, ?)", 1, "a", 5000);
124 insertAsciiColumn(count++,conn,"insert into x1 values (6, ?, 6, ?)", 1, "a", 10000);
125 insertAsciiColumn(count++,conn,"insert into x1 values (7, ?, 7, ?)", 1, "a", 16500);
126 insertAsciiColumn(count++,conn,"insert into x1 values (8, ?, 8, ?)", 1, "a", 32500);
127 insertAsciiColumn(count++,conn,"insert into x1 values (9, ?, 9, ?)", 1, "a", 0);
128 insertAsciiColumn(count++,conn,"insert into x1 values (10, ?, 10, ?)", 1, "a", 666);
129
130 executeStatement(conn,"update x1 set x = x+1");
131 executeStatement(conn,"update x1 set x = null");
132 executeStatement(conn,"insert into x1 select * from x1");
133 executeStatement(conn,"delete from x1");
134 }
135
136 private static void createTriggersWithBinaryStream(Connection conn) throws SQLException {
137
138 System.out.println("Start creating triggers for binary stream tests ...");
139
140 Statement stmt = conn.createStatement();
141 stmt.execute("create trigger t21 NO CASCADE before update of x,y on x2 "
142 + "for each statement mode db2sql values getBinaryColumn( 0, 2, 'a')");
143 stmt.execute("create trigger t22 after update of x,y on x2 for each row"
144 + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
145 stmt.execute("create trigger t23 after insert on x2 for each statement"
146 + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
147 stmt.execute("create trigger t24 NO CASCADE before insert on x2 for each row"
148 + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
149 stmt.execute("create trigger t25 NO CASCADE before delete on x2 for each statement"
150 + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
151 stmt.execute("create trigger t26 after delete on x2 for each row mode db2sql"
152 + " values getBinaryColumn( 0, 2, 'a')");
153 stmt.close();
154
155 System.out.println("... done creating triggers for binary stream tests.");
156 }
157
158 private static void testTriggersWithBinaryStream(Connection conn) throws Throwable {
159
160 int count = 1;
161 insertBinaryColumn(count++,conn,"insert into x2 values (1, ?, 1, ?)", 1, "a", 1);
162 insertBinaryColumn(count++,conn,"insert into x2 values (2, ?, 2, ?)", 1, "a", 10);
163 insertBinaryColumn(count++,conn,"insert into x2 values (3, ?, 3, ?)", 1, "a", 100);
164 insertBinaryColumn(count++,conn,"insert into x2 values (4, ?, 4, ?)", 1, "a", 1000);
165 insertBinaryColumn(count++,conn,"insert into x2 values (5, ?, 5, ?)", 1, "a", 10000);
166 insertBinaryColumn(count++,conn,"insert into x2 values (6, ?, 6, ?)", 1, "a", 32700);
167 insertBinaryColumn(count++,conn,"insert into x2 values (7, ?, 7, ?)", 1, "a", 32699);
168 insertBinaryColumn(count++,conn,"insert into x2 values (8, ?, 8, ?)", 1, "a", 16384);
169 insertBinaryColumn(count++,conn,"insert into x2 values (9, ?, 9, ?)", 1, "a", 16383);
170 insertBinaryColumn(count++,conn,"insert into x2 values (10, ?, 10, ?)", 1, "a", 0);
171 insertBinaryColumn(count++,conn,"insert into x2 values (11, ?, 11, ?)", 1, "a", 666);
172
173
174 executeStatement(conn,"select x, length(c1) from x2 order by 1");
175 executeStatement(conn,"update x2 set x = x+1");
176 executeStatement(conn,"select x, length(c1) from x2 order by 1");
177 executeStatement(conn,"update x2 set x = null");
178 executeStatement(conn,"select x, length(c1) from x2 order by 2");
179 executeStatement(conn,"insert into x2 select * from x2");
180 executeStatement(conn,"select x, length(c1) from x2 order by 2");
181 executeStatement(conn,"delete from x2");
182
183 }
184
185 private static void executeStatement(Connection conn, String str) throws SQLException {
186 System.out.println("#### Executing \""+ str + "\"");
187 Statement stmt = conn.createStatement();
188
189 if(str.startsWith("select")) {
191 ResultSet rs = stmt.executeQuery(str);
192 JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
193 rs.close();
194 } else
195 stmt.execute(str);
196
197
198 stmt.close();
199 }
200
201 private static void insertAsciiColumn
202 (
203 int count,
204 Connection conn,
205 String stmtText,
206 int colNumber,
207 String value,
208 int length
209 )
210 throws Throwable
211 {
212 System.out.println("Call #" + count + " to insertAsciiColumn");
213 StreamUtil.insertAsciiColumn(conn, stmtText, colNumber, value, length);
214 }
215
216 private static void insertBinaryColumn
217 (
218 int count,
219 Connection conn,
220 String stmtText,
221 int colNumber,
222 String value,
223 int length
224 )
225 throws Throwable
226 {
227 System.out.println("Call #" + count + " to insertBinaryColumn");
228 StreamUtil.insertBinaryColumn(conn, stmtText, colNumber, value, length);
229 }
230
231 }
232 |
Popular Tags |
Java API By Example, From Geeks To Geeks.