Java > Open Source Codes > testsuite > regression > ResultSetRegressionTest


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.regression;
26
27 import com.mysql.jdbc.NonRegisteringDriver;
28 import com.mysql.jdbc.NotUpdatable;
29 import com.mysql.jdbc.SQLError;
30
31 import testsuite.BaseTestCase;
32
33 import java.io.Reader ;
34 import java.math.BigDecimal ;
35
36 import java.sql.Clob ;
37 import java.sql.Connection ;
38 import java.sql.DatabaseMetaData ;
39 import java.sql.PreparedStatement ;
40 import java.sql.ResultSet ;
41 import java.sql.ResultSetMetaData ;
42 import java.sql.SQLException ;
43 import java.sql.Statement ;
44 import java.sql.Timestamp ;
45
46 import java.util.ArrayList ;
47 import java.util.Calendar ;
48 import java.util.GregorianCalendar ;
49 import java.util.List ;
50 import java.util.Properties ;
51
52 /**
53  * Regression test cases for the ResultSet class.
54  *
55  * @author Mark Matthews
56  */
57 public class ResultSetRegressionTest extends BaseTestCase {
58     /**
59      * Creates a new ResultSetRegressionTest
60      *
61      * @param name
62      * the name of the test to run
63      */
64     public ResultSetRegressionTest(String name) {
65         super(name);
66     }
67
68     /**
69      * Runs all test cases in this test suite
70      *
71      * @param args
72      */
73     public static void main(String [] args) {
74         junit.textui.TestRunner.run(ResultSetRegressionTest.class);
75     }
76
77     /**
78      * Tests fix for BUG#???? -- Numeric types and server-side prepared
79      * statements incorrectly detect nulls.
80      *
81      * @throws Exception
82      * if the test fails
83      */
84     public void testBug2359() throws Exception {
85         try {
86             /*
87              * this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
88              * this.stmt.executeUpdate("CREATE TABLE testBug2359 (field1 INT)
89              * TYPE=InnoDB"); this.stmt.executeUpdate("INSERT INTO testBug2359
90              * VALUES (null), (1)");
91              *
92              * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM
93              * testBug2359 WHERE field1 IS NULL"); this.rs =
94              * this.pstmt.executeQuery();
95              *
96              * assertTrue(this.rs.next());
97              *
98              * assertTrue(this.rs.getByte(1) == 0);
99              * assertTrue(this.rs.wasNull());
100              *
101              * assertTrue(this.rs.getShort(1) == 0);
102              * assertTrue(this.rs.wasNull());
103              *
104              * assertTrue(this.rs.getInt(1) == 0);
105              * assertTrue(this.rs.wasNull());
106              *
107              * assertTrue(this.rs.getLong(1) == 0);
108              * assertTrue(this.rs.wasNull());
109              *
110              * assertTrue(this.rs.getFloat(1) == 0);
111              * assertTrue(this.rs.wasNull());
112              *
113              * assertTrue(this.rs.getDouble(1) == 0);
114              * assertTrue(this.rs.wasNull());
115              *
116              * assertTrue(this.rs.getBigDecimal(1) == null);
117              * assertTrue(this.rs.wasNull());
118              *
119              * this.rs.close();
120              *
121              * this.pstmt = this.conn.prepareStatement("SELECT max(field1) FROM
122              * testBug2359 WHERE field1 IS NOT NULL"); this.rs =
123              * this.pstmt.executeQuery(); assertTrue(this.rs.next());
124              *
125              * assertTrue(this.rs.getByte(1) == 1);
126              * assertTrue(!this.rs.wasNull());
127              *
128              * assertTrue(this.rs.getShort(1) == 1);
129              * assertTrue(!this.rs.wasNull());
130              *
131              * assertTrue(this.rs.getInt(1) == 1);
132              * assertTrue(!this.rs.wasNull());
133              *
134              * assertTrue(this.rs.getLong(1) == 1);
135              * assertTrue(!this.rs.wasNull());
136              *
137              * assertTrue(this.rs.getFloat(1) == 1);
138              * assertTrue(!this.rs.wasNull());
139              *
140              * assertTrue(this.rs.getDouble(1) == 1);
141              * assertTrue(!this.rs.wasNull());
142              *
143              * assertTrue(this.rs.getBigDecimal(1) != null);
144              * assertTrue(!this.rs.wasNull());
145              *
146              */
147             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
148             this.stmt
149                     .executeUpdate("CREATE TABLE testBug2359_1 (id INT) TYPE=InnoDB");
150             this.stmt.executeUpdate("INSERT INTO testBug2359_1 VALUES (1)");
151
152             this.pstmt = this.conn
153                     .prepareStatement("SELECT max(id) FROM testBug2359_1");
154             this.rs = this.pstmt.executeQuery();
155
156             if (this.rs.next()) {
157                 assertTrue(this.rs.getInt(1) != 0);
158                 this.rs.close();
159             }
160
161             this.rs.close();
162         } finally {
163             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
164             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
165
166             this.rs.close();
167             this.pstmt.close();
168         }
169     }
170
171     /**
172      * Tests fix for BUG#2643, ClassCastException when using this.rs.absolute()
173      * and server-side prepared statements.
174      *
175      * @throws Exception
176      */
177     public void testBug2623() throws Exception {
178         PreparedStatement pStmt = null;
179
180         try {
181             pStmt = this.conn
182                     .prepareStatement("SELECT NOW()",
183                             ResultSet.TYPE_SCROLL_SENSITIVE,
184                             ResultSet.CONCUR_READ_ONLY);
185
186             this.rs = pStmt.executeQuery();
187
188             this.rs.absolute(1);
189         } finally {
190             if (this.rs != null) {
191                 this.rs.close();
192             }
193
194             this.rs = null;
195
196             if (pStmt != null) {
197                 pStmt.close();
198             }
199         }
200     }
201
202     /**
203      * Tests fix for BUG#2654, "Column 'column.table' not found" when "order by"
204      * in query"
205      *
206      * @throws Exception
207      * if the test fails
208      */
209     public void testBug2654() throws Exception {
210         if (false) { // this is currently a server-level bug
211
212             try {
213                 this.stmt.executeUpdate("DROP TABLE IF EXISTS foo");
214                 this.stmt.executeUpdate("DROP TABLE IF EXISTS bar");
215
216                 this.stmt.executeUpdate("CREATE TABLE foo ("
217                         + " id tinyint(3) default NULL,"
218                         + " data varchar(255) default NULL"
219                         + ") TYPE=MyISAM DEFAULT CHARSET=latin1");
220                 this.stmt
221                         .executeUpdate("INSERT INTO foo VALUES (1,'male'),(2,'female')");
222
223                 this.stmt.executeUpdate("CREATE TABLE bar ("
224                         + "id tinyint(3) unsigned default NULL,"
225                         + "data char(3) default '0'"
226                         + ") TYPE=MyISAM DEFAULT CHARSET=latin1");
227
228                 this.stmt
229                         .executeUpdate("INSERT INTO bar VALUES (1,'yes'),(2,'no')");
230
231                 String statement = "select foo.id, foo.data, "
232                         + "bar.data from foo, bar" + " where "
233                         + "foo.id = bar.id order by foo.id";
234
235                 String column = "foo.data";
236
237                 this.rs = this.stmt.executeQuery(statement);
238
239                 ResultSetMetaData rsmd = this.rs.getMetaData();
240                 System.out.println(rsmd.getTableName(1));
241                 System.out.println(rsmd.getColumnName(1));
242
243                 this.rs.next();
244
245                 String fooData = this.rs.getString(column);
246             } finally {
247                 this.stmt.executeUpdate("DROP TABLE IF EXISTS foo");
248                 this.stmt.executeUpdate("DROP TABLE IF EXISTS bar");
249             }
250         }
251     }
252
253     /**
254      * Tests for fix to BUG#1130
255      *
256      * @throws Exception
257      * if the test fails
258      */
259     public void testClobTruncate() throws Exception {
260         try {
261             this.stmt.executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
262             this.stmt
263                     .executeUpdate("CREATE TABLE testClobTruncate (field1 TEXT)");
264             this.stmt
265                     .executeUpdate("INSERT INTO testClobTruncate VALUES ('abcdefg')");
266
267             this.rs = this.stmt.executeQuery("SELECT * FROM testClobTruncate");
268             this.rs.next();
269
270             Clob clob = this.rs.getClob(1);
271             clob.truncate(3);
272
273             Reader reader = clob.getCharacterStream();
274             char[] buf = new char[8];
275             int charsRead = reader.read(buf);
276
277             String clobAsString = new String (buf, 0, charsRead);
278
279             assertTrue(clobAsString.equals("abc"));
280         } finally {
281             this.stmt.executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
282         }
283     }
284
285     /**
286      * Tests that streaming result sets are registered correctly.
287      *
288      * @throws Exception
289      * if any errors occur
290      */
291     public void testClobberStreamingRS() throws Exception {
292         try {
293             Properties props = new Properties ();
294             props.setProperty("clobberStreamingResults", "true");
295
296             Connection clobberConn = getConnectionWithProps(props);
297
298             Statement clobberStmt = clobberConn.createStatement();
299
300             clobberStmt.executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
301             clobberStmt
302                     .executeUpdate("CREATE TABLE StreamingClobber ( DUMMYID "
303                             + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
304             clobberStmt
305                     .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
306             clobberStmt
307                     .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
308             clobberStmt
309                     .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
310             clobberStmt
311                     .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");
312
313             Statement streamStmt = null;
314
315             try {
316                 streamStmt = clobberConn.createStatement(
317                         java.sql.ResultSet.TYPE_FORWARD_ONLY,
318                         java.sql.ResultSet.CONCUR_READ_ONLY);
319                 streamStmt.setFetchSize(Integer.MIN_VALUE);
320
321                 this.rs = streamStmt.executeQuery("SELECT DUMMYID, DUMMYNAME "
322                         + "FROM StreamingClobber ORDER BY DUMMYID");
323
324                 this.rs.next();
325
326                 // This should proceed normally, after the driver
327 // clears the input stream
328 clobberStmt.executeQuery("SHOW VARIABLES");
329                 this.rs.close();
330             } finally {
331                 if (streamStmt != null) {
332                     streamStmt.close();
333                 }
334             }
335         } finally {
336             this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
337         }
338     }
339
340     /**
341      * DOCUMENT ME!
342      *
343      * @throws Exception
344      * DOCUMENT ME!
345      */
346     public void testEmptyResultSetGet() throws Exception {
347         try {
348             this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'foo'");
349             System.out.println(this.rs.getInt(1));
350         } catch (SQLException sqlEx) {
351             assertTrue("Correct exception not thrown",
352                     SQLError.SQL_STATE_GENERAL_ERROR
353                             .equals(sqlEx.getSQLState()));
354         }
355     }
356
357     /**
358      * Checks fix for BUG#1592 -- cross-database updatable result sets are not
359      * checked for updatability correctly.
360      *
361      * @throws Exception
362      * if the test fails.
363      */
364     public void testFixForBug1592() throws Exception {
365         if (versionMeetsMinimum(4, 1)) {
366             Statement updatableStmt = this.conn
367                     .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
368                             ResultSet.CONCUR_UPDATABLE);
369
370             try {
371                 updatableStmt.execute("SELECT * FROM mysql.user");
372
373                 this.rs = updatableStmt.getResultSet();
374             } catch (SQLException sqlEx) {
375                 String message = sqlEx.getMessage();
376
377                 if ((message != null) && (message.indexOf("denied") != -1)) {
378                     System.err
379                             .println("WARN: Can't complete testFixForBug1592(), access to"
380                                     + " 'mysql' database not allowed");
381                 } else {
382                     throw sqlEx;
383                 }
384             }
385         }
386     }
387
388     /**
389      * Tests fix for BUG#2006, where 2 columns with same name in a result set
390      * are returned via findColumn() in the wrong order...The JDBC spec states,
391      * that the _first_ matching column should be returned.
392      *
393      * @throws Exception
394      * if the test fails
395      */
396     public void testFixForBug2006() throws Exception {
397         try {
398             this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
399             this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
400             this.stmt
401                     .executeUpdate("CREATE TABLE testFixForBug2006_1 (key_field INT NOT NULL)");
402             this.stmt
403                     .executeUpdate("CREATE TABLE testFixForBug2006_2 (key_field INT NULL)");
404             this.stmt
405                     .executeUpdate("INSERT INTO testFixForBug2006_1 VALUES (1)");
406
407             this.rs = this.stmt
408                     .executeQuery("SELECT testFixForBug2006_1.key_field, testFixForBug2006_2.key_field FROM testFixForBug2006_1 LEFT JOIN testFixForBug2006_2 USING(key_field)");
409
410             ResultSetMetaData rsmd = this.rs.getMetaData();
411
412             assertTrue(rsmd.getColumnName(1).equals(rsmd.getColumnName(2)));
413             assertTrue(rsmd.isNullable(this.rs.findColumn("key_field")) == ResultSetMetaData.columnNoNulls);
414             assertTrue(rsmd.isNullable(2) == ResultSetMetaData.columnNullable);
415             assertTrue(this.rs.next());
416             assertTrue(this.rs.getObject(1) != null);
417             assertTrue(this.rs.getObject(2) == null);
418         } finally {
419             if (this.rs != null) {
420                 try {
421                     this.rs.close();
422                 } catch (SQLException sqlEx) {
423                     // ignore
424 }
425
426                 this.rs = null;
427             }
428
429             this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
430             this.stmt.executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
431         }
432     }
433
434     /**
435      * Tests that ResultSet.getLong() does not truncate values.
436      *
437      * @throws Exception
438      * if any errors occur
439      */
440     public void testGetLongBug() throws Exception {
441         this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
442         this.stmt
443                 .executeUpdate("CREATE TABLE IF NOT EXISTS getLongBug (int_col int, bigint_col bigint)");
444
445         int intVal = 123456;
446         long longVal1 = 123456789012345678L;
447         long longVal2 = -2079305757640172711L;
448         this.stmt.executeUpdate("INSERT INTO getLongBug "
449                 + "(int_col, bigint_col) " + "VALUES (" + intVal + ", "
450                 + longVal1 + "), " + "(" + intVal + ", " + longVal2 + ")");
451
452         try {
453             this.rs = this.stmt
454                     .executeQuery("SELECT int_col, bigint_col FROM getLongBug ORDER BY bigint_col DESC");
455             this.rs.next();
456             assertTrue(
457                     "Values not decoded correctly",
458                     ((this.rs.getInt(1) == intVal) && (this.rs.getLong(2) == longVal1)));
459             this.rs.next();
460             assertTrue(
461                     "Values not decoded correctly",
462                     ((this.rs.getInt(1) == intVal) && (this.rs.getLong(2) == longVal2)));
463         } finally {
464             if (this.rs != null) {
465                 try {
466                     this.rs.close();
467                 } catch (Exception ex) {
468                     // ignore
469 }
470             }
471
472             this.stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
473         }
474     }
475
476     /**
477      * DOCUMENT ME!
478      *
479      * @throws Exception
480      * DOCUMENT ME!
481      */
482     public void testGetTimestampWithDate() throws Exception {
483         try {
484             this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
485             this.stmt.executeUpdate("CREATE TABLE testGetTimestamp (d date)");
486             this.stmt
487                     .executeUpdate("INSERT INTO testGetTimestamp values (now())");
488
489             this.rs = this.stmt.executeQuery("SELECT * FROM testGetTimestamp");
490             this.rs.next();
491             System.out.println(this.rs.getTimestamp(1));
492         } finally {
493             this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
494         }
495     }
496
497     /**
498      * Tests a bug where ResultSet.isBefireFirst() would return true when the
499      * result set was empty (which is incorrect)
500      *
501      * @throws Exception
502      * if an error occurs.
503      */
504     public void testIsBeforeFirstOnEmpty() throws Exception {
505         try {
506             // Query with valid rows: isBeforeFirst() correctly returns True
507 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
508             assertTrue("Non-empty search should return true", this.rs
509                     .isBeforeFirst());
510
511             // Query with empty result: isBeforeFirst() falsely returns True
512 // Sun's documentation says it should return false
513 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'garbage'");
514             assertTrue("Empty search should return false ", !this.rs
515                     .isBeforeFirst());
516         } finally {
517             this.rs.close();
518         }
519     }
520
521     /**
522      * Tests a bug where ResultSet.isBefireFirst() would return true when the
523      * result set was empty (which is incorrect)
524      *
525      * @throws Exception
526      * if an error occurs.
527      */
528     public void testMetaDataIsWritable() throws Exception {
529         try {
530             // Query with valid rows
531 this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'version'");
532
533             ResultSetMetaData rsmd = this.rs.getMetaData();
534
535             int numColumns = rsmd.getColumnCount();
536
537             for (int i = 1; i <= numColumns; i++) {
538                 assertTrue("rsmd.isWritable() should != rsmd.isReadOnly()",
539                         rsmd.isWritable(i) != rsmd.isReadOnly(i));
540             }
541         } finally {
542             this.rs.close();
543         }
544     }
545
546     /**
547      * Tests fix for bug # 496
548      *
549      * @throws Exception
550      * if an error happens.
551      */
552     public void testNextAndPrevious() throws Exception {
553         try {
554             this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
555             this.stmt
556                     .executeUpdate("CREATE TABLE testNextAndPrevious (field1 int)");
557             this.stmt
558                     .executeUpdate("INSERT INTO testNextAndPrevious VALUES (1)");
559
560             this.rs = this.stmt
561                     .executeQuery("SELECT * from testNextAndPrevious");
562
563             System.out.println("Currently at row " + this.rs.getRow());
564             this.rs.next();
565             System.out.println("Value at row " + this.rs.getRow() + " is "
566                     + this.rs.getString(1));
567
568             this.rs.previous();
569
570             try {
571                 System.out.println("Value at row " + this.rs.getRow() + " is "
572                         + this.rs.getString(1));
573                 fail("Should not be able to retrieve values with invalid cursor");
574             } catch (SQLException sqlEx) {
575                 assertTrue(sqlEx.getMessage().startsWith("Before start"));
576             }
577
578             this.rs.next();
579
580             this.rs.next();
581
582             try {
583                 System.out.println("Value at row " + this.rs.getRow() + " is "
584                         + this.rs.getString(1));
585                 fail("Should not be able to retrieve values with invalid cursor");
586             } catch (SQLException sqlEx) {
587                 assertTrue(sqlEx.getMessage().startsWith("After end"));
588             }
589         } finally {
590             this.stmt.executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
591         }
592     }
593
594     /**
595      * Tests fix for BUG#1630 (not updatable exception turning into NPE on
596      * second updateFoo() method call.
597      *
598      * @throws Exception
599      * if an unexpected exception is thrown.
600      */
601     public void testNotUpdatable() throws Exception {
602         this.rs = null;
603
604         try {
605             String sQuery = "SHOW VARIABLES";
606             this.pstmt = this.conn
607                     .prepareStatement(sQuery, ResultSet.TYPE_SCROLL_SENSITIVE,
608                             ResultSet.CONCUR_UPDATABLE);
609
610             this.rs = this.pstmt.executeQuery();
611
612             if (this.rs.next()) {
613                 this.rs.absolute(1);
614
615                 try {
616                     this.rs.updateInt(1, 1);
617                 } catch (SQLException sqlEx) {
618                     assertTrue(sqlEx instanceof NotUpdatable);
619                 }
620
621                 try {
622                     this.rs.updateString(1, "1");
623                 } catch (SQLException sqlEx) {
624                     assertTrue(sqlEx instanceof NotUpdatable);
625                 }
626             }
627         } finally {
628             if (this.pstmt != null) {
629                 try {
630                     this.pstmt.close();
631                 } catch (Exception e) {
632                     // ignore
633 }
634             }
635         }
636     }
637
638     /**
639      * Tests that streaming result sets are registered correctly.
640      *
641      * @throws Exception
642      * if any errors occur
643      */
644     public void testStreamingRegBug() throws Exception {
645         try {
646             this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
647             this.stmt
648                     .executeUpdate("CREATE TABLE StreamingRegBug ( DUMMYID "
649                             + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
650             this.stmt
651                     .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
652             this.stmt
653                     .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
654             this.stmt
655                     .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
656             this.stmt
657                     .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");
658
659             PreparedStatement streamStmt = null;
660
661             try {
662                 streamStmt = this.conn.prepareStatement(
663                         "SELECT DUMMYID, DUMMYNAME "
664                                 + "FROM StreamingRegBug ORDER BY DUMMYID",
665                         java.sql.ResultSet.TYPE_FORWARD_ONLY,
666                         java.sql.ResultSet.CONCUR_READ_ONLY);
667                 streamStmt.setFetchSize(Integer.MIN_VALUE);
668
669                 this.rs = streamStmt.executeQuery();
670
671                 while (this.rs.next()) {
672                     this.rs.getString(1);
673                 }
674
675                 this.rs.close(); // error occurs here
676 } catch (SQLException sqlEx) {
677
678             } finally {
679                 if (streamStmt != null) {
680                     try {
681                         streamStmt.close();
682                     } catch (SQLException exWhileClose) {
683                         exWhileClose.printStackTrace();
684                     }
685                 }
686             }
687         } finally {
688             this.stmt.executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
689         }
690     }
691
692     /**
693      * Tests that result sets can be updated when all parameters are correctly
694      * set.
695      *
696      * @throws Exception
697      * if any errors occur
698      */
699     public void testUpdatability() throws Exception {
700         this.rs = null;
701
702         this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
703         this.stmt.execute("CREATE TABLE IF NOT EXISTS updatabilityBug ("
704                 + " id int(10) unsigned NOT NULL auto_increment,"
705                 + " field1 varchar(32) NOT NULL default '',"
706                 + " field2 varchar(128) NOT NULL default '',"
707                 + " field3 varchar(128) default NULL,"
708                 + " field4 varchar(128) default NULL,"
709                 + " field5 varchar(64) default NULL,"
710                 + " field6 int(10) unsigned default NULL,"
711                 + " field7 varchar(64) default NULL," + " PRIMARY KEY (id)"
712                 + ") TYPE=InnoDB;");
713         this.stmt.executeUpdate("insert into updatabilityBug (id) values (1)");
714
715         try {
716             String sQuery = " SELECT * FROM updatabilityBug WHERE id = ? ";
717             this.pstmt = this.conn
718                     .prepareStatement(sQuery, ResultSet.TYPE_SCROLL_SENSITIVE,
719                             ResultSet.CONCUR_UPDATABLE);
720             this.conn.setAutoCommit(false);
721             this.pstmt.setInt(1, 1);
722             this.rs = this.pstmt.executeQuery();
723
724             if (this.rs.next()) {
725                 this.rs.absolute(1);
726                 this.rs.updateInt("id", 1);
727                 this.rs.updateString("field1", "1");
728                 this.rs.updateString("field2", "1");
729                 this.rs.updateString("field3", "1");
730                 this.rs.updateString("field4", "1");
731                 this.rs.updateString("field5", "1");
732                 this.rs.updateInt("field6", 1);
733                 this.rs.updateString("field7", "1");
734                 this.rs.updateRow();
735             }
736
737             this.conn.commit();
738             this.conn.setAutoCommit(true);
739         } finally {
740             if (this.pstmt != null) {
741                 try {
742                     this.pstmt.close();
743                 } catch (Exception e) {
744                     // ignore
745 }
746             }
747
748             this.stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
749         }
750     }
751
752     /**
753      * Test fixes for BUG#1071
754      *
755      * @throws Exception
756      * if the test fails.
757      */
758     public void testUpdatabilityAndEscaping() throws Exception {
759         Properties props = new Properties ();
760         props.setProperty("useUnicode", "true");
761         props.setProperty("characterEncoding", "big5");
762
763         Connection updConn = getConnectionWithProps(props);
764         Statement updStmt = updConn.createStatement(
765                 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
766
767         try {
768             updStmt
769                     .executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
770             updStmt
771                     .executeUpdate("CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
772             updStmt
773                     .executeUpdate("INSERT INTO testUpdatesWithEscaping VALUES (1, null)");
774
775             String stringToUpdate = "\" \\ '";
776
777             this.rs = updStmt
778                     .executeQuery("SELECT * from testUpdatesWithEscaping");
779
780             t