0001: /*
0002: Copyright (C) 2002-2007 MySQL AB
0003:
0004: This program is free software; you can redistribute it and/or modify
0005: it under the terms of version 2 of the GNU General Public License as
0006: published by the Free Software Foundation.
0007:
0008: There are special exceptions to the terms and conditions of the GPL
0009: as it is applied to this software. View the full text of the
0010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
0011: software distribution.
0012:
0013: This program is distributed in the hope that it will be useful,
0014: but WITHOUT ANY WARRANTY; without even the implied warranty of
0015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0016: GNU General Public License for more details.
0017:
0018: You should have received a copy of the GNU General Public License
0019: along with this program; if not, write to the Free Software
0020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0021:
0022:
0023:
0024: */
0025: package testsuite.regression;
0026:
0027: import java.io.Reader;
0028: import java.lang.reflect.Array;
0029: import java.lang.reflect.InvocationTargetException;
0030: import java.lang.reflect.Method;
0031: import java.math.BigDecimal;
0032: import java.sql.CallableStatement;
0033: import java.sql.Clob;
0034: import java.sql.Connection;
0035: import java.sql.DatabaseMetaData;
0036: import java.sql.Date;
0037: import java.sql.PreparedStatement;
0038: import java.sql.ResultSet;
0039: import java.sql.ResultSetMetaData;
0040: import java.sql.SQLException;
0041: import java.sql.Statement;
0042: import java.sql.Timestamp;
0043: import java.sql.Types;
0044: import java.util.ArrayList;
0045: import java.util.Calendar;
0046: import java.util.GregorianCalendar;
0047: import java.util.List;
0048: import java.util.Locale;
0049: import java.util.Properties;
0050: import java.util.TimeZone;
0051:
0052: import testsuite.BaseTestCase;
0053:
0054: import com.mysql.jdbc.Messages;
0055: import com.mysql.jdbc.MysqlDataTruncation;
0056: import com.mysql.jdbc.NotUpdatable;
0057: import com.mysql.jdbc.SQLError;
0058: import com.mysql.jdbc.StringUtils;
0059: import com.mysql.jdbc.Util;
0060: import com.mysql.jdbc.log.StandardLogger;
0061:
0062: /**
0063: * Regression test cases for the ResultSet class.
0064: *
0065: * @author Mark Matthews
0066: */
0067: public class ResultSetRegressionTest extends BaseTestCase {
0068: /**
0069: * Creates a new ResultSetRegressionTest
0070: *
0071: * @param name
0072: * the name of the test to run
0073: */
0074: public ResultSetRegressionTest(String name) {
0075: super (name);
0076: }
0077:
0078: /**
0079: * Runs all test cases in this test suite
0080: *
0081: * @param args
0082: */
0083: public static void main(String[] args) {
0084: junit.textui.TestRunner.run(ResultSetRegressionTest.class);
0085: }
0086:
0087: /**
0088: * Tests fix for BUG#???? -- Numeric types and server-side prepared
0089: * statements incorrectly detect nulls.
0090: *
0091: * @throws Exception
0092: * if the test fails
0093: */
0094: public void testBug2359() throws Exception {
0095: try {
0096: /*
0097: * this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
0098: * this.stmt.executeUpdate("CREATE TABLE testBug2359 (field1 INT)
0099: * TYPE=InnoDB"); this.stmt.executeUpdate("INSERT INTO testBug2359
0100: * VALUES (null), (1)");
0101: *
0102: * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM
0103: * testBug2359 WHERE field1 IS NULL"); this.rs =
0104: * this.pstmt.executeQuery();
0105: *
0106: * assertTrue(this.rs.next());
0107: *
0108: * assertTrue(this.rs.getByte(1) == 0);
0109: * assertTrue(this.rs.wasNull());
0110: *
0111: * assertTrue(this.rs.getShort(1) == 0);
0112: * assertTrue(this.rs.wasNull());
0113: *
0114: * assertTrue(this.rs.getInt(1) == 0);
0115: * assertTrue(this.rs.wasNull());
0116: *
0117: * assertTrue(this.rs.getLong(1) == 0);
0118: * assertTrue(this.rs.wasNull());
0119: *
0120: * assertTrue(this.rs.getFloat(1) == 0);
0121: * assertTrue(this.rs.wasNull());
0122: *
0123: * assertTrue(this.rs.getDouble(1) == 0);
0124: * assertTrue(this.rs.wasNull());
0125: *
0126: * assertTrue(this.rs.getBigDecimal(1) == null);
0127: * assertTrue(this.rs.wasNull());
0128: *
0129: * this.rs.close();
0130: *
0131: * this.pstmt = this.conn.prepareStatement("SELECT max(field1) FROM
0132: * testBug2359 WHERE field1 IS NOT NULL"); this.rs =
0133: * this.pstmt.executeQuery(); assertTrue(this.rs.next());
0134: *
0135: * assertTrue(this.rs.getByte(1) == 1);
0136: * assertTrue(!this.rs.wasNull());
0137: *
0138: * assertTrue(this.rs.getShort(1) == 1);
0139: * assertTrue(!this.rs.wasNull());
0140: *
0141: * assertTrue(this.rs.getInt(1) == 1);
0142: * assertTrue(!this.rs.wasNull());
0143: *
0144: * assertTrue(this.rs.getLong(1) == 1);
0145: * assertTrue(!this.rs.wasNull());
0146: *
0147: * assertTrue(this.rs.getFloat(1) == 1);
0148: * assertTrue(!this.rs.wasNull());
0149: *
0150: * assertTrue(this.rs.getDouble(1) == 1);
0151: * assertTrue(!this.rs.wasNull());
0152: *
0153: * assertTrue(this.rs.getBigDecimal(1) != null);
0154: * assertTrue(!this.rs.wasNull());
0155: *
0156: */
0157: this .stmt
0158: .executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
0159: this .stmt
0160: .executeUpdate("CREATE TABLE testBug2359_1 (id INT) TYPE=InnoDB");
0161: this .stmt
0162: .executeUpdate("INSERT INTO testBug2359_1 VALUES (1)");
0163:
0164: this .pstmt = this .conn
0165: .prepareStatement("SELECT max(id) FROM testBug2359_1");
0166: this .rs = this .pstmt.executeQuery();
0167:
0168: if (this .rs.next()) {
0169: assertTrue(this .rs.getInt(1) != 0);
0170: this .rs.close();
0171: }
0172:
0173: this .rs.close();
0174: } finally {
0175: this .stmt
0176: .executeUpdate("DROP TABLE IF EXISTS testBug2359_1");
0177: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug2359");
0178:
0179: this .rs.close();
0180: this .pstmt.close();
0181: }
0182: }
0183:
0184: /**
0185: * Tests fix for BUG#2643, ClassCastException when using this.rs.absolute()
0186: * and server-side prepared statements.
0187: *
0188: * @throws Exception
0189: */
0190: public void testBug2623() throws Exception {
0191: PreparedStatement pStmt = null;
0192:
0193: try {
0194: pStmt = this .conn.prepareStatement("SELECT NOW()",
0195: ResultSet.TYPE_SCROLL_SENSITIVE,
0196: ResultSet.CONCUR_READ_ONLY);
0197:
0198: this .rs = pStmt.executeQuery();
0199:
0200: this .rs.absolute(1);
0201: } finally {
0202: if (this .rs != null) {
0203: this .rs.close();
0204: }
0205:
0206: this .rs = null;
0207:
0208: if (pStmt != null) {
0209: pStmt.close();
0210: }
0211: }
0212: }
0213:
0214: /**
0215: * Tests fix for BUG#2654, "Column 'column.table' not found" when "order by"
0216: * in query"
0217: *
0218: * @throws Exception
0219: * if the test fails
0220: */
0221: public void testBug2654() throws Exception {
0222: if (false) { // this is currently a server-level bug
0223:
0224: try {
0225: this .stmt.executeUpdate("DROP TABLE IF EXISTS foo");
0226: this .stmt.executeUpdate("DROP TABLE IF EXISTS bar");
0227:
0228: this .stmt.executeUpdate("CREATE TABLE foo ("
0229: + " id tinyint(3) default NULL,"
0230: + " data varchar(255) default NULL"
0231: + ") TYPE=MyISAM DEFAULT CHARSET=latin1");
0232: this .stmt
0233: .executeUpdate("INSERT INTO foo VALUES (1,'male'),(2,'female')");
0234:
0235: this .stmt.executeUpdate("CREATE TABLE bar ("
0236: + "id tinyint(3) unsigned default NULL,"
0237: + "data char(3) default '0'"
0238: + ") TYPE=MyISAM DEFAULT CHARSET=latin1");
0239:
0240: this .stmt
0241: .executeUpdate("INSERT INTO bar VALUES (1,'yes'),(2,'no')");
0242:
0243: String statement = "select foo.id, foo.data, "
0244: + "bar.data from foo, bar" + " where "
0245: + "foo.id = bar.id order by foo.id";
0246:
0247: String column = "foo.data";
0248:
0249: this .rs = this .stmt.executeQuery(statement);
0250:
0251: ResultSetMetaData rsmd = this .rs.getMetaData();
0252: System.out.println(rsmd.getTableName(1));
0253: System.out.println(rsmd.getColumnName(1));
0254:
0255: this .rs.next();
0256:
0257: String fooData = this .rs.getString(column);
0258: } finally {
0259: this .stmt.executeUpdate("DROP TABLE IF EXISTS foo");
0260: this .stmt.executeUpdate("DROP TABLE IF EXISTS bar");
0261: }
0262: }
0263: }
0264:
0265: /**
0266: * Tests for fix to BUG#1130
0267: *
0268: * @throws Exception
0269: * if the test fails
0270: */
0271: public void testClobTruncate() throws Exception {
0272: if (isRunningOnJdk131()) {
0273: return; // test not valid on JDK-1.3.1
0274: }
0275:
0276: try {
0277: this .stmt
0278: .executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
0279: this .stmt
0280: .executeUpdate("CREATE TABLE testClobTruncate (field1 TEXT)");
0281: this .stmt
0282: .executeUpdate("INSERT INTO testClobTruncate VALUES ('abcdefg')");
0283:
0284: this .rs = this .stmt
0285: .executeQuery("SELECT * FROM testClobTruncate");
0286: this .rs.next();
0287:
0288: Clob clob = this .rs.getClob(1);
0289: clob.truncate(3);
0290:
0291: Reader reader = clob.getCharacterStream();
0292: char[] buf = new char[8];
0293: int charsRead = reader.read(buf);
0294:
0295: String clobAsString = new String(buf, 0, charsRead);
0296:
0297: assertTrue(clobAsString.equals("abc"));
0298: } finally {
0299: this .stmt
0300: .executeUpdate("DROP TABLE IF EXISTS testClobTruncate");
0301: }
0302: }
0303:
0304: /**
0305: * Tests that streaming result sets are registered correctly.
0306: *
0307: * @throws Exception
0308: * if any errors occur
0309: */
0310: public void testClobberStreamingRS() throws Exception {
0311: try {
0312: Properties props = new Properties();
0313: props.setProperty("clobberStreamingResults", "true");
0314:
0315: Connection clobberConn = getConnectionWithProps(props);
0316:
0317: Statement clobberStmt = clobberConn.createStatement();
0318:
0319: clobberStmt
0320: .executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
0321: clobberStmt
0322: .executeUpdate("CREATE TABLE StreamingClobber ( DUMMYID "
0323: + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
0324: clobberStmt
0325: .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
0326: clobberStmt
0327: .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
0328: clobberStmt
0329: .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
0330: clobberStmt
0331: .executeUpdate("INSERT INTO StreamingClobber (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");
0332:
0333: Statement streamStmt = null;
0334:
0335: try {
0336: streamStmt = clobberConn.createStatement(
0337: java.sql.ResultSet.TYPE_FORWARD_ONLY,
0338: java.sql.ResultSet.CONCUR_READ_ONLY);
0339: streamStmt.setFetchSize(Integer.MIN_VALUE);
0340:
0341: this .rs = streamStmt
0342: .executeQuery("SELECT DUMMYID, DUMMYNAME "
0343: + "FROM StreamingClobber ORDER BY DUMMYID");
0344:
0345: this .rs.next();
0346:
0347: // This should proceed normally, after the driver
0348: // clears the input stream
0349: clobberStmt.executeQuery("SHOW VARIABLES");
0350: this .rs.close();
0351: } finally {
0352: if (streamStmt != null) {
0353: streamStmt.close();
0354: }
0355: }
0356: } finally {
0357: this .stmt
0358: .executeUpdate("DROP TABLE IF EXISTS StreamingClobber");
0359: }
0360: }
0361:
0362: /**
0363: * DOCUMENT ME!
0364: *
0365: * @throws Exception
0366: * DOCUMENT ME!
0367: */
0368: public void testEmptyResultSetGet() throws Exception {
0369: try {
0370: this .rs = this .stmt
0371: .executeQuery("SHOW VARIABLES LIKE 'foo'");
0372: System.out.println(this .rs.getInt(1));
0373: } catch (SQLException sqlEx) {
0374: assertTrue("Correct exception not thrown",
0375: SQLError.SQL_STATE_GENERAL_ERROR.equals(sqlEx
0376: .getSQLState()));
0377: }
0378: }
0379:
0380: /**
0381: * Checks fix for BUG#1592 -- cross-database updatable result sets are not
0382: * checked for updatability correctly.
0383: *
0384: * @throws Exception
0385: * if the test fails.
0386: */
0387: public void testFixForBug1592() throws Exception {
0388: if (versionMeetsMinimum(4, 1)) {
0389: Statement updatableStmt = this .conn.createStatement(
0390: ResultSet.TYPE_SCROLL_SENSITIVE,
0391: ResultSet.CONCUR_UPDATABLE);
0392:
0393: try {
0394: updatableStmt.execute("SELECT * FROM mysql.user");
0395:
0396: this .rs = updatableStmt.getResultSet();
0397: } catch (SQLException sqlEx) {
0398: String message = sqlEx.getMessage();
0399:
0400: if ((message != null)
0401: && (message.indexOf("denied") != -1)) {
0402: System.err
0403: .println("WARN: Can't complete testFixForBug1592(), access to"
0404: + " 'mysql' database not allowed");
0405: } else {
0406: throw sqlEx;
0407: }
0408: }
0409: }
0410: }
0411:
0412: /**
0413: * Tests fix for BUG#2006, where 2 columns with same name in a result set
0414: * are returned via findColumn() in the wrong order...The JDBC spec states,
0415: * that the _first_ matching column should be returned.
0416: *
0417: * @throws Exception
0418: * if the test fails
0419: */
0420: public void testFixForBug2006() throws Exception {
0421: try {
0422: this .stmt
0423: .executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
0424: this .stmt
0425: .executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
0426: this .stmt
0427: .executeUpdate("CREATE TABLE testFixForBug2006_1 (key_field INT NOT NULL)");
0428: this .stmt
0429: .executeUpdate("CREATE TABLE testFixForBug2006_2 (key_field INT NULL)");
0430: this .stmt
0431: .executeUpdate("INSERT INTO testFixForBug2006_1 VALUES (1)");
0432:
0433: this .rs = this .stmt
0434: .executeQuery("SELECT testFixForBug2006_1.key_field, testFixForBug2006_2.key_field FROM testFixForBug2006_1 LEFT JOIN testFixForBug2006_2 USING(key_field)");
0435:
0436: ResultSetMetaData rsmd = this .rs.getMetaData();
0437:
0438: assertTrue(rsmd.getColumnName(1).equals(
0439: rsmd.getColumnName(2)));
0440: assertTrue(rsmd.isNullable(this .rs.findColumn("key_field")) == ResultSetMetaData.columnNoNulls);
0441: assertTrue(rsmd.isNullable(2) == ResultSetMetaData.columnNullable);
0442: assertTrue(this .rs.next());
0443: assertTrue(this .rs.getObject(1) != null);
0444: assertTrue(this .rs.getObject(2) == null);
0445: } finally {
0446: if (this .rs != null) {
0447: try {
0448: this .rs.close();
0449: } catch (SQLException sqlEx) {
0450: // ignore
0451: }
0452:
0453: this .rs = null;
0454: }
0455:
0456: this .stmt
0457: .executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_1");
0458: this .stmt
0459: .executeUpdate("DROP TABLE IF EXISTS testFixForBug2006_2");
0460: }
0461: }
0462:
0463: /**
0464: * Tests that ResultSet.getLong() does not truncate values.
0465: *
0466: * @throws Exception
0467: * if any errors occur
0468: */
0469: public void testGetLongBug() throws Exception {
0470: this .stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
0471: this .stmt
0472: .executeUpdate("CREATE TABLE IF NOT EXISTS getLongBug (int_col int, bigint_col bigint)");
0473:
0474: int intVal = 123456;
0475: long longVal1 = 123456789012345678L;
0476: long longVal2 = -2079305757640172711L;
0477: this .stmt.executeUpdate("INSERT INTO getLongBug "
0478: + "(int_col, bigint_col) " + "VALUES (" + intVal + ", "
0479: + longVal1 + "), " + "(" + intVal + ", " + longVal2
0480: + ")");
0481:
0482: try {
0483: this .rs = this .stmt
0484: .executeQuery("SELECT int_col, bigint_col FROM getLongBug ORDER BY bigint_col DESC");
0485: this .rs.next();
0486: assertTrue("Values not decoded correctly",
0487: ((this .rs.getInt(1) == intVal) && (this .rs
0488: .getLong(2) == longVal1)));
0489: this .rs.next();
0490: assertTrue("Values not decoded correctly",
0491: ((this .rs.getInt(1) == intVal) && (this .rs
0492: .getLong(2) == longVal2)));
0493: } finally {
0494: if (this .rs != null) {
0495: try {
0496: this .rs.close();
0497: } catch (Exception ex) {
0498: // ignore
0499: }
0500: }
0501:
0502: this .stmt.executeUpdate("DROP TABLE IF EXISTS getLongBug");
0503: }
0504: }
0505:
0506: /**
0507: * DOCUMENT ME!
0508: *
0509: * @throws Exception
0510: * DOCUMENT ME!
0511: */
0512: public void testGetTimestampWithDate() throws Exception {
0513: try {
0514: this .stmt
0515: .executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
0516: this .stmt
0517: .executeUpdate("CREATE TABLE testGetTimestamp (d date)");
0518: this .stmt
0519: .executeUpdate("INSERT INTO testGetTimestamp values (now())");
0520:
0521: this .rs = this .stmt
0522: .executeQuery("SELECT * FROM testGetTimestamp");
0523: this .rs.next();
0524: System.out.println(this .rs.getTimestamp(1));
0525: } finally {
0526: this .stmt
0527: .executeUpdate("DROP TABLE IF EXISTS testGetTimestamp");
0528: }
0529: }
0530:
0531: /**
0532: * Tests a bug where ResultSet.isBefireFirst() would return true when the
0533: * result set was empty (which is incorrect)
0534: *
0535: * @throws Exception
0536: * if an error occurs.
0537: */
0538: public void testIsBeforeFirstOnEmpty() throws Exception {
0539: try {
0540: // Query with valid rows: isBeforeFirst() correctly returns True
0541: this .rs = this .stmt
0542: .executeQuery("SHOW VARIABLES LIKE 'version'");
0543: assertTrue("Non-empty search should return true", this .rs
0544: .isBeforeFirst());
0545:
0546: // Query with empty result: isBeforeFirst() falsely returns True
0547: // Sun's documentation says it should return false
0548: this .rs = this .stmt
0549: .executeQuery("SHOW VARIABLES LIKE 'garbage'");
0550: assertTrue("Empty search should return false ", !this .rs
0551: .isBeforeFirst());
0552: } finally {
0553: this .rs.close();
0554: }
0555: }
0556:
0557: /**
0558: * Tests a bug where ResultSet.isBefireFirst() would return true when the
0559: * result set was empty (which is incorrect)
0560: *
0561: * @throws Exception
0562: * if an error occurs.
0563: */
0564: public void testMetaDataIsWritable() throws Exception {
0565: try {
0566: // Query with valid rows
0567: this .rs = this .stmt
0568: .executeQuery("SHOW VARIABLES LIKE 'version'");
0569:
0570: ResultSetMetaData rsmd = this .rs.getMetaData();
0571:
0572: int numColumns = rsmd.getColumnCount();
0573:
0574: for (int i = 1; i <= numColumns; i++) {
0575: assertTrue(
0576: "rsmd.isWritable() should != rsmd.isReadOnly()",
0577: rsmd.isWritable(i) != rsmd.isReadOnly(i));
0578: }
0579: } finally {
0580: this .rs.close();
0581: }
0582: }
0583:
0584: /**
0585: * Tests fix for bug # 496
0586: *
0587: * @throws Exception
0588: * if an error happens.
0589: */
0590: public void testNextAndPrevious() throws Exception {
0591: try {
0592: this .stmt
0593: .executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
0594: this .stmt
0595: .executeUpdate("CREATE TABLE testNextAndPrevious (field1 int)");
0596: this .stmt
0597: .executeUpdate("INSERT INTO testNextAndPrevious VALUES (1)");
0598:
0599: this .rs = this .stmt
0600: .executeQuery("SELECT * from testNextAndPrevious");
0601:
0602: System.out.println("Currently at row " + this .rs.getRow());
0603: this .rs.next();
0604: System.out.println("Value at row " + this .rs.getRow()
0605: + " is " + this .rs.getString(1));
0606:
0607: this .rs.previous();
0608:
0609: try {
0610: System.out.println("Value at row " + this .rs.getRow()
0611: + " is " + this .rs.getString(1));
0612: fail("Should not be able to retrieve values with invalid cursor");
0613: } catch (SQLException sqlEx) {
0614: assertTrue(sqlEx.getMessage()
0615: .startsWith("Before start"));
0616: }
0617:
0618: this .rs.next();
0619:
0620: this .rs.next();
0621:
0622: try {
0623: System.out.println("Value at row " + this .rs.getRow()
0624: + " is " + this .rs.getString(1));
0625: fail("Should not be able to retrieve values with invalid cursor");
0626: } catch (SQLException sqlEx) {
0627: assertTrue(sqlEx.getMessage().startsWith("After end"));
0628: }
0629: } finally {
0630: this .stmt
0631: .executeUpdate("DROP TABLE IF EXISTS testNextAndPrevious");
0632: }
0633: }
0634:
0635: /**
0636: * Tests fix for BUG#1630 (not updatable exception turning into NPE on
0637: * second updateFoo() method call.
0638: *
0639: * @throws Exception
0640: * if an unexpected exception is thrown.
0641: */
0642: public void testNotUpdatable() throws Exception {
0643: this .rs = null;
0644:
0645: try {
0646: String sQuery = "SHOW VARIABLES";
0647: this .pstmt = this .conn.prepareStatement(sQuery,
0648: ResultSet.TYPE_SCROLL_SENSITIVE,
0649: ResultSet.CONCUR_UPDATABLE);
0650:
0651: this .rs = this .pstmt.executeQuery();
0652:
0653: if (this .rs.next()) {
0654: this .rs.absolute(1);
0655:
0656: try {
0657: this .rs.updateInt(1, 1);
0658: } catch (SQLException sqlEx) {
0659: assertTrue(sqlEx instanceof NotUpdatable);
0660: }
0661:
0662: try {
0663: this .rs.updateString(1, "1");
0664: } catch (SQLException sqlEx) {
0665: assertTrue(sqlEx instanceof NotUpdatable);
0666: }
0667: }
0668: } finally {
0669: if (this .pstmt != null) {
0670: try {
0671: this .pstmt.close();
0672: } catch (Exception e) {
0673: // ignore
0674: }
0675: }
0676: }
0677: }
0678:
0679: /**
0680: * Tests that streaming result sets are registered correctly.
0681: *
0682: * @throws Exception
0683: * if any errors occur
0684: */
0685: public void testStreamingRegBug() throws Exception {
0686: try {
0687: this .stmt
0688: .executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
0689: this .stmt
0690: .executeUpdate("CREATE TABLE StreamingRegBug ( DUMMYID "
0691: + " INTEGER NOT NULL, DUMMYNAME VARCHAR(32),PRIMARY KEY (DUMMYID) )");
0692: this .stmt
0693: .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (0, NULL)");
0694: this .stmt
0695: .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (1, 'nro 1')");
0696: this .stmt
0697: .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (2, 'nro 2')");
0698: this .stmt
0699: .executeUpdate("INSERT INTO StreamingRegBug (DUMMYID, DUMMYNAME) VALUES (3, 'nro 3')");
0700:
0701: PreparedStatement streamStmt = null;
0702:
0703: try {
0704: streamStmt = this .conn
0705: .prepareStatement(
0706: "SELECT DUMMYID, DUMMYNAME "
0707: + "FROM StreamingRegBug ORDER BY DUMMYID",
0708: java.sql.ResultSet.TYPE_FORWARD_ONLY,
0709: java.sql.ResultSet.CONCUR_READ_ONLY);
0710: streamStmt.setFetchSize(Integer.MIN_VALUE);
0711:
0712: this .rs = streamStmt.executeQuery();
0713:
0714: while (this .rs.next()) {
0715: this .rs.getString(1);
0716: }
0717:
0718: this .rs.close(); // error occurs here
0719: } catch (SQLException sqlEx) {
0720:
0721: } finally {
0722: if (streamStmt != null) {
0723: try {
0724: streamStmt.close();
0725: } catch (SQLException exWhileClose) {
0726: exWhileClose.printStackTrace();
0727: }
0728: }
0729: }
0730: } finally {
0731: this .stmt
0732: .executeUpdate("DROP TABLE IF EXISTS StreamingRegBug");
0733: }
0734: }
0735:
0736: /**
0737: * Tests that result sets can be updated when all parameters are correctly
0738: * set.
0739: *
0740: * @throws Exception
0741: * if any errors occur
0742: */
0743: public void testUpdatability() throws Exception {
0744: this .rs = null;
0745:
0746: this .stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
0747: this .stmt
0748: .execute("CREATE TABLE IF NOT EXISTS updatabilityBug ("
0749: + " id int(10) unsigned NOT NULL auto_increment,"
0750: + " field1 varchar(32) NOT NULL default '',"
0751: + " field2 varchar(128) NOT NULL default '',"
0752: + " field3 varchar(128) default NULL,"
0753: + " field4 varchar(128) default NULL,"
0754: + " field5 varchar(64) default NULL,"
0755: + " field6 int(10) unsigned default NULL,"
0756: + " field7 varchar(64) default NULL,"
0757: + " PRIMARY KEY (id)" + ") TYPE=InnoDB;");
0758: this .stmt
0759: .executeUpdate("insert into updatabilityBug (id) values (1)");
0760:
0761: try {
0762: String sQuery = " SELECT * FROM updatabilityBug WHERE id = ? ";
0763: this .pstmt = this .conn.prepareStatement(sQuery,
0764: ResultSet.TYPE_SCROLL_SENSITIVE,
0765: ResultSet.CONCUR_UPDATABLE);
0766: this .conn.setAutoCommit(false);
0767: this .pstmt.setInt(1, 1);
0768: this .rs = this .pstmt.executeQuery();
0769:
0770: if (this .rs.next()) {
0771: this .rs.absolute(1);
0772: this .rs.updateInt("id", 1);
0773: this .rs.updateString("field1", "1");
0774: this .rs.updateString("field2", "1");
0775: this .rs.updateString("field3", "1");
0776: this .rs.updateString("field4", "1");
0777: this .rs.updateString("field5", "1");
0778: this .rs.updateInt("field6", 1);
0779: this .rs.updateString("field7", "1");
0780: this .rs.updateRow();
0781: }
0782:
0783: this .conn.commit();
0784: this .conn.setAutoCommit(true);
0785: } finally {
0786: if (this .pstmt != null) {
0787: try {
0788: this .pstmt.close();
0789: } catch (Exception e) {
0790: // ignore
0791: }
0792: }
0793:
0794: this .stmt.execute("DROP TABLE IF EXISTS updatabilityBug");
0795: }
0796: }
0797:
0798: /**
0799: * Test fixes for BUG#1071
0800: *
0801: * @throws Exception
0802: * if the test fails.
0803: */
0804: public void testUpdatabilityAndEscaping() throws Exception {
0805: Properties props = new Properties();
0806: props.setProperty("useUnicode", "true");
0807: props.setProperty("characterEncoding", "big5");
0808:
0809: Connection updConn = getConnectionWithProps(props);
0810: Statement updStmt = updConn.createStatement(
0811: ResultSet.TYPE_SCROLL_INSENSITIVE,
0812: ResultSet.CONCUR_UPDATABLE);
0813:
0814: try {
0815: updStmt
0816: .executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
0817: updStmt
0818: .executeUpdate("CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
0819: updStmt
0820: .executeUpdate("INSERT INTO testUpdatesWithEscaping VALUES (1, null)");
0821:
0822: String stringToUpdate = "\" \\ '";
0823:
0824: this .rs = updStmt
0825: .executeQuery("SELECT * from testUpdatesWithEscaping");
0826:
0827: this .rs.next();
0828: this .rs.updateString(2, stringToUpdate);
0829: this .rs.updateRow();
0830:
0831: assertTrue(stringToUpdate.equals(this .rs.getString(2)));
0832: } finally {
0833: updStmt
0834: .executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
0835: updStmt.close();
0836: updConn.close();
0837: }
0838: }
0839:
0840: /**
0841: * Tests the fix for BUG#661 ... refreshRow() fails when primary key values
0842: * have escaped data in them.
0843: *
0844: * @throws Exception
0845: * if an error occurs
0846: */
0847: public void testUpdatabilityWithQuotes() throws Exception {
0848: Statement updStmt = null;
0849:
0850: try {
0851: this .stmt
0852: .executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");
0853: this .stmt
0854: .executeUpdate("CREATE TABLE testUpdWithQuotes (keyField CHAR(32) PRIMARY KEY NOT NULL, field2 int)");
0855:
0856: PreparedStatement pStmt = this .conn
0857: .prepareStatement("INSERT INTO testUpdWithQuotes VALUES (?, ?)");
0858: pStmt.setString(1, "Abe's");
0859: pStmt.setInt(2, 1);
0860: pStmt.executeUpdate();
0861:
0862: updStmt = this .conn.createStatement(
0863: ResultSet.TYPE_SCROLL_SENSITIVE,
0864: ResultSet.CONCUR_UPDATABLE);
0865:
0866: this .rs = updStmt
0867: .executeQuery("SELECT * FROM testUpdWithQuotes");
0868: this .rs.next();
0869: this .rs.updateInt(2, 2);
0870: this .rs.updateRow();
0871: } finally {
0872: this .stmt
0873: .executeUpdate("DROP TABLE IF EXISTS testUpdWithQuotes");
0874:
0875: if (this .rs != null) {
0876: this .rs.close();
0877: }
0878:
0879: this .rs = null;
0880:
0881: if (updStmt != null) {
0882: updStmt.close();
0883: }
0884:
0885: updStmt = null;
0886: }
0887: }
0888:
0889: /**
0890: * Checks whether or not ResultSet.updateClob() is implemented
0891: *
0892: * @throws Exception
0893: * if the test fails
0894: */
0895: public void testUpdateClob() throws Exception {
0896: if (isRunningOnJdk131()) {
0897: return; // test not valid on JDK-1.3.1
0898: }
0899:
0900: Statement updatableStmt = this .conn.createStatement(
0901: ResultSet.TYPE_SCROLL_SENSITIVE,
0902: ResultSet.CONCUR_UPDATABLE);
0903:
0904: try {
0905: this .stmt
0906: .executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
0907: this .stmt
0908: .executeUpdate("CREATE TABLE testUpdateClob(intField INT NOT NULL PRIMARY KEY, clobField TEXT)");
0909: this .stmt
0910: .executeUpdate("INSERT INTO testUpdateClob VALUES (1, 'foo')");
0911:
0912: this .rs = updatableStmt
0913: .executeQuery("SELECT intField, clobField FROM testUpdateClob");
0914: this .rs.next();
0915:
0916: Clob clob = this .rs.getClob(2);
0917:
0918: clob.setString(1, "bar");
0919:
0920: this .rs.updateClob(2, clob);
0921: this .rs.updateRow();
0922:
0923: this .rs.moveToInsertRow();
0924:
0925: clob.setString(1, "baz");
0926: this .rs.updateInt(1, 2);
0927: this .rs.updateClob(2, clob);
0928: this .rs.insertRow();
0929:
0930: clob.setString(1, "bat");
0931: this .rs.updateInt(1, 3);
0932: this .rs.updateClob(2, clob);
0933: this .rs.insertRow();
0934:
0935: this .rs.close();
0936:
0937: this .rs = this .stmt
0938: .executeQuery("SELECT intField, clobField FROM testUpdateClob ORDER BY intField");
0939:
0940: this .rs.next();
0941: assertTrue((this .rs.getInt(1) == 1)
0942: && this .rs.getString(2).equals("bar"));
0943:
0944: this .rs.next();
0945: assertTrue((this .rs.getInt(1) == 2)
0946: && this .rs.getString(2).equals("baz"));
0947:
0948: this .rs.next();
0949: assertTrue((this .rs.getInt(1) == 3)
0950: && this .rs.getString(2).equals("bat"));
0951: } finally {
0952: this .stmt
0953: .executeUpdate("DROP TABLE IF EXISTS testUpdateClob");
0954: }
0955: }
0956:
0957: /**
0958: * Tests fix for BUG#4482, ResultSet.getObject() returns wrong type for
0959: * strings when using prepared statements.
0960: *
0961: * @throws Exception
0962: * if the test fails.
0963: */
0964: public void testBug4482() throws Exception {
0965: this .rs = this .conn.prepareStatement("SELECT 'abcdef'")
0966: .executeQuery();
0967: assertTrue(this .rs.next());
0968: assertTrue(this .rs.getObject(1) instanceof String);
0969: }
0970:
0971: /**
0972: * Test fix for BUG#4689 - WasNull not getting set correctly for binary
0973: * result sets.
0974: */
0975: public void testBug4689() throws Exception {
0976: try {
0977: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4689");
0978: this .stmt
0979: .executeUpdate("CREATE TABLE testBug4689 (tinyintField tinyint, tinyintFieldNull tinyint, "
0980: + "intField int, intFieldNull int, "
0981: + "bigintField bigint, bigintFieldNull bigint, "
0982: + "shortField smallint, shortFieldNull smallint, "
0983: + "doubleField double, doubleFieldNull double)");
0984:
0985: this .stmt
0986: .executeUpdate("INSERT INTO testBug4689 VALUES (1, null, "
0987: + "1, null, "
0988: + "1, null, "
0989: + "1, null, "
0990: + "1, null)");
0991:
0992: PreparedStatement pStmt = this .conn
0993: .prepareStatement("SELECT tinyintField, tinyintFieldNull,"
0994: + "intField, intFieldNull, "
0995: + "bigintField, bigintFieldNull, "
0996: + "shortField, shortFieldNull, "
0997: + "doubleField, doubleFieldNull FROM testBug4689");
0998: this .rs = pStmt.executeQuery();
0999: assertTrue(this .rs.next());
1000:
1001: assertTrue(this .rs.getByte(1) == 1);
1002: assertTrue(this .rs.wasNull() == false);
1003: assertTrue(this .rs.getByte(2) == 0);
1004: assertTrue(this .rs.wasNull() == true);
1005:
1006: assertTrue(this .rs.getInt(3) == 1);
1007: assertTrue(this .rs.wasNull() == false);
1008: assertTrue(this .rs.getInt(4) == 0);
1009: assertTrue(this .rs.wasNull() == true);
1010:
1011: assertTrue(this .rs.getInt(5) == 1);
1012: assertTrue(this .rs.wasNull() == false);
1013: assertTrue(this .rs.getInt(6) == 0);
1014: assertTrue(this .rs.wasNull() == true);
1015:
1016: assertTrue(this .rs.getShort(7) == 1);
1017: assertTrue(this .rs.wasNull() == false);
1018: assertTrue(this .rs.getShort(8) == 0);
1019: assertTrue(this .rs.wasNull() == true);
1020:
1021: assertTrue(this .rs.getDouble(9) == 1);
1022: assertTrue(this .rs.wasNull() == false);
1023: assertTrue(this .rs.getDouble(10) == 0);
1024: assertTrue(this .rs.wasNull() == true);
1025: } finally {
1026: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4689");
1027: }
1028: }
1029:
1030: /**
1031: * Tests fix for BUG#5032 -- ResultSet.getObject() doesn't return type
1032: * Boolean for pseudo-bit types from prepared statements on 4.1.x.
1033: *
1034: * @throws Exception
1035: * if the test fails.
1036: */
1037: public void testBug5032() throws Exception {
1038: if (versionMeetsMinimum(4, 1)) {
1039: PreparedStatement pStmt = null;
1040:
1041: try {
1042: this .stmt
1043: .executeUpdate("DROP TABLE IF EXISTS testBug5032");
1044: this .stmt
1045: .executeUpdate("CREATE TABLE testBug5032(field1 BIT)");
1046: this .stmt
1047: .executeUpdate("INSERT INTO testBug5032 VALUES (1)");
1048:
1049: pStmt = this .conn
1050: .prepareStatement("SELECT field1 FROM testBug5032");
1051: this .rs = pStmt.executeQuery();
1052: assertTrue(this .rs.next());
1053: assertTrue(this .rs.getObject(1) instanceof Boolean);
1054: } finally {
1055: this .stmt
1056: .executeUpdate("DROP TABLE IF EXISTS testBug5032");
1057:
1058: if (pStmt != null) {
1059: pStmt.close();
1060: }
1061: }
1062: }
1063: }
1064:
1065: /**
1066: * Tests fix for BUG#5069 -- ResultSet.getMetaData() should not return
1067: * incorrectly-initialized metadata if the result set has been closed, but
1068: * should instead throw a SQLException. Also tests fix for getRow() and
1069: * getWarnings() and traversal methods.
1070: *
1071: * @throws Exception
1072: * if the test fails.
1073: */
1074: public void testBug5069() throws Exception {
1075: try {
1076: this .rs = this .stmt.executeQuery("SELECT 1");
1077: this .rs.close();
1078:
1079: try {
1080: ResultSetMetaData md = this .rs.getMetaData();
1081: } catch (NullPointerException npEx) {
1082: fail("Should not catch NullPointerException here");
1083: } catch (SQLException sqlEx) {
1084: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1085: .equals(sqlEx.getSQLState()));
1086: }
1087:
1088: try {
1089: this .rs.getRow();
1090: } catch (NullPointerException npEx) {
1091: fail("Should not catch NullPointerException here");
1092: } catch (SQLException sqlEx) {
1093: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1094: .equals(sqlEx.getSQLState()));
1095: }
1096:
1097: try {
1098: this .rs.getWarnings();
1099: } catch (NullPointerException npEx) {
1100: fail("Should not catch NullPointerException here");
1101: } catch (SQLException sqlEx) {
1102: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1103: .equals(sqlEx.getSQLState()));
1104: }
1105:
1106: try {
1107: this .rs.first();
1108: } catch (NullPointerException npEx) {
1109: fail("Should not catch NullPointerException here");
1110: } catch (SQLException sqlEx) {
1111: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1112: .equals(sqlEx.getSQLState()));
1113: }
1114:
1115: try {
1116: this .rs.beforeFirst();
1117: } catch (NullPointerException npEx) {
1118: fail("Should not catch NullPointerException here");
1119: } catch (SQLException sqlEx) {
1120: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1121: .equals(sqlEx.getSQLState()));
1122: }
1123:
1124: try {
1125: this .rs.last();
1126: } catch (NullPointerException npEx) {
1127: fail("Should not catch NullPointerException here");
1128: } catch (SQLException sqlEx) {
1129: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1130: .equals(sqlEx.getSQLState()));
1131: }
1132:
1133: try {
1134: this .rs.afterLast();
1135: } catch (NullPointerException npEx) {
1136: fail("Should not catch NullPointerException here");
1137: } catch (SQLException sqlEx) {
1138: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1139: .equals(sqlEx.getSQLState()));
1140: }
1141:
1142: try {
1143: this .rs.relative(0);
1144: } catch (NullPointerException npEx) {
1145: fail("Should not catch NullPointerException here");
1146: } catch (SQLException sqlEx) {
1147: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1148: .equals(sqlEx.getSQLState()));
1149: }
1150:
1151: try {
1152: this .rs.next();
1153: } catch (NullPointerException npEx) {
1154: fail("Should not catch NullPointerException here");
1155: } catch (SQLException sqlEx) {
1156: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1157: .equals(sqlEx.getSQLState()));
1158: }
1159:
1160: try {
1161: this .rs.previous();
1162: } catch (NullPointerException npEx) {
1163: fail("Should not catch NullPointerException here");
1164: } catch (SQLException sqlEx) {
1165: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1166: .equals(sqlEx.getSQLState()));
1167: }
1168:
1169: try {
1170: this .rs.isBeforeFirst();
1171: } catch (NullPointerException npEx) {
1172: fail("Should not catch NullPointerException here");
1173: } catch (SQLException sqlEx) {
1174: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1175: .equals(sqlEx.getSQLState()));
1176: }
1177:
1178: try {
1179: this .rs.isFirst();
1180: } catch (NullPointerException npEx) {
1181: fail("Should not catch NullPointerException here");
1182: } catch (SQLException sqlEx) {
1183: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1184: .equals(sqlEx.getSQLState()));
1185: }
1186:
1187: try {
1188: this .rs.isAfterLast();
1189: } catch (NullPointerException npEx) {
1190: fail("Should not catch NullPointerException here");
1191: } catch (SQLException sqlEx) {
1192: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1193: .equals(sqlEx.getSQLState()));
1194: }
1195:
1196: try {
1197: this .rs.isLast();
1198: } catch (NullPointerException npEx) {
1199: fail("Should not catch NullPointerException here");
1200: } catch (SQLException sqlEx) {
1201: assertTrue(SQLError.SQL_STATE_GENERAL_ERROR
1202: .equals(sqlEx.getSQLState()));
1203: }
1204: } finally {
1205: if (this .rs != null) {
1206: this .rs.close();
1207: this .rs = null;
1208: }
1209: }
1210: }
1211:
1212: /**
1213: * Tests for BUG#5235, ClassCastException on all-zero date field when
1214: * zeroDatetimeBehavior is 'convertToNull'...however it appears that this
1215: * bug doesn't exist. This is a placeholder until we get more data from the
1216: * user on how they provoke this bug to happen.
1217: *
1218: * @throws Exception
1219: * if the test fails.
1220: */
1221: public void testBug5235() throws Exception {
1222: try {
1223: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
1224: this .stmt
1225: .executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
1226: this .stmt
1227: .executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");
1228:
1229: Properties props = new Properties();
1230: props.setProperty("zeroDateTimeBehavior", "convertToNull");
1231:
1232: Connection nullConn = getConnectionWithProps(props);
1233:
1234: this .rs = nullConn.createStatement().executeQuery(
1235: "SELECT field1 FROM testBug5235");
1236: this .rs.next();
1237: assertTrue(null == this .rs.getObject(1));
1238: } finally {
1239: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
1240: }
1241: }
1242:
1243: /**
1244: * Tests for BUG#5136, GEOMETRY types getting corrupted, turns out to be a
1245: * server bug.
1246: *
1247: * @throws Exception
1248: * if the test fails.
1249: */
1250: public void testBug5136() throws Exception {
1251: if (false) {
1252: PreparedStatement toGeom = this .conn
1253: .prepareStatement("select GeomFromText(?)");
1254: PreparedStatement toText = this .conn
1255: .prepareStatement("select AsText(?)");
1256:
1257: String inText = "POINT(146.67596278 -36.54368233)";
1258:
1259: // First assert that the problem is not at the server end
1260: this .rs = this .stmt
1261: .executeQuery("select AsText(GeomFromText('"
1262: + inText + "'))");
1263: this .rs.next();
1264:
1265: String outText = this .rs.getString(1);
1266: this .rs.close();
1267: assertTrue("Server side only\n In: " + inText + "\nOut: "
1268: + outText, inText.equals(outText));
1269:
1270: // Now bring a binary geometry object to the client and send it back
1271: toGeom.setString(1, inText);
1272: this .rs = toGeom.executeQuery();
1273: this .rs.next();
1274:
1275: // Return a binary geometry object from the WKT
1276: Object geom = this .rs.getObject(1);
1277: this .rs.close();
1278: toText.setObject(1, geom);
1279: this .rs = toText.executeQuery();
1280: this .rs.next();
1281:
1282: // Return WKT from the binary geometry
1283: outText = this .rs.getString(1);
1284: this .rs.close();
1285: assertTrue("Server to client and back\n In: " + inText
1286: + "\nOut: " + outText, inText.equals(outText));
1287: }
1288: }
1289:
1290: /**
1291: * Tests fix for BUG#5664, ResultSet.updateByte() when on insert row throws
1292: * ArrayOutOfBoundsException.
1293: *
1294: * @throws Exception
1295: * if the test fails.
1296: */
1297: public void testBug5664() throws Exception {
1298: try {
1299: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5664");
1300: this .stmt
1301: .executeUpdate("CREATE TABLE testBug5664 (pkfield int PRIMARY KEY NOT NULL, field1 SMALLINT)");
1302: this .stmt
1303: .executeUpdate("INSERT INTO testBug5664 VALUES (1, 1)");
1304:
1305: Statement updatableStmt = this .conn.createStatement(
1306: ResultSet.TYPE_SCROLL_SENSITIVE,
1307: ResultSet.CONCUR_UPDATABLE);
1308:
1309: this .rs = updatableStmt
1310: .executeQuery("SELECT pkfield, field1 FROM testBug5664");
1311: this .rs.next();
1312: this .rs.moveToInsertRow();
1313: this .rs.updateInt(1, 2);
1314: this .rs.updateByte(2, (byte) 2);
1315: } finally {
1316: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5664");
1317: }
1318: }
1319:
1320: public void testBogusTimestampAsString() throws Exception {
1321:
1322: this .rs = this .stmt
1323: .executeQuery("SELECT '2004-08-13 13:21:17.'");
1324:
1325: this .rs.next();
1326:
1327: // We're only checking for an exception being thrown here as the bug
1328: this .rs.getTimestamp(1);
1329:
1330: }
1331:
1332: /**
1333: * Tests our ability to reject NaN and +/- INF in
1334: * PreparedStatement.setDouble();
1335: */
1336: public void testBug5717() throws Exception {
1337: try {
1338: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5717");
1339: this .stmt
1340: .executeUpdate("CREATE TABLE testBug5717 (field1 DOUBLE)");
1341: this .pstmt = this .conn
1342: .prepareStatement("INSERT INTO testBug5717 VALUES (?)");
1343:
1344: try {
1345: this .pstmt.setDouble(1, Double.NEGATIVE_INFINITY);
1346: fail("Exception should've been thrown");
1347: } catch (Exception ex) {
1348: // expected
1349: }
1350:
1351: try {
1352: this .pstmt.setDouble(1, Double.POSITIVE_INFINITY);
1353: fail("Exception should've been thrown");
1354: } catch (Exception ex) {
1355: // expected
1356: }
1357:
1358: try {
1359: this .pstmt.setDouble(1, Double.NaN);
1360: fail("Exception should've been thrown");
1361: } catch (Exception ex) {
1362: // expected
1363: }
1364: } finally {
1365: if (this .pstmt != null) {
1366: this .pstmt.close();
1367: }
1368:
1369: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5717");
1370: }
1371: }
1372:
1373: /**
1374: * Tests fix for server issue that drops precision on aggregate operations
1375: * on DECIMAL types, because they come back as DOUBLEs.
1376: *
1377: * @throws Exception
1378: * if the test fails.
1379: */
1380: public void testBug6537() throws Exception {
1381: if (versionMeetsMinimum(4, 1, 0)) {
1382: String tableName = "testBug6537";
1383:
1384: try {
1385: createTable(
1386: tableName,
1387: "(`id` int(11) NOT NULL default '0',"
1388: + "`value` decimal(10,2) NOT NULL default '0.00', `stringval` varchar(10),"
1389: + "PRIMARY KEY (`id`)"
1390: + ") ENGINE=MyISAM DEFAULT CHARSET=latin1");
1391: this .stmt
1392: .executeUpdate("INSERT INTO "
1393: + tableName
1394: + "(id, value, stringval) VALUES (1, 100.00, '100.00'), (2, 200, '200')");
1395:
1396: String sql = "SELECT SUM(value) as total FROM "
1397: + tableName + " WHERE id = ? ";
1398: PreparedStatement pStmt = this .conn
1399: .prepareStatement(sql);
1400: pStmt.setInt(1, 1);
1401: this .rs = pStmt.executeQuery();
1402: assertTrue(this .rs.next());
1403:
1404: assertTrue("100.00".equals(this .rs.getBigDecimal(
1405: "total").toString()));
1406:
1407: sql = "SELECT stringval as total FROM " + tableName
1408: + " WHERE id = ? ";
1409: pStmt = this .conn.prepareStatement(sql);
1410: pStmt.setInt(1, 2);
1411: this .rs = pStmt.executeQuery();
1412: assertTrue(this .rs.next());
1413:
1414: assertTrue("200.00".equals(this .rs.getBigDecimal(
1415: "total", 2).toString()));
1416:
1417: } finally {
1418: dropTable(tableName);
1419: }
1420: }
1421: }
1422:
1423: /**
1424: * Tests fix for BUG#6231, ResultSet.getTimestamp() on a column with TIME in
1425: * it fails.
1426: *
1427: * @throws Exception
1428: * if the test fails.
1429: */
1430: public void testBug6231() throws Exception {
1431:
1432: try {
1433: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
1434: this .stmt
1435: .executeUpdate("CREATE TABLE testBug6231 (field1 TIME)");
1436: this .stmt
1437: .executeUpdate("INSERT INTO testBug6231 VALUES ('09:16:00')");
1438:
1439: this .rs = this .stmt
1440: .executeQuery("SELECT field1 FROM testBug6231");
1441: this .rs.next();
1442: long asMillis = this .rs.getTimestamp(1).getTime();
1443: Calendar cal = Calendar.getInstance();
1444:
1445: if (isRunningOnJdk131()) {
1446: cal.setTime(new Date(asMillis));
1447: } else {
1448: cal.setTimeInMillis(asMillis);
1449: }
1450:
1451: assertEquals(9, cal.get(Calendar.HOUR));
1452: assertEquals(16, cal.get(Calendar.MINUTE));
1453: assertEquals(0, cal.get(Calendar.SECOND));
1454: } finally {
1455: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug6231");
1456: }
1457: }
1458:
1459: public void testBug6619() throws Exception {
1460: try {
1461: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
1462: this .stmt
1463: .executeUpdate("CREATE TABLE testBug6619 (field1 int)");
1464: this .stmt
1465: .executeUpdate("INSERT INTO testBug6619 VALUES (1), (2)");
1466:
1467: PreparedStatement pStmt = this .conn
1468: .prepareStatement("SELECT SUM(field1) FROM testBug6619");
1469:
1470: this .rs = pStmt.executeQuery();
1471: this .rs.next();
1472: System.out.println(this .rs.getString(1));
1473:
1474: } finally {
1475: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug6619");
1476: }
1477: }
1478:
1479: public void testBug6743() throws Exception {
1480: // 0x835C U+30BD # KATAKANA LETTER SO
1481: String katakanaStr = "\u30BD";
1482:
1483: Properties props = new Properties();
1484:
1485: props.setProperty("useUnicode", "true");
1486: props.setProperty("characterEncoding", "SJIS");
1487:
1488: Connection sjisConn = null;
1489: Statement sjisStmt = null;
1490:
1491: try {
1492: sjisConn = getConnectionWithProps(props);
1493: sjisStmt = sjisConn.createStatement(
1494: ResultSet.TYPE_SCROLL_INSENSITIVE,
1495: ResultSet.CONCUR_UPDATABLE);
1496:
1497: sjisStmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
1498: StringBuffer queryBuf = new StringBuffer(
1499: "CREATE TABLE testBug6743 (pkField INT NOT NULL PRIMARY KEY, field1 VARCHAR(32)");
1500:
1501: if (versionMeetsMinimum(4, 1)) {
1502: queryBuf.append(" CHARACTER SET SJIS");
1503: }
1504:
1505: queryBuf.append(")");
1506: sjisStmt.executeUpdate(queryBuf.toString());
1507: sjisStmt
1508: .executeUpdate("INSERT INTO testBug6743 VALUES (1, 'abc')");
1509:
1510: this .rs = sjisStmt
1511: .executeQuery("SELECT pkField, field1 FROM testBug6743");
1512: this .rs.next();
1513: this .rs.updateString(2, katakanaStr);
1514: this .rs.updateRow();
1515:
1516: String retrString = this .rs.getString(2);
1517: assertTrue(katakanaStr.equals(retrString));
1518:
1519: this .rs = sjisStmt
1520: .executeQuery("SELECT pkField, field1 FROM testBug6743");
1521: this .rs.next();
1522:
1523: retrString = this .rs.getString(2);
1524: assertTrue(katakanaStr.equals(retrString));
1525: } finally {
1526: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
1527:
1528: if (sjisStmt != null) {
1529: sjisStmt.close();
1530: }
1531:
1532: if (sjisConn != null) {
1533: sjisConn.close();
1534: }
1535: }
1536: }
1537:
1538: /**
1539: * Tests for presence of BUG#6561, NPE thrown when dealing with 0 dates and
1540: * non-unpacked result sets.
1541: *
1542: * @throws Exception
1543: * if the test occurs.
1544: */
1545: public void testBug6561() throws Exception {
1546:
1547: try {
1548: Properties props = new Properties();
1549: props.setProperty("zeroDateTimeBehavior", "convertToNull");
1550:
1551: Connection zeroConn = getConnectionWithProps(props);
1552:
1553: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug6561");
1554: this .stmt
1555: .executeUpdate("CREATE TABLE testBug6561 (ofield int, field1 DATE, field2 integer, field3 integer)");
1556: this .stmt
1557: .executeUpdate("INSERT INTO testBug6561 (ofield, field1,field2,field3) VALUES (1, 0,NULL,0)");
1558: this .stmt
1559: .executeUpdate("INSERT INTO testBug6561 (ofield, field1,field2,field3) VALUES (2, '2004-11-20',NULL,0)");
1560:
1561: PreparedStatement ps = zeroConn
1562: .prepareStatement("SELECT field1,field2,field3 FROM testBug6561 ORDER BY ofield");
1563: this .rs = ps.executeQuery();
1564:
1565: assertTrue(this .rs.next());
1566: assertTrue(null == this .rs.getObject("field1"));
1567: assertTrue(null == this .rs.getObject("field2"));
1568: assertTrue(0 == this .rs.getInt("field3"));
1569:
1570: assertTrue(this .rs.next());
1571: assertEquals("2004-11-20", this .rs.getString("field1"));
1572: assertTrue(null == this .rs.getObject("field2"));
1573: assertTrue(0 == this .rs.getInt("field3"));
1574:
1575: ps.close();
1576: } finally {
1577: this .stmt.executeUpdate("DROP TABLE IF EXISTS test");
1578: }
1579: }
1580:
1581: public void testBug7686() throws SQLException {
1582: String tableName = "testBug7686";
1583: createTable(tableName, "(id1 int(10) unsigned NOT NULL,"
1584: + " id2 DATETIME, "
1585: + " field1 varchar(128) NOT NULL default '',"
1586: + " PRIMARY KEY (id1, id2)) TYPE=InnoDB;");
1587:
1588: this .stmt.executeUpdate("insert into " + tableName
1589: + " (id1, id2, field1)"
1590: + " values (1, '2005-01-05 13:59:20', 'foo')");
1591:
1592: String sQuery = " SELECT * FROM " + tableName
1593: + " WHERE id1 = ? AND id2 = ?";
1594: this .pstmt = this .conn.prepareStatement(sQuery,
1595: ResultSet.TYPE_SCROLL_SENSITIVE,
1596: ResultSet.CONCUR_UPDATABLE);
1597:
1598: this .conn.setAutoCommit(false);
1599: this .pstmt.setInt(1, 1);
1600: GregorianCalendar cal = new GregorianCalendar();
1601: cal.clear();
1602: cal.set(2005, 00, 05, 13, 59, 20);
1603:
1604: Timestamp jan5before2pm = null;
1605:
1606: if (isRunningOnJdk131()) {
1607: jan5before2pm = new java.sql.Timestamp(cal.getTime()
1608: .getTime());
1609: } else {
1610: jan5before2pm = new java.sql.Timestamp(cal
1611: .getTimeInMillis());
1612: }
1613:
1614: this .pstmt.setTimestamp(2, jan5before2pm);
1615: this .rs = this .pstmt.executeQuery();
1616: assertTrue(this .rs.next());
1617: this .rs.absolute(1);
1618: this .rs.updateString("field1", "bar");
1619: this .rs.updateRow();
1620: this .conn.commit();
1621: this .conn.setAutoCommit(true);
1622: }
1623:
1624: /**
1625: * Tests fix for BUG#7715 - Timestamps converted incorrectly to strings with
1626: * SSPS and Upd. Result Sets.
1627: *
1628: * @throws Exception
1629: * if the test fails.
1630: */
1631: public void testBug7715() throws Exception {
1632: PreparedStatement pStmt = null;
1633:
1634: try {
1635: this .stmt
1636: .executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
1637: this .stmt
1638: .executeUpdate("CREATE TABLE testConvertedBinaryTimestamp (field1 VARCHAR(32), field2 VARCHAR(32), field3 VARCHAR(32), field4 TIMESTAMP)");
1639: this .stmt
1640: .executeUpdate("INSERT INTO testConvertedBinaryTimestamp VALUES ('abc', 'def', 'ghi', NOW())");
1641:
1642: pStmt = this .conn
1643: .prepareStatement(
1644: "SELECT field1, field2, field3, field4 FROM testConvertedBinaryTimestamp",
1645: ResultSet.TYPE_SCROLL_SENSITIVE,
1646: ResultSet.CONCUR_UPDATABLE);
1647:
1648: this .rs = pStmt.executeQuery();
1649: assertTrue(this .rs.next());
1650:
1651: this .rs.getObject(4); // fails if bug exists
1652: } finally {
1653: this .stmt
1654: .executeUpdate("DROP TABLE IF EXISTS testConvertedBinaryTimestamp");
1655: }
1656: }
1657:
1658: /**
1659: * Tests fix for BUG#8428 - getString() doesn't maintain format stored on
1660: * server.
1661: *
1662: * @throws Exception
1663: * if the test fails.
1664: */
1665: public void testBug8428() throws Exception {
1666: Connection noSyncConn = null;
1667:
1668: try {
1669: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
1670: this .stmt
1671: .executeUpdate("CREATE TABLE testBug8428 (field1 YEAR, field2 DATETIME)");
1672: this .stmt
1673: .executeUpdate("INSERT INTO testBug8428 VALUES ('1999', '2005-02-11 12:54:41')");
1674:
1675: Properties props = new Properties();
1676: props.setProperty("noDatetimeStringSync", "true");
1677: props.setProperty("useUsageAdvisor", "true");
1678: props.setProperty("yearIsDateType", "false"); // for 3.1.9+
1679:
1680: noSyncConn = getConnectionWithProps(props);
1681:
1682: this .rs = noSyncConn.createStatement().executeQuery(
1683: "SELECT field1, field2 FROM testBug8428");
1684: this .rs.next();
1685: assertEquals("1999", this .rs.getString(1));
1686: assertEquals("2005-02-11 12:54:41", this .rs.getString(2));
1687:
1688: this .rs = noSyncConn.prepareStatement(
1689: "SELECT field1, field2 FROM testBug8428")
1690: .executeQuery();
1691: this .rs.next();
1692: assertEquals("1999", this .rs.getString(1));
1693: assertEquals("2005-02-11 12:54:41", this .rs.getString(2));
1694: } finally {
1695: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug8428");
1696: }
1697: }
1698:
1699: /**
1700: * Tests fix for Bug#8868, DATE_FORMAT() queries returned as BLOBs from
1701: * getObject().
1702: *
1703: * @throws Exception
1704: * if the test fails.
1705: */
1706: public void testBug8868() throws Exception {
1707: if (versionMeetsMinimum(4, 1)) {
1708: createTable("testBug8868",
1709: "(field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY)");
1710: this .stmt
1711: .executeUpdate("INSERT INTO testBug8868 VALUES (NOW(), 'abcd')");
1712: try {
1713: this .rs = this .stmt
1714: .executeQuery("SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868");
1715: this .rs.next();
1716: assertEquals("java.lang.String", this .rs.getObject(1)
1717: .getClass().getName());
1718: } finally {
1719: if (this .rs != null) {
1720: this .rs.close();
1721: }
1722: }
1723: }
1724: }
1725:
1726: /**
1727: * Tests fix for BUG#9098 - Server doesn't give us info to distinguish
1728: * between CURRENT_TIMESTAMP and 'CURRENT_TIMESTAMP' for default values.
1729: *
1730: * @throws Exception
1731: * if the test fails
1732: */
1733: public void testBug9098() throws Exception {
1734: if (versionMeetsMinimum(4, 1, 10)) {
1735: Statement updatableStmt = null;
1736:
1737: try {
1738: this .stmt
1739: .executeUpdate("DROP TABLE IF EXISTS testBug9098");
1740: this .stmt
1741: .executeUpdate("CREATE TABLE testBug9098(pkfield INT PRIMARY KEY NOT NULL AUTO_INCREMENT, \n"
1742: + "tsfield TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsfield2 TIMESTAMP NOT NULL DEFAULT '2005-12-25 12:20:52', charfield VARCHAR(4) NOT NULL DEFAULT 'abcd')");
1743: updatableStmt = this .conn.createStatement(
1744: ResultSet.TYPE_SCROLL_INSENSITIVE,
1745: ResultSet.CONCUR_UPDATABLE);
1746: this .rs = updatableStmt
1747: .executeQuery("SELECT pkfield, tsfield, tsfield2, charfield FROM testBug9098");
1748: this .rs.moveToInsertRow();
1749: this .rs.insertRow();
1750:
1751: } finally {
1752: this .stmt
1753: .executeUpdate("DROP TABLE IF EXISTS testBug9098");
1754: }
1755: }
1756: }
1757:
1758: /**
1759: * Tests fix for BUG#9236, a continuation of BUG#8868, where functions used
1760: * in queries that should return non-string types when resolved by temporary
1761: * tables suddenly become opaque binary strings (work-around for server
1762: * limitation)
1763: *
1764: * @throws Exception
1765: * if the test fails.
1766: */
1767: public void testBug9236() throws Exception {
1768: if (versionMeetsMinimum(4, 1)) {
1769: try {
1770: createTable(
1771: "testBug9236",
1772: "("
1773: + "field_1 int(18) NOT NULL auto_increment,"
1774: + "field_2 varchar(50) NOT NULL default '',"
1775: + "field_3 varchar(12) default NULL,"
1776: + "field_4 int(18) default NULL,"
1777: + "field_5 int(18) default NULL,"
1778: + "field_6 datetime default NULL,"
1779: + "field_7 varchar(30) default NULL,"
1780: + "field_8 varchar(50) default NULL,"
1781: + "field_9 datetime default NULL,"
1782: + "field_10 int(18) NOT NULL default '0',"
1783: + "field_11 int(18) default NULL,"
1784: + "field_12 datetime NOT NULL default '0000-00-00 00:00:00',"
1785: + "PRIMARY KEY (field_1),"
1786: + "KEY (field_4),"
1787: + "KEY (field_2),"
1788: + "KEY (field_3),"
1789: + "KEY (field_7,field_1),"
1790: + "KEY (field_5),"
1791: + "KEY (field_6,field_10,field_9),"
1792: + "KEY (field_11,field_10),"
1793: + "KEY (field_12,field_10)"
1794: + ") ENGINE=InnoDB DEFAULT CHARSET=latin1");
1795:
1796: this .stmt
1797: .executeUpdate("INSERT INTO testBug9236 VALUES "
1798: + "(1,'0',NULL,-1,0,'0000-00-00 00:00:00','123456789','-1','2004-03-13 14:21:38',0,NULL,'2004-03-13 14:21:38'),"
1799: + "(2,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'1',NULL,0,NULL,'2004-07-13 14:29:52'),"
1800: + "(3,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'2',NULL,0,NULL,'2004-07-16 13:20:51'),"
1801: + "(4,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'3','2004-07-16 13:43:39',0,NULL,'2004-07-16 13:22:01'),"
1802: + "(5,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'4','2004-07-16 13:23:48',0,NULL,'2004-07-16 13:23:01'),"
1803: + "(6,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'5',NULL,0,NULL,'2004-07-16 14:41:07'),"
1804: + "(7,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'6',NULL,0,NULL,'2004-07-16 14:41:34'),"
1805: + "(8,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'7',NULL,0,NULL,'2004-07-16 14:41:54'),"
1806: + "(9,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'8',NULL,0,NULL,'2004-07-16 14:42:42'),"
1807: + "(10,'0','PI',1,0,'0000-00-00 00:00:00',NULL,'9',NULL,0,NULL,'2004-07-18 10:51:30'),"
1808: + "(11,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'10','2004-07-23 17:23:06',0,NULL,'2004-07-23 17:18:19'),"
1809: + "(12,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'11','2004-07-23 17:24:45',0,NULL,'2004-07-23 17:23:57'),"
1810: + "(13,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'12','2004-07-23 17:30:51',0,NULL,'2004-07-23 17:30:15'),"
1811: + "(14,'0',NULL,1,0,'0000-00-00 00:00:00',NULL,'13','2004-07-26 17:50:19',0,NULL,'2004-07-26 17:49:38'),"
1812: + "(15,'0','FRL',1,0,'0000-00-00 00:00:00',NULL,'1',NULL,0,NULL,'2004-08-19 18:29:18'),"
1813: + "(16,'0','FRL',1,0,'0000-00-00 00:00:00',NULL,'15',NULL,0,NULL,'2005-03-16 12:08:28')");
1814:
1815: createTable("testBug9236_1",
1816: "(field1 CHAR(2) CHARACTER SET BINARY)");
1817: this .stmt
1818: .executeUpdate("INSERT INTO testBug9236_1 VALUES ('ab')");
1819: this .rs = this .stmt
1820: .executeQuery("SELECT field1 FROM testBug9236_1");
1821:
1822: ResultSetMetaData rsmd = this .rs.getMetaData();
1823: assertEquals("[B", rsmd.getColumnClassName(1));
1824: assertTrue(this .rs.next());
1825: Object asObject = this .rs.getObject(1);
1826: assertEquals("[B", asObject.getClass().getName());
1827:
1828: this .rs = this .stmt
1829: .executeQuery("select DATE_FORMAT(field_12, '%Y-%m-%d') as date, count(*) as count from testBug9236 where field_10 = 0 and field_3 = 'FRL' and field_12 >= '2005-03-02 00:00:00' and field_12 <= '2005-03-17 00:00:00' group by date");
1830: rsmd = this .rs.getMetaData();
1831: assertEquals("java.lang.String", rsmd
1832: .getColumnClassName(1));
1833: this .rs.next();
1834: asObject = this .rs.getObject(1);
1835: assertEquals("java.lang.String", asObject.getClass()
1836: .getName());
1837:
1838: this .rs.close();
1839:
1840: createTable("testBug8868_2",
1841: "(field1 CHAR(4) CHARACTER SET BINARY)");
1842: this .stmt
1843: .executeUpdate("INSERT INTO testBug8868_2 VALUES ('abc')");
1844: this .rs = this .stmt
1845: .executeQuery("SELECT field1 FROM testBug8868_2");
1846:
1847: rsmd = this .rs.getMetaData();
1848: assertEquals("[B", rsmd.getColumnClassName(1));
1849: this .rs.next();
1850: asObject = this .rs.getObject(1);
1851: assertEquals("[B", asObject.getClass().getName());
1852: } finally {
1853: if (this .rs != null) {
1854: this .rs.close();
1855: this .rs = null;
1856: }
1857: }
1858: }
1859: }
1860:
1861: /**
1862: * Tests fix for BUG#9437, IF() returns type of [B or java.lang.String
1863: * depending on platform. Fixed earlier, but in here to catch if it ever
1864: * regresses.
1865: *
1866: * @throws Exception
1867: * if the test fails.
1868: */
1869: public void testBug9437() throws Exception {
1870: String tableName = "testBug9437";
1871:
1872: if (versionMeetsMinimum(4, 1, 0)) {
1873: try {
1874: createTable(
1875: tableName,
1876: "("
1877: + "languageCode char(2) NOT NULL default '',"
1878: + "countryCode char(2) NOT NULL default '',"
1879: + "supported enum('no','yes') NOT NULL default 'no',"
1880: + "ordering int(11) default NULL,"
1881: + "createDate datetime NOT NULL default '1000-01-01 00:00:03',"
1882: + "modifyDate timestamp NOT NULL default CURRENT_TIMESTAMP on update"
1883: + " CURRENT_TIMESTAMP,"
1884: + "PRIMARY KEY (languageCode,countryCode),"
1885: + "KEY languageCode (languageCode),"
1886: + "KEY countryCode (countryCode),"
1887: + "KEY ordering (ordering),"
1888: + "KEY modifyDate (modifyDate)"
1889: + ") ENGINE=InnoDB DEFAULT CHARSET=utf8");
1890:
1891: this .stmt.executeUpdate("INSERT INTO " + tableName
1892: + " (languageCode) VALUES ('en')");
1893:
1894: String alias = "someLocale";
1895: String sql = "select if ( languageCode = ?, ?, ? ) as "
1896: + alias + " from " + tableName;
1897: this .pstmt = this .conn.prepareStatement(sql);
1898:
1899: int count = 1;
1900: this .pstmt.setObject(count++, "en");
1901: this .pstmt.setObject(count++, "en_US");
1902: this .pstmt.setObject(count++, "en_GB");
1903:
1904: this .rs = this .pstmt.executeQuery();
1905:
1906: assertTrue(this .rs.next());
1907:
1908: Object object = this .rs.getObject(alias);
1909:
1910: if (object != null) {
1911: assertEquals("java.lang.String", object.getClass()
1912: .getName());
1913: assertEquals("en_US", object.toString());
1914: }
1915:
1916: } finally {
1917: if (this .rs != null) {
1918: this .rs.close();
1919: this .rs = null;
1920: }
1921:
1922: if (this .pstmt != null) {
1923: this .pstmt.close();
1924: this .pstmt = null;
1925: }
1926: }
1927: }
1928: }
1929:
1930: public void testBug9684() throws Exception {
1931: if (versionMeetsMinimum(4, 1, 9)) {
1932: String tableName = "testBug9684";
1933:
1934: try {
1935: createTable(tableName,
1936: "(sourceText text character set utf8 collate utf8_bin)");
1937: this .stmt.executeUpdate("INSERT INTO " + tableName
1938: + " VALUES ('abc')");
1939: this .rs = this .stmt
1940: .executeQuery("SELECT sourceText FROM "
1941: + tableName);
1942: assertTrue(this .rs.next());
1943: assertEquals("java.lang.String", this .rs.getString(1)
1944: .getClass().getName());
1945: assertEquals("abc", this .rs.getString(1));
1946: } finally {
1947: if (this .rs != null) {
1948: this .rs.close();
1949: this .rs = null;
1950: }
1951: }
1952: }
1953: }
1954:
1955: /**
1956: * Tests fix for BUG#10156 - Unsigned SMALLINT treated as signed
1957: *
1958: * @throws Exception
1959: * if the test fails.
1960: */
1961: public void testBug10156() throws Exception {
1962: String tableName = "testBug10156";
1963: try {
1964: createTable(tableName, "(field1 smallint(5) unsigned, "
1965: + "field2 tinyint unsigned,"
1966: + "field3 int unsigned)");
1967: this .stmt.executeUpdate("INSERT INTO " + tableName
1968: + " VALUES (32768, 255, 4294967295)");
1969: this .rs = this .conn.prepareStatement(
1970: "SELECT field1, field2, field3 FROM " + tableName)
1971: .executeQuery();
1972: assertTrue(this .rs.next());
1973: assertEquals(32768, this .rs.getInt(1));
1974: assertEquals(255, this .rs.getInt(2));
1975: assertEquals(4294967295L, this .rs.getLong(3));
1976:
1977: assertEquals(String.valueOf(this .rs.getObject(1)), String
1978: .valueOf(this .rs.getInt(1)));
1979: assertEquals(String.valueOf(this .rs.getObject(2)), String
1980: .valueOf(this .rs.getInt(2)));
1981: assertEquals(String.valueOf(this .rs.getObject(3)), String
1982: .valueOf(this .rs.getLong(3)));
1983:
1984: } finally {
1985: if (this .rs != null) {
1986: this .rs.close();
1987: this .rs = null;
1988: }
1989: }
1990: }
1991:
1992: public void testBug10212() throws Exception {
1993: String tableName = "testBug10212";
1994:
1995: try {
1996: createTable(tableName, "(field1 YEAR(4))");
1997: this .stmt.executeUpdate("INSERT INTO " + tableName
1998: + " VALUES (1974)");
1999: this .rs = this .conn.prepareStatement(
2000: "SELECT field1 FROM " + tableName).executeQuery();
2001:
2002: ResultSetMetaData rsmd = this .rs.getMetaData();
2003: assertTrue(this .rs.next());
2004: assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
2005: assertEquals("java.sql.Date", this .rs.getObject(1)
2006: .getClass().getName());
2007:
2008: this .rs = this .stmt.executeQuery("SELECT field1 FROM "
2009: + tableName);
2010:
2011: rsmd = this .rs.getMetaData();
2012: assertTrue(this .rs.next());
2013: assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
2014: assertEquals("java.sql.Date", this .rs.getObject(1)
2015: .getClass().getName());
2016: } finally {
2017: if (this .rs != null) {
2018: this .rs.close();
2019: this .rs = null;
2020: }
2021: }
2022: }
2023:
2024: /**
2025: * Tests fix for BUG#11190 - ResultSet.moveToCurrentRow() fails to work when
2026: * preceeded with .moveToInsertRow().
2027: *
2028: * @throws Exception
2029: * if the test fails.
2030: */
2031: public void testBug11190() throws Exception {
2032:
2033: createTable("testBug11190",
2034: "(a CHAR(4) PRIMARY KEY, b VARCHAR(20))");
2035: this .stmt
2036: .executeUpdate("INSERT INTO testBug11190 VALUES('3000','L'),('3001','H'),('1050','B')");
2037:
2038: Statement updStmt = null;
2039:
2040: try {
2041: updStmt = this .conn.createStatement(
2042: ResultSet.TYPE_SCROLL_SENSITIVE,
2043: ResultSet.CONCUR_UPDATABLE);
2044:
2045: this .rs = updStmt
2046: .executeQuery("select * from testBug11190");
2047: assertTrue("must return a row", this .rs.next());
2048: String savedValue = this .rs.getString(1);
2049: this .rs.moveToInsertRow();
2050: this .rs.updateString(1, "4000");
2051: this .rs.updateString(2, "C");
2052: this .rs.insertRow();
2053:
2054: this .rs.moveToCurrentRow();
2055: assertEquals(savedValue, this .rs.getString(1));
2056: } finally {
2057: if (this .rs != null) {
2058: this .rs.close();
2059: this .rs = null;
2060: }
2061:
2062: if (updStmt != null) {
2063: updStmt.close();
2064: }
2065: }
2066: }
2067:
2068: /**
2069: * Tests fix for BUG#12104 - Geometry types not handled with server-side
2070: * prepared statements.
2071: *
2072: * @throws Exception
2073: * if the test fails
2074: */
2075: public void testBug12104() throws Exception {
2076: if (versionMeetsMinimum(4, 1)) {
2077: createTable("testBug12104",
2078: "(field1 GEOMETRY) ENGINE=MyISAM");
2079:
2080: try {
2081: this .stmt
2082: .executeUpdate("INSERT INTO testBug12104 VALUES (GeomFromText('POINT(1 1)'))");
2083: this .pstmt = this .conn
2084: .prepareStatement("SELECT field1 FROM testBug12104");
2085: this .rs = this .pstmt.executeQuery();
2086: assertTrue(this .rs.next());
2087: System.out.println(this .rs.getObject(1));
2088: } finally {
2089:
2090: }
2091: }
2092: }
2093:
2094: /**
2095: * Tests fix for BUG#13043 - when 'gatherPerfMetrics' is enabled for servers <
2096: * 4.1.0, a NPE is thrown from the constructor of ResultSet if the query
2097: * doesn't use any tables.
2098: *
2099: * @throws Exception
2100: * if the test fails
2101: */
2102: public void testBug13043() throws Exception {
2103: if (!versionMeetsMinimum(4, 1)) {
2104: Connection perfConn = null;
2105:
2106: try {
2107: Properties props = new Properties();
2108: props.put("gatherPerfMetrics", "true"); // this property is
2109: // reported as the cause
2110: // of
2111: // NullPointerException
2112: props.put("reportMetricsIntervalMillis", "30000"); // this
2113: // property
2114: // is
2115: // reported
2116: // as the
2117: // cause of
2118: // NullPointerException
2119: perfConn = getConnectionWithProps(props);
2120: perfConn.createStatement().executeQuery("SELECT 1");
2121: } finally {
2122: if (perfConn != null) {
2123: perfConn.close();
2124: }
2125: }
2126: }
2127: }
2128:
2129: /**
2130: * Tests fix for BUG#13374 - ResultSet.getStatement() on closed result set
2131: * returns NULL (as per JDBC 4.0 spec, but not backwards-compatible).
2132: *
2133: * @throws Exception
2134: * if the test fails
2135: */
2136:
2137: public void testBug13374() throws Exception {
2138: Statement retainStmt = null;
2139: Connection retainConn = null;
2140:
2141: try {
2142: Properties props = new Properties();
2143:
2144: props.setProperty("retainStatementAfterResultSetClose",
2145: "true");
2146:
2147: retainConn = getConnectionWithProps(props);
2148:
2149: retainStmt = retainConn.createStatement();
2150:
2151: this .rs = retainStmt.executeQuery("SELECT 1");
2152: this .rs.close();
2153: assertNotNull(this .rs.getStatement());
2154:
2155: this .rs = this .stmt.executeQuery("SELECT 1");
2156: this .rs.close();
2157:
2158: try {
2159: this .rs.getStatement();
2160: } catch (SQLException sqlEx) {
2161: assertEquals(sqlEx.getSQLState(),
2162: SQLError.SQL_STATE_GENERAL_ERROR);
2163: }
2164:
2165: } finally {
2166: if (this .rs != null) {
2167: this .rs.close();
2168: this .rs = null;
2169: }
2170:
2171: if (retainStmt != null) {
2172: retainStmt.close();
2173: }
2174:
2175: if (retainConn != null) {
2176: retainConn.close();
2177: }
2178: }
2179: }
2180:
2181: /**
2182: * Tests bugfix for BUG#14562 - metadata/type for MEDIUMINT UNSIGNED is
2183: * incorrect.
2184: *
2185: * @throws Exception
2186: * if the test fails.
2187: */
2188: public void testBug14562() throws Exception {
2189: createTable("testBug14562",
2190: "(row_order INT, signed_field MEDIUMINT, unsigned_field MEDIUMINT UNSIGNED)");
2191:
2192: try {
2193: this .stmt
2194: .executeUpdate("INSERT INTO testBug14562 VALUES (1, -8388608, 0), (2, 8388607, 16777215)");
2195:
2196: this .rs = this .stmt
2197: .executeQuery("SELECT signed_field, unsigned_field FROM testBug14562 ORDER BY row_order");
2198: traverseResultSetBug14562();
2199:
2200: this .rs = this .conn
2201: .prepareStatement(
2202: "SELECT signed_field, unsigned_field FROM testBug14562 ORDER BY row_order")
2203: .executeQuery();
2204: traverseResultSetBug14562();
2205:
2206: if (versionMeetsMinimum(5, 0)) {
2207: CallableStatement storedProc = null;
2208:
2209: try {
2210: this .stmt
2211: .executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug14562");
2212: this .stmt
2213: .executeUpdate("CREATE PROCEDURE sp_testBug14562() BEGIN SELECT signed_field, unsigned_field FROM testBug14562 ORDER BY row_order; END");
2214: storedProc = this .conn
2215: .prepareCall("{call sp_testBug14562()}");
2216: storedProc.execute();
2217: this .rs = storedProc.getResultSet();
2218: traverseResultSetBug14562();
2219:
2220: this .stmt
2221: .executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug14562_1");
2222: this .stmt
2223: .executeUpdate("CREATE PROCEDURE sp_testBug14562_1(OUT param_1 MEDIUMINT, OUT param_2 MEDIUMINT UNSIGNED) BEGIN SELECT signed_field, unsigned_field INTO param_1, param_2 FROM testBug14562 WHERE row_order=1; END");
2224: storedProc = this .conn
2225: .prepareCall("{call sp_testBug14562_1(?, ?)}");
2226: storedProc.registerOutParameter(1, Types.INTEGER);
2227: storedProc.registerOutParameter(2, Types.INTEGER);
2228:
2229: storedProc.execute();
2230:
2231: assertEquals("java.lang.Integer", storedProc
2232: .getObject(1).getClass().getName());
2233:
2234: if (versionMeetsMinimum(5, 1)) {
2235: assertEquals("java.lang.Long", storedProc
2236: .getObject(2).getClass().getName());
2237: } else {
2238: assertEquals("java.lang.Integer", storedProc
2239: .getObject(2).getClass().getName());
2240: }
2241:
2242: } finally {
2243: if (storedProc != null) {
2244: storedProc.close();
2245: }
2246:
2247: this .stmt
2248: .executeUpdate("DROP PROCEDURE IF EXISTS sp_testBug14562");
2249: }
2250: }
2251:
2252: this .rs = this .conn.getMetaData().getColumns(
2253: this .conn.getCatalog(), null, "testBug14562",
2254: "%field");
2255:
2256: assertTrue(this .rs.next());
2257:
2258: assertEquals(Types.INTEGER, this .rs.getInt("DATA_TYPE"));
2259: assertEquals("MEDIUMINT", this .rs.getString("TYPE_NAME")
2260: .toUpperCase(Locale.US));
2261:
2262: assertTrue(this .rs.next());
2263:
2264: assertEquals(Types.INTEGER, this .rs.getInt("DATA_TYPE"));
2265: assertEquals("MEDIUMINT UNSIGNED", this .rs.getString(
2266: "TYPE_NAME").toUpperCase(Locale.US));
2267:
2268: //
2269: // The following test is harmless in the 3.1 driver, but
2270: // is needed for the 5.0 driver, so we'll leave it here
2271: //
2272: if (versionMeetsMinimum(5, 0, 14)) {
2273: Connection infoSchemConn = null;
2274:
2275: try {
2276: Properties props = new Properties();
2277: props.setProperty("useInformationSchema", "true");
2278:
2279: infoSchemConn = getConnectionWithProps(props);
2280:
2281: this .rs = infoSchemConn.getMetaData().getColumns(
2282: infoSchemConn.getCatalog(), null,
2283: "testBug14562", "%field");
2284:
2285: assertTrue(this .rs.next());
2286:
2287: assertEquals(Types.INTEGER, this .rs
2288: .getInt("DATA_TYPE"));
2289: assertEquals("MEDIUMINT", this .rs.getString(
2290: "TYPE_NAME").toUpperCase(Locale.US));
2291:
2292: assertTrue(this .rs.next());
2293:
2294: assertEquals(Types.INTEGER, this .rs
2295: .getInt("DATA_TYPE"));
2296: assertEquals("MEDIUMINT UNSIGNED", this .rs
2297: .getString("TYPE_NAME").toUpperCase(
2298: Locale.US));
2299:
2300: } finally {
2301: if (infoSchemConn != null) {
2302: infoSchemConn.close();
2303: }
2304: }
2305: }
2306: } finally {
2307:
2308: }
2309: }
2310:
2311: public void testBug14897() throws Exception {
2312: createTable("table1", "(id int, name_id int)");
2313: createTable("table2", "(id int)");
2314: createTable(
2315: "lang_table",
2316: "(id int, en varchar(255) CHARACTER SET utf8, cz varchar(255) CHARACTER SET utf8)");
2317:
2318: this .stmt.executeUpdate("insert into table1 values (0, 0)");
2319: this .stmt.executeUpdate("insert into table2 values (0)");
2320: this .stmt
2321: .executeUpdate("insert into lang_table values (0, 'abcdef', 'ghijkl')");
2322: this .rs = this .stmt
2323: .executeQuery("select a.id, b.id, c.en, c.cz from table1 as a, table2 as b, lang_table as c where a.id = b.id and a.name_id = c.id");
2324: assertTrue(this .rs.next());
2325: this .rs.getString("c.cz");
2326:
2327: this .rs = this .stmt
2328: .executeQuery("select table1.*, table2.* FROM table1, table2");
2329: this .rs.findColumn("table1.id");
2330: this .rs.findColumn("table2.id");
2331: }
2332:
2333: /**
2334: * Tests fix for BUG#14609 - Exception thrown for new decimal type when
2335: * using updatable result sets.
2336: *
2337: * @throws Exception
2338: * if the test fails
2339: */
2340: public void testBug14609() throws Exception {
2341: if (versionMeetsMinimum(5, 0)) {
2342: createTable("testBug14609",
2343: "(field1 int primary key, field2 decimal)");
2344: this .stmt
2345: .executeUpdate("INSERT INTO testBug14609 VALUES (1, 1)");
2346:
2347: PreparedStatement updatableStmt = this .conn
2348: .prepareStatement(
2349: "SELECT field1, field2 FROM testBug14609",
2350: ResultSet.TYPE_SCROLL_INSENSITIVE,
2351: ResultSet.CONCUR_UPDATABLE);
2352:
2353: try {
2354: this .rs = updatableStmt.executeQuery();
2355: } finally {
2356: if (this .rs != null) {
2357: ResultSet toClose = this .rs;
2358: this .rs = null;
2359: toClose.close();
2360: }
2361:
2362: if (updatableStmt != null) {
2363: updatableStmt.close();
2364: }
2365: }
2366: }
2367: }
2368:
2369: /**
2370: * Tests fix for BUG#16169 - ResultSet.getNativeShort() causes stack
2371: * overflow error via recurisve calls.
2372: *
2373: * @throws Exception
2374: * if the tests fails
2375: */
2376: public void testBug16169() throws Exception {
2377: createTable("testBug16169", "(field1 smallint)");
2378:
2379: try {
2380:
2381: this .stmt
2382: .executeUpdate("INSERT INTO testBug16169 (field1) VALUES (0)");
2383:
2384: this .pstmt = this .conn
2385: .prepareStatement("SELECT * FROM testBug16169");
2386: this .rs = this .pstmt.executeQuery();
2387: assertTrue(this .rs.next());
2388:
2389: assertEquals(0, ((Integer) rs.getObject("field1"))
2390: .intValue());
2391: } finally {
2392: if (this .rs != null) {
2393: ResultSet toCloseRs = this .rs;
2394: this .rs = null;
2395: toCloseRs.close();
2396: }
2397:
2398: if (this .pstmt != null) {
2399: PreparedStatement toCloseStmt = this .pstmt;
2400: this .pstmt = null;
2401: toCloseStmt.close();
2402: }
2403: }
2404: }
2405:
2406: /**
2407: * Tests fix for BUG#16841 - updatable result set doesn't return
2408: * AUTO_INCREMENT values for insertRow() when multiple column primary keys
2409: * are used.
2410: *
2411: * @throws Exception
2412: * if the test fails.
2413: */
2414: public void testBug16841() throws Exception {
2415:
2416: createTable("testBug16841", "("
2417: + "CID int( 20 ) NOT NULL default '0',"
2418: + "OID int( 20 ) NOT NULL AUTO_INCREMENT ,"
2419: + "PatientID int( 20 ) default NULL ,"
2420: + "PRIMARY KEY ( CID , OID ) ," + "KEY OID ( OID ) ,"
2421: + "KEY Path ( CID, PatientID)" + ") TYPE = MYISAM");
2422:
2423: String sSQLQuery = "SELECT * FROM testBug16841 WHERE 1 = 0";
2424: Statement updStmt = null;
2425:
2426: try {
2427: updStmt = this .conn.createStatement(
2428: ResultSet.TYPE_FORWARD_ONLY,
2429: ResultSet.CONCUR_UPDATABLE);
2430:
2431: this .rs = updStmt.executeQuery(sSQLQuery);
2432:
2433: this .rs.moveToInsertRow();
2434:
2435: this .rs.updateInt("CID", 1);
2436: this .rs.updateInt("PatientID", 1);
2437:
2438: this .rs.insertRow();
2439:
2440: this .rs.last();
2441: assertEquals(1, this .rs.getInt("OID"));
2442: } finally {
2443: if (this .rs != null) {
2444: ResultSet toClose = this .rs;
2445: this .rs = null;
2446: toClose.close();
2447: }
2448:
2449: if (updStmt != null) {
2450: updStmt.close();
2451: }
2452: }
2453: }
2454:
2455: /**
2456: * Tests fix for BUG#17450 - ResultSet.wasNull() not always reset correctly
2457: * for booleans when done via conversion for server-side prepared
2458: * statements.
2459: *
2460: * @throws Exception
2461: * if the test fails.
2462: */
2463: public void testBug17450() throws Exception {
2464: if (versionMeetsMinimum(4, 1, 0)) {
2465: createTable("testBug17450",
2466: "(FOO VARCHAR(100), BAR CHAR NOT NULL)");
2467:
2468: this .stmt
2469: .execute("insert into testBug17450 (foo,bar) values ('foo',true)");
2470: this .stmt
2471: .execute("insert into testBug17450 (foo,bar) values (null,true)");
2472:
2473: this .pstmt = this .conn
2474: .prepareStatement("select * from testBug17450 where foo=?");
2475: this .pstmt.setString(1, "foo");
2476: this .rs = this .pstmt.executeQuery();
2477: checkResult17450();
2478:
2479: this .pstmt = this .conn
2480: .prepareStatement("select * from testBug17450 where foo is null");
2481: this .rs = this .pstmt.executeQuery();
2482: checkResult17450();
2483:
2484: this .rs = this .stmt
2485: .executeQuery("select * from testBug17450 where foo='foo'");
2486: checkResult17450();
2487:
2488: this .rs = this .stmt
2489: .executeQuery("select * from testBug17450 where foo is null");
2490: checkResult17450();
2491: }
2492: }
2493:
2494: /**
2495: * Tests fix for BUG#19282 - ResultSet.wasNull() returns incorrect value
2496: * when extracting native string from server-side prepared statement
2497: * generated result set.
2498: *
2499: * @throws Exception
2500: * if the test fails.
2501: */
2502: public void testBug19282() throws Exception {
2503: createTable("testBug19282", "(field1 VARCHAR(32))");
2504: try {
2505: this .pstmt = this .conn
2506: .prepareStatement("SELECT field1 FROM testBug19282");
2507: this .stmt
2508: .executeUpdate("INSERT INTO testBug19282 VALUES ('abcdefg')");
2509:
2510: this .rs = this .pstmt.executeQuery();
2511: this .rs.next();
2512: assertEquals(false, this .rs.wasNull());
2513: this .rs.getString(1);
2514: assertEquals(false, this .rs.wasNull());
2515: } finally {
2516: if (this .rs != null) {
2517: ResultSet toClose = this .rs;
2518: this .rs = null;
2519: toClose.close();
2520: }
2521:
2522: if (this .pstmt != null) {
2523: PreparedStatement toClose = this .pstmt;
2524: this .pstmt = null;
2525: toClose.close();
2526: }
2527: }
2528: }
2529:
2530: private void checkResult17450() throws Exception {
2531: this .rs.next();
2532: this .rs.getString(1);
2533: boolean bar = this .rs.getBoolean(2);
2534:
2535: assertEquals("field 2 should be true", true, bar);
2536: assertFalse("wasNull should return false", this .rs.wasNull());
2537: }
2538:
2539: /**
2540: * Tests fix for BUG#
2541: *
2542: * @throws Exception
2543: */
2544: public void testBug19568() throws Exception {
2545: if (versionMeetsMinimum(4, 1, 0)) {
2546: createTable("testBug19568", "(field1 BOOLEAN,"
2547: + (versionMeetsMinimum(5, 0, 0) ? "field2 BIT"
2548: : "field2 BOOLEAN") + ")");
2549:
2550: this .stmt
2551: .executeUpdate("INSERT INTO testBug19568 VALUES (1,0), (0, 1)");
2552:
2553: try {
2554: this .pstmt = this .conn
2555: .prepareStatement("SELECT field1, field2 FROM testBug19568 ORDER BY field1 DESC");
2556: this .rs = this .pstmt.executeQuery();
2557:
2558: checkResultsBug19568();
2559:
2560: this .rs = this .stmt
2561: .executeQuery("SELECT field1, field2 FROM testBug19568 ORDER BY field1 DESC");
2562: checkResultsBug19568();
2563: } finally {
2564: closeMemberJDBCResources();
2565: }
2566: }
2567: }
2568:
2569: private void checkResultsBug19568() throws SQLException {
2570: // Test all numerical getters, and make sure to alternate true/false
2571: // across rows so we can catch
2572: // false-positives if off-by-one errors exist in the column getters.
2573:
2574: for (int i = 0; i < 2; i++) {
2575: assertTrue(this .rs.next());
2576:
2577: for (int j = 0; j < 2; j++) {
2578: assertEquals((i == 1 && j == 1) || (i == 0 && j == 0),
2579: this .rs.getBoolean(j + 1));
2580: assertEquals(
2581: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2582: : 0), this .rs.getBigDecimal(j + 1)
2583: .intValue());
2584: assertEquals(
2585: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2586: : 0), this .rs.getByte(j + 1));
2587: assertEquals(
2588: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2589: : 0), this .rs.getShort(j + 1));
2590: assertEquals(
2591: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2592: : 0), this .rs.getInt(j + 1));
2593: assertEquals(
2594: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2595: : 0), this .rs.getLong(j + 1));
2596: assertEquals(
2597: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2598: : 0), this .rs.getFloat(j + 1), .1);
2599: assertEquals(
2600: ((i == 1 && j == 1) || (i == 0 && j == 0) ? 1
2601: : 0), this .rs.getDouble(j + 1), .1);
2602: }
2603: }
2604: }
2605:
2606: public void testBug19724() throws Exception {
2607: if (versionMeetsMinimum(4, 1)) {
2608: // can't set this via session on 4.0 :(
2609:
2610: createTable("test19724",
2611: "(col1 INTEGER NOT NULL, col2 VARCHAR(255) NULL, PRIMARY KEY (col1))");
2612:
2613: this .stmt
2614: .execute("INSERT IGNORE INTO test19724 VALUES (0, 'Blah'),(1,'Boo')");
2615:
2616: Connection ansiConn = null;
2617: Statement updStmt = null;
2618:
2619: Properties props = new Properties();
2620: props.setProperty("sessionVariables", "sql_mode=ansi");
2621:
2622: try {
2623: ansiConn = getConnectionWithProps(props);
2624: updStmt = ansiConn.createStatement(
2625: ResultSet.TYPE_SCROLL_INSENSITIVE,
2626: ResultSet.CONCUR_UPDATABLE);
2627: this .rs = updStmt
2628: .executeQuery("SELECT * FROM test19724");
2629:
2630: this .rs.beforeFirst();
2631:
2632: this .rs.next();
2633:
2634: this .rs.updateString("col2", "blah2");
2635: this .rs.updateRow();
2636: } finally {
2637: closeMemberJDBCResources();
2638:
2639: if (ansiConn != null) {
2640: ansiConn.close();
2641: }
2642: }
2643: }
2644: }
2645:
2646: private void traverseResultSetBug14562() throws SQLException {
2647: assertTrue(this .rs.next());
2648:
2649: ResultSetMetaData rsmd = this .rs.getMetaData();
2650: assertEquals("MEDIUMINT", rsmd.getColumnTypeName(1));
2651: assertEquals("MEDIUMINT UNSIGNED", rsmd.getColumnTypeName(2));
2652:
2653: assertEquals(Types.INTEGER, rsmd.getColumnType(1));
2654: assertEquals(Types.INTEGER, rsmd.getColumnType(2));
2655:
2656: assertEquals("java.lang.Integer", rsmd.getColumnClassName(1));
2657: assertEquals("java.lang.Integer", rsmd.getColumnClassName(2));
2658:
2659: assertEquals(-8388608, this .rs.getInt(1));
2660: assertEquals(0, this .rs.getInt(2));
2661:
2662: assertEquals("java.lang.Integer", this .rs.getObject(1)
2663: .getClass().getName());
2664: assertEquals("java.lang.Integer", this .rs.getObject(2)
2665: .getClass().getName());
2666:
2667: assertTrue(this .rs.next());
2668:
2669: assertEquals(8388607, this .rs.getInt(1));
2670: assertEquals(16777215, this .rs.getInt(2));
2671:
2672: assertEquals("java.lang.Integer", this .rs.getObject(1)
2673: .getClass().getName());
2674: assertEquals("java.lang.Integer", this .rs.getObject(2)
2675: .getClass().getName());
2676: }
2677:
2678: /*
2679: * public void testBug16458() throws Exception { createTable("a", "(id
2680: * INTEGER NOT NULL, primary key (id)) Type=InnoDB"); createTable("b", "(id
2681: * INTEGER NOT NULL, primary key (id)) Type=InnoDB"); createTable("c", "(id
2682: * INTEGER NOT NULL, primary key (id)) Type=InnoDB");
2683: *
2684: * createTable( "problem_table", "(id int(11) NOT NULL auto_increment," +
2685: * "a_id int(11) NOT NULL default '0'," + "b_id int(11) NOT NULL default
2686: * '0'," + "c_id int(11) default NULL," + "order_num int(2) NOT NULL default
2687: * '0'," + "PRIMARY KEY (id)," + "KEY idx_problem_table__b_id (b_id)," +
2688: * "KEY idx_problem_table__a_id (a_id)," + "KEY idx_problem_table__c_id
2689: * (c_id)," + "CONSTRAINT fk_problem_table__c FOREIGN KEY (c_id) REFERENCES
2690: * c (id)," + "CONSTRAINT fk_problem_table__a FOREIGN KEY (a_id) REFERENCES
2691: * a (id)," + "CONSTRAINT fk_problem_table__b FOREIGN KEY (b_id) REFERENCES
2692: * b (id)" + ")" + "Type=InnoDB");
2693: *
2694: * this.stmt .executeUpdate("INSERT INTO `a` VALUES " +
2695: * "(1),(4),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23" +
2696: * "),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39" +
2697: * "),(40),(41),(42),(43),(45),(46),(47),(48),(49),(50)");
2698: *
2699: * this.stmt .executeUpdate("INSERT INTO `b` VALUES " +
2700: * "(1),(2),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19" +
2701: * "),(20)");
2702: *
2703: * this.stmt .executeUpdate("INSERT INTO `c` VALUES " +
2704: * "(1),(2),(3),(13),(15),(16),(22),(30),(31),(32),(33),(34),(35),(36),(37),(148),(1" +
2705: * "59),(167),(174),(176),(177),(178),(179),(180),(187),(188),(189),(190),(191),(192" +
2706: * "),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205)," +
2707: * "(206),(207),(208)");
2708: *
2709: * this.stmt .executeUpdate("INSERT INTO `problem_table` VALUES " +
2710: * "(1,1,1,NULL,1),(2,1,4,NULL,1),(3,1,5,NULL,1),(4,1,8,NULL,1),(5,23,1,NULL,1),(6,2" +
2711: * "3,4,NULL,1),(7,24,1,NULL,1),(8,24,2,NULL,1),(9,24,4,NULL,1),(10,25,1,NULL,1),(11" +
2712: * ",25,2,NULL,1),(12,25,4,NULL,1),(13,27,1,NULL,1),(14,28,1,NULL,1),(15,29,1,NULL,1" +
2713: * "),(16,15,2,NULL,1),(17,15,5,NULL,1),(18,15,8,NULL,1),(19,30,1,NULL,1),(20,31,1,N" +
2714: * "ULL,1),(21,31,4,NULL,1),(22,32,2,NULL,1),(23,32,4,NULL,1),(24,32,6,NULL,1),(25,3" +
2715: * "2,8,NULL,1),(26,32,10,NULL,1),(27,32,11,NULL,1),(28,32,13,NULL,1),(29,32,16,NULL" +
2716: * ",1),(30,32,17,NULL,1),(31,32,18,NULL,1),(32,32,19,NULL,1),(33,32,20,NULL,1),(34," +
2717: * "33,15,NULL,1),(35,33,15,NULL,1),(36,32,20,206,1),(96,32,9,NULL,1),(100,47,6,NULL" +
2718: * ",1),(101,47,10,NULL,1),(102,47,5,NULL,1),(105,47,19,NULL,1)");
2719: * PreparedStatement ps = null;
2720: *
2721: * try { ps = conn.prepareStatement("SELECT DISTINCT id,order_num FROM
2722: * problem_table WHERE a_id=? FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY,
2723: * ResultSet.CONCUR_UPDATABLE);
2724: *
2725: * ps.setInt(1, 32);
2726: *
2727: * this.rs = ps.executeQuery();
2728: *
2729: * while(this.rs.next()) { this.rs.updateInt(3, 51);
2730: *
2731: * this.rs.updateRow(); } } finally { if (this.rs != null) { ResultSet
2732: * toCloseRs = this.rs; this.rs = null; toCloseRs.close(); }
2733: *
2734: * if (ps != null) { PreparedStatement toClosePs = ps; ps = null;
2735: * toClosePs.close(); } } }
2736: */
2737:
2738: public void testNPEWithUsageAdvisor() throws Exception {
2739: Connection advisorConn = null;
2740:
2741: try {
2742: Properties props = new Properties();
2743: props.setProperty("useUsageAdvisor", "true");
2744:
2745: advisorConn = getConnectionWithProps(props);
2746: this .pstmt = advisorConn.prepareStatement("SELECT 1");
2747: this .rs = this .pstmt.executeQuery();
2748: this .rs.close();
2749: this .rs = this .pstmt.executeQuery();
2750:
2751: } finally {
2752: }
2753: }
2754:
2755: public void testAllTypesForNull() throws Exception {
2756: if (!isRunningOnJdk131()) {
2757: Properties props = new Properties();
2758: props.setProperty("jdbcCompliantTruncation", "false");
2759: props.setProperty("zeroDateTimeBehavior", "round");
2760: Connection conn2 = getConnectionWithProps(props);
2761: Statement stmt2 = conn2.createStatement();
2762:
2763: DatabaseMetaData dbmd = this .conn.getMetaData();
2764:
2765: this .rs = dbmd.getTypeInfo();
2766:
2767: boolean firstColumn = true;
2768: int numCols = 1;
2769: StringBuffer createStatement = new StringBuffer(
2770: "CREATE TABLE testAllTypes (");
2771: List wasDatetimeTypeList = new ArrayList();
2772:
2773: while (this .rs.next()) {
2774: String dataType = this .rs.getString("TYPE_NAME")
2775: .toUpperCase();
2776:
2777: boolean wasDateTime = false;
2778:
2779: if (dataType.indexOf("DATE") != -1
2780: || dataType.indexOf("TIME") != -1) {
2781: wasDateTime = true;
2782: }
2783:
2784: if (!"BOOL".equalsIgnoreCase(dataType)
2785: && !"LONG VARCHAR".equalsIgnoreCase(dataType)
2786: && !"LONG VARBINARY".equalsIgnoreCase(dataType)
2787: && !"ENUM".equalsIgnoreCase(dataType)
2788: && !"SET".equalsIgnoreCase(dataType)) {
2789: wasDatetimeTypeList.add(new Boolean(wasDateTime));
2790: createStatement.append("\n\t");
2791: if (!firstColumn) {
2792: createStatement.append(",");
2793: } else {
2794: firstColumn = false;
2795: }
2796:
2797: createStatement.append("field_");
2798: createStatement.append(numCols++);
2799: createStatement.append(" ");
2800:
2801: createStatement.append(dataType);
2802:
2803: if (dataType.indexOf("CHAR") != -1
2804: || dataType.indexOf("BINARY") != -1
2805: && dataType.indexOf("BLOB") == -1
2806: && dataType.indexOf("TEXT") == -1) {
2807: createStatement.append("(");
2808: createStatement.append(this .rs
2809: .getString("PRECISION"));
2810: createStatement.append(")");
2811: }
2812:
2813: createStatement.append(" NULL DEFAULT NULL");
2814: }
2815: }
2816:
2817: createStatement.append("\n)");
2818:
2819: stmt2.executeUpdate("DROP TABLE IF EXISTS testAllTypes");
2820:
2821: stmt2.executeUpdate(createStatement.toString());
2822: StringBuffer insertStatement = new StringBuffer(
2823: "INSERT INTO testAllTypes VALUES (NULL");
2824: for (int i = 1; i < numCols - 1; i++) {
2825: insertStatement.append(", NULL");
2826: }
2827: insertStatement.append(")");
2828: stmt2.executeUpdate(insertStatement.toString());
2829:
2830: this .rs = stmt2.executeQuery("SELECT * FROM testAllTypes");
2831:
2832: testAllFieldsForNull(this .rs);
2833: this .rs.close();
2834:
2835: this .rs = this .conn.prepareStatement(
2836: "SELECT * FROM testAllTypes").executeQuery();
2837: testAllFieldsForNull(this .rs);
2838:
2839: stmt2.executeUpdate("DELETE FROM testAllTypes");
2840:
2841: insertStatement = new StringBuffer(
2842: "INSERT INTO testAllTypes VALUES (");
2843:
2844: boolean needsNow = ((Boolean) wasDatetimeTypeList.get(0))
2845: .booleanValue();
2846:
2847: if (needsNow) {
2848: insertStatement.append("NOW()");
2849: } else {
2850: insertStatement.append("'0'");
2851: }
2852:
2853: for (int i = 1; i < numCols - 1; i++) {
2854: needsNow = ((Boolean) wasDatetimeTypeList.get(i))
2855: .booleanValue();
2856: insertStatement.append(",");
2857: if (needsNow) {
2858: insertStatement.append("NOW()");
2859: } else {
2860: insertStatement.append("'0'");
2861: }
2862: }
2863:
2864: insertStatement.append(")");
2865:
2866: stmt2.executeUpdate(insertStatement.toString());
2867:
2868: this .rs = stmt2.executeQuery("SELECT * FROM testAllTypes");
2869:
2870: testAllFieldsForNotNull(this .rs, wasDatetimeTypeList);
2871: this .rs.close();
2872:
2873: this .rs = conn2.prepareStatement(
2874: "SELECT * FROM testAllTypes").executeQuery();
2875: testAllFieldsForNotNull(this .rs, wasDatetimeTypeList);
2876: }
2877: }
2878:
2879: private void testAllFieldsForNull(ResultSet rsToTest)
2880: throws Exception {
2881: ResultSetMetaData rsmd = this .rs.getMetaData();
2882: int numCols = rsmd.getColumnCount();
2883:
2884: while (rsToTest.next()) {
2885: for (int i = 0; i < numCols - 1; i++) {
2886: String typeName = rsmd.getColumnTypeName(i + 1);
2887:
2888: if ("VARBINARY".equalsIgnoreCase(typeName)) {
2889: System.out.println();
2890: }
2891:
2892: if (!"BIT".equalsIgnoreCase(typeName)) {
2893: assertEquals(false, rsToTest.getBoolean(i + 1));
2894: assertTrue("for type " + typeName, rsToTest
2895: .wasNull());
2896:
2897: assertEquals(0, rsToTest.getDouble(i + 1), 0 /* delta */);
2898: assertTrue("for type " + typeName, rsToTest
2899: .wasNull());
2900: assertEquals(0, rsToTest.getFloat(i + 1), 0 /* delta */);
2901: assertTrue("for type " + typeName, rsToTest
2902: .wasNull());
2903: assertEquals(0, rsToTest.getInt(i + 1));
2904: assertTrue("for type " + typeName, rsToTest
2905: .wasNull());
2906: assertEquals(0, rsToTest.getLong(i + 1));
2907: assertTrue("for type " + typeName, rsToTest
2908: .wasNull());
2909: assertEquals(null, rsToTest.getObject(i + 1));
2910: assertTrue("for type " + typeName, rsToTest
2911: .wasNull());
2912: assertEquals(null, rsToTest.getString(i + 1));
2913: assertTrue("for type " + typeName, rsToTest
2914: .wasNull());
2915: assertEquals(null, rsToTest.getAsciiStream(i + 1));
2916: assertTrue("for type " + typeName, rsToTest
2917: .wasNull());
2918: assertEquals(null, rsToTest.getBigDecimal(i + 1));
2919: assertTrue("for type " + typeName, rsToTest
2920: .wasNull());
2921: assertEquals(null, rsToTest.getBinaryStream(i + 1));
2922: assertTrue("for type " + typeName, rsToTest
2923: .wasNull());
2924: assertEquals(null, rsToTest.getBlob(i + 1));
2925: assertTrue("for type " + typeName, rsToTest
2926: .wasNull());
2927: assertEquals(0, rsToTest.getByte(i + 1));
2928: assertTrue("for type " + typeName, rsToTest
2929: .wasNull());
2930: assertEquals(null, rsToTest.getBytes(i + 1));
2931: assertTrue("for type " + typeName, rsToTest
2932: .wasNull());
2933: assertEquals(null, rsToTest
2934: .getCharacterStream(i + 1));
2935: assertTrue("for type " + typeName, rsToTest
2936: .wasNull());
2937: assertEquals(null, rsToTest.getClob(i + 1));
2938: assertTrue("for type " + typeName, rsToTest
2939: .wasNull());
2940: assertEquals(null, rsToTest.getDate(i + 1));
2941: assertTrue("for type " + typeName, rsToTest
2942: .wasNull());
2943: assertEquals(0, rsToTest.getShort(i + 1));
2944: assertTrue("for type " + typeName, rsToTest
2945: .wasNull());
2946: assertEquals(null, rsToTest.getTime(i + 1));
2947: assertTrue("for type " + typeName, rsToTest
2948: .wasNull());
2949: assertEquals(null, rsToTest.getTimestamp(i + 1));
2950: assertTrue("for type " + typeName, rsToTest
2951: .wasNull());
2952: assertEquals(null, rsToTest.getUnicodeStream(i + 1));
2953: assertTrue("for type " + typeName, rsToTest
2954: .wasNull());
2955: assertEquals(null, rsToTest.getURL(i + 1));
2956: assertTrue("for type " + typeName, rsToTest
2957: .wasNull());
2958: }
2959: }
2960: }
2961: }
2962:
2963: private void testAllFieldsForNotNull(ResultSet rsToTest,
2964: List wasDatetimeTypeList) throws Exception {
2965: ResultSetMetaData rsmd = this .rs.getMetaData();
2966: int numCols = rsmd.getColumnCount();
2967:
2968: while (rsToTest.next()) {
2969: for (int i = 0; i < numCols - 1; i++) {
2970: boolean wasDatetimeType = ((Boolean) wasDatetimeTypeList
2971: .get(i)).booleanValue();
2972: String typeName = rsmd.getColumnTypeName(i + 1);
2973: int sqlType = rsmd.getColumnType(i + 1);
2974:
2975: if (!"BIT".equalsIgnoreCase(typeName)
2976: && sqlType != Types.BINARY
2977: && sqlType != Types.VARBINARY
2978: && sqlType != Types.LONGVARBINARY) {
2979: if (!wasDatetimeType) {
2980:
2981: assertEquals(false, rsToTest.getBoolean(i + 1));
2982:
2983: assertTrue(!rsToTest.wasNull());
2984:
2985: assertEquals(0, rsToTest.getDouble(i + 1), 0 /* delta */);
2986: assertTrue(!rsToTest.wasNull());
2987: assertEquals(0, rsToTest.getFloat(i + 1), 0 /* delta */);
2988: assertTrue(!rsToTest.wasNull());
2989: assertEquals(0, rsToTest.getInt(i + 1));
2990: assertTrue(!rsToTest.wasNull());
2991: assertEquals(0, rsToTest.getLong(i + 1));
2992: assertTrue(!rsToTest.wasNull());
2993: assertEquals(0, rsToTest.getByte(i + 1));
2994: assertTrue(!rsToTest.wasNull());
2995: assertEquals(0, rsToTest.getShort(i + 1));
2996: assertTrue(!rsToTest.wasNull());
2997: }
2998:
2999: assertNotNull(rsToTest.getObject(i + 1));
3000: assertTrue(!rsToTest.wasNull());
3001: assertNotNull(rsToTest.getString(i + 1));
3002: assertTrue(!rsToTest.wasNull());
3003: assertNotNull(rsToTest.getAsciiStream(i + 1));
3004: assertTrue(!rsToTest.wasNull());
3005:
3006: assertNotNull(rsToTest.getBinaryStream(i + 1));
3007: assertTrue(!rsToTest.wasNull());
3008: assertNotNull(rsToTest.getBlob(i + 1));
3009: assertTrue(!rsToTest.wasNull());
3010: assertNotNull(rsToTest.getBytes(i + 1));
3011: assertTrue(!rsToTest.wasNull());
3012: assertNotNull(rsToTest.getCharacterStream(i + 1));
3013: assertTrue(!rsToTest.wasNull());
3014: assertNotNull(rsToTest.getClob(i + 1));
3015: assertTrue(!rsToTest.wasNull());
3016:
3017: String columnClassName = rsmd
3018: .getColumnClassName(i + 1);
3019:
3020: boolean canBeUsedAsDate = !("java.lang.Boolean"
3021: .equals(columnClassName)
3022: || "java.lang.Double"
3023: .equals(columnClassName)
3024: || "java.lang.Float"
3025: .equals(columnClassName)
3026: || "java.lang.Real".equals(columnClassName) || "java.math.BigDecimal"
3027: .equals(columnClassName));
3028:
3029: if (canBeUsedAsDate) {
3030: assertNotNull(rsToTest.getDate(i + 1));
3031: assertTrue(!rsToTest.wasNull());
3032: assertNotNull(rsToTest.getTime(i + 1));
3033: assertTrue(!rsToTest.wasNull());
3034: assertNotNull(rsToTest.getTimestamp(i + 1));
3035: assertTrue(!rsToTest.wasNull());
3036: }
3037:
3038: assertNotNull(rsToTest.getUnicodeStream(i + 1));
3039: assertTrue(!rsToTest.wasNull());
3040:
3041: try {
3042: if (!isRunningOnJdk131()) {
3043: assertNotNull(rsToTest.getURL(i + 1));
3044: }
3045: } catch (SQLException sqlEx) {
3046: assertTrue(sqlEx.getMessage().indexOf("URL") != -1);
3047: }
3048:
3049: assertTrue(!rsToTest.wasNull());
3050: }
3051: }
3052: }
3053: }
3054:
3055: public void testNPEWithStatementsAndTime() throws Exception {
3056: try {
3057: this .stmt.executeUpdate("DROP TABLE IF EXISTS testNPETime");
3058: this .stmt
3059: .executeUpdate("CREATE TABLE testNPETime (field1 TIME NULL, field2 DATETIME NULL, field3 DATE NULL)");
3060: this .stmt
3061: .executeUpdate("INSERT INTO testNPETime VALUES (null, null, null)");
3062: this .pstmt = this .conn
3063: .prepareStatement("SELECT field1, field2, field3 FROM testNPETime");
3064: this .rs = this .pstmt.executeQuery();
3065: this .rs.next();
3066:
3067: for (int i = 0; i < 3; i++) {
3068: assertEquals(null, this .rs.getTime(i + 1));
3069: assertEquals(true, this .rs.wasNull());
3070: }
3071:
3072: for (int i = 0; i < 3; i++) {
3073: assertEquals(null, this .rs.getTimestamp(i + 1));
3074: assertEquals(true, this .rs.wasNull());
3075: }
3076:
3077: for (int i = 0; i < 3; i++) {
3078: assertEquals(null, this .rs.getDate(i + 1));
3079: assertEquals(true, this .rs.wasNull());
3080: }
3081: } finally {
3082: this .stmt.executeUpdate("DROP TABLE IF EXISTS testNPETime");
3083: }
3084: }
3085:
3086: public void testEmptyStringsWithNumericGetters() throws Exception {
3087: try {
3088: createTable("emptyStringTable", "(field1 char(32))");
3089: this .stmt
3090: .executeUpdate("INSERT INTO emptyStringTable VALUES ('')");
3091: this .rs = this .stmt
3092: .executeQuery("SELECT field1 FROM emptyStringTable");
3093: assertTrue(this .rs.next());
3094: createTable("emptyStringTable", "(field1 char(32))");
3095: this .stmt
3096: .executeUpdate("INSERT INTO emptyStringTable VALUES ('')");
3097:
3098: this .rs = this .stmt
3099: .executeQuery("SELECT field1 FROM emptyStringTable");
3100: assertTrue(this .rs.next());
3101: checkEmptyConvertToZero();
3102:
3103: this .rs = this .conn.prepareStatement(
3104: "SELECT field1 FROM emptyStringTable")
3105: .executeQuery();
3106: assertTrue(this .rs.next());
3107: checkEmptyConvertToZero();
3108:
3109: Properties props = new Properties();
3110: props.setProperty("useFastIntParsing", "false");
3111:
3112: Connection noFastIntParseConn = getConnectionWithProps(props);
3113: Statement noFastIntStmt = noFastIntParseConn
3114: .createStatement();
3115:
3116: this .rs = noFastIntStmt
3117: .executeQuery("SELECT field1 FROM emptyStringTable");
3118: assertTrue(this .rs.next());
3119: checkEmptyConvertToZero();
3120:
3121: this .rs = noFastIntParseConn.prepareStatement(
3122: "SELECT field1 FROM emptyStringTable")
3123: .executeQuery();
3124: assertTrue(this .rs.next());
3125: checkEmptyConvertToZero();
3126:
3127: //
3128: // Now, be more pedantic....
3129: //
3130:
3131: props = new Properties();
3132: props.setProperty("emptyStringsConvertToZero", "false");
3133:
3134: Connection pedanticConn = getConnectionWithProps(props);
3135: Statement pedanticStmt = pedanticConn.createStatement();
3136:
3137: this .rs = pedanticStmt
3138: .executeQuery("SELECT field1 FROM emptyStringTable");
3139: assertTrue(this .rs.next());
3140:
3141: checkEmptyConvertToZeroException();
3142:
3143: this .rs = pedanticConn.prepareStatement(
3144: "SELECT field1 FROM emptyStringTable")
3145: .executeQuery();
3146: assertTrue(this .rs.next());
3147: checkEmptyConvertToZeroException();
3148:
3149: props = new Properties();
3150: props.setProperty("emptyStringsConvertToZero", "false");
3151: props.setProperty("useFastIntParsing", "false");
3152:
3153: pedanticConn = getConnectionWithProps(props);
3154: pedanticStmt = pedanticConn.createStatement();
3155:
3156: this .rs = pedanticStmt
3157: .executeQuery("SELECT field1 FROM emptyStringTable");
3158: assertTrue(this .rs.next());
3159:
3160: checkEmptyConvertToZeroException();
3161:
3162: this .rs = pedanticConn.prepareStatement(
3163: "SELECT field1 FROM emptyStringTable")
3164: .executeQuery();
3165: assertTrue(this .rs.next());
3166: checkEmptyConvertToZeroException();
3167:
3168: } finally {
3169: if (this .rs != null) {
3170: this .rs.close();
3171:
3172: this .rs = null;
3173: }
3174: }
3175: }
3176:
3177: public void testNegativeOneIsTrue() throws Exception {
3178: if (!versionMeetsMinimum(5, 0, 3)) {
3179: String tableName = "testNegativeOneIsTrue";
3180: Connection tinyInt1IsBitConn = null;
3181:
3182: try {
3183: createTable(tableName, "(field1 BIT)");
3184: this .stmt.executeUpdate("INSERT INTO " + tableName
3185: + " VALUES (-1)");
3186:
3187: Properties props = new Properties();
3188: props.setProperty("tinyInt1isBit", "true");
3189: tinyInt1IsBitConn = getConnectionWithProps(props);
3190:
3191: this .rs = tinyInt1IsBitConn
3192: .createStatement()
3193: .executeQuery("SELECT field1 FROM " + tableName);
3194: assertTrue(this .rs.next());
3195: assertEquals(true, this .rs.getBoolean(1));
3196:
3197: this .rs = tinyInt1IsBitConn.prepareStatement(
3198: "SELECT field1 FROM " + tableName)
3199: .executeQuery();
3200: assertTrue(this .rs.next());
3201: assertEquals(true, this .rs.getBoolean(1));
3202:
3203: } finally {
3204: if (tinyInt1IsBitConn != null) {
3205: tinyInt1IsBitConn.close();
3206: }
3207: }
3208: }
3209: }
3210:
3211: /**
3212: * @throws SQLException
3213: */
3214: private void checkEmptyConvertToZero() throws SQLException {
3215: assertEquals(0, this .rs.getByte(1));
3216: assertEquals(0, this .rs.getShort(1));
3217: assertEquals(0, this .rs.getInt(1));
3218: assertEquals(0, this .rs.getLong(1));
3219: assertEquals(0, this .rs.getFloat(1), 0.1);
3220: assertEquals(0, this .rs.getDouble(1), 0.1);
3221: assertEquals(0, this .rs.getBigDecimal(1).intValue());
3222: }
3223:
3224: /**
3225: *
3226: */
3227: private void checkEmptyConvertToZeroException() {
3228: try {
3229: assertEquals(0, this .rs.getByte(1));
3230: fail("Should've thrown an exception!");
3231: } catch (SQLException sqlEx) {
3232: assertEquals(
3233: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3234: sqlEx.getSQLState());
3235: }
3236: try {
3237: assertEquals(0, this .rs.getShort(1));
3238: fail("Should've thrown an exception!");
3239: } catch (SQLException sqlEx) {
3240: assertEquals(
3241: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3242: sqlEx.getSQLState());
3243: }
3244: try {
3245: assertEquals(0, this .rs.getInt(1));
3246: fail("Should've thrown an exception!");
3247: } catch (SQLException sqlEx) {
3248: assertEquals(
3249: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3250: sqlEx.getSQLState());
3251: }
3252: try {
3253: assertEquals(0, this .rs.getLong(1));
3254: fail("Should've thrown an exception!");
3255: } catch (SQLException sqlEx) {
3256: assertEquals(
3257: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3258: sqlEx.getSQLState());
3259: }
3260: try {
3261: assertEquals(0, this .rs.getFloat(1), 0.1);
3262: fail("Should've thrown an exception!");
3263: } catch (SQLException sqlEx) {
3264: assertEquals(
3265: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3266: sqlEx.getSQLState());
3267: }
3268: try {
3269: assertEquals(0, this .rs.getDouble(1), 0.1);
3270: fail("Should've thrown an exception!");
3271: } catch (SQLException sqlEx) {
3272: assertEquals(
3273: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3274: sqlEx.getSQLState());
3275: }
3276: try {
3277: assertEquals(0, this .rs.getBigDecimal(1).intValue());
3278: fail("Should've thrown an exception!");
3279: } catch (SQLException sqlEx) {
3280: assertEquals(
3281: SQLError.SQL_STATE_INVALID_CHARACTER_VALUE_FOR_CAST,
3282: sqlEx.getSQLState());
3283: }
3284: }
3285:
3286: /**
3287: * Tests fix for BUG#10485, SQLException thrown when retrieving YEAR(2) with
3288: * ResultSet.getString().
3289: *
3290: * @throws Exception
3291: * if the test fails.
3292: */
3293: public void testBug10485() throws Exception {
3294: String tableName = "testBug10485";
3295:
3296: Calendar nydCal = null;
3297:
3298: if (((com.mysql.jdbc.Connection) this .conn)
3299: .getUseGmtMillisForDatetimes()) {
3300: nydCal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
3301: } else {
3302: nydCal = Calendar.getInstance();
3303: }
3304:
3305: nydCal.set(2005, 0, 1, 0, 0, 0);
3306:
3307: Date newYears2005 = new Date(nydCal.getTime().getTime());
3308:
3309: createTable(tableName, "(field1 YEAR(2))");
3310: this .stmt.executeUpdate("INSERT INTO " + tableName
3311: + " VALUES ('05')");
3312:
3313: this .rs = this .stmt.executeQuery("SELECT field1 FROM "
3314: + tableName);
3315: assertTrue(this .rs.next());
3316:
3317: assertEquals(newYears2005.toString(), this .rs.getString(1));
3318:
3319: this .rs = this .conn.prepareStatement(
3320: "SELECT field1 FROM " + tableName).executeQuery();
3321: assertTrue(this .rs.next());
3322: assertEquals(newYears2005.toString(), this .rs.getString(1));
3323:
3324: Properties props = new Properties();
3325: props.setProperty("yearIsDateType", "false");
3326:
3327: Connection yearShortConn = getConnectionWithProps(props);
3328: this .rs = yearShortConn.createStatement().executeQuery(
3329: "SELECT field1 FROM " + tableName);
3330: assertTrue(this .rs.next());
3331: assertEquals("05", this .rs.getString(1));
3332:
3333: this .rs = yearShortConn.prepareStatement(
3334: "SELECT field1 FROM " + tableName).executeQuery();
3335: assertTrue(this .rs.next());
3336: assertEquals("05", this .rs.getString(1));
3337:
3338: if (versionMeetsMinimum(5, 0)) {
3339: try {
3340: this .stmt
3341: .executeUpdate("DROP PROCEDURE IF EXISTS testBug10485");
3342: this .stmt
3343: .executeUpdate("CREATE PROCEDURE testBug10485()\nBEGIN\nSELECT field1 FROM "
3344: + tableName + ";\nEND");
3345:
3346: this .rs = this .conn
3347: .prepareCall("{CALL testBug10485()}")
3348: .executeQuery();
3349: assertTrue(this .rs.next());
3350: assertEquals(newYears2005.toString(), this .rs
3351: .getString(1));
3352:
3353: this .rs = yearShortConn.prepareCall(
3354: "{CALL testBug10485()}").executeQuery();
3355: assertTrue(this .rs.next());
3356: assertEquals("05", this .rs.getString(1));
3357: } finally {
3358: this .stmt
3359: .executeUpdate("DROP PROCEDURE IF EXISTS testBug10485");
3360: }
3361: }
3362: }
3363:
3364: /**
3365: * Tests fix for BUG#11552, wrong values returned from server-side prepared
3366: * statements if values are unsigned.
3367: *
3368: * @throws Exception
3369: * if the test fails.
3370: */
3371: public void testBug11552() throws Exception {
3372: try {
3373: createTable(
3374: "testBug11552",
3375: "(field1 INT UNSIGNED, field2 TINYINT UNSIGNED, field3 SMALLINT UNSIGNED, field4 BIGINT UNSIGNED)");
3376: this .stmt
3377: .executeUpdate("INSERT INTO testBug11552 VALUES (2, 2, 2, 2), (4294967294, 255, 32768, 18446744073709551615 )");
3378: this .rs = this .conn
3379: .prepareStatement(
3380: "SELECT field1, field2, field3, field4 FROM testBug11552 ORDER BY field1 ASC")
3381: .executeQuery();
3382: this .rs.next();
3383: assertEquals("2", this .rs.getString(1));
3384: assertEquals("2", this .rs.getObject(1).toString());
3385: assertEquals("2", String.valueOf(this .rs.getLong(1)));
3386:
3387: assertEquals("2", this .rs.getString(2));
3388: assertEquals("2", this .rs.getObject(2).toString());
3389: assertEquals("2", String.valueOf(this .rs.getLong(2)));
3390:
3391: assertEquals("2", this .rs.getString(3));
3392: assertEquals("2", this .rs.getObject(3).toString());
3393: assertEquals("2", String.valueOf(this .rs.getLong(3)));
3394:
3395: assertEquals("2", this .rs.getString(4));
3396: assertEquals("2", this .rs.getObject(4).toString());
3397: assertEquals("2", String.valueOf(this .rs.getLong(4)));
3398:
3399: this .rs.next();
3400:
3401: assertEquals("4294967294", this .rs.getString(1));
3402: assertEquals("4294967294", this .rs.getObject(1).toString());
3403: assertEquals("4294967294", String.valueOf(this .rs
3404: .getLong(1)));
3405:
3406: assertEquals("255", this .rs.getString(2));
3407: assertEquals("255", this .rs.getObject(2).toString());
3408: assertEquals("255", String.valueOf(this .rs.getLong(2)));
3409:
3410: assertEquals("32768", this .rs.getString(3));
3411: assertEquals("32768", this .rs.getObject(3).toString());
3412: assertEquals("32768", String.valueOf(this .rs.getLong(3)));
3413:
3414: assertEquals("18446744073709551615", this .rs.getString(4));
3415: assertEquals("18446744073709551615", this .rs.getObject(4)
3416: .toString());
3417: } finally {
3418: if (this .rs != null) {
3419: this .rs.close();
3420: this .rs = null;
3421: }
3422: }
3423: }
3424:
3425: /**
3426: * Tests correct detection of truncation of non-sig digits.
3427: *
3428: * @throws Exception
3429: * if the test fails.
3430: */
3431: public void testTruncationOfNonSigDigits() throws Exception {
3432: if (versionMeetsMinimum(4, 1, 0)) {
3433: createTable("testTruncationOfNonSigDigits",
3434: "(field1 decimal(12,2), field2 varchar(2)) ENGINE=Innodb");
3435:
3436: this .stmt
3437: .executeUpdate("INSERT INTO testTruncationOfNonSigDigits VALUES (123456.2345, 'ab')");
3438:
3439: try {
3440: this .stmt
3441: .executeUpdate("INSERT INTO testTruncationOfNonSigDigits VALUES (1234561234561.2345, 'ab')");
3442: fail("Should have thrown a truncation error");
3443: } catch (MysqlDataTruncation truncEx) {
3444: // We expect this
3445: }
3446:
3447: try {
3448: this .stmt
3449: .executeUpdate("INSERT INTO testTruncationOfNonSigDigits VALUES (1234.2345, 'abcd')");
3450: fail("Should have thrown a truncation error");
3451: } catch (MysqlDataTruncation truncEx) {
3452: // We expect this
3453: }
3454: }
3455: }
3456:
3457: /**
3458: * Tests fix for BUG#20479 - Updatable result set throws ClassCastException
3459: * when there is row data and moveToInsertRow() is called.
3460: *
3461: * @throws Exception if the test fails.
3462: */
3463: public void testBug20479() throws Exception {
3464: PreparedStatement updStmt = null;
3465:
3466: createTable("testBug20479", "(field1 INT NOT NULL PRIMARY KEY)");
3467: this .stmt
3468: .executeUpdate("INSERT INTO testBug20479 VALUES (2), (3), (4)");
3469:
3470: try {
3471: updStmt = this .conn
3472: .prepareStatement(
3473: "SELECT * FROM testBug20479 Where field1 > ? ORDER BY field1",
3474: ResultSet.TYPE_SCROLL_SENSITIVE,
3475: ResultSet.CONCUR_UPDATABLE);
3476:
3477: updStmt.setInt(1, 1);
3478: this .rs = updStmt.executeQuery();
3479: this .rs.next();
3480: this .rs.moveToInsertRow();
3481: this .rs.updateInt(1, 45);
3482: this .rs.insertRow();
3483: this .rs.moveToCurrentRow();
3484: assertEquals(2, this .rs.getInt(1));
3485: this .rs.next();
3486: this .rs.next();
3487: this .rs.next();
3488: assertEquals(45, this .rs.getInt(1));
3489: } finally {
3490: if (this .rs != null) {
3491: this .rs.close();
3492: this .rs = null;
3493: }
3494:
3495: if (updStmt != null) {
3496: updStmt.close();
3497: }
3498: }
3499: }
3500:
3501: /**
3502: * Tests fix for BUG#20485 - Updatable result set that contains
3503: * a BIT column fails when server-side prepared statements are used.
3504: *
3505: * @throws Exception if the test fails.
3506: */
3507: public void testBug20485() throws Exception {
3508: if (!versionMeetsMinimum(5, 0)) {
3509: return;
3510: }
3511:
3512: PreparedStatement updStmt = null;
3513:
3514: createTable("testBug20485",
3515: "(field1 INT NOT NULL PRIMARY KEY, field2 BIT)");
3516: this .stmt
3517: .executeUpdate("INSERT INTO testBug20485 VALUES (2, 1), (3, 1), (4, 1)");
3518:
3519: try {
3520: updStmt = this .conn.prepareStatement(
3521: "SELECT * FROM testBug20485 ORDER BY field1",
3522: ResultSet.TYPE_SCROLL_SENSITIVE,
3523: ResultSet.CONCUR_UPDATABLE);
3524: this .rs = updStmt.executeQuery();
3525: } finally {
3526: if (this .rs != null) {
3527: this .rs.close();
3528: this .rs = null;
3529: }
3530:
3531: if (updStmt != null) {
3532: updStmt.close();
3533: }
3534: }
3535: }
3536:
3537: /**
3538: * Tests fix for BUG#20306 - ResultSet.getShort() for UNSIGNED TINYINT
3539: * returns incorrect values when using server-side prepared statements.
3540: *
3541: * @throws Exception if the test fails.
3542: */
3543: public void testBug20306() throws Exception {
3544: createTable("testBug20306",
3545: "(field1 TINYINT UNSIGNED, field2 TINYINT UNSIGNED)");
3546: this .stmt
3547: .executeUpdate("INSERT INTO testBug20306 VALUES (2, 133)");
3548: try {
3549: this .pstmt = this .conn
3550: .prepareStatement("SELECT field1, field2 FROM testBug20306");
3551: this .rs = this .pstmt.executeQuery();
3552: this .rs.next();
3553: checkBug20306();
3554:
3555: this .rs = this .stmt
3556: .executeQuery("SELECT field1, field2 FROM testBug20306");
3557: this .rs.next();
3558: checkBug20306();
3559:
3560: } finally {
3561: closeMemberJDBCResources();
3562: }
3563: }
3564:
3565: private void checkBug20306() throws Exception {
3566: assertEquals(2, this .rs.getByte(1));
3567: assertEquals(2, this .rs.getInt(1));
3568: assertEquals(2, this .rs.getShort(1));
3569: assertEquals(2, this .rs.getLong(1));
3570: assertEquals(2.0, this .rs.getFloat(1), 0);
3571: assertEquals(2.0, this .rs.getDouble(1), 0);
3572: assertEquals(2, this .rs.getBigDecimal(1).intValue());
3573:
3574: assertEquals(133, this .rs.getInt(2));
3575: assertEquals(133, this .rs.getShort(2));
3576: assertEquals(133, this .rs.getLong(2));
3577: assertEquals(133.0, this .rs.getFloat(2), 0);
3578: assertEquals(133.0, this .rs.getDouble(2), 0);
3579: assertEquals(133, this .rs.getBigDecimal(2).intValue());
3580: }
3581:
3582: /**
3583: * Tests fix for BUG#21062 - ResultSet.getSomeInteger() doesn't work for BIT(>1)
3584: *
3585: * @throws Exception if the test fails.
3586: */
3587: public void testBug21062() throws Exception {
3588: if (versionMeetsMinimum(5, 0, 5)) {
3589: createTable("testBug21062",
3590: "(bit_7_field BIT(7), bit_31_field BIT(31), bit_12_field BIT(12))");
3591:
3592: int max7Bits = 127;
3593: long max31Bits = 2147483647L;
3594: int max12Bits = 4095;
3595:
3596: this .stmt.executeUpdate("INSERT INTO testBug21062 VALUES ("
3597: + max7Bits + "," + max31Bits + "," + max12Bits
3598: + ")");
3599:
3600: this .rs = this .stmt
3601: .executeQuery("SELECT * FROM testBug21062");
3602:
3603: this .rs.next();
3604:
3605: assertEquals(127, this .rs.getInt(1));
3606: assertEquals(127, this .rs.getShort(1));
3607: assertEquals(127, this .rs.getLong(1));
3608:
3609: assertEquals(2147483647, this .rs.getInt(2));
3610: assertEquals(2147483647, this .rs.getLong(2));
3611:
3612: assertEquals(4095, this .rs.getInt(3));
3613: assertEquals(4095, this .rs.getShort(3));
3614: assertEquals(4095, this .rs.getLong(3));
3615: }
3616: }
3617:
3618: /**
3619: * Tests fix for BUG#18880 - ResultSet.getFloatFromString() can't retrieve
3620: * values near Float.MIN/MAX_VALUE.
3621: *
3622: * @throws Exception if the test fails.
3623: */
3624: public void testBug18880() throws Exception {
3625: try {
3626: this .rs = this .stmt.executeQuery("SELECT 3.4E38,1.4E-45");
3627: this .rs.next();
3628: this .rs.getFloat(1);
3629: this .rs.getFloat(2);
3630: } finally {
3631: closeMemberJDBCResources();
3632: }
3633: }
3634:
3635: /**
3636: * Tests fix for BUG#15677, wrong values returned from getShort() if SQL
3637: * values are tinyint unsigned.
3638: *
3639: * @throws Exception
3640: * if the test fails.
3641: */
3642: public void testBug15677() throws Exception {
3643: try {
3644: createTable("testBug15677",
3645: "(id BIGINT, field1 TINYINT UNSIGNED)");
3646: this .stmt
3647: .executeUpdate("INSERT INTO testBug15677 VALUES (1, 0), (2, 127), (3, 128), (4, 255)");
3648: this .rs = this .conn.prepareStatement(
3649: "SELECT field1 FROM testBug15677 ORDER BY id ASC")
3650: .executeQuery();
3651: this .rs.next();
3652: assertEquals("0", this .rs.getString(1));
3653: assertEquals("0", this .rs.getObject(1).toString());
3654: assertEquals("0", String.valueOf(this .rs.getShort(1)));
3655:
3656: this .rs.next();
3657: assertEquals("127", this .rs.getString(1));
3658: assertEquals("127", this .rs.getObject(1).toString());
3659: assertEquals("127", String.valueOf(this .rs.getShort(1)));
3660:
3661: this .rs.next();
3662: assertEquals("128", this .rs.getString(1));
3663: assertEquals("128", this .rs.getObject(1).toString());
3664: assertEquals("128", String.valueOf(this .rs.getShort(1)));
3665:
3666: this .rs.next();
3667: assertEquals("255", this .rs.getString(1));
3668: assertEquals("255", this .rs.getObject(1).toString());
3669: assertEquals("255", String.valueOf(this .rs.getShort(1)));
3670: } finally {
3671: closeMemberJDBCResources();
3672: }
3673: }
3674:
3675: public void testBooleans() throws Exception {
3676: if (versionMeetsMinimum(5, 0)) {
3677: try {
3678: createTable(
3679: "testBooleans",
3680: "(ob int, field1 BOOLEAN, field2 TINYINT, field3 SMALLINT, field4 INT, field5 MEDIUMINT, field6 BIGINT, field7 FLOAT, field8 DOUBLE, field9 DECIMAL, field10 VARCHAR(32), field11 BINARY(3), field12 VARBINARY(3), field13 BLOB)");
3681: this .pstmt = this .conn
3682: .prepareStatement("INSERT INTO testBooleans VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
3683:
3684: this .pstmt.setInt(1, 1);
3685: this .pstmt.setBoolean(2, false);
3686: this .pstmt.setByte(3, (byte) 0);
3687: this .pstmt.setInt(4, 0);
3688: this .pstmt.setInt(5, 0);
3689: this .pstmt.setInt(6, 0);
3690: this .pstmt.setLong(7, 0);
3691: this .pstmt.setFloat(8, 0);
3692: this .pstmt.setDouble(9, 0);
3693: this .pstmt.setBigDecimal(10, new BigDecimal("0"));
3694: this .pstmt.setString(11, "false");
3695: this .pstmt.setBytes(12, new byte[] { 0 });
3696: this .pstmt.setBytes(13, new byte[] { 0 });
3697: this .pstmt.setBytes(14, new byte[] { 0 });
3698:
3699: this .pstmt.executeUpdate();
3700:
3701: this .pstmt.setInt(1, 2);
3702: this .pstmt.setBoolean(2, true);
3703: this .pstmt.setByte(3, (byte) 1);
3704: this .pstmt.setInt(4, 1);
3705: this .pstmt.setInt(5, 1);
3706: this .pstmt.setInt(6, 1);
3707: this .pstmt.setLong(7, 1);
3708: this .pstmt.setFloat(8, 1);
3709: this .pstmt.setDouble(9, 1);
3710: this .pstmt.setBigDecimal(10, new BigDecimal("1"));
3711: this .pstmt.setString(11, "true");
3712: this .pstmt.setBytes(12, new byte[] { 1 });
3713: this .pstmt.setBytes(13, new byte[] { 1 });
3714: this .pstmt.setBytes(14, new byte[] { 1 });
3715: this .pstmt.executeUpdate();
3716:
3717: this .pstmt.setInt(1, 3);
3718: this .pstmt.setBoolean(2, true);
3719: this .pstmt.setByte(3, (byte) 1);
3720: this .pstmt.setInt(4, 1);
3721: this .pstmt.setInt(5, 1);
3722: this .pstmt.setInt(6, 1);
3723: this .pstmt.setLong(7, 1);
3724: this .pstmt.setFloat(8, 1);
3725: this .pstmt.setDouble(9, 1);
3726: this .pstmt.setBigDecimal(10, new BigDecimal("1"));
3727: this .pstmt.setString(11, "true");
3728: this .pstmt.setBytes(12, new byte[] { 2 });
3729: this .pstmt.setBytes(13, new byte[] { 2 });
3730: this .pstmt.setBytes(14, new byte[] { 2 });
3731: this .pstmt.executeUpdate();
3732:
3733: this .pstmt.setInt(1, 4);
3734: this .pstmt.setBoolean(2, true);
3735: this .pstmt.setByte(3, (byte) 1);
3736: this .pstmt.setInt(4, 1);
3737: this .pstmt.setInt(5, 1);
3738: this .pstmt.setInt(6, 1);
3739: this .pstmt.setLong(7, 1);
3740: this .pstmt.setFloat(8, 1);
3741: this .pstmt.setDouble(9, 1);
3742: this .pstmt.setBigDecimal(10, new BigDecimal("1"));
3743: this .pstmt.setString(11, "true");
3744: this .pstmt.setBytes(12, new byte[] { -1 });
3745: this .pstmt.setBytes(13, new byte[] { -1 });
3746: this .pstmt.setBytes(14, new byte[] { -1 });
3747: this .pstmt.executeUpdate();
3748:
3749: this .pstmt.setInt(1, 5);
3750: this .pstmt.setBoolean(2, false);
3751: this .pstmt.setByte(3, (byte) 0);
3752: this .pstmt.setInt(4, 0);
3753: this .pstmt.setInt(5, 0);
3754: this .pstmt.setInt(6, 0);
3755: this .pstmt.setLong(7, 0);
3756: this .pstmt.setFloat(8, 0);
3757: this .pstmt.setDouble(9, 0);
3758: this .pstmt.setBigDecimal(10, new BigDecimal("0"));
3759: this .pstmt.setString(11, "false");
3760: this .pstmt.setBytes(12, new byte[] { 0, 0 });
3761: this .pstmt.setBytes(13, new byte[] { 0, 0 });
3762: this .pstmt.setBytes(14, new byte[] { 0, 0 });
3763: this .pstmt.executeUpdate();
3764:
3765: this .pstmt.setInt(1, 6);
3766: this .pstmt.setBoolean(2, true);
3767: this .pstmt.setByte(3, (byte) 1);
3768: this .pstmt.setInt(4, 1);
3769: this .pstmt.setInt(5, 1);
3770: this .pstmt.setInt(6, 1);
3771: this .pstmt.setLong(7, 1);
3772: this .pstmt.setFloat(8, 1);
3773: this .pstmt.setDouble(9, 1);
3774: this .pstmt.setBigDecimal(10, new BigDecimal("1"));
3775: this .pstmt.setString(11, "true");
3776: this .pstmt.setBytes(12, new byte[] { 1, 0 });
3777: this .pstmt.setBytes(13, new byte[] { 1, 0 });
3778: this .pstmt.setBytes(14, new byte[] { 1, 0 });
3779: this .pstmt.executeUpdate();
3780:
3781: this .pstmt.setInt(1, 7);
3782: this .pstmt.setBoolean(2, false);
3783: this .pstmt.setByte(3, (byte) 0);
3784: this .pstmt.setInt(4, 0);
3785: this .pstmt.setInt(5, 0);
3786: this .pstmt.setInt(6, 0);
3787: this .pstmt.setLong(7, 0);
3788: this .pstmt.setFloat(8, 0);
3789: this .pstmt.setDouble(9, 0);
3790: this .pstmt.setBigDecimal(10, new BigDecimal("0"));
3791: this .pstmt.setString(11, "");
3792: this .pstmt.setBytes(12, new byte[] {});
3793: this .pstmt.setBytes(13, new byte[] {});
3794: this .pstmt.setBytes(14, new byte[] {});
3795: this .pstmt.executeUpdate();
3796:
3797: this .rs = this .stmt
3798: .executeQuery("SELECT field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13 FROM testBooleans ORDER BY ob");
3799:
3800: boolean[] testVals = new boolean[] { false, true, true,
3801: true, false, true, false };
3802:
3803: int i = 0;
3804:
3805: while (this .rs.next()) {
3806: for (int j = 0; j > 13; j++) {
3807: assertEquals("For field_" + (j + 1) + ", row "
3808: + (i + 1), testVals[i], this .rs
3809: .getBoolean(j + 1));
3810: }
3811:
3812: i++;
3813: }
3814:
3815: this .rs = this .conn
3816: .prepareStatement(
3817: "SELECT field1, field2, field3 FROM testBooleans ORDER BY ob")
3818: .executeQuery();
3819:
3820: i = 0;
3821:
3822: while (this .rs.next()) {
3823: for (int j = 0; j > 13; j++) {
3824: assertEquals("For field_" + (j + 1) + ", row "
3825: + (i + 1), testVals[i], this .rs
3826: .getBoolean(j + 1));
3827: }
3828:
3829: i++;
3830: }
3831: } finally {
3832: closeMemberJDBCResources();
3833: }
3834: }
3835: }
3836:
3837: /**
3838: * Tests fix(es) for BUG#21379 - column names don't match metadata
3839: * in cases where server doesn't return original column names (functions)
3840: * thus breaking compatibility with applications that expect 1-1 mappings
3841: * between findColumn() and rsmd.getColumnName().
3842: *
3843: * @throws Exception if the test fails.
3844: */
3845: public void testBug21379() throws Exception {
3846: try {
3847: //
3848: // Test the 1-1 mapping between rs.findColumn() and rsmd.getColumnName()
3849: // in the case where original column names are not returned,
3850: // thus preserving pre-C/J 5.0 behavior for these cases
3851: //
3852:
3853: this .rs = this .stmt
3854: .executeQuery("SELECT LAST_INSERT_ID() AS id");
3855: this .rs.next();
3856: assertEquals("id", this .rs.getMetaData().getColumnName(1));
3857: assertEquals(1, this .rs.findColumn("id"));
3858:
3859: if (versionMeetsMinimum(4, 1)) {
3860: //
3861: // test complete emulation of C/J 3.1 and earlier behavior
3862: // through configuration option
3863: //
3864:
3865: createTable("testBug21379", "(field1 int)");
3866: Connection legacyConn = null;
3867: Statement legacyStmt = null;
3868:
3869: try {
3870: Properties props = new Properties();
3871: props.setProperty("useOldAliasMetadataBehavior",
3872: "true");
3873: legacyConn = getConnectionWithProps(props);
3874: legacyStmt = legacyConn.createStatement();
3875:
3876: this .rs = legacyStmt
3877: .executeQuery("SELECT field1 AS foo, NOW() AS bar FROM testBug21379 AS blah");
3878: assertEquals(1, this .rs.findColumn("foo"));
3879: assertEquals(2, this .rs.findColumn("bar"));
3880: assertEquals("blah", this .rs.getMetaData()
3881: .getTableName(1));
3882: } finally {
3883: if (legacyConn != null) {
3884: legacyConn.close();
3885: }
3886: }
3887: }
3888: } finally {
3889: closeMemberJDBCResources();
3890: }
3891: }
3892:
3893: /**
3894: * Tests fix for BUG#21814 - time values outside valid range silently wrap
3895: *
3896: * @throws Exception if the test fails.
3897: */
3898: public void testBug21814() throws Exception {
3899: try {
3900: try {
3901: this .rs = this .stmt.executeQuery("SELECT '24:01'");
3902: this .rs.next();
3903: this .rs.getTime(1);
3904: fail("Expected exception");
3905: } catch (SQLException sqlEx) {
3906: assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
3907: .getSQLState());
3908: }
3909:
3910: try {
3911: this .rs = this .stmt.executeQuery("SELECT '23:92'");
3912: this .rs.next();
3913: this .rs.getTime(1);
3914: fail("Expected exception");
3915: } catch (SQLException sqlEx) {
3916: assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
3917: .getSQLState());
3918: }
3919: } finally {
3920: closeMemberJDBCResources();
3921: }
3922: }
3923:
3924: /**
3925: * Tests for a server bug - needs to be revisited when the server is fixed.
3926: *
3927: * @throws Exception
3928: * if the test fails.
3929: */
3930: public void testBug24710() throws Exception {
3931: if (!versionMeetsMinimum(6, 0)) {
3932: return;
3933: }
3934:
3935: createTable("testBug24710", "(x varbinary(256))");
3936:
3937: try {
3938: this .stmt
3939: .executeUpdate("insert into testBug24710(x) values(0x0000000000),"
3940: + "(0x1111111111),"
3941: + "(0x2222222222),"
3942: + "(0x3333333333),"
3943: + "(0x4444444444),"
3944: + "(0x5555555555),"
3945: + "(0x6666666666),"
3946: + "(0x7777777777),"
3947: + "(0x8888888888),"
3948: + "(0x9999999999),"
3949: + "(0xaaaaaaaaaa),"
3950: + "(0xbbbbbbbbbb),"
3951: + "(0xcccccccccc),"
3952: + "(0xdddddddddd),"
3953: + "(0xeeeeeeeeee),"
3954: + "(0xffffffffff)");
3955:
3956: this .rs = this .stmt
3957: .executeQuery("select t1.x t1x,(select x from testBug24710 t2 where t2.x=t1.x) t2x from testBug24710 t1");
3958:
3959: assertEquals(Types.VARBINARY, this .rs.getMetaData()
3960: .getColumnType(1));
3961: assertEquals(Types.VARBINARY, this .rs.getMetaData()
3962: .getColumnType(2));
3963:
3964: this .rs = ((com.mysql.jdbc.Connection) this .conn)
3965: .serverPrepareStatement(
3966: "select t1.x t1x,(select x from testBug24710 t2 where t2.x=t1.x) t2x from testBug24710 t1")
3967: .executeQuery();
3968:
3969: assertEquals(Types.VARBINARY, this .rs.getMetaData()
3970: .getColumnType(1));
3971: assertEquals(Types.VARBINARY, this .rs.getMetaData()
3972: .getColumnType(2));
3973: } finally {
3974: closeMemberJDBCResources();
3975: }
3976: }
3977:
3978: /**
3979: * Tests fix for BUG#25328 - BIT(> 1) is returned as java.lang.String
3980: * from ResultSet.getObject() rather than byte[].
3981: *
3982: * @throws Exception if the test fails.
3983: */
3984: public void testbug25328() throws Exception {
3985: if (!versionMeetsMinimum(5, 0)) {
3986: return;
3987: }
3988:
3989: createTable("testBug25382", "(BINARY_VAL BIT(64) NULL)");
3990:
3991: byte[] bytearr = new byte[8];
3992:
3993: this .pstmt = this .conn
3994: .prepareStatement("INSERT INTO testBug25382 VALUES(?)");
3995: try {
3996:
3997: this .pstmt.setObject(1, bytearr, java.sql.Types.BINARY);
3998: assertEquals(1, this .pstmt.executeUpdate());
3999: this .pstmt.clearParameters();
4000:
4001: this .rs = this .stmt
4002: .executeQuery("Select BINARY_VAL from testBug25382");
4003: this .rs.next();
4004: assertEquals(this .rs.getObject(1).getClass(), bytearr
4005: .getClass());
4006: } finally {
4007: closeMemberJDBCResources();
4008: }
4009: }
4010:
4011: /**
4012: * Tests fix for BUG#25517 - Statement.setMaxRows() is not effective
4013: * on result sets materialized from cursors.
4014: *
4015: * @throws Exception if the test fails
4016: */
4017: public void testBug25517() throws Exception {
4018: Connection fetchConn = null;
4019: Statement fetchStmt = null;
4020:
4021: createTable("testBug25517", "(field1 int)");
4022:
4023: StringBuffer insertBuf = new StringBuffer(
4024: "INSERT INTO testBug25517 VALUES (1)");
4025:
4026: for (int i = 0; i < 100; i++) {
4027: insertBuf.append(",(" + i + ")");
4028: }
4029:
4030: this .stmt.executeUpdate(insertBuf.toString());
4031:
4032: try {
4033: Properties props = new Properties();
4034: props.setProperty("useServerPrepStmts", "true");
4035: props.setProperty("useCursorFetch", "true");
4036:
4037: fetchConn = getConnectionWithProps(props);
4038: fetchStmt = fetchConn.createStatement();
4039:
4040: //int[] maxRows = new int[] {1, 4, 5, 11, 12, 13, 16, 50, 51, 52, 100};
4041: int[] fetchSizes = new int[] { 1, 4, 10, 25, 100 };
4042: List maxRows = new ArrayList();
4043: maxRows.add(new Integer(1));
4044:
4045: for (int i = 0; i < fetchSizes.length; i++) {
4046: if (fetchSizes[i] != 1) {
4047: maxRows.add(new Integer(fetchSizes[i] - 1));
4048: }
4049:
4050: maxRows.add(new Integer(fetchSizes[i]));
4051:
4052: if (i != fetchSizes.length - 1) {
4053: maxRows.add(new Integer(fetchSizes[i] + 1));
4054: }
4055: }
4056:
4057: for (int fetchIndex = 0; fetchIndex < fetchSizes.length; fetchIndex++) {
4058: fetchStmt.setFetchSize(fetchSizes[fetchIndex]);
4059:
4060: for (int maxRowIndex = 0; maxRowIndex < maxRows.size(); maxRowIndex++) {
4061:
4062: int maxRowsToExpect = ((Integer) maxRows
4063: .get(maxRowIndex)).intValue();
4064: fetchStmt.setMaxRows(maxRowsToExpect);
4065:
4066: int rowCount = 0;
4067:
4068: this .rs = fetchStmt
4069: .executeQuery("SELECT * FROM testBug25517");
4070:
4071: while (this .rs.next()) {
4072: rowCount++;
4073: }
4074:
4075: assertEquals(maxRowsToExpect, rowCount);
4076: }
4077: }
4078:
4079: this .pstmt = fetchConn
4080: .prepareStatement("SELECT * FROM testBug25517");
4081:
4082: for (int fetchIndex = 0; fetchIndex < fetchSizes.length; fetchIndex++) {
4083: this .pstmt.setFetchSize(fetchSizes[fetchIndex]);
4084:
4085: for (int maxRowIndex = 0; maxRowIndex < maxRows.size(); maxRowIndex++) {
4086:
4087: int maxRowsToExpect = ((Integer) maxRows
4088: .get(maxRowIndex)).intValue();
4089: this .pstmt.setMaxRows(maxRowsToExpect);
4090:
4091: int rowCount = 0;
4092:
4093: this .rs = this .pstmt.executeQuery();
4094:
4095: while (this .rs.next()) {
4096: rowCount++;
4097: }
4098:
4099: assertEquals(maxRowsToExpect, rowCount);
4100: }
4101: }
4102:
4103: } finally {
4104: closeMemberJDBCResources();
4105:
4106: if (fetchStmt != null) {
4107: fetchStmt.close();
4108: }
4109:
4110: if (fetchConn != null) {
4111: fetchConn.close();
4112: }
4113: }
4114: }
4115:
4116: /**
4117: * Tests fix for BUG#25787 - java.util.Date should be serialized for PreparedStatement.setObject().
4118: *
4119: * We add a new configuration option "treatUtilDateAsTimestamp", which is false by default,
4120: * as (1) We already had specific behavior to treat java.util.Date as a java.sql.Timestamp because
4121: * it's useful to many folks, and (2) that behavior will very likely be in JDBC-post-4.0 as a
4122: * requirement.
4123: *
4124: * @throws Exception if the test fails.
4125: */
4126: public void testBug25787() throws Exception {
4127: createTable("testBug25787", "(MY_OBJECT_FIELD BLOB)");
4128:
4129: Connection deserializeConn = null;
4130:
4131: Properties props = new Properties();
4132: props.setProperty("autoDeserialize", "true");
4133: props.setProperty("treatUtilDateAsTimestamp", "false");
4134:
4135: try {
4136: deserializeConn = getConnectionWithProps(props);
4137:
4138: this .pstmt = deserializeConn
4139: .prepareStatement("INSERT INTO testBug25787 (MY_OBJECT_FIELD) VALUES (?)");
4140: java.util.Date dt = new java.util.Date();
4141:
4142: this .pstmt.setObject(1, dt);
4143: this .pstmt.execute();
4144:
4145: this .rs = deserializeConn.createStatement().executeQuery(
4146: "SELECT MY_OBJECT_FIELD FROM testBug25787");
4147: this .rs.next();
4148: assertEquals("java.util.Date", this .rs.getObject(1)
4149: .getClass().getName());
4150: assertEquals(dt, this .rs.getObject(1));
4151: } finally {
4152: closeMemberJDBCResources();
4153: }
4154: }
4155:
4156: public void testTruncationDisable() throws Exception {
4157: Properties props = new Properties();
4158: props.setProperty("jdbcCompliantTruncation", "false");
4159: Connection truncConn = null;
4160:
4161: try {
4162: truncConn = getConnectionWithProps(props);
4163: this .rs = truncConn.createStatement().executeQuery(
4164: "SELECT " + Long.MAX_VALUE);
4165: this .rs.next();
4166: this .rs.getInt(1);
4167: } finally {
4168: closeMemberJDBCResources();
4169: }
4170:
4171: }
4172:
4173: public void testUsageAdvisorOnZeroRowResultSet() throws Exception {
4174: Connection advisorConn = null;
4175: Statement advisorStmt = null;
4176:
4177: try {
4178: Properties props = new Properties();
4179: props.setProperty("useUsageAdvisor", "true");
4180:
4181: advisorConn = getConnectionWithProps(props);
4182:
4183: advisorStmt = advisorConn.createStatement();
4184:
4185: StringBuffer advisorBuf = new StringBuffer();
4186: StandardLogger.bufferedLog = advisorBuf;
4187:
4188: this .rs = advisorStmt.executeQuery("SELECT 1, 2 LIMIT 0");
4189: this .rs.next();
4190: this .rs.close();
4191:
4192: advisorStmt.close();
4193:
4194: advisorStmt = advisorConn.createStatement(
4195: ResultSet.TYPE_FORWARD_ONLY,
4196: ResultSet.CONCUR_READ_ONLY);
4197:
4198: advisorStmt.setFetchSize(Integer.MIN_VALUE);
4199:
4200: this .rs = advisorStmt.executeQuery("SELECT 1, 2 LIMIT 0");
4201: this .rs.next();
4202: this .rs.close();
4203:
4204: StandardLogger.bufferedLog = null;
4205:
4206: if (versionMeetsMinimum(5, 0, 2)) {
4207: advisorConn.close();
4208:
4209: props.setProperty("useCursorFetch", "true");
4210: props.setProperty("useServerPrepStmts", "true");
4211:
4212: advisorConn = getConnectionWithProps(props);
4213:
4214: advisorStmt = advisorConn.createStatement();
4215: advisorStmt.setFetchSize(1);
4216:
4217: StandardLogger.bufferedLog = advisorBuf;
4218:
4219: this .rs = advisorStmt
4220: .executeQuery("SELECT 1, 2 LIMIT 0");
4221: this .rs.next();
4222: this .rs.close();
4223: }
4224:
4225: assertEquals("", advisorBuf.toString());
4226: } finally {
4227: StandardLogger.bufferedLog = null;
4228:
4229: closeMemberJDBCResources();
4230:
4231: if (advisorStmt != null) {
4232: advisorStmt.close();
4233: }
4234:
4235: if (advisorConn != null) {
4236: advisorConn.close();
4237: }
4238: }
4239: }
4240:
4241: public void testBug25894() throws Exception {
4242: createTable("bug25894", "(" + "tinyInt_type TINYINT DEFAULT 1,"
4243: + "tinyIntU_type TINYINT UNSIGNED DEFAULT 1,"
4244: + "smallInt_type SMALLINT DEFAULT 1,"
4245: + "smallIntU_type SMALLINT UNSIGNED DEFAULT 1,"
4246: + "mediumInt_type MEDIUMINT DEFAULT 1,"
4247: + "mediumIntU_type MEDIUMINT UNSIGNED DEFAULT 1,"
4248: + "int_type INT DEFAULT 1,"
4249: + "intU_type INT UNSIGNED DEFAULT 1,"
4250: + "bigInt_type BIGINT DEFAULT 1,"
4251: + "bigIntU_type BIGINT UNSIGNED DEFAULT 1" + ");");
4252: try {
4253: this .stmt
4254: .executeUpdate("INSERT INTO bug25894 VALUES (-1,1,-1,1,-1,1,-1,1,-1,1)");
4255: this .rs = this .stmt.executeQuery("SELECT * FROM bug25894");
4256: java.sql.ResultSetMetaData tblMD = this .rs.getMetaData();
4257: this .rs.first();
4258: for (int i = 1; i < tblMD.getColumnCount() + 1; i++) {
4259: String typesName = "";
4260: switch (tblMD.getColumnType(i)) {
4261: case Types.INTEGER:
4262: typesName = "Types.INTEGER";
4263: break;
4264: case Types.TINYINT:
4265: typesName = "Types.TINYINT";
4266: break;
4267: case Types.BIGINT:
4268: typesName = "Types.BIGINT";
4269: break;
4270: case Types.SMALLINT:
4271: typesName = "Types.SMALLINT";
4272: break;
4273: }
4274:
4275: System.out.println(i + " .fld: "
4276: + tblMD.getColumnName(i) + "T: " + typesName
4277: + ", MDC: " + tblMD.getColumnClassName(i) + " "
4278: + tblMD.getColumnTypeName(i) + " "
4279: + ", getObj: "
4280: + this .rs.getObject(i).getClass());
4281: }
4282:
4283: } finally {
4284: closeMemberJDBCResources();
4285: }
4286: }
4287:
4288: /**
4289: * Tests fix for BUG#26173 - fetching rows via cursor retrieves
4290: * corrupted data.
4291: *
4292: * @throws Exception if the test fails.
4293: */
4294: public void testBug26173() throws Exception {
4295: if (!versionMeetsMinimum(5, 0)) {
4296: return;
4297: }
4298:
4299: createTable("testBug26173",
4300: "(fkey int, fdate date, fprice decimal(15, 2), fdiscount decimal(5,3))");
4301: this .stmt
4302: .executeUpdate("insert into testBug26173 values (1, '2007-02-23', 99.9, 0.02)");
4303:
4304: Connection fetchConn = null;
4305: Statement stmtRead = null;
4306:
4307: Properties props = new Properties();
4308: props.setProperty("useServerPrepStmts", "true");
4309: props.setProperty("useCursorFetch", "true");
4310:
4311: try {
4312:
4313: fetchConn = getConnectionWithProps(props);
4314: stmtRead = fetchConn.createStatement();
4315: stmtRead.setFetchSize(1000);
4316:
4317: this .rs = stmtRead
4318: .executeQuery("select extract(year from fdate) as fyear, fprice * (1 - fdiscount) as fvalue from testBug26173");
4319:
4320: assertTrue(this .rs.next());
4321: assertEquals(2007, this .rs.getInt(1));
4322: assertEquals("97.90200", this .rs.getString(2));
4323: } finally {
4324: if (stmtRead != null) {
4325: stmtRead.close();
4326: }
4327:
4328: if (fetchConn != null) {
4329: fetchConn.close();
4330: }
4331:
4332: closeMemberJDBCResources();
4333: }
4334: }
4335:
4336: /**
4337: * Tests fix for BUG#26789 - fast date/time parsing doesn't take into
4338: * account 00:00:00 as a legal value.
4339: *
4340: * @throws Exception
4341: * if the test fails
4342: */
4343: public void testBug26789() throws Exception {
4344: try {
4345: this .rs = this .stmt.executeQuery("SELECT '00:00:00'");
4346: this .rs.next();
4347: this .rs.getTime(1);
4348: assertEquals("00:00:00", this .rs.getTime(1).toString());
4349: assertEquals("1970-01-01 00:00:00.0", this .rs.getTimestamp(
4350: 1).toString());
4351: assertEquals("1970-01-01", this .rs.getDate(1).toString());
4352:
4353: this .rs.close();
4354:
4355: this .rs = this .stmt
4356: .executeQuery("SELECT '00/00/0000 00:00:00'");
4357: this .rs.next();
4358:
4359: try {
4360: this .rs.getTime(1);
4361: } catch (SQLException sqlEx) {
4362: assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
4363: .getSQLState());
4364: }
4365:
4366: try {
4367: this .rs.getTimestamp(1);
4368: } catch (SQLException sqlEx) {
4369: assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
4370: .getSQLState());
4371: }
4372:
4373: try {
4374: this .rs.getDate(1);
4375: } catch (SQLException sqlEx) {
4376: assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
4377: .getSQLState());
4378: }
4379: } finally {
4380: closeMemberJDBCResources();
4381: }
4382: }
4383:
4384: /**
4385: * Tests fix for BUG#27317 - column index < 1 returns misleading
4386: * error message.
4387: *
4388: * @throws Exception if the test fails.
4389: */
4390: public void testBug27317() throws Exception {
4391: try {
4392: this .rs = this .stmt.executeQuery("SELECT NULL");
4393: this .rs.next();
4394: String messageLowBound = null;
4395:
4396: Method[] getterMethods = ResultSet.class.getMethods();
4397: Integer zeroIndex = new Integer(0);
4398: Integer twoIndex = new Integer(2);
4399:
4400: for (int i = 0; i < getterMethods.length; i++) {
4401: Class[] parameterTypes = getterMethods[i]
4402: .getParameterTypes();
4403:
4404: if (getterMethods[i].getName().startsWith("get")
4405: && parameterTypes.length == 1
4406: && (parameterTypes[0].equals(Integer.TYPE) || parameterTypes[0]
4407: .equals(Integer.class))) {
4408: if (getterMethods[i].getName().equals("getRowId")) {
4409: continue; // we don't support this yet, ever?
4410: }
4411:
4412: try {
4413: getterMethods[i].invoke(this .rs,
4414: new Object[] { zeroIndex });
4415: } catch (InvocationTargetException invokeEx) {
4416: Throwable ex = invokeEx.getTargetException();
4417:
4418: if (ex != null && ex instanceof SQLException) {
4419: SQLException sqlEx = (SQLException) ex;
4420:
4421: assertEquals(
4422: SQLError.SQL_STATE_ILLEGAL_ARGUMENT,
4423: sqlEx.getSQLState());
4424:
4425: messageLowBound = sqlEx.getMessage();
4426: } else {
4427: throw new RuntimeException(Util
4428: .stackTraceToString(ex), ex);
4429: }
4430: }
4431:
4432: String messageHighBound = null;
4433:
4434: try {
4435: getterMethods[i].invoke(this .rs,
4436: new Object[] { twoIndex });
4437: } catch (InvocationTargetException invokeEx) {
4438: Throwable ex = invokeEx.getTargetException();
4439:
4440: if (ex != null && ex instanceof SQLException) {
4441: SQLException sqlEx = (SQLException) ex;
4442:
4443: assertEquals(
4444: SQLError.SQL_STATE_ILLEGAL_ARGUMENT,
4445: sqlEx.getSQLState());
4446:
4447: messageHighBound = sqlEx.getMessage();
4448: } else {
4449: throw new RuntimeException(ex);
4450: }
4451: }
4452:
4453: assertNotNull("Exception message null for method "
4454: + getterMethods[i], messageHighBound);
4455: assertNotNull("Exception message null for method "
4456: + getterMethods[i], messageLowBound);
4457:
4458: assertTrue(!messageHighBound
4459: .equals(messageLowBound));
4460: }
4461: }
4462: } finally {
4463: closeMemberJDBCResources();
4464: }
4465: }
4466:
4467: /**
4468: * Tests fix for BUG#28085 - Need more useful error messages for diagnostics
4469: * when the driver thinks a result set isn't updatable.
4470: *
4471: * @throws Exception if the tests fail.
4472: */
4473: public void testBug28085() throws Exception {
4474:
4475: Statement updStmt = null;
4476:
4477: try {
4478: createTable("testBug28085_oneKey",
4479: "(pk int primary key not null, field2 varchar(3))");
4480:
4481: this .stmt
4482: .executeUpdate("INSERT INTO testBug28085_oneKey (pk, field2) VALUES (1, 'abc')");
4483:
4484: createTable(
4485: "testBug28085_multiKey",
4486: "(pk1 int not null, pk2 int not null, field2 varchar(3), primary key (pk1, pk2))");
4487:
4488: this .stmt
4489: .executeUpdate("INSERT INTO testBug28085_multiKey VALUES (1,2,'abc')");
4490:
4491: createTable("testBug28085_noKey",
4492: "(field1 varchar(3) not null)");
4493:
4494: this .stmt
4495: .executeUpdate("INSERT INTO testBug28085_noKey VALUES ('abc')");
4496:
4497: updStmt = this .conn.createStatement(
4498: ResultSet.TYPE_FORWARD_ONLY,
4499: ResultSet.CONCUR_UPDATABLE);
4500:
4501: this .rs = updStmt
4502: .executeQuery("SELECT field2 FROM testBug28085_oneKey");
4503: exerciseUpdatableResultSet(1, "NotUpdatableReason.4");
4504:
4505: this .rs = updStmt
4506: .executeQuery("SELECT pk1, field2 FROM testBug28085_multiKey");
4507: this .rs.next();
4508: exerciseUpdatableResultSet(1, "NotUpdatableReason.7");
4509:
4510: this .rs = updStmt
4511: .executeQuery("SELECT t1.field2, t1.pk, t2.pk1 FROM testBug28085_oneKey t1 INNER JOIN testBug28085_multiKey t2 ON t1.pk = t2.pk1");
4512: exerciseUpdatableResultSet(1, "NotUpdatableReason.0");
4513:
4514: this .rs = updStmt
4515: .executeQuery("SELECT field1 FROM testBug28085_noKey");
4516: exerciseUpdatableResultSet(1, "NotUpdatableReason.5");
4517:
4518: this .rs = updStmt.executeQuery("SELECT 1");
4519: exerciseUpdatableResultSet(1, "NotUpdatableReason.3");
4520:
4521: this .rs = updStmt
4522: .executeQuery("SELECT pk1, pk2, LEFT(field2, 2) FROM testBug28085_multiKey");
4523: this .rs.next();
4524: exerciseUpdatableResultSet(1, "NotUpdatableReason.3");
4525: } finally {
4526: closeMemberJDBCResources();
4527:
4528: if (updStmt != null) {
4529: updStmt.close();
4530: }
4531: }
4532: }
4533:
4534: private void exerciseUpdatableResultSet(int columnUpdateIndex,
4535: String messageToCheck) throws Exception {
4536: this .rs.next();
4537:
4538: try {
4539: this .rs.updateString(columnUpdateIndex, "def");
4540: } catch (SQLException sqlEx) {
4541: checkUpdatabilityMessage(sqlEx, messageToCheck);
4542: }
4543:
4544: try {
4545: this .rs.moveToInsertRow();
4546: } catch (SQLException sqlEx) {
4547: checkUpdatabilityMessage(sqlEx, messageToCheck);
4548: }
4549:
4550: try {
4551: this .rs.deleteRow();
4552: } catch (SQLException sqlEx) {
4553: checkUpdatabilityMessage(sqlEx, messageToCheck);
4554: }
4555:
4556: this .rs.close();
4557: }
4558:
4559: private void checkUpdatabilityMessage(SQLException sqlEx,
4560: String messageToCheck) throws Exception {
4561:
4562: String message = sqlEx.getMessage();
4563:
4564: assertNotNull(message);
4565:
4566: String localizedMessage = Messages.getString(messageToCheck);
4567:
4568: assertTrue("Didn't find required message component '"
4569: + localizedMessage + "', instead found:\n\n" + message,
4570: message.indexOf(localizedMessage) != -1);
4571: }
4572:
4573: public void testBug24886() throws Exception {
4574: Properties props = new Properties();
4575: props.setProperty("blobsAreStrings", "true");
4576:
4577: Connection noBlobConn = getConnectionWithProps(props);
4578:
4579: createTable("testBug24886", "(sepallength double,"
4580: + "sepalwidth double," + "petallength double,"
4581: + "petalwidth double," + "Class mediumtext, "
4582: + "fy TIMESTAMP)");
4583:
4584: noBlobConn
4585: .createStatement()
4586: .executeUpdate(
4587: "INSERT INTO testBug24886 VALUES (1,2,3,4,'1234', now()),(5,6,7,8,'12345678', now())");
4588: this .rs = noBlobConn
4589: .createStatement()
4590: .executeQuery(
4591: "SELECT concat(Class,petallength), COUNT(*) FROM `testBug24886` GROUP BY `concat(Class,petallength)`");
4592: this .rs.next();
4593: assertEquals("java.lang.String", this .rs.getObject(1)
4594: .getClass().getName());
4595:
4596: props.clear();
4597: props.setProperty("functionsNeverReturnBlobs", "true");
4598: noBlobConn = getConnectionWithProps(props);
4599: this .rs = noBlobConn
4600: .createStatement()
4601: .executeQuery(
4602: "SELECT concat(Class,petallength), COUNT(*) FROM `testBug24886` GROUP BY `concat(Class,petallength)`");
4603: this .rs.next();
4604: assertEquals("java.lang.String", this .rs.getObject(1)
4605: .getClass().getName());
4606: }
4607:
4608: /**
4609: * Tests fix for BUG#30664. Note that this fix only works
4610: * for MySQL server 5.0.25 and newer, since earlier versions
4611: * didn't consistently return correct metadata for functions,
4612: * and thus results from subqueries and functions were indistinguishable
4613: * from each other, leading to type-related bugs.
4614: *
4615: * @throws Exception
4616: */
4617: public void testBug30664() throws Exception {
4618: if (!versionMeetsMinimum(5, 0, 25)) {
4619: return;
4620: }
4621:
4622: createTable("testBug30664_1", "(id int)");
4623: createTable("testBug30664_2",
4624: "(id int, binaryvalue varbinary(10))");
4625:
4626: try {
4627: this .stmt
4628: .executeUpdate("insert into testBug30664_1 values (1),(2),(3)");
4629: this .stmt
4630: .executeUpdate("insert into testBug30664_2 values (1,'¢‚¤'),(2,'‚¢‚¤'),(3,' ‚¢¤')");
4631: this .rs = this .stmt
4632: .executeQuery("select testBug30664_1.id, (select testBug30664_2.binaryvalue from testBug30664_2 where testBug30664_2.id=testBug30664_1.id) as value from testBug30664_1");
4633: ResultSetMetaData tblMD = this .rs.getMetaData();
4634:
4635: for (int i = 1; i < tblMD.getColumnCount() + 1; i++) {
4636: switch (i) {
4637: case 1:
4638: assertEquals("INTEGER", tblMD.getColumnTypeName(i)
4639: .toUpperCase());
4640: break;
4641: case 2:
4642: assertEquals("VARBINARY", tblMD
4643: .getColumnTypeName(i).toUpperCase());
4644: break;
4645: }
4646: }
4647: } finally {
4648: closeMemberJDBCResources();
4649: }
4650: }
4651:
4652: /**
4653: * Tests fix for BUG#30851, NPE with null column values when
4654: * "padCharsWithSpace" is set to "true".
4655: *
4656: * @throws Exception
4657: */
4658: public void testbug30851() throws Exception {
4659: Connection padConn = getConnectionWithProps("padCharsWithSpace=true");
4660:
4661: try {
4662: createTable("bug30851", "(CharCol CHAR(10) DEFAULT NULL)");
4663: this .stmt.execute("INSERT INTO bug30851 VALUES (NULL)");
4664: this .rs = padConn.createStatement().executeQuery(
4665: "SELECT * FROM bug30851");
4666: this .rs.first();
4667: String strvar = this .rs.getString(1);
4668: //assertNotNull("Should be null", strvar);
4669:
4670: } finally {
4671: closeMemberJDBCResources();
4672:
4673: if (padConn != null) {
4674: padConn.close();
4675: }
4676: }
4677: }
4678: }
|