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
17package libcore.java.sql;
18
19import java.sql.PreparedStatement;
20import java.sql.ResultSet;
21import java.sql.SQLException;
22import java.sql.Statement;
23import tests.support.DatabaseCreator;
24
25public 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
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
55    public void tearDown() throws SQLException {
56        super.tearDown();
57        target.close();
58        stForward.close();
59    }
60
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
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.beforeFirst();
84            fail("Should get SQLException");
85        } catch (SQLException e) {
86        }
87    }
88
89    // statement.close() does not wrap up
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     */
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     */
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
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
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
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
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
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     */
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
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     */
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
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
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
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
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     */
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     */
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