UpdatabilityTest


1   /*
2    Copyright (C) 2002-2004 MySQL AB
3   
4    This program is free software; you can redistribute it and/or modify
5    it under the terms of version 2 of the GNU General Public License as 
6    published by the Free Software Foundation.
7   
8    There are special exceptions to the terms and conditions of the GPL 
9    as it is applied to this software. View the full text of the 
10   exception in file EXCEPTIONS-CONNECTOR-J in the directory of this 
11   software distribution.
12  
13   This program is distributed in the hope that it will be useful,
14   but WITHOUT ANY WARRANTY; without even the implied warranty of
15   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16   GNU General Public License for more details.
17  
18   You should have received a copy of the GNU General Public License
19   along with this program; if not, write to the Free Software
20   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
21  
22  
23  
24   */
25  package testsuite.simple;
26  
27  import com.mysql.jdbc.NotUpdatable;
28  
29  import testsuite.BaseTestCase;
30  
31  import java.sql.DatabaseMetaData  ;
32  import java.sql.ResultSet  ;
33  import java.sql.SQLException  ;
34  import java.sql.Statement  ;
35  
36  /**
37   * Tests for updatable result sets
38   * 
39   * @author Mark Matthews
40   * @version $Id: UpdatabilityTest.java,v 1.1.2.1 2005/05/13 18:58:37 mmatthews
41   *          Exp $
42   */
43  public class UpdatabilityTest extends BaseTestCase {
44      /**
45       * Creates a new UpdatabilityTest object.
46       * 
47       * @param name
48       *            DOCUMENT ME!
49       */
50      public UpdatabilityTest(String   name) {
51          super(name);
52      }
53  
54      /**
55       * Runs all test cases in this test suite
56       * 
57       * @param args
58       */
59      public static void main(String  [] args) {
60          junit.textui.TestRunner.run(UpdatabilityTest.class);
61      }
62  
63      /**
64       * DOCUMENT ME!
65       * 
66       * @throws Exception
67       *             DOCUMENT ME!
68       */
69      public void setUp() throws Exception   {
70          super.setUp();
71          createTestTable();
72      }
73  
74      /**
75       * If using MySQL-4.1, tests if aliased tables work as updatable result
76       * sets.
77       * 
78       * @throws Exception
79       *             if an error occurs
80       */
81      public void testAliasedTables() throws Exception   {
82          DatabaseMetaData   dbmd = this.conn.getMetaData();
83  
84          if ((dbmd.getDatabaseMajorVersion() >= 4)
85                  && (dbmd.getDatabaseMinorVersion() >= 1)) {
86              Statement   scrollableStmt = null;
87  
88              try {
89                  scrollableStmt = this.conn.createStatement(
90                          ResultSet.TYPE_SCROLL_INSENSITIVE,
91                          ResultSet.CONCUR_UPDATABLE);
92                  this.rs = scrollableStmt
93                          .executeQuery("SELECT pos1 AS p1, pos2 AS P2, char_field AS cf FROM UPDATABLE AS UPD LIMIT 1");
94                  this.rs.next();
95                  this.rs.close();
96                  this.rs = null;
97  
98                  scrollableStmt.close();
99                  scrollableStmt = null;
100             } finally {
101                 if (this.rs != null) {
102                     try {
103                         this.rs.close();
104                     } catch (SQLException   sqlEx) {
105                         ; // ignore
106                     }
107 
108                     this.rs = null;
109                 }
110 
111                 if (scrollableStmt != null) {
112                     try {
113                         scrollableStmt.close();
114                     } catch (SQLException   sqlEx) {
115                         ; // ignore
116                     }
117 
118                     scrollableStmt = null;
119                 }
120             }
121         }
122     }
123 
124     /**
125      * Tests that the driver does not let you update result sets that come from
126      * tables that don't have primary keys
127      * 
128      * @throws SQLException
129      *             if an error occurs
130      */
131     public void testBogusTable() throws SQLException   {
132         this.stmt.executeUpdate("DROP TABLE IF EXISTS BOGUS_UPDATABLE");
133         this.stmt.executeUpdate("CREATE TABLE BOGUS_UPDATABLE (field1 int)");
134 
135         Statement   scrollableStmt = null;
136 
137         try {
138             scrollableStmt = this.conn.createStatement(
139                     ResultSet.TYPE_SCROLL_INSENSITIVE,
140                     ResultSet.CONCUR_UPDATABLE);
141             this.rs = scrollableStmt
142                     .executeQuery("SELECT * FROM BOGUS_UPDATABLE");
143 
144             try {
145                 this.rs.moveToInsertRow();
146                 fail("ResultSet.moveToInsertRow() should not succeed on non-updatable table");
147             } catch (NotUpdatable noUpdate) {
148                 // ignore
149             }
150         } finally {
151             if (scrollableStmt != null) {
152                 try {
153                     scrollableStmt.close();
154                 } catch (SQLException   sqlEx) {
155                     ;
156                 }
157             }
158 
159             this.stmt.executeUpdate("DROP TABLE IF EXISTS BOGUS_UPDATABLE");
160         }
161     }
162 
163     /**
164      * Tests that the driver does not let you update result sets that come from
165      * queries that haven't selected all primary keys
166      * 
167      * @throws SQLException
168      *             if an error occurs
169      */
170     public void testMultiKeyTable() throws SQLException   {
171         this.stmt.executeUpdate("DROP TABLE IF EXISTS MULTI_UPDATABLE");
172         this.stmt
173                 .executeUpdate("CREATE TABLE MULTI_UPDATABLE (field1 int NOT NULL, field2 int NOT NULL, PRIMARY KEY (field1, field2))");
174 
175         Statement   scrollableStmt = null;
176 
177         try {
178             scrollableStmt = this.conn.createStatement(
179                     ResultSet.TYPE_SCROLL_INSENSITIVE,
180                     ResultSet.CONCUR_UPDATABLE);
181             this.rs = scrollableStmt
182                     .executeQuery("SELECT field1 FROM MULTI_UPDATABLE");
183 
184             try {
185                 this.rs.moveToInsertRow();
186                 fail("ResultSet.moveToInsertRow() should not succeed on query that does not select all primary keys");
187             } catch (NotUpdatable noUpdate) {
188                 // ignore
189             }
190         } finally {
191             if (scrollableStmt != null) {
192                 try {
193                     scrollableStmt.close();
194                 } catch (SQLException   sqlEx) {
195                     // ignore
196                 }
197             }
198 
199             this.stmt.executeUpdate("DROP TABLE IF EXISTS MULTI_UPDATABLE");
200         }
201     }
202 
203     /**
204      * DOCUMENT ME!
205      * 
206      * @throws SQLException
207      *             DOCUMENT ME!
208      */
209     public void testUpdatability() throws SQLException   {
210         Statement   scrollableStmt = null;
211 
212         try {
213             scrollableStmt = this.conn.createStatement(
214                     ResultSet.TYPE_SCROLL_INSENSITIVE,
215                     ResultSet.CONCUR_UPDATABLE);
216             this.rs = scrollableStmt
217                     .executeQuery("SELECT * FROM UPDATABLE ORDER BY pos1");
218 
219             this.rs.getMetaData().getColumnCount();
220 
221             while (this.rs.next()) {
222                 int rowPos = this.rs.getInt(1);
223                 this.rs.updateString(3, "New Data" + (100 - rowPos));
224                 this.rs.updateRow();
225             }
226 
227             //
228             // Insert a new row
229             //
230             this.rs.moveToInsertRow();
231             this.rs.updateInt(1, 400);
232             this.rs.updateInt(2, 400);
233             this.rs.updateString(3, "New Data" + (100 - 400));
234             this.rs.insertRow();
235 
236             // Test moveToCurrentRow
237             int rememberedPosition = this.rs.getRow();
238             this.rs.moveToInsertRow();
239             this.rs.moveToCurrentRow();
240             assertTrue("ResultSet.moveToCurrentRow() failed",
241                     this.rs.getRow() == rememberedPosition);
242             this.rs.close();
243             this.rs = scrollableStmt
244                     .executeQuery("SELECT * FROM UPDATABLE ORDER BY pos1");
245 
246             boolean dataGood = true;
247 
248             while (this.rs.next()) {
249                 int rowPos = this.rs.getInt(1);
250 
251                 if (!this.rs.getString(3).equals("New Data" + (100 - rowPos))) {
252                     dataGood = false;
253                 }
254             }
255 
256             assertTrue("Updates failed", dataGood);
257 
258             // move back, and change the primary key
259             // This should work
260             int newPrimaryKeyId = 99999;
261             this.rs.absolute(1);
262             this.rs.updateInt(1, newPrimaryKeyId);
263             this.rs.updateRow();
264 
265             int savedPrimaryKeyId = this.rs.getInt(1);
266             assertTrue("Updated primary key does not match",
267                     (newPrimaryKeyId == savedPrimaryKeyId));
268 
269             // Check cancelRowUpdates()
270             this.rs.absolute(1);
271 
272             int primaryKey = this.rs.getInt(1);
273             int originalValue = this.rs.getInt(2);
274             this.rs.updateInt(2, -3);
275             this.rs.cancelRowUpdates();
276 
277             int newValue = this.rs.getInt(2);
278             assertTrue("ResultSet.cancelRowUpdates() failed",
279                     newValue == originalValue);
280 
281             // Now check refreshRow()
282             // Check cancelRowUpdates()
283             this.rs.absolute(1);
284             primaryKey = this.rs.getInt(1);
285             this.stmt
286                     .executeUpdate("UPDATE UPDATABLE SET char_field='foo' WHERE pos1="
287                             + primaryKey);
288             this.rs.refreshRow();
289             assertTrue("ResultSet.refreshRow failed", this.rs.getString(
290                     "char_field").equals("foo"));
291 
292             // Now check deleteRow()
293             this.rs.last();
294 
295             int oldLastRow = this.rs.getRow();
296             this.rs.deleteRow();
297             this.rs.last();
298             assertTrue("ResultSet.deleteRow() failed",
299                     this.rs.getRow() == (oldLastRow - 1));
300             this.rs.close();
301 
302             /*
303              * FIXME: Move to regression
304              * 
305              * scrollableStmt.executeUpdate("DROP TABLE IF EXISTS test");
306              * scrollableStmt.executeUpdate("CREATE TABLE test (ident INTEGER
307              * PRIMARY KEY, name TINYTEXT, expiry DATETIME default null)");
308              * scrollableStmt.executeUpdate("INSERT INTO test SET ident=1,
309              * name='original'");
310              * 
311              * //Select to get a resultset to work on ResultSet this.rs =
312              * this.stmt.executeQuery("SELECT ident, name, expiry FROM test");
313              * 
314              * //Check that the expiry field was null before we did our update
315              * this.rs.first();
316              * 
317              * java.sql.Date before = this.rs.getDate("expiry");
318              * 
319              * if (this.rs.wasNull()) { System.out.println("Expiry was correctly
320              * SQL null before update"); }
321              * 
322              * //Update a different field this.rs.updateString("name",
323              * "Updated"); this.rs.updateRow();
324              * 
325              * //Test to see if field has been altered java.sql.Date after =
326              * this.rs.getDate(3);
327              * 
328              * if (this.rs.wasNull()) System.out.println("Bug disproved - expiry
329              * SQL null after update"); else System.out.println("Bug proved -
330              * expiry corrupted to '" + after + "'");
331              */
332         } finally {
333             if (scrollableStmt != null) {
334                 try {
335                     scrollableStmt.close();
336                 } catch (SQLException   sqlEx) {
337                     ;
338                 }
339             }
340         }
341     }
342 
343     private void createTestTable() throws SQLException   {
344         //
345         // Catch the error, the table might exist
346         //
347         try {
348             this.stmt.executeUpdate("DROP TABLE UPDATABLE");
349         } catch (SQLException   SQLE) {
350             ;
351         }
352 
353         this.stmt
354                 .executeUpdate("CREATE TABLE UPDATABLE (pos1 int not null, pos2 int not null, char_field VARCHAR(32), PRIMARY KEY (pos1, pos2))");
355 
356         for (int i = 0; i < 100; i++) {
357             this.stmt.executeUpdate("INSERT INTO UPDATABLE VALUES (" + i + ", "
358                     + i + ",'StringData" + i + "')");
359         }
360     }
361 }
362
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags