001: /*
002: *
003: * Derby - Class UpdatableResultSetTest
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,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
017: * either express or implied. See the License for the specific
018: * language governing permissions and limitations under the License.
019: */
020: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
021:
022: import org.apache.derbyTesting.functionTests.util.TestUtil;
023: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
024: import org.apache.derbyTesting.junit.JDBC;
025:
026: import junit.framework.*;
027: import java.sql.*;
028:
029: /**
030: * Tests updatable result sets.
031: *
032: * DERBY-1767 - Test that the deleteRow, insertRow and updateRow methods
033: * with column/table/schema/cursor names containing quotes.
034: *
035: */
036: public class UpdatableResultSetTest extends BaseJDBCTestCase {
037:
038: /** Creates a new instance of UpdatableResultSetTest */
039: public UpdatableResultSetTest(String name) {
040: super (name);
041: }
042:
043: private Connection conn = null;
044:
045: protected void setUp() throws SQLException {
046: conn = getConnection();
047: conn.setAutoCommit(false);
048: Statement stmt = conn.createStatement();
049:
050: // Quoted table
051: stmt
052: .executeUpdate("create table \"my \"\"quoted\"\" table\" (x int)");
053: stmt
054: .executeUpdate("insert into \"my \"\"quoted\"\" table\" (x) "
055: + "values (1), (2), (3)");
056:
057: // Quoted columns
058: stmt.executeUpdate("create table \"my quoted columns\" "
059: + "(\"my \"\"quoted\"\" column\" int)");
060: stmt.executeUpdate("insert into \"my quoted columns\" "
061: + "values (1), (2), (3) ");
062:
063: // Quoted schema
064: stmt.executeUpdate("create table \"my \"\"quoted\"\" schema\"."
065: + "\"my quoted schema\" (x int)");
066: stmt.executeUpdate("insert into \"my \"\"quoted\"\" schema\"."
067: + "\"my quoted schema\" values (1), (2), (3) ");
068:
069: // No quotes, use with quoted cursor
070: stmt.executeUpdate("create table \"my table\" (x int)");
071: stmt
072: .executeUpdate("insert into \"my table\" values (1), (2), (3) ");
073:
074: stmt.close();
075: }
076:
077: protected void tearDown() throws Exception {
078: conn.rollback();
079: conn.close();
080: super .tearDown();
081: conn = null;
082: }
083:
084: /** Create a test suite with all tests in this class. */
085: public static Test suite() {
086: TestSuite ts = new TestSuite();
087:
088: // Test will fail with JCC.
089: if (usingDerbyNet()) {
090: return ts;
091: }
092:
093: ts.addTestSuite(UpdatableResultSetTest.class);
094: return ts;
095: }
096:
097: /**
098: * Tests insertRow with table name containing quotes
099: */
100: public void testInsertRowOnQuotedTable() throws SQLException {
101: ResultSet rs = null;
102: Statement stmt = conn
103: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
104: ResultSet.CONCUR_UPDATABLE);
105: rs = stmt
106: .executeQuery("select * from \"my \"\"quoted\"\" table\"");
107: rs.next();
108: rs.moveToInsertRow();
109: rs.updateInt(1, 4);
110: rs.insertRow();
111: rs.moveToCurrentRow();
112: rs.close();
113:
114: rs = stmt
115: .executeQuery("select * from \"my \"\"quoted\"\" table\" "
116: + "order by x");
117: for (int i = 1; i <= 4; i++) {
118: assertTrue("there is a row", rs.next());
119: assertEquals("row contains correct value", i, rs.getInt(1));
120: }
121: rs.close();
122: stmt.close();
123: }
124:
125: /**
126: * Tests updateRow with table name containing quotes
127: */
128: public void testUpdateRowOnQuotedTable() throws SQLException {
129: ResultSet rs = null;
130: Statement stmt = conn
131: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
132: ResultSet.CONCUR_UPDATABLE);
133: rs = stmt
134: .executeQuery("select * from \"my \"\"quoted\"\" table\"");
135: rs.next();
136: rs.updateInt(1, 4);
137: rs.updateRow();
138: rs.close();
139:
140: rs = stmt
141: .executeQuery("select * from \"my \"\"quoted\"\" table\" "
142: + "order by x");
143: for (int i = 2; i <= 4; i++) {
144: assertTrue("there is a row", rs.next());
145: assertEquals("row contains correct value", i, rs.getInt(1));
146: }
147: rs.close();
148: stmt.close();
149: }
150:
151: /**
152: * Tests deleteRow with table name containing quotes
153: */
154: public void testDeleteRowOnQuotedTable() throws SQLException {
155: ResultSet rs = null;
156: Statement stmt = conn
157: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
158: ResultSet.CONCUR_UPDATABLE);
159: rs = stmt
160: .executeQuery("select * from \"my \"\"quoted\"\" table\"");
161: rs.next();
162: rs.deleteRow();
163: rs.close();
164:
165: rs = stmt
166: .executeQuery("select * from \"my \"\"quoted\"\" table\" "
167: + "order by x");
168: for (int i = 2; i <= 3; i++) {
169: assertTrue("there is a row", rs.next());
170: assertEquals("row contains correct value", i, rs.getInt(1));
171: }
172: rs.close();
173: stmt.close();
174: }
175:
176: /**
177: * Tests insertRow with column name containing quotes
178: */
179: public void testInsertRowOnQuotedColumn() throws SQLException {
180: ResultSet rs = null;
181: Statement stmt = conn
182: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
183: ResultSet.CONCUR_UPDATABLE);
184: rs = stmt.executeQuery("select * from \"my quoted columns\"");
185: rs.next();
186: rs.moveToInsertRow();
187: rs.updateInt(1, 4);
188: rs.insertRow();
189: rs.moveToCurrentRow();
190: rs.close();
191:
192: rs = stmt.executeQuery("select * from \"my quoted columns\" "
193: + "order by \"my \"\"quoted\"\" column\"");
194: for (int i = 1; i <= 4; i++) {
195: assertTrue("there is a row", rs.next());
196: assertEquals("row contains correct value", i, rs.getInt(1));
197: }
198: rs.close();
199: stmt.close();
200: }
201:
202: /**
203: * Tests updateRow with column name containing quotes
204: */
205: public void testUpdateRowOnQuotedColumn() throws SQLException {
206: ResultSet rs = null;
207: Statement stmt = conn
208: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
209: ResultSet.CONCUR_UPDATABLE);
210: rs = stmt.executeQuery("select * from \"my quoted columns\"");
211: rs.next();
212: rs.updateInt(1, 4);
213: rs.updateRow();
214: rs.close();
215:
216: rs = stmt.executeQuery("select * from \"my quoted columns\" "
217: + "order by \"my \"\"quoted\"\" column\"");
218: for (int i = 2; i <= 4; i++) {
219: assertTrue("there is a row", rs.next());
220: assertEquals("row contains correct value", i, rs.getInt(1));
221: }
222: rs.close();
223: stmt.close();
224: }
225:
226: /**
227: * Tests deleteRow with column name containing quotes
228: */
229: public void testDeleteRowOnQuotedColumn() throws SQLException {
230: ResultSet rs = null;
231: Statement stmt = conn
232: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
233: ResultSet.CONCUR_UPDATABLE);
234: rs = stmt.executeQuery("select * from \"my quoted columns\"");
235: rs.next();
236: rs.deleteRow();
237: rs.close();
238:
239: rs = stmt.executeQuery("select * from \"my quoted columns\" "
240: + "order by \"my \"\"quoted\"\" column\"");
241: for (int i = 2; i <= 3; i++) {
242: assertTrue("there is a row", rs.next());
243: assertEquals("row contains correct value", i, rs.getInt(1));
244: }
245: rs.close();
246: stmt.close();
247: }
248:
249: /**
250: * Tests insertRow with schema name containing quotes
251: */
252: public void testInsertRowOnQuotedSchema() throws SQLException {
253: ResultSet rs = null;
254: Statement stmt = conn
255: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
256: ResultSet.CONCUR_UPDATABLE);
257: rs = stmt
258: .executeQuery("select * from \"my \"\"quoted\"\" schema\"."
259: + "\"my quoted schema\"");
260: rs.next();
261: rs.moveToInsertRow();
262: rs.updateInt(1, 4);
263: rs.insertRow();
264: rs.moveToCurrentRow();
265: rs.close();
266:
267: rs = stmt
268: .executeQuery("select * from \"my \"\"quoted\"\" schema\"."
269: + "\"my quoted schema\" order by x");
270: for (int i = 1; i <= 4; i++) {
271: assertTrue("there is a row", rs.next());
272: assertEquals("row contains correct value", i, rs.getInt(1));
273: }
274: rs.close();
275: stmt.close();
276: }
277:
278: /**
279: * Tests updateRow with schema name containing quotes
280: */
281: public void testUpdateRowOnQuotedSchema() throws SQLException {
282: ResultSet rs = null;
283: Statement stmt = conn
284: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
285: ResultSet.CONCUR_UPDATABLE);
286: rs = stmt
287: .executeQuery("select * from \"my \"\"quoted\"\" schema\"."
288: + "\"my quoted schema\"");
289: rs.next();
290: rs.updateInt(1, 4);
291: rs.updateRow();
292: rs.close();
293:
294: rs = stmt
295: .executeQuery("select * from \"my \"\"quoted\"\" schema\"."
296: + "\"my quoted schema\" order by x");
297: for (int i = 2; i <= 4; i++) {
298: assertTrue("there is a row", rs.next());
299: assertEquals("row contains correct value", i, rs.getInt(1));
300: }
301: rs.close();
302: stmt.close();
303: }
304:
305: /**
306: * Tests deleteRow with schema name containing quotes
307: */
308: public void testDeleteRowOnQuotedSchema() throws SQLException {
309: ResultSet rs = null;
310: Statement stmt = conn
311: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
312: ResultSet.CONCUR_UPDATABLE);
313: rs = stmt
314: .executeQuery("select * from \"my \"\"quoted\"\" schema\"."
315: + "\"my quoted schema\"");
316: rs.next();
317: rs.deleteRow();
318: rs.close();
319:
320: rs = stmt
321: .executeQuery("select * from \"my \"\"quoted\"\" schema\"."
322: + "\"my quoted schema\" order by x");
323: for (int i = 2; i <= 3; i++) {
324: assertTrue("there is a row", rs.next());
325: assertEquals("row contains correct value", i, rs.getInt(1));
326: }
327: rs.close();
328: stmt.close();
329: }
330:
331: /**
332: * Tests insertRow with cursor name containing quotes
333: */
334: public void testInsertRowOnQuotedCursor() throws SQLException {
335: ResultSet rs = null;
336: Statement stmt = conn
337: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
338: ResultSet.CONCUR_UPDATABLE);
339: stmt.setCursorName("my \"\"\"\"quoted\"\"\"\" cursor\"\"");
340: rs = stmt.executeQuery("select * from \"my table\"");
341: rs.next();
342: rs.moveToInsertRow();
343: rs.updateInt(1, 4);
344: rs.insertRow();
345: rs.moveToCurrentRow();
346: rs.close();
347:
348: rs = stmt.executeQuery("select * from \"my table\" order by x");
349: for (int i = 1; i <= 4; i++) {
350: assertTrue("there is a row", rs.next());
351: assertEquals("row contains correct value", i, rs.getInt(1));
352: }
353: rs.close();
354: stmt.close();
355: }
356:
357: /**
358: * Tests updateRow with cursor name containing quotes
359: */
360: public void testUpdateRowOnQuotedCursor() throws SQLException {
361: ResultSet rs = null;
362: Statement stmt = conn
363: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
364: ResultSet.CONCUR_UPDATABLE);
365: stmt.setCursorName("\"\"my quoted cursor");
366: rs = stmt.executeQuery("select * from \"my table\"");
367: rs.next();
368: rs.updateInt(1, 4);
369: rs.updateRow();
370: rs.close();
371:
372: rs = stmt.executeQuery("select * from \"my table\" order by x");
373: for (int i = 2; i <= 4; i++) {
374: assertTrue("there is a row", rs.next());
375: assertEquals("row contains correct value", i, rs.getInt(1));
376: }
377: rs.close();
378: stmt.close();
379: }
380:
381: /**
382: * Tests deleteRow with cursor name containing quotes
383: */
384: public void testDeleteRowOnQuotedCursor() throws SQLException {
385: ResultSet rs = null;
386: Statement stmt = conn
387: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
388: ResultSet.CONCUR_UPDATABLE);
389: stmt.setCursorName("\"\"my quoted cursor\"\"");
390: rs = stmt.executeQuery("select * from \"my table\"");
391: rs.next();
392: rs.deleteRow();
393: rs.close();
394:
395: rs = stmt.executeQuery("select * from \"my table\" order by x");
396: for (int i = 2; i <= 3; i++) {
397: assertTrue("there is a row", rs.next());
398: assertEquals("row contains correct value", i, rs.getInt(1));
399: }
400: rs.close();
401: stmt.close();
402: }
403: }
|