1 /*
2  * Copyright (C) 2007 The Android Open Source Project
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  *     http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 
17 package libcore.java.sql;
18 
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22 import java.sql.Statement;
23 import tests.support.DatabaseCreator;
24 
25 public final class OldResultSetTest extends OldSQLTest {
26 
27     ResultSet target = null;
28     ResultSet emptyTarget = null;
29     ResultSet scrollableTarget = null;
30     ResultSet writableTarget = null;
31     Statement stForward = null;
32     Statement stScrollable = null;
33     Statement stWritable = null;
34     final String selectAllAnimals = "select id, name from zoo";
35     final String selectEmptyTable = "select * from "+DatabaseCreator.SIMPLE_TABLE1;
36 
setUp()37     @Override public void setUp() throws Exception {
38         super.setUp();
39 
40         conn.setAutoCommit(false);
41         stForward = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
42                 ResultSet.CONCUR_READ_ONLY);
43         stForward.execute(selectAllAnimals);
44         target = stForward.getResultSet();
45         assertNotNull(target);
46 
47         // empty table
48         stForward = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
49                 ResultSet.CONCUR_READ_ONLY);
50         stForward.execute(DatabaseCreator.CREATE_TABLE_SIMPLE1);
51         stForward.execute(selectEmptyTable);
52         emptyTarget = stForward.getResultSet();
53     }
54 
tearDown()55     public void tearDown() throws SQLException {
56         super.tearDown();
57         target.close();
58         stForward.close();
59     }
60 
testAbsolute()61     public void testAbsolute() throws SQLException {
62         assertTrue(target.isBeforeFirst());
63         assertFalse(target.absolute(0));
64         assertTrue(target.absolute(1));
65         assertTrue(target.isFirst());
66         assertTrue(target.absolute(-1));
67         assertTrue(target.isLast());
68         target.next();
69         assertTrue(target.isAfterLast());
70     }
71 
72     // res.close() does not wrap up
testAfterLast()73     public void testAfterLast() throws SQLException {
74         target.afterLast();
75         assertTrue(target.isAfterLast());
76         assertFalse(target.next());
77 
78         emptyTarget.afterLast();
79         assertFalse(emptyTarget.isAfterLast());
80 
81         try {
82             target.close();
83             target.afterLast();
84             fail("Should get SQLException");
85         } catch (SQLException e) {
86         }
87     }
88 
89     // statement.close() does not wrap up
testBeforeFirst()90     public void testBeforeFirst() throws SQLException {
91         target.beforeFirst();
92         assertTrue(target.isBeforeFirst());
93         assertTrue(target.next());
94         assertFalse(target.isBeforeFirst());
95 
96         emptyTarget.beforeFirst();
97         assertFalse(emptyTarget.isBeforeFirst());
98 
99         try {
100             target.close();
101             target.beforeFirst();
102             fail("Should get SQLException");
103         } catch (SQLException e) {
104         }
105     }
106 
107     /**
108      * According to the JDBC spec close has to "Releases this ResultSet
109      * object's database and JDBC resources immediately", and this implies
110      * the fields should be released as well (so that garbage collection
111      *  can take place)
112      */
testClose1()113     public void testClose1() {
114         try {
115             target.close();
116             target.next();
117             fail("Should get SQLException");
118         } catch (SQLException e) {
119             //ok
120         }
121     }
122 
123     /**
124      * Test that exception in one prepared statement does not affect second
125      * statement. (Atomicity Rule)
126      */
testClose()127     public void testClose() throws SQLException {
128         PreparedStatement ps1 = null;
129         PreparedStatement ps2 = null;
130         try {
131             Statement s = conn.createStatement();
132             s.addBatch("create table t1 (a text);");
133 
134             s.addBatch("insert into t1 values('abc');");
135             s.addBatch("insert into t1 values('def');");
136             s.addBatch("insert into t1 values('ghi');");
137             s.executeBatch();
138             s.close();
139 
140             conn.commit();
141             ps1 = conn.prepareStatement("select * from t1");
142             ps2 = conn.prepareStatement("select * from t1 whe a like '?000'");
143 
144             ResultSet rs1 = ps1.executeQuery();
145             try {
146                 ResultSet rs2 = ps2.executeQuery();
147                 while (rs2.next()){
148                     // do nothing
149                 }
150                 fail("Should get SQLException");
151             } catch (SQLException sqle) {
152                 // ok : Division by zero
153             }
154 
155             // Although exception happened on ps2 rs1 should still work
156             // Isolation property if ACID rules
157 
158             while (rs1.next()) {
159                 // do nothing: switching of rows should be possible
160             }
161 
162             conn.commit();
163 
164             rs1.close();
165             ps1.close();
166             ps2.close();
167         } finally {
168             try {
169                 if (ps1 != null) ps1.close();
170                 if (ps2 != null) ps2.close();
171                 conn.rollback();
172             } catch (SQLException e) {
173                 // TODO Auto-generated catch block
174                 e.printStackTrace();
175             }
176         }
177     }
178 
testFindColumn()179     public void testFindColumn() throws SQLException {
180         assertEquals(1, target.findColumn("id"));
181         assertEquals(2, target.findColumn("name"));
182 
183         try {
184             target.findColumn("bla");
185             fail("Should get SQLException");
186         } catch (SQLException e) {
187             // ok
188         }
189     }
190 
191     // statement.close() does not wrap up
testtestFirst()192     public void testtestFirst() throws SQLException {
193         assertFalse(emptyTarget.first());
194         assertTrue(target.first());
195 
196         try {
197             target.close();
198             // releases all resources such that it can be finalized!
199             target.first();
200             fail("Should get SQLException");
201         } catch (SQLException e) {
202         }
203     }
204 
205     // statement.close() does not wrap up
testtestIsAfterLast()206     public void testtestIsAfterLast() throws SQLException {
207         assertFalse(target.isAfterLast());
208         target.absolute(-1); // last
209         target.next();
210         assertTrue(target.isAfterLast());
211         assertFalse(emptyTarget.isAfterLast());
212 
213         try {
214             target.close();
215             // releases all resources such that it can be finalized!
216             target.isAfterLast();
217             fail("Should get SQLException");
218         } catch (SQLException e) {
219         }
220     }
221 
222     // In Second code block assertion fails. statement.close() does not wrap up
testtestIsBeforeFirst()223     public void testtestIsBeforeFirst() throws SQLException {
224         assertTrue(target.isBeforeFirst());
225         assertTrue(target.next());
226         assertFalse(target.isBeforeFirst());
227         assertTrue(target.isFirst());
228         assertTrue(emptyTarget.isBeforeFirst());
229 
230         try {
231             target.close();
232             // releases all resources such that it can be finalized!
233             target.isBeforeFirst();
234             fail("Should get SQLException");
235         } catch (SQLException e) {
236             //ok
237         }
238     }
239 
240     // statement.close() does not wrap up
testtestIsFirst()241     public void testtestIsFirst() throws SQLException {
242         assertFalse(target.isFirst());
243         target.first();
244         assertTrue(target.isFirst());
245         target.next();
246         assertFalse(target.isFirst());
247 
248         assertFalse(emptyTarget.isFirst());
249 
250         try {
251             target.close();
252             // releases all resources such that it can be finalized!
253             target.isFirst();
254             fail("Should get SQLException");
255         } catch (SQLException e) {
256         }
257     }
258 
259     /**
260      * Second block first assertion fails. Is Last should evaluate true if the
261      * row on which the cursor is actually provides a result.statment.close()
262      * does not wrap up
263      */
testtestIsLast()264     public void testtestIsLast() throws SQLException {
265         assertFalse(target.isLast());
266         target.absolute(-1);
267         assertTrue(target.isLast());
268 
269         //check default value no valid row
270         assertFalse(emptyTarget.isLast());
271         assertFalse(emptyTarget.next());
272         assertFalse(emptyTarget.isLast());
273 
274         try {
275             target.close();
276             target.isLast();
277             fail("Should get SQLException");
278         } catch (SQLException e) {
279             // ok
280         }
281     }
282 
283     // statement.close() does not wrap up
testtestLast()284     public void testtestLast() throws SQLException {
285         assertFalse(target.isLast());
286         target.last();
287         assertTrue(target.isLast());
288 
289         try {
290             target.close();
291             target.last();
292             fail("Should get SQLException");
293         } catch (SQLException e) {
294             // ok
295         }
296     }
297 
298     /**
299      * SQLException checking test fails. Clearing of warnings and closed streams
300      * not supported.
301      */
testNext()302     public void testNext() throws SQLException {
303         //before first - first
304         assertTrue(target.next());
305         //first - second
306         assertTrue(target.next());
307         //after last
308         assertFalse(target.next());
309         assertTrue(target.isAfterLast());
310         // one more
311         assertFalse(target.next());
312 
313         assertFalse(emptyTarget.next());
314 
315         target.close();
316         try {
317             target.next();
318             fail("Exception expected");
319         } catch (SQLException e) {
320             //ok
321         }
322     }
323 
testPrevious()324     public void testPrevious() throws SQLException {
325         target.first();
326         target.previous();
327         assertTrue(target.isBeforeFirst());
328 
329         target.last();
330         target.next();
331         target.previous();
332         assertFalse(target.isAfterLast());
333 
334         target.close();
335         try {
336             target.previous();
337             fail("Exception expected");
338         } catch (SQLException e) {
339             //ok
340         }
341     }
342 
343     // no exception is thrown when moving cursor backwards on forward only statement
testRelative()344     public void testRelative() throws SQLException {
345 
346         // forward only
347         int initialRow = target.getRow();
348         assertFalse(target.relative(0));
349         assertEquals(initialRow, target.getRow());
350 
351         assertTrue(target.relative(1));
352         assertTrue(target.isFirst());
353         assertEquals(1, target.getRow());
354 
355         assertTrue(target.relative(1));
356         assertFalse(target.isFirst());
357         assertEquals(2, target.getRow());
358         assertFalse(target.relative(2));
359 
360         try {
361             // should not be able to scroll backwards in forward only RS
362             target.relative(-2);
363             assertEquals(2,target.getRow());
364             fail("Should get SQLException");
365         } catch (SQLException e) {
366             // ok
367         } catch (Exception e) {
368             fail("Unexpected exception: " + e.getMessage());
369         }
370 
371         assertFalse(emptyTarget.relative(Integer.MAX_VALUE));
372         assertTrue(emptyTarget.isAfterLast());
373     }
374 
375     // Scrollable resultSet. Not supported
testRelativeScrollableResultSet()376     public void testRelativeScrollableResultSet() throws SQLException {
377      // scrollable resultSet
378         int initialRow = scrollableTarget.getRow();
379         assertFalse(scrollableTarget.relative(0));
380         assertEquals(initialRow, scrollableTarget.getRow());
381 
382         assertTrue(scrollableTarget.relative(1));
383         assertTrue(scrollableTarget.isFirst());
384         assertEquals(1, scrollableTarget.getRow());
385 
386         assertTrue(scrollableTarget.relative(1));
387         assertFalse(scrollableTarget.isFirst());
388 
389         assertEquals(2, scrollableTarget.getRow());
390         assertFalse(scrollableTarget.relative(2));
391         scrollableTarget.relative(-2);
392         assertEquals(2,scrollableTarget.getRow());
393 
394         assertFalse(scrollableTarget.relative(Integer.MIN_VALUE));
395         assertTrue(scrollableTarget.isBeforeFirst());
396 
397         stScrollable.close();
398         try {
399             scrollableTarget.relative(1);
400             fail("Exception expected");
401         } catch (SQLException e) {
402             //ok
403         }
404     }
405 
406     // not supported
testUpdateObjectStringObject()407     public void testUpdateObjectStringObject() throws SQLException {
408         writableTarget.next();
409         writableTarget.updateObject("family","bird");
410 
411         try {
412            target.next();
413            target.updateObject("family","bird");
414            fail("SQLException was not thrown");
415         } catch (SQLException e) {
416            fail("Unexpected exception: " + e.getMessage());
417         }
418     }
419 
420     /**
421      * Only exception testing. Missing testing for wrong type
422      */
testUpdateStringStringString()423     public void testUpdateStringStringString() throws Exception {
424         writableTarget.next();
425         writableTarget.updateString("family","bird");
426 
427          // non writable target.
428          try {
429             target.next();
430             target.updateString("family","bird");
431             fail("SQLException was not thrown");
432          } catch (SQLException e) {
433             //ok
434          }
435 
436 
437          // writable but wrong type
438         target.updateString(1,"test");
439 
440         target.close();
441 
442         // Exception test
443         try {
444             target.updateString("family", "test");
445             fail("Exception expected");
446         } catch (SQLException e) {
447             //ok
448         }
449     }
450 
451     /**
452      * Test method for {@link java.sql.ResultSet#wasNull()}.
453      * Spec sais: if something was read... -> if nothing was read it should be false
454      */
testWasNull()455     public void testWasNull() throws SQLException {
456         // Check default: select statement executed but no get on target called yet
457         // Either false or throw an exception.
458         try {
459             assertFalse(target.wasNull());
460         } catch (SQLException e) {
461             //ok
462         }
463 
464         stForward.execute("insert into zoo values(8,null,null);");
465         stForward.execute(selectAllAnimals);
466         target = stForward.getResultSet();
467         assertNotNull(target);
468         assertTrue(target.last());
469         assertNull(target.getObject(2));
470         assertTrue(target.wasNull());
471         assertNotNull(target.getObject(1));
472         assertFalse(target.wasNull());
473 
474         target.close();
475         try {
476             target.wasNull();
477             fail("Exception expected");
478         } catch (SQLException e) {
479             //ok
480         }
481     }
482 }
483