KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  *
3  * Derby - Class UpdatableResultSetTest
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,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
17  * either express or implied. See the License for the specific
18  * language governing permissions and limitations under the License.
19  */
20 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
21 import org.apache.derbyTesting.functionTests.util.TestUtil;
22 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
23 import org.apache.derbyTesting.junit.JDBC;
24
25 import junit.framework.*;
26 import java.sql.*;
27
28 /**
29  * Tests updatable result sets.
30  *
31  * DERBY-1767 - Test that the deleteRow, insertRow and updateRow methods
32  * with column/table/schema/cursor names containing quotes.
33  *
34  */
35 public class UpdatableResultSetTest extends BaseJDBCTestCase {
36     
37     /** Creates a new instance of UpdatableResultSetTest */
38     public UpdatableResultSetTest(String JavaDoc name) {
39         super(name);
40     }
41
42     private Connection conn = null;
43     
44     protected void setUp() throws SQLException {
45         conn = getConnection();
46         conn.setAutoCommit(false);
47         Statement stmt = conn.createStatement();
48         
49         // Quoted table
50 stmt.executeUpdate("create table \"my \"\"quoted\"\" table\" (x int)");
51         stmt.executeUpdate("insert into \"my \"\"quoted\"\" table\" (x) " +
52                 "values (1), (2), (3)");
53         
54         // Quoted columns
55 stmt.executeUpdate("create table \"my quoted columns\" " +
56                 "(\"my \"\"quoted\"\" column\" int)");
57         stmt.executeUpdate("insert into \"my quoted columns\" " +
58                 "values (1), (2), (3) ");
59         
60         // Quoted schema
61 stmt.executeUpdate("create table \"my \"\"quoted\"\" schema\"." +
62                 "\"my quoted schema\" (x int)");
63         stmt.executeUpdate("insert into \"my \"\"quoted\"\" schema\"." +
64                 "\"my quoted schema\" values (1), (2), (3) ");
65         
66         // No quotes, use with quoted cursor
67 stmt.executeUpdate("create table \"my table\" (x int)");
68         stmt.executeUpdate("insert into \"my table\" values (1), (2), (3) ");
69         
70         
71         
72         stmt.close();
73     }
74
75     protected void tearDown() throws Exception JavaDoc {
76         conn.rollback();
77         conn.close();
78         super.tearDown();
79         conn = null;
80     }
81     
82     /** Create a test suite with all tests in this class. */
83     public static Test suite() {
84         TestSuite ts = new TestSuite();
85
86         // Test will fail with JCC.
87 if (usingDerbyNet()) {
88             return ts;
89         }
90
91         ts.addTestSuite(UpdatableResultSetTest.class);
92         return ts;
93     }
94     
95     /**
96      * Tests insertRow with table name containing quotes
97      */
98     public void testInsertRowOnQuotedTable() throws SQLException {
99         ResultSet rs = null;
100         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
101                 ResultSet.CONCUR_UPDATABLE);
102         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\"");
103         rs.next();
104         rs.moveToInsertRow();
105         rs.updateInt(1, 4);
106         rs.insertRow();
107         rs.moveToCurrentRow();
108         rs.close();
109         
110         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\" " +
111                 "order by x");
112         for (int i=1; i<=4; i++) {
113             assertTrue("there is a row", rs.next());
114             assertEquals("row contains correct value", i, rs.getInt(1));
115         }
116         rs.close();
117         stmt.close();
118     }
119
120     /**
121      * Tests updateRow with table name containing quotes
122      */
123     public void testUpdateRowOnQuotedTable() throws SQLException {
124         ResultSet rs = null;
125         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
126                 ResultSet.CONCUR_UPDATABLE);
127         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\"");
128         rs.next();
129         rs.updateInt(1, 4);
130         rs.updateRow();
131         rs.close();
132         
133         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\" " +
134                 "order by x");
135         for (int i=2; i<=4; i++) {
136             assertTrue("there is a row", rs.next());
137             assertEquals("row contains correct value", i, rs.getInt(1));
138         }
139         rs.close();
140         stmt.close();
141     }
142
143     /**
144      * Tests deleteRow with table name containing quotes
145      */
146     public void testDeleteRowOnQuotedTable() throws SQLException {
147         ResultSet rs = null;
148         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
149                 ResultSet.CONCUR_UPDATABLE);
150         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\"");
151         rs.next();
152         rs.deleteRow();
153         rs.close();
154         
155         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" table\" " +
156                 "order by x");
157         for (int i=2; i<=3; i++) {
158             assertTrue("there is a row", rs.next());
159             assertEquals("row contains correct value", i, rs.getInt(1));
160         }
161         rs.close();
162         stmt.close();
163     }
164
165     /**
166      * Tests insertRow with column name containing quotes
167      */
168     public void testInsertRowOnQuotedColumn() throws SQLException {
169         ResultSet rs = null;
170         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
171                 ResultSet.CONCUR_UPDATABLE);
172         rs = stmt.executeQuery("select * from \"my quoted columns\"");
173         rs.next();
174         rs.moveToInsertRow();
175         rs.updateInt(1, 4);
176         rs.insertRow();
177         rs.moveToCurrentRow();
178         rs.close();
179         
180         rs = stmt.executeQuery("select * from \"my quoted columns\" " +
181                 "order by \"my \"\"quoted\"\" column\"");
182         for (int i=1; i<=4; i++) {
183             assertTrue("there is a row", rs.next());
184             assertEquals("row contains correct value", i, rs.getInt(1));
185         }
186         rs.close();
187         stmt.close();
188     }
189
190     /**
191      * Tests updateRow with column name containing quotes
192      */
193     public void testUpdateRowOnQuotedColumn() throws SQLException {
194         ResultSet rs = null;
195         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
196                 ResultSet.CONCUR_UPDATABLE);
197         rs = stmt.executeQuery("select * from \"my quoted columns\"");
198         rs.next();
199         rs.updateInt(1, 4);
200         rs.updateRow();
201         rs.close();
202         
203         rs = stmt.executeQuery("select * from \"my quoted columns\" " +
204                 "order by \"my \"\"quoted\"\" column\"");
205         for (int i=2; i<=4; i++) {
206             assertTrue("there is a row", rs.next());
207             assertEquals("row contains correct value", i, rs.getInt(1));
208         }
209         rs.close();
210         stmt.close();
211     }
212
213     /**
214      * Tests deleteRow with column name containing quotes
215      */
216     public void testDeleteRowOnQuotedColumn() throws SQLException {
217         ResultSet rs = null;
218         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
219                 ResultSet.CONCUR_UPDATABLE);
220         rs = stmt.executeQuery("select * from \"my quoted columns\"");
221         rs.next();
222         rs.deleteRow();
223         rs.close();
224         
225         rs = stmt.executeQuery("select * from \"my quoted columns\" " +
226                 "order by \"my \"\"quoted\"\" column\"");
227         for (int i=2; i<=3; i++) {
228             assertTrue("there is a row", rs.next());
229             assertEquals("row contains correct value", i, rs.getInt(1));
230         }
231         rs.close();
232         stmt.close();
233     }
234
235     /**
236      * Tests insertRow with schema name containing quotes
237      */
238     public void testInsertRowOnQuotedSchema() throws SQLException {
239         ResultSet rs = null;
240         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
241                 ResultSet.CONCUR_UPDATABLE);
242         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." +
243                 "\"my quoted schema\"");
244         rs.next();
245         rs.moveToInsertRow();
246         rs.updateInt(1, 4);
247         rs.insertRow();
248         rs.moveToCurrentRow();
249         rs.close();
250         
251         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." +
252                 "\"my quoted schema\" order by x");
253         for (int i=1; i<=4; i++) {
254             assertTrue("there is a row", rs.next());
255             assertEquals("row contains correct value", i, rs.getInt(1));
256         }
257         rs.close();
258         stmt.close();
259     }
260
261     /**
262      * Tests updateRow with schema name containing quotes
263      */
264     public void testUpdateRowOnQuotedSchema() throws SQLException {
265         ResultSet rs = null;
266         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
267                 ResultSet.CONCUR_UPDATABLE);
268         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." +
269                 "\"my quoted schema\"");
270         rs.next();
271         rs.updateInt(1, 4);
272         rs.updateRow();
273         rs.close();
274         
275         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." +
276                 "\"my quoted schema\" order by x");
277         for (int i=2; i<=4; i++) {
278             assertTrue("there is a row", rs.next());
279             assertEquals("row contains correct value", i, rs.getInt(1));
280         }
281         rs.close();
282         stmt.close();
283     }
284
285     /**
286      * Tests deleteRow with schema name containing quotes
287      */
288     public void testDeleteRowOnQuotedSchema() throws SQLException {
289         ResultSet rs = null;
290         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
291                 ResultSet.CONCUR_UPDATABLE);
292         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." +
293                 "\"my quoted schema\"");
294         rs.next();
295         rs.deleteRow();
296         rs.close();
297         
298         rs = stmt.executeQuery("select * from \"my \"\"quoted\"\" schema\"." +
299                 "\"my quoted schema\" order by x");
300         for (int i=2; i<=3; i++) {
301             assertTrue("there is a row", rs.next());
302             assertEquals("row contains correct value", i, rs.getInt(1));
303         }
304         rs.close();
305         stmt.close();
306     }
307
308     /**
309      * Tests insertRow with cursor name containing quotes
310      */
311     public void testInsertRowOnQuotedCursor() throws SQLException {
312         ResultSet rs = null;
313         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
314                 ResultSet.CONCUR_UPDATABLE);
315         stmt.setCursorName("my \"\"\"\"quoted\"\"\"\" cursor\"\"");
316         rs = stmt.executeQuery("select * from \"my table\"");
317         rs.next();
318         rs.moveToInsertRow();
319         rs.updateInt(1, 4);
320         rs.insertRow();
321         rs.moveToCurrentRow();
322         rs.close();
323         
324         rs = stmt.executeQuery("select * from \"my table\" order by x");
325         for (int i=1; i<=4; i++) {
326             assertTrue("there is a row", rs.next());
327             assertEquals("row contains correct value", i, rs.getInt(1));
328         }
329         rs.close();
330         stmt.close();
331     }
332
333     /**
334      * Tests updateRow with cursor name containing quotes
335      */
336     public void testUpdateRowOnQuotedCursor() throws SQLException {
337         ResultSet rs = null;
338         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
339                 ResultSet.CONCUR_UPDATABLE);
340         stmt.setCursorName("\"\"my quoted cursor");
341         rs = stmt.executeQuery("select * from \"my table\"");
342         rs.next();
343         rs.updateInt(1, 4);
344         rs.updateRow();
345         rs.close();
346         
347         rs = stmt.executeQuery("select * from \"my table\" order by x");
348         for (int i=2; i<=4; i++) {
349             assertTrue("there is a row", rs.next());
350             assertEquals("row contains correct value", i, rs.getInt(1));
351         }
352         rs.close();
353         stmt.close();
354     }
355
356     /**
357      * Tests deleteRow with cursor name containing quotes
358      */
359     public void testDeleteRowOnQuotedCursor() throws SQLException {
360         ResultSet rs = null;
361         Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
362                 ResultSet.CONCUR_UPDATABLE);
363         stmt.setCursorName("\"\"my quoted cursor\"\"");
364         rs = stmt.executeQuery("select * from \"my table\"");
365         rs.next();
366         rs.deleteRow();
367         rs.close();
368         
369         rs = stmt.executeQuery("select * from \"my table\" order by x");
370         for (int i=2; i<=3; i++) {
371             assertTrue("there is a row", rs.next());
372             assertEquals("row contains correct value", i, rs.getInt(1));
373         }
374         rs.close();
375         stmt.close();
376     }
377 }
378
Popular Tags