001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.simple;
026:
027: import com.mysql.jdbc.NotUpdatable;
028:
029: import testsuite.BaseTestCase;
030:
031: import java.sql.DatabaseMetaData;
032: import java.sql.ResultSet;
033: import java.sql.SQLException;
034: import java.sql.Statement;
035:
036: /**
037: * Tests for updatable result sets
038: *
039: * @author Mark Matthews
040: * @version $Id: UpdatabilityTest.java,v 1.1.2.1 2005/05/13 18:58:37 mmatthews
041: * Exp $
042: */
043: public class UpdatabilityTest extends BaseTestCase {
044: /**
045: * Creates a new UpdatabilityTest object.
046: *
047: * @param name
048: * DOCUMENT ME!
049: */
050: public UpdatabilityTest(String name) {
051: super (name);
052: }
053:
054: /**
055: * Runs all test cases in this test suite
056: *
057: * @param args
058: */
059: public static void main(String[] args) {
060: junit.textui.TestRunner.run(UpdatabilityTest.class);
061: }
062:
063: /**
064: * DOCUMENT ME!
065: *
066: * @throws Exception
067: * DOCUMENT ME!
068: */
069: public void setUp() throws Exception {
070: super .setUp();
071: createTestTable();
072: }
073:
074: /**
075: * If using MySQL-4.1, tests if aliased tables work as updatable result
076: * sets.
077: *
078: * @throws Exception
079: * if an error occurs
080: */
081: public void testAliasedTables() throws Exception {
082: DatabaseMetaData dbmd = this .conn.getMetaData();
083:
084: if (versionMeetsMinimum(4, 1)) {
085: Statement scrollableStmt = null;
086:
087: try {
088: scrollableStmt = this .conn.createStatement(
089: ResultSet.TYPE_SCROLL_INSENSITIVE,
090: ResultSet.CONCUR_UPDATABLE);
091: this .rs = scrollableStmt
092: .executeQuery("SELECT pos1 AS p1, pos2 AS P2, char_field AS cf FROM UPDATABLE AS UPD LIMIT 1");
093: this .rs.next();
094: this .rs.close();
095: this .rs = null;
096:
097: scrollableStmt.close();
098: scrollableStmt = null;
099: } finally {
100: if (this .rs != null) {
101: try {
102: this .rs.close();
103: } catch (SQLException sqlEx) {
104: ; // ignore
105: }
106:
107: this .rs = null;
108: }
109:
110: if (scrollableStmt != null) {
111: try {
112: scrollableStmt.close();
113: } catch (SQLException sqlEx) {
114: ; // ignore
115: }
116:
117: scrollableStmt = null;
118: }
119: }
120: }
121: }
122:
123: /**
124: * Tests that the driver does not let you update result sets that come from
125: * tables that don't have primary keys
126: *
127: * @throws SQLException
128: * if an error occurs
129: */
130: public void testBogusTable() throws SQLException {
131: this .stmt.executeUpdate("DROP TABLE IF EXISTS BOGUS_UPDATABLE");
132: this .stmt
133: .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
160: .executeUpdate("DROP TABLE IF EXISTS BOGUS_UPDATABLE");
161: }
162: }
163:
164: /**
165: * Tests that the driver does not let you update result sets that come from
166: * queries that haven't selected all primary keys
167: *
168: * @throws SQLException
169: * if an error occurs
170: */
171: public void testMultiKeyTable() throws SQLException {
172: this .stmt.executeUpdate("DROP TABLE IF EXISTS MULTI_UPDATABLE");
173: this .stmt
174: .executeUpdate("CREATE TABLE MULTI_UPDATABLE (field1 int NOT NULL, field2 int NOT NULL, PRIMARY KEY (field1, field2))");
175:
176: Statement scrollableStmt = null;
177:
178: try {
179: scrollableStmt = this .conn.createStatement(
180: ResultSet.TYPE_SCROLL_INSENSITIVE,
181: ResultSet.CONCUR_UPDATABLE);
182: this .rs = scrollableStmt
183: .executeQuery("SELECT field1 FROM MULTI_UPDATABLE");
184:
185: try {
186: this .rs.moveToInsertRow();
187: fail("ResultSet.moveToInsertRow() should not succeed on query that does not select all primary keys");
188: } catch (NotUpdatable noUpdate) {
189: // ignore
190: }
191: } finally {
192: if (scrollableStmt != null) {
193: try {
194: scrollableStmt.close();
195: } catch (SQLException sqlEx) {
196: // ignore
197: }
198: }
199:
200: this .stmt
201: .executeUpdate("DROP TABLE IF EXISTS MULTI_UPDATABLE");
202: }
203: }
204:
205: /**
206: * DOCUMENT ME!
207: *
208: * @throws SQLException
209: * DOCUMENT ME!
210: */
211: public void testUpdatability() throws SQLException {
212: Statement scrollableStmt = null;
213:
214: try {
215: scrollableStmt = this .conn.createStatement(
216: ResultSet.TYPE_SCROLL_INSENSITIVE,
217: ResultSet.CONCUR_UPDATABLE);
218: this .rs = scrollableStmt
219: .executeQuery("SELECT * FROM UPDATABLE ORDER BY pos1");
220:
221: this .rs.getMetaData().getColumnCount();
222:
223: while (this .rs.next()) {
224: int rowPos = this .rs.getInt(1);
225: this .rs.updateString(3, "New Data" + (100 - rowPos));
226: this .rs.updateRow();
227: }
228:
229: //
230: // Insert a new row
231: //
232: this .rs.moveToInsertRow();
233: this .rs.updateInt(1, 400);
234: this .rs.updateInt(2, 400);
235: this .rs.updateString(3, "New Data" + (100 - 400));
236: this .rs.insertRow();
237:
238: // Test moveToCurrentRow
239: int rememberedPosition = this .rs.getRow();
240: this .rs.moveToInsertRow();
241: this .rs.moveToCurrentRow();
242: assertTrue("ResultSet.moveToCurrentRow() failed", this .rs
243: .getRow() == rememberedPosition);
244: this .rs.close();
245: this .rs = scrollableStmt
246: .executeQuery("SELECT * FROM UPDATABLE ORDER BY pos1");
247:
248: boolean dataGood = true;
249:
250: while (this .rs.next()) {
251: int rowPos = this .rs.getInt(1);
252:
253: if (!this .rs.getString(3).equals(
254: "New Data" + (100 - rowPos))) {
255: dataGood = false;
256: }
257: }
258:
259: assertTrue("Updates failed", dataGood);
260:
261: // move back, and change the primary key
262: // This should work
263: int newPrimaryKeyId = 99999;
264: this .rs.absolute(1);
265: this .rs.updateInt(1, newPrimaryKeyId);
266: this .rs.updateRow();
267:
268: int savedPrimaryKeyId = this .rs.getInt(1);
269: assertTrue("Updated primary key does not match",
270: (newPrimaryKeyId == savedPrimaryKeyId));
271:
272: // Check cancelRowUpdates()
273: this .rs.absolute(1);
274:
275: int primaryKey = this .rs.getInt(1);
276: int originalValue = this .rs.getInt(2);
277: this .rs.updateInt(2, -3);
278: this .rs.cancelRowUpdates();
279:
280: int newValue = this .rs.getInt(2);
281: assertTrue("ResultSet.cancelRowUpdates() failed",
282: newValue == originalValue);
283:
284: // Now check refreshRow()
285: // Check cancelRowUpdates()
286: this .rs.absolute(1);
287: primaryKey = this .rs.getInt(1);
288: this .stmt
289: .executeUpdate("UPDATE UPDATABLE SET char_field='foo' WHERE pos1="
290: + primaryKey);
291: this .rs.refreshRow();
292: assertTrue("ResultSet.refreshRow failed", this .rs
293: .getString("char_field").equals("foo"));
294:
295: // Now check deleteRow()
296: this .rs.last();
297:
298: int oldLastRow = this .rs.getRow();
299: this .rs.deleteRow();
300: this .rs.last();
301: assertTrue("ResultSet.deleteRow() failed",
302: this .rs.getRow() == (oldLastRow - 1));
303: this .rs.close();
304:
305: /*
306: * FIXME: Move to regression
307: *
308: * scrollableStmt.executeUpdate("DROP TABLE IF EXISTS test");
309: * scrollableStmt.executeUpdate("CREATE TABLE test (ident INTEGER
310: * PRIMARY KEY, name TINYTEXT, expiry DATETIME default null)");
311: * scrollableStmt.executeUpdate("INSERT INTO test SET ident=1,
312: * name='original'");
313: *
314: * //Select to get a resultset to work on ResultSet this.rs =
315: * this.stmt.executeQuery("SELECT ident, name, expiry FROM test");
316: *
317: * //Check that the expiry field was null before we did our update
318: * this.rs.first();
319: *
320: * java.sql.Date before = this.rs.getDate("expiry");
321: *
322: * if (this.rs.wasNull()) { System.out.println("Expiry was correctly
323: * SQL null before update"); }
324: *
325: * //Update a different field this.rs.updateString("name",
326: * "Updated"); this.rs.updateRow();
327: *
328: * //Test to see if field has been altered java.sql.Date after =
329: * this.rs.getDate(3);
330: *
331: * if (this.rs.wasNull()) System.out.println("Bug disproved - expiry
332: * SQL null after update"); else System.out.println("Bug proved -
333: * expiry corrupted to '" + after + "'");
334: */
335: } finally {
336: if (scrollableStmt != null) {
337: try {
338: scrollableStmt.close();
339: } catch (SQLException sqlEx) {
340: ;
341: }
342: }
343: }
344: }
345:
346: private void createTestTable() throws SQLException {
347: //
348: // Catch the error, the table might exist
349: //
350: try {
351: this .stmt.executeUpdate("DROP TABLE UPDATABLE");
352: } catch (SQLException SQLE) {
353: ;
354: }
355:
356: this .stmt
357: .executeUpdate("CREATE TABLE UPDATABLE (pos1 int not null, pos2 int not null, char_field VARCHAR(32), PRIMARY KEY (pos1, pos2))");
358:
359: for (int i = 0; i < 100; i++) {
360: this .stmt.executeUpdate("INSERT INTO UPDATABLE VALUES ("
361: + i + ", " + i + ",'StringData" + i + "')");
362: }
363: }
364: }
|