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.ByteArrayInputStream;
0028: import java.io.ByteArrayOutputStream;
0029: import java.io.CharArrayReader;
0030: import java.io.File;
0031: import java.io.FileOutputStream;
0032: import java.io.FileWriter;
0033: import java.io.IOException;
0034: import java.io.PrintStream;
0035: import java.io.StringReader;
0036: import java.io.Writer;
0037: import java.math.BigDecimal;
0038: import java.math.BigInteger;
0039: import java.sql.BatchUpdateException;
0040: import java.sql.Blob;
0041: import java.sql.Clob;
0042: import java.sql.Connection;
0043: import java.sql.DataTruncation;
0044: import java.sql.Date;
0045: import java.sql.PreparedStatement;
0046: import java.sql.ResultSet;
0047: import java.sql.SQLException;
0048: import java.sql.SQLWarning;
0049: import java.sql.Statement;
0050: import java.sql.Time;
0051: import java.sql.Timestamp;
0052: import java.sql.Types;
0053: import java.text.SimpleDateFormat;
0054: import java.util.Calendar;
0055: import java.util.Locale;
0056: import java.util.Properties;
0057: import java.util.TimeZone;
0058:
0059: import testsuite.BaseTestCase;
0060:
0061: import com.mysql.jdbc.SQLError;
0062: import com.mysql.jdbc.ServerPreparedStatement;
0063: import com.mysql.jdbc.exceptions.MySQLTimeoutException;
0064:
0065: /**
0066: * Regression tests for the Statement class
0067: *
0068: * @author Mark Matthews
0069: */
0070: public class StatementRegressionTest extends BaseTestCase {
0071: class PrepareThread extends Thread {
0072: Connection c;
0073:
0074: PrepareThread(Connection cn) {
0075: this .c = cn;
0076: }
0077:
0078: public void run() {
0079: for (int i = 0; i < 20; i++) // force this to end eventually
0080: {
0081: try {
0082: this .c.prepareStatement("SELECT 1");
0083: StatementRegressionTest.this .testServerPrepStmtDeadlockCounter++;
0084: Thread.sleep(400);
0085: } catch (SQLException sqlEx) {
0086: throw new RuntimeException(sqlEx);
0087: } catch (InterruptedException e) {
0088: e.printStackTrace();
0089: }
0090: }
0091: }
0092: }
0093:
0094: static int count = 0;
0095:
0096: static int nextID = 1; // The next ID we expected to generate
0097:
0098: /*
0099: * Each row in this table is to be converted into a single REPLACE
0100: * statement. If the value is zero, a new record is to be created using then
0101: * autoincrement feature. If the value is non-zero, the existing row of that
0102: * value is to be replace with, obviously, the same key. I expect one
0103: * Generated Key for each zero value - but I would accept one key for each
0104: * value, with non-zero values coming back as themselves.
0105: */
0106: static final int[][] tests = { { 0 }, // generate 1
0107: { 1, 0, 0 }, // update 1, generate 2, 3
0108: { 2, 0, 0, }, // update 2, generate 3, 4
0109: };
0110:
0111: /**
0112: * Runs all test cases in this test suite
0113: *
0114: * @param args
0115: */
0116: public static void main(String[] args) {
0117: junit.textui.TestRunner.run(StatementRegressionTest.class);
0118: }
0119:
0120: private int testServerPrepStmtDeadlockCounter = 0;
0121:
0122: /**
0123: * Constructor for StatementRegressionTest.
0124: *
0125: * @param name
0126: * the name of the test to run
0127: */
0128: public StatementRegressionTest(String name) {
0129: super (name);
0130: }
0131:
0132: private void addBatchItems(Statement statement,
0133: PreparedStatement pStmt, String tableName, int i)
0134: throws SQLException {
0135: pStmt.setString(1, "ps_batch_" + i);
0136: pStmt.setString(2, "ps_batch_" + i);
0137: pStmt.addBatch();
0138:
0139: statement.addBatch("INSERT INTO " + tableName
0140: + " (strdata1, strdata2) VALUES " + "(\"s_batch_" + i
0141: + "\",\"s_batch_" + i + "\")");
0142: }
0143:
0144: private void createGGKTables() throws Exception {
0145: // Delete and recreate table
0146: dropGGKTables();
0147:
0148: this .stmt.executeUpdate("CREATE TABLE testggk ("
0149: + "id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,"
0150: + "val INT NOT NULL" + ")");
0151: }
0152:
0153: private void doGGKTestPreparedStatement(int[] values,
0154: boolean useUpdate) throws Exception {
0155: // Generate the the multiple replace command
0156: StringBuffer cmd = new StringBuffer(
0157: "REPLACE INTO testggk VALUES ");
0158: int newKeys = 0;
0159:
0160: for (int i = 0; i < values.length; i++) {
0161: cmd.append("(");
0162:
0163: if (values[i] == 0) {
0164: cmd.append("NULL");
0165: newKeys += 1;
0166: } else {
0167: cmd.append(values[i]);
0168: }
0169:
0170: cmd.append(", ");
0171: cmd.append(count++);
0172: cmd.append("), ");
0173: }
0174:
0175: cmd.setLength(cmd.length() - 2); // trim the final ", "
0176:
0177: // execute and print it
0178: System.out.println(cmd.toString());
0179:
0180: PreparedStatement pStmt = this .conn.prepareStatement(cmd
0181: .toString(), Statement.RETURN_GENERATED_KEYS);
0182:
0183: if (useUpdate) {
0184: pStmt.executeUpdate();
0185: } else {
0186: pStmt.execute();
0187: }
0188:
0189: // print out what actually happened
0190: System.out.println("Expect " + newKeys
0191: + " generated keys, starting from " + nextID);
0192:
0193: this .rs = pStmt.getGeneratedKeys();
0194: StringBuffer res = new StringBuffer("Got keys");
0195:
0196: int[] generatedKeys = new int[newKeys];
0197: int i = 0;
0198:
0199: while (this .rs.next()) {
0200: if (i < generatedKeys.length) {
0201: generatedKeys[i] = this .rs.getInt(1);
0202: }
0203:
0204: i++;
0205:
0206: res.append(" " + this .rs.getInt(1));
0207: }
0208:
0209: int numberOfGeneratedKeys = i;
0210:
0211: assertTrue(
0212: "Didn't retrieve expected number of generated keys, expected "
0213: + newKeys + ", found " + numberOfGeneratedKeys,
0214: numberOfGeneratedKeys == newKeys);
0215: assertTrue("Keys didn't start with correct sequence: ",
0216: generatedKeys[0] == nextID);
0217:
0218: System.out.println(res.toString());
0219:
0220: // Read and print the new state of the table
0221: this .rs = this .stmt.executeQuery("SELECT id, val FROM testggk");
0222: System.out.println("New table contents ");
0223:
0224: while (this .rs.next())
0225: System.out.println("Id " + this .rs.getString(1) + " val "
0226: + this .rs.getString(2));
0227:
0228: // Tidy up
0229: System.out.println("");
0230: nextID += newKeys;
0231: }
0232:
0233: private void doGGKTestStatement(int[] values, boolean useUpdate)
0234: throws Exception {
0235: // Generate the the multiple replace command
0236: StringBuffer cmd = new StringBuffer(
0237: "REPLACE INTO testggk VALUES ");
0238: int newKeys = 0;
0239:
0240: for (int i = 0; i < values.length; i++) {
0241: cmd.append("(");
0242:
0243: if (values[i] == 0) {
0244: cmd.append("NULL");
0245: newKeys += 1;
0246: } else {
0247: cmd.append(values[i]);
0248: }
0249:
0250: cmd.append(", ");
0251: cmd.append(count++);
0252: cmd.append("), ");
0253: }
0254:
0255: cmd.setLength(cmd.length() - 2); // trim the final ", "
0256:
0257: // execute and print it
0258: System.out.println(cmd.toString());
0259:
0260: if (useUpdate) {
0261: this .stmt.executeUpdate(cmd.toString(),
0262: Statement.RETURN_GENERATED_KEYS);
0263: } else {
0264: this .stmt.execute(cmd.toString(),
0265: Statement.RETURN_GENERATED_KEYS);
0266: }
0267:
0268: // print out what actually happened
0269: System.out.println("Expect " + newKeys
0270: + " generated keys, starting from " + nextID);
0271:
0272: this .rs = this .stmt.getGeneratedKeys();
0273: StringBuffer res = new StringBuffer("Got keys");
0274:
0275: int[] generatedKeys = new int[newKeys];
0276: int i = 0;
0277:
0278: while (this .rs.next()) {
0279: if (i < generatedKeys.length) {
0280: generatedKeys[i] = this .rs.getInt(1);
0281: }
0282:
0283: i++;
0284:
0285: res.append(" " + this .rs.getInt(1));
0286: }
0287:
0288: int numberOfGeneratedKeys = i;
0289:
0290: assertTrue(
0291: "Didn't retrieve expected number of generated keys, expected "
0292: + newKeys + ", found " + numberOfGeneratedKeys,
0293: numberOfGeneratedKeys == newKeys);
0294: assertTrue("Keys didn't start with correct sequence: ",
0295: generatedKeys[0] == nextID);
0296:
0297: System.out.println(res.toString());
0298:
0299: // Read and print the new state of the table
0300: this .rs = this .stmt.executeQuery("SELECT id, val FROM testggk");
0301: System.out.println("New table contents ");
0302:
0303: while (this .rs.next())
0304: System.out.println("Id " + this .rs.getString(1) + " val "
0305: + this .rs.getString(2));
0306:
0307: // Tidy up
0308: System.out.println("");
0309: nextID += newKeys;
0310: }
0311:
0312: private void dropGGKTables() throws Exception {
0313: this .stmt.executeUpdate("DROP TABLE IF EXISTS testggk");
0314: }
0315:
0316: /**
0317: * @param pStmt
0318: * @param catId
0319: * @throws SQLException
0320: */
0321: private void execQueryBug5191(PreparedStatement pStmt, int catId)
0322: throws SQLException {
0323: pStmt.setInt(1, catId);
0324:
0325: this .rs = pStmt.executeQuery();
0326:
0327: assertTrue(this .rs.next());
0328: assertTrue(this .rs.next());
0329: // assertTrue(rs.next());
0330:
0331: assertFalse(this .rs.next());
0332: }
0333:
0334: private String getByteArrayString(byte[] ba) {
0335: StringBuffer buffer = new StringBuffer();
0336: if (ba != null) {
0337: for (int i = 0; i < ba.length; i++) {
0338: buffer.append("0x" + Integer.toHexString(ba[i] & 0xff)
0339: + " ");
0340: }
0341: } else {
0342: buffer.append("null");
0343: }
0344: return buffer.toString();
0345: }
0346:
0347: /**
0348: * @param continueBatchOnError
0349: * @throws SQLException
0350: */
0351: private void innerBug6823(boolean continueBatchOnError)
0352: throws SQLException {
0353: Properties continueBatchOnErrorProps = new Properties();
0354: continueBatchOnErrorProps.setProperty("continueBatchOnError",
0355: String.valueOf(continueBatchOnError));
0356: this .conn = getConnectionWithProps(continueBatchOnErrorProps);
0357: Statement statement = this .conn.createStatement();
0358:
0359: String tableName = "testBug6823";
0360:
0361: createTable(
0362: tableName,
0363: "(id int not null primary key auto_increment,"
0364: + " strdata1 varchar(255) not null, strdata2 varchar(255),"
0365: + " UNIQUE INDEX (strdata1))");
0366:
0367: PreparedStatement pStmt = this .conn
0368: .prepareStatement("INSERT INTO " + tableName
0369: + " (strdata1, strdata2) VALUES (?,?)");
0370:
0371: int c = 0;
0372: addBatchItems(statement, pStmt, tableName, ++c);
0373: addBatchItems(statement, pStmt, tableName, ++c);
0374: addBatchItems(statement, pStmt, tableName, ++c);
0375: addBatchItems(statement, pStmt, tableName, c); // duplicate entry
0376: addBatchItems(statement, pStmt, tableName, ++c);
0377: addBatchItems(statement, pStmt, tableName, ++c);
0378:
0379: int expectedUpdateCounts = continueBatchOnError ? 6 : 3;
0380:
0381: BatchUpdateException e1 = null;
0382: BatchUpdateException e2 = null;
0383:
0384: int[] updateCountsPstmt = null;
0385: try {
0386: updateCountsPstmt = pStmt.executeBatch();
0387: } catch (BatchUpdateException e) {
0388: e1 = e;
0389: updateCountsPstmt = e1.getUpdateCounts();
0390: }
0391:
0392: int[] updateCountsStmt = null;
0393: try {
0394: updateCountsStmt = statement.executeBatch();
0395: } catch (BatchUpdateException e) {
0396: e2 = e;
0397: updateCountsStmt = e1.getUpdateCounts();
0398: }
0399:
0400: assertNotNull(e1);
0401: assertNotNull(e2);
0402:
0403: assertEquals(expectedUpdateCounts, updateCountsPstmt.length);
0404: assertEquals(expectedUpdateCounts, updateCountsStmt.length);
0405:
0406: if (continueBatchOnError) {
0407: assertTrue(updateCountsPstmt[3] == Statement.EXECUTE_FAILED);
0408: assertTrue(updateCountsStmt[3] == Statement.EXECUTE_FAILED);
0409: }
0410:
0411: int psRows = 0;
0412: this .rs = this .stmt.executeQuery("SELECT * from " + tableName
0413: + " WHERE strdata1 like \"ps_%\"");
0414: while (this .rs.next()) {
0415: psRows++;
0416: }
0417: assertTrue(psRows > 0);
0418:
0419: int sRows = 0;
0420: this .rs = this .stmt.executeQuery("SELECT * from " + tableName
0421: + " WHERE strdata1 like \"s_%\"");
0422: while (this .rs.next()) {
0423: sRows++;
0424: }
0425: assertTrue(sRows > 0);
0426:
0427: assertTrue(psRows + "!=" + sRows, psRows == sRows);
0428: }
0429:
0430: /**
0431: * Tests fix for BUG#10155, double quotes not recognized when parsing
0432: * client-side prepared statements.
0433: *
0434: * @throws Exception
0435: * if the test fails.
0436: */
0437: public void testBug10155() throws Exception {
0438: this .conn.prepareStatement(
0439: "SELECT \"Test question mark? Test single quote'\"")
0440: .executeQuery().close();
0441: }
0442:
0443: /**
0444: * Tests fix for BUG#10630, Statement.getWarnings() fails with NPE if
0445: * statement has been closed.
0446: */
0447: public void testBug10630() throws Exception {
0448: Connection conn2 = null;
0449: Statement stmt2 = null;
0450:
0451: try {
0452: conn2 = getConnectionWithProps((Properties) null);
0453: stmt2 = conn2.createStatement();
0454:
0455: conn2.close();
0456: stmt2.getWarnings();
0457: fail("Should've caught an exception here");
0458: } catch (SQLException sqlEx) {
0459: assertEquals("08003", sqlEx.getSQLState());
0460: } finally {
0461: if (stmt2 != null) {
0462: stmt2.close();
0463: }
0464:
0465: if (conn2 != null) {
0466: conn2.close();
0467: }
0468: }
0469: }
0470:
0471: /**
0472: * Tests fix for BUG#11115, Varbinary data corrupted when using server-side
0473: * prepared statements.
0474: */
0475: public void testBug11115() throws Exception {
0476: String tableName = "testBug11115";
0477:
0478: if (versionMeetsMinimum(4, 1, 0)) {
0479:
0480: createTable(tableName,
0481: "(pwd VARBINARY(30)) TYPE=InnoDB DEFAULT CHARACTER SET utf8");
0482:
0483: byte[] bytesToTest = new byte[] { 17, 120, -1, -73, -5 };
0484:
0485: PreparedStatement insStmt = this .conn
0486: .prepareStatement("INSERT INTO " + tableName
0487: + " (pwd) VALUES (?)");
0488: insStmt.setBytes(1, bytesToTest);
0489: insStmt.executeUpdate();
0490:
0491: this .rs = this .stmt.executeQuery("SELECT pwd FROM "
0492: + tableName);
0493: this .rs.next();
0494:
0495: byte[] fromDatabase = this .rs.getBytes(1);
0496:
0497: assertEquals(bytesToTest.length, fromDatabase.length);
0498:
0499: for (int i = 0; i < bytesToTest.length; i++) {
0500: assertEquals(bytesToTest[i], fromDatabase[i]);
0501: }
0502:
0503: this .rs = this .conn.prepareStatement(
0504: "SELECT pwd FROM " + tableName).executeQuery();
0505: this .rs.next();
0506:
0507: fromDatabase = this .rs.getBytes(1);
0508:
0509: assertEquals(bytesToTest.length, fromDatabase.length);
0510:
0511: for (int i = 0; i < bytesToTest.length; i++) {
0512: assertEquals(bytesToTest[i], fromDatabase[i]);
0513: }
0514: }
0515: }
0516:
0517: public void testBug11540() throws Exception {
0518: Locale originalLocale = Locale.getDefault();
0519: Connection thaiConn = null;
0520: Statement thaiStmt = null;
0521: PreparedStatement thaiPrepStmt = null;
0522:
0523: try {
0524: createTable("testBug11540",
0525: "(field1 DATE, field2 TIMESTAMP)");
0526: this .stmt
0527: .executeUpdate("INSERT INTO testBug11540 VALUES (NOW(), NOW())");
0528: Locale.setDefault(new Locale("th", "TH"));
0529: Properties props = new Properties();
0530: props.setProperty("jdbcCompliantTruncation", "false");
0531:
0532: thaiConn = getConnectionWithProps(props);
0533: thaiStmt = thaiConn.createStatement();
0534:
0535: this .rs = thaiStmt
0536: .executeQuery("SELECT field1, field2 FROM testBug11540");
0537: this .rs.next();
0538:
0539: Date origDate = this .rs.getDate(1);
0540: Timestamp origTimestamp = this .rs.getTimestamp(1);
0541: this .rs.close();
0542:
0543: thaiStmt.executeUpdate("TRUNCATE TABLE testBug11540");
0544:
0545: thaiPrepStmt = ((com.mysql.jdbc.Connection) thaiConn)
0546: .clientPrepareStatement("INSERT INTO testBug11540 VALUES (?,?)");
0547: thaiPrepStmt.setDate(1, origDate);
0548: thaiPrepStmt.setTimestamp(2, origTimestamp);
0549: thaiPrepStmt.executeUpdate();
0550:
0551: this .rs = thaiStmt
0552: .executeQuery("SELECT field1, field2 FROM testBug11540");
0553: this .rs.next();
0554:
0555: Date testDate = this .rs.getDate(1);
0556: Timestamp testTimestamp = this .rs.getTimestamp(1);
0557: this .rs.close();
0558:
0559: assertEquals(origDate, testDate);
0560: assertEquals(origTimestamp, testTimestamp);
0561:
0562: } finally {
0563: Locale.setDefault(originalLocale);
0564: }
0565: }
0566:
0567: /**
0568: * Tests fix for BUG#11663, autoGenerateTestcaseScript uses bogus parameter
0569: * names for server-side prepared statements.
0570: *
0571: * @throws Exception
0572: * if the test fails.
0573: */
0574: public void testBug11663() throws Exception {
0575: if (versionMeetsMinimum(4, 1, 0)
0576: && ((com.mysql.jdbc.Connection) this .conn)
0577: .getUseServerPreparedStmts()) {
0578: Connection testcaseGenCon = null;
0579: PrintStream oldErr = System.err;
0580:
0581: try {
0582: createTable("testBug11663", "(field1 int)");
0583:
0584: Properties props = new Properties();
0585: props.setProperty("autoGenerateTestcaseScript", "true");
0586: testcaseGenCon = getConnectionWithProps(props);
0587: ByteArrayOutputStream testStream = new ByteArrayOutputStream();
0588: PrintStream testErr = new PrintStream(testStream);
0589: System.setErr(testErr);
0590: this .pstmt = testcaseGenCon
0591: .prepareStatement("SELECT field1 FROM testBug11663 WHERE field1=?");
0592: this .pstmt.setInt(1, 1);
0593: this .pstmt.execute();
0594: System.setErr(oldErr);
0595: String testString = new String(testStream.toByteArray());
0596:
0597: int setIndex = testString
0598: .indexOf("SET @debug_stmt_param");
0599: int equalsIndex = testString.indexOf("=", setIndex);
0600: String paramName = testString.substring(setIndex + 4,
0601: equalsIndex);
0602:
0603: int usingIndex = testString.indexOf("USING "
0604: + paramName, equalsIndex);
0605:
0606: assertTrue(usingIndex != -1);
0607: } finally {
0608: System.setErr(oldErr);
0609:
0610: if (this .pstmt != null) {
0611: this .pstmt.close();
0612: this .pstmt = null;
0613: }
0614:
0615: if (testcaseGenCon != null) {
0616: testcaseGenCon.close();
0617: }
0618:
0619: }
0620: }
0621: }
0622:
0623: /**
0624: * Tests fix for BUG#11798 - Pstmt.setObject(...., Types.BOOLEAN) throws
0625: * exception.
0626: *
0627: * @throws Exception
0628: * if the test fails.
0629: */
0630: public void testBug11798() throws Exception {
0631: if (isRunningOnJdk131()) {
0632: return; // test not valid on JDK-1.3.1
0633: }
0634:
0635: try {
0636: this .pstmt = this .conn.prepareStatement("SELECT ?");
0637: this .pstmt.setObject(1, Boolean.TRUE, Types.BOOLEAN);
0638: this .pstmt.setObject(1, new BigDecimal("1"), Types.BOOLEAN);
0639: this .pstmt.setObject(1, "true", Types.BOOLEAN);
0640: } finally {
0641: if (this .pstmt != null) {
0642: this .pstmt.close();
0643: this .pstmt = null;
0644: }
0645: }
0646: }
0647:
0648: /**
0649: * Tests fix for BUG#13255 - Reconnect during middle of executeBatch()
0650: * should not happen.
0651: *
0652: * @throws Exception
0653: * if the test fails.
0654: */
0655: public void testBug13255() throws Exception {
0656:
0657: createTable("testBug13255", "(field_1 int)");
0658:
0659: Properties props = new Properties();
0660: props.setProperty("autoReconnect", "true");
0661:
0662: Connection reconnectConn = null;
0663: Statement reconnectStmt = null;
0664: PreparedStatement reconnectPStmt = null;
0665:
0666: try {
0667: reconnectConn = getConnectionWithProps(props);
0668: reconnectStmt = reconnectConn.createStatement();
0669:
0670: String connectionId = getSingleIndexedValueWithQuery(
0671: reconnectConn, 1, "SELECT CONNECTION_ID()")
0672: .toString();
0673:
0674: reconnectStmt
0675: .addBatch("INSERT INTO testBug13255 VALUES (1)");
0676: reconnectStmt
0677: .addBatch("INSERT INTO testBug13255 VALUES (2)");
0678: reconnectStmt.addBatch("KILL " + connectionId);
0679:
0680: for (int i = 0; i < 100; i++) {
0681: reconnectStmt
0682: .addBatch("INSERT INTO testBug13255 VALUES ("
0683: + i + ")");
0684: }
0685:
0686: try {
0687: reconnectStmt.executeBatch();
0688: } catch (SQLException sqlEx) {
0689: // We expect this...we killed the connection
0690: }
0691:
0692: assertEquals(2, getRowCount("testBug13255"));
0693:
0694: this .stmt.executeUpdate("TRUNCATE TABLE testBug13255");
0695:
0696: reconnectConn.close();
0697:
0698: reconnectConn = getConnectionWithProps(props);
0699:
0700: connectionId = getSingleIndexedValueWithQuery(
0701: reconnectConn, 1, "SELECT CONNECTION_ID()")
0702: .toString();
0703:
0704: reconnectPStmt = reconnectConn
0705: .prepareStatement("INSERT INTO testBug13255 VALUES (?)");
0706: reconnectPStmt.setInt(1, 1);
0707: reconnectPStmt.addBatch();
0708: reconnectPStmt.setInt(1, 2);
0709: reconnectPStmt.addBatch();
0710: reconnectPStmt.addBatch("KILL " + connectionId);
0711:
0712: for (int i = 3; i < 100; i++) {
0713: reconnectPStmt.setInt(1, i);
0714: reconnectPStmt.addBatch();
0715: }
0716:
0717: try {
0718: reconnectPStmt.executeBatch();
0719: } catch (SQLException sqlEx) {
0720: // We expect this...we killed the connection
0721: }
0722:
0723: assertEquals(2, getRowCount("testBug13255"));
0724:
0725: } finally {
0726: if (reconnectStmt != null) {
0727: reconnectStmt.close();
0728: }
0729:
0730: if (reconnectConn != null) {
0731: reconnectConn.close();
0732: }
0733: }
0734: }
0735:
0736: /**
0737: * Tests fix for BUG#15024 - Driver incorrectly closes streams passed as
0738: * arguments to PreparedStatements.
0739: *
0740: * @throws Exception
0741: * if the test fails.
0742: */
0743: public void testBug15024() throws Exception {
0744: createTable("testBug15024", "(field1 BLOB)");
0745:
0746: try {
0747: this .pstmt = this .conn
0748: .prepareStatement("INSERT INTO testBug15024 VALUES (?)");
0749: testStreamsForBug15024(false, false);
0750:
0751: Properties props = new Properties();
0752: props.setProperty("useConfigs", "3-0-Compat");
0753:
0754: Connection compatConn = null;
0755:
0756: try {
0757: compatConn = getConnectionWithProps(props);
0758:
0759: this .pstmt = compatConn
0760: .prepareStatement("INSERT INTO testBug15024 VALUES (?)");
0761: testStreamsForBug15024(true, false);
0762: } finally {
0763: if (compatConn != null) {
0764: compatConn.close();
0765: }
0766: }
0767: } finally {
0768: if (this .pstmt != null) {
0769: PreparedStatement toClose = this .pstmt;
0770: this .pstmt = null;
0771:
0772: toClose.close();
0773: }
0774: }
0775: }
0776:
0777: /**
0778: * PreparedStatement should call EscapeProcessor.escapeSQL?
0779: *
0780: * @throws Exception
0781: * if the test fails
0782: */
0783: public void testBug15141() throws Exception {
0784: try {
0785: createTable("testBug15141", "(field1 VARCHAR(32))");
0786: this .stmt
0787: .executeUpdate("INSERT INTO testBug15141 VALUES ('abc')");
0788:
0789: this .pstmt = this .conn
0790: .prepareStatement("select {d '1997-05-24'} FROM testBug15141");
0791: this .rs = this .pstmt.executeQuery();
0792: assertTrue(this .rs.next());
0793: assertEquals("1997-05-24", this .rs.getString(1));
0794: this .rs.close();
0795: this .rs = null;
0796: this .pstmt.close();
0797: this .pstmt = null;
0798:
0799: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
0800: .clientPrepareStatement("select {d '1997-05-24'} FROM testBug15141");
0801: this .rs = this .pstmt.executeQuery();
0802: assertTrue(this .rs.next());
0803: assertEquals("1997-05-24", this .rs.getString(1));
0804: this .rs.close();
0805: this .rs = null;
0806: this .pstmt.close();
0807: this .pstmt = null;
0808: } finally {
0809: if (this .rs != null) {
0810: ResultSet toCloseRs = this .rs;
0811: this .rs = null;
0812: toCloseRs.close();
0813: }
0814:
0815: if (this .pstmt != null) {
0816: PreparedStatement toClosePstmt = this .pstmt;
0817: this .pstmt = null;
0818: toClosePstmt.close();
0819: }
0820: }
0821: }
0822:
0823: /**
0824: * Tests fix for BUG#18041 - Server-side prepared statements don't cause
0825: * truncation exceptions to be thrown.
0826: *
0827: * @throws Exception
0828: * if the test fails
0829: */
0830: public void testBug18041() throws Exception {
0831: if (versionMeetsMinimum(4, 1)) {
0832: createTable("testBug18041", "(`a` tinyint(4) NOT NULL,"
0833: + "`b` char(4) default NULL)");
0834:
0835: Properties props = new Properties();
0836: props.setProperty("jdbcCompliantTruncation", "true");
0837: props.setProperty("useServerPrepStmts", "true");
0838:
0839: Connection truncConn = null;
0840: PreparedStatement stm = null;
0841:
0842: try {
0843: truncConn = getConnectionWithProps(props);
0844:
0845: stm = truncConn
0846: .prepareStatement("insert into testBug18041 values (?,?)");
0847: stm.setInt(1, 1000);
0848: stm.setString(2, "nnnnnnnnnnnnnnnnnnnnnnnnnnnnnn");
0849: stm.executeUpdate();
0850: fail("Truncation exception should have been thrown");
0851: } catch (DataTruncation truncEx) {
0852: // we expect this
0853: } finally {
0854: if (this .stmt != null) {
0855: this .stmt.close();
0856: }
0857:
0858: if (truncConn != null) {
0859: truncConn.close();
0860: }
0861: }
0862: }
0863: }
0864:
0865: private void testStreamsForBug15024(boolean shouldBeClosedStream,
0866: boolean shouldBeClosedReader) throws SQLException {
0867: IsClosedInputStream bIn = new IsClosedInputStream(new byte[4]);
0868: IsClosedReader readerIn = new IsClosedReader("abcdef");
0869:
0870: this .pstmt.setBinaryStream(1, bIn, 4);
0871: this .pstmt.execute();
0872: assertEquals(shouldBeClosedStream, bIn.isClosed());
0873:
0874: this .pstmt.setCharacterStream(1, readerIn, 6);
0875: this .pstmt.execute();
0876: assertEquals(shouldBeClosedReader, readerIn.isClosed());
0877:
0878: this .pstmt.close();
0879: }
0880:
0881: class IsClosedReader extends StringReader {
0882:
0883: boolean isClosed = false;
0884:
0885: public IsClosedReader(String arg0) {
0886: super (arg0);
0887: }
0888:
0889: public void close() {
0890: super .close();
0891:
0892: this .isClosed = true;
0893: }
0894:
0895: public boolean isClosed() {
0896: return this .isClosed;
0897: }
0898:
0899: }
0900:
0901: class IsClosedInputStream extends ByteArrayInputStream {
0902:
0903: boolean isClosed = false;
0904:
0905: public IsClosedInputStream(byte[] arg0, int arg1, int arg2) {
0906: super (arg0, arg1, arg2);
0907: }
0908:
0909: public IsClosedInputStream(byte[] arg0) {
0910: super (arg0);
0911: }
0912:
0913: public void close() throws IOException {
0914: // TODO Auto-generated method stub
0915: super .close();
0916: this .isClosed = true;
0917: }
0918:
0919: public boolean isClosed() {
0920: return this .isClosed;
0921: }
0922: }
0923:
0924: /**
0925: * Tests fix for BUG#1774 -- Truncated words after double quote
0926: *
0927: * @throws Exception
0928: * if the test fails.
0929: */
0930: public void testBug1774() throws Exception {
0931: try {
0932: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
0933: this .stmt
0934: .executeUpdate("CREATE TABLE testBug1774 (field1 VARCHAR(255))");
0935:
0936: PreparedStatement pStmt = this .conn
0937: .prepareStatement("INSERT INTO testBug1774 VALUES (?)");
0938:
0939: String testString = "The word contains \" character";
0940:
0941: pStmt.setString(1, testString);
0942: pStmt.executeUpdate();
0943:
0944: this .rs = this .stmt
0945: .executeQuery("SELECT * FROM testBug1774");
0946: this .rs.next();
0947: assertEquals(this .rs.getString(1), testString);
0948: } finally {
0949: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
0950: }
0951: }
0952:
0953: /**
0954: * Tests fix for BUG#1901 -- PreparedStatement.setObject(int, Object, int,
0955: * int) doesn't support CLOB or BLOB types.
0956: *
0957: * @throws Exception
0958: * if this test fails for any reason
0959: */
0960: public void testBug1901() throws Exception {
0961: try {
0962: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
0963: this .stmt
0964: .executeUpdate("CREATE TABLE testBug1901 (field1 VARCHAR(255))");
0965: this .stmt
0966: .executeUpdate("INSERT INTO testBug1901 VALUES ('aaa')");
0967:
0968: this .rs = this .stmt
0969: .executeQuery("SELECT field1 FROM testBug1901");
0970: this .rs.next();
0971:
0972: Clob valueAsClob = this .rs.getClob(1);
0973: Blob valueAsBlob = this .rs.getBlob(1);
0974:
0975: PreparedStatement pStmt = this .conn
0976: .prepareStatement("INSERT INTO testBug1901 VALUES (?)");
0977: pStmt.setObject(1, valueAsClob, java.sql.Types.CLOB, 0);
0978: pStmt.executeUpdate();
0979: pStmt.setObject(1, valueAsBlob, java.sql.Types.BLOB, 0);
0980: pStmt.executeUpdate();
0981: } finally {
0982: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
0983: }
0984: }
0985:
0986: /**
0987: * Test fix for BUG#1933 -- Driver property 'maxRows' has no effect.
0988: *
0989: * @throws Exception
0990: * if the test fails.
0991: */
0992: public void testBug1933() throws Exception {
0993: if (versionMeetsMinimum(4, 0)) {
0994: Connection maxRowsConn = null;
0995: PreparedStatement maxRowsPrepStmt = null;
0996: Statement maxRowsStmt = null;
0997:
0998: try {
0999: Properties props = new Properties();
1000:
1001: props.setProperty("maxRows", "1");
1002:
1003: maxRowsConn = getConnectionWithProps(props);
1004:
1005: maxRowsStmt = maxRowsConn.createStatement();
1006:
1007: assertTrue(maxRowsStmt.getMaxRows() == 1);
1008:
1009: this .rs = maxRowsStmt
1010: .executeQuery("SELECT 1 UNION SELECT 2");
1011:
1012: this .rs.next();
1013:
1014: maxRowsPrepStmt = maxRowsConn
1015: .prepareStatement("SELECT 1 UNION SELECT 2");
1016:
1017: assertTrue(maxRowsPrepStmt.getMaxRows() == 1);
1018:
1019: this .rs = maxRowsPrepStmt.executeQuery();
1020:
1021: this .rs.next();
1022:
1023: assertTrue(!this .rs.next());
1024:
1025: props.setProperty("useServerPrepStmts", "false");
1026:
1027: maxRowsConn = getConnectionWithProps(props);
1028:
1029: maxRowsPrepStmt = maxRowsConn
1030: .prepareStatement("SELECT 1 UNION SELECT 2");
1031:
1032: assertTrue(maxRowsPrepStmt.getMaxRows() == 1);
1033:
1034: this .rs = maxRowsPrepStmt.executeQuery();
1035:
1036: this .rs.next();
1037:
1038: assertTrue(!this .rs.next());
1039: } finally {
1040: maxRowsConn.close();
1041: }
1042: }
1043: }
1044:
1045: /**
1046: * Tests the fix for BUG#1934 -- prepareStatement dies silently when
1047: * encountering Statement.RETURN_GENERATED_KEY
1048: *
1049: * @throws Exception
1050: * if the test fails
1051: */
1052: public void testBug1934() throws Exception {
1053: if (isRunningOnJdk131()) {
1054: return; // test not valid on JDK-1.3.1
1055: }
1056:
1057: try {
1058: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
1059: this .stmt
1060: .executeUpdate("CREATE TABLE testBug1934 (field1 INT)");
1061:
1062: System.out.println("Before prepareStatement()");
1063:
1064: this .pstmt = this .conn.prepareStatement(
1065: "INSERT INTO testBug1934 VALUES (?)",
1066: java.sql.Statement.RETURN_GENERATED_KEYS);
1067:
1068: assertTrue(this .pstmt != null);
1069:
1070: System.out.println("After prepareStatement() - "
1071: + this .pstmt);
1072: } finally {
1073: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
1074: }
1075: }
1076:
1077: /**
1078: * Tests fix for BUG#1958 - Improper bounds checking on
1079: * PreparedStatement.setFoo().
1080: *
1081: * @throws Exception
1082: * if the test fails.
1083: */
1084: public void testBug1958() throws Exception {
1085: PreparedStatement pStmt = null;
1086:
1087: try {
1088: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1958");
1089: this .stmt
1090: .executeUpdate("CREATE TABLE testBug1958 (field1 int)");
1091:
1092: pStmt = this .conn
1093: .prepareStatement("SELECT * FROM testBug1958 WHERE field1 IN (?, ?, ?)");
1094:
1095: try {
1096: pStmt.setInt(4, 1);
1097: } catch (SQLException sqlEx) {
1098: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
1099: .equals(sqlEx.getSQLState()));
1100: }
1101: } finally {
1102: if (pStmt != null) {
1103: pStmt.close();
1104: }
1105:
1106: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug1958");
1107: }
1108: }
1109:
1110: /**
1111: * Tests the fix for BUG#2606, server-side prepared statements not returning
1112: * datatype YEAR correctly.
1113: *
1114: * @throws Exception
1115: * if the test fails.
1116: */
1117: public void testBug2606() throws Exception {
1118: try {
1119: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
1120: this .stmt
1121: .executeUpdate("CREATE TABLE testBug2606(year_field YEAR)");
1122: this .stmt
1123: .executeUpdate("INSERT INTO testBug2606 VALUES (2004)");
1124:
1125: PreparedStatement yrPstmt = this .conn
1126: .prepareStatement("SELECT year_field FROM testBug2606");
1127:
1128: this .rs = yrPstmt.executeQuery();
1129:
1130: assertTrue(this .rs.next());
1131:
1132: assertEquals(2004, this .rs.getInt(1));
1133: } finally {
1134: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
1135: }
1136: }
1137:
1138: /**
1139: * Tests the fix for BUG#2671, nulls encoded incorrectly in server-side
1140: * prepared statements.
1141: *
1142: * @throws Exception
1143: * if an error occurs.
1144: */
1145: public void testBug2671() throws Exception {
1146: if (versionMeetsMinimum(4, 1)) {
1147: try {
1148: this .stmt.executeUpdate("DROP TABLE IF EXISTS test3");
1149: this .stmt
1150: .executeUpdate("CREATE TABLE test3 ("
1151: + " `field1` int(8) NOT NULL auto_increment,"
1152: + " `field2` int(8) unsigned zerofill default NULL,"
1153: + " `field3` varchar(30) binary NOT NULL default '',"
1154: + " `field4` varchar(100) default NULL,"
1155: + " `field5` datetime NULL default '0000-00-00 00:00:00',"
1156: + " PRIMARY KEY (`field1`),"
1157: + " UNIQUE KEY `unq_id` (`field2`),"
1158: + " UNIQUE KEY (`field3`),"
1159: + " UNIQUE KEY (`field2`)"
1160: + " ) TYPE=InnoDB CHARACTER SET utf8");
1161:
1162: this .stmt
1163: .executeUpdate("insert into test3 (field1, field3, field4) values (1,'blewis','Bob Lewis')");
1164:
1165: String query = " "
1166: + "UPDATE "
1167: + " test3 "
1168: + "SET "
1169: + " field2=? "
1170: + " ,field3=? "
1171: + " ,field4=? "
1172: + " ,field5=? "
1173: + "WHERE "
1174: + " field1 = ? ";
1175:
1176: java.sql.Date mydate = null;
1177:
1178: this .pstmt = this .conn.prepareStatement(query);
1179:
1180: this .pstmt.setInt(1, 13);
1181: this .pstmt.setString(2, "abc");
1182: this .pstmt.setString(3, "def");
1183: this .pstmt.setDate(4, mydate);
1184: this .pstmt.setInt(5, 1);
1185:
1186: int retval = this .pstmt.executeUpdate();
1187: assertTrue(retval == 1);
1188: } finally {
1189: this .stmt.executeUpdate("DROP TABLE IF EXISTS test3");
1190: }
1191: }
1192: }
1193:
1194: /**
1195: * Tests fix for BUG#3103 -- java.util.Date not accepted as parameter to
1196: * PreparedStatement.setObject().
1197: *
1198: * @throws Exception
1199: * if the test fails
1200: *
1201: * @deprecated uses deprecated methods of Date class
1202: */
1203: public void testBug3103() throws Exception {
1204: try {
1205: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
1206: this .stmt
1207: .executeUpdate("CREATE TABLE testBug3103 (field1 DATETIME)");
1208:
1209: PreparedStatement pStmt = this .conn
1210: .prepareStatement("INSERT INTO testBug3103 VALUES (?)");
1211:
1212: java.util.Date utilDate = new java.util.Date();
1213:
1214: pStmt.setObject(1, utilDate);
1215: pStmt.executeUpdate();
1216:
1217: this .rs = this .stmt
1218: .executeQuery("SELECT field1 FROM testBug3103");
1219: this .rs.next();
1220:
1221: java.util.Date retrUtilDate = new java.util.Date(this .rs
1222: .getTimestamp(1).getTime());
1223:
1224: // We can only compare on the day/month/year hour/minute/second
1225: // interval, because the timestamp has added milliseconds to the
1226: // internal date...
1227: assertTrue("Dates not equal",
1228: (utilDate.getMonth() == retrUtilDate.getMonth())
1229: && (utilDate.getDate() == retrUtilDate
1230: .getDate())
1231: && (utilDate.getYear() == retrUtilDate
1232: .getYear())
1233: && (utilDate.getHours() == retrUtilDate
1234: .getHours())
1235: && (utilDate.getMinutes() == retrUtilDate
1236: .getMinutes())
1237: && (utilDate.getSeconds() == retrUtilDate
1238: .getSeconds()));
1239: } finally {
1240: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
1241: }
1242: }
1243:
1244: /**
1245: * Tests fix for BUG#3520
1246: *
1247: * @throws Exception
1248: * ...
1249: */
1250: public void testBug3520() throws Exception {
1251: try {
1252: this .stmt.executeUpdate("DROP TABLE IF EXISTS t");
1253: this .stmt
1254: .executeUpdate("CREATE TABLE t (s1 int,primary key (s1))");
1255: this .stmt.executeUpdate("INSERT INTO t VALUES (1)");
1256: this .stmt.executeUpdate("INSERT INTO t VALUES (1)");
1257: } catch (SQLException sqlEx) {
1258: System.out.println(sqlEx.getSQLState());
1259: } finally {
1260: this .stmt.executeUpdate("DROP TABLE IF EXISTS t");
1261: }
1262: }
1263:
1264: /**
1265: * Test fix for BUG#3557 -- UpdatableResultSet not picking up default values
1266: *
1267: * @throws Exception
1268: * if test fails.
1269: */
1270: public void testBug3557() throws Exception {
1271: boolean populateDefaults = ((com.mysql.jdbc.ConnectionProperties) this .conn)
1272: .getPopulateInsertRowWithDefaultValues();
1273:
1274: try {
1275: ((com.mysql.jdbc.ConnectionProperties) this .conn)
1276: .setPopulateInsertRowWithDefaultValues(true);
1277:
1278: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");
1279:
1280: this .stmt.executeUpdate("CREATE TABLE testBug3557 ( "
1281: + "`a` varchar(255) NOT NULL default 'XYZ', "
1282: + "`b` varchar(255) default '123', "
1283: + "PRIMARY KEY (`a`))");
1284:
1285: Statement updStmt = this .conn.createStatement(
1286: ResultSet.TYPE_SCROLL_SENSITIVE,
1287: ResultSet.CONCUR_UPDATABLE);
1288: this .rs = updStmt.executeQuery("SELECT * FROM testBug3557");
1289:
1290: assertTrue(this .rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE);
1291:
1292: this .rs.moveToInsertRow();
1293:
1294: assertEquals("XYZ", this .rs.getObject(1));
1295: assertEquals("123", this .rs.getObject(2));
1296: } finally {
1297: ((com.mysql.jdbc.ConnectionProperties) this .conn)
1298: .setPopulateInsertRowWithDefaultValues(populateDefaults);
1299:
1300: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");
1301: }
1302: }
1303:
1304: /**
1305: * Tests fix for BUG#3620 -- Timezone not respected correctly.
1306: *
1307: * @throws SQLException
1308: * if the test fails.
1309: */
1310: public void testBug3620() throws SQLException {
1311: if (isRunningOnJRockit()) {
1312: // bug with their timezones
1313: return;
1314: }
1315:
1316: if (isRunningOnJdk131()) {
1317: // bug with timezones, no update
1318: // for new DST in USA
1319: return;
1320: }
1321:
1322: long epsillon = 3000; // 3 seconds time difference
1323:
1324: try {
1325: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
1326: this .stmt
1327: .executeUpdate("CREATE TABLE testBug3620 (field1 TIMESTAMP)");
1328:
1329: PreparedStatement tsPstmt = this .conn
1330: .prepareStatement("INSERT INTO testBug3620 VALUES (?)");
1331:
1332: Calendar pointInTime = Calendar.getInstance();
1333: pointInTime.set(2004, 02, 29, 10, 0, 0);
1334:
1335: long pointInTimeOffset = pointInTime.getTimeZone()
1336: .getRawOffset();
1337:
1338: java.sql.Timestamp ts = new java.sql.Timestamp(pointInTime
1339: .getTime().getTime());
1340:
1341: tsPstmt.setTimestamp(1, ts);
1342: tsPstmt.executeUpdate();
1343:
1344: String tsValueAsString = getSingleValue("testBug3620",
1345: "field1", null).toString();
1346:
1347: System.out.println("Timestamp as string with no calendar: "
1348: + tsValueAsString.toString());
1349:
1350: Calendar cal = Calendar.getInstance(TimeZone
1351: .getTimeZone("UTC"));
1352:
1353: this .stmt.executeUpdate("DELETE FROM testBug3620");
1354:
1355: Properties props = new Properties();
1356: props.put("useTimezone", "true");
1357: // props.put("serverTimezone", "UTC");
1358:
1359: Connection tzConn = getConnectionWithProps(props);
1360:
1361: Statement tsStmt = tzConn.createStatement();
1362:
1363: tsPstmt = tzConn
1364: .prepareStatement("INSERT INTO testBug3620 VALUES (?)");
1365:
1366: tsPstmt.setTimestamp(1, ts, cal);
1367: tsPstmt.executeUpdate();
1368:
1369: tsValueAsString = getSingleValue("testBug3620", "field1",
1370: null).toString();
1371:
1372: Timestamp tsValueAsTimestamp = (Timestamp) getSingleValue(
1373: "testBug3620", "field1", null);
1374:
1375: System.out
1376: .println("Timestamp as string with UTC calendar: "
1377: + tsValueAsString.toString());
1378: System.out
1379: .println("Timestamp as Timestamp with UTC calendar: "
1380: + tsValueAsTimestamp);
1381:
1382: this .rs = tsStmt
1383: .executeQuery("SELECT field1 FROM testBug3620");
1384: this .rs.next();
1385:
1386: Timestamp tsValueUTC = this .rs.getTimestamp(1, cal);
1387:
1388: //
1389: // We use this testcase with other vendors, JDBC spec
1390: // requires result set fields can only be read once,
1391: // although MySQL doesn't require this ;)
1392: //
1393: this .rs = tsStmt
1394: .executeQuery("SELECT field1 FROM testBug3620");
1395: this .rs.next();
1396:
1397: Timestamp tsValueStmtNoCal = this .rs.getTimestamp(1);
1398:
1399: System.out
1400: .println("Timestamp specifying UTC calendar from normal statement: "
1401: + tsValueUTC.toString());
1402:
1403: PreparedStatement tsPstmtRetr = tzConn
1404: .prepareStatement("SELECT field1 FROM testBug3620");
1405:
1406: this .rs = tsPstmtRetr.executeQuery();
1407: this .rs.next();
1408:
1409: Timestamp tsValuePstmtUTC = this .rs.getTimestamp(1, cal);
1410:
1411: System.out
1412: .println("Timestamp specifying UTC calendar from prepared statement: "
1413: + tsValuePstmtUTC.toString());
1414:
1415: //
1416: // We use this testcase with other vendors, JDBC spec
1417: // requires result set fields can only be read once,
1418: // although MySQL doesn't require this ;)
1419: //
1420: this .rs = tsPstmtRetr.executeQuery();
1421: this .rs.next();
1422:
1423: Timestamp tsValuePstmtNoCal = this .rs.getTimestamp(1);
1424:
1425: System.out
1426: .println("Timestamp specifying no calendar from prepared statement: "
1427: + tsValuePstmtNoCal.toString());
1428:
1429: long stmtDeltaTWithCal = (ts.getTime() - tsValueStmtNoCal
1430: .getTime());
1431:
1432: long deltaOrig = Math.abs(stmtDeltaTWithCal
1433: - pointInTimeOffset);
1434:
1435: assertTrue(
1436: "Difference between original timestamp and timestamp retrieved using java.sql.Statement "
1437: + "set in database using UTC calendar is not ~= "
1438: + epsillon
1439: + ", it is actually "
1440: + deltaOrig, (deltaOrig < epsillon));
1441:
1442: long pStmtDeltaTWithCal = (ts.getTime() - tsValuePstmtNoCal
1443: .getTime());
1444:
1445: System.out
1446: .println(Math.abs(pStmtDeltaTWithCal
1447: - pointInTimeOffset)
1448: + " < "
1449: + epsillon
1450: + (Math.abs(pStmtDeltaTWithCal
1451: - pointInTimeOffset) < epsillon));
1452: assertTrue(
1453: "Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement "
1454: + "set in database using UTC calendar is not ~= "
1455: + epsillon
1456: + ", it is actually "
1457: + pStmtDeltaTWithCal,
1458: (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));
1459:
1460: System.out
1461: .println("Difference between original ts and ts with no calendar: "
1462: + (ts.getTime() - tsValuePstmtNoCal
1463: .getTime())
1464: + ", offset should be "
1465: + pointInTimeOffset);
1466: } finally {
1467: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
1468: }
1469: }
1470:
1471: /**
1472: * Tests that DataTruncation is thrown when data is truncated.
1473: *
1474: * @throws Exception
1475: * if the test fails.
1476: */
1477: public void testBug3697() throws Exception {
1478: try {
1479: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
1480: this .stmt
1481: .executeUpdate("CREATE TABLE testBug3697 (field1 VARCHAR(255))");
1482:
1483: StringBuffer updateBuf = new StringBuffer(
1484: "INSERT INTO testBug3697 VALUES ('");
1485:
1486: for (int i = 0; i < 512; i++) {
1487: updateBuf.append("A");
1488: }
1489:
1490: updateBuf.append("')");
1491:
1492: try {
1493: this .stmt.executeUpdate(updateBuf.toString());
1494: } catch (DataTruncation dtEx) {
1495: // This is an expected exception....
1496: }
1497:
1498: SQLWarning warningChain = this .stmt.getWarnings();
1499:
1500: System.out.println(warningChain);
1501: } finally {
1502: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
1503: }
1504: }
1505:
1506: /**
1507: * Tests fix for BUG#3804, data truncation on server should throw
1508: * DataTruncation exception.
1509: *
1510: * @throws Exception
1511: * if the test fails
1512: */
1513: public void testBug3804() throws Exception {
1514: if (versionMeetsMinimum(4, 1)) {
1515: try {
1516: this .stmt
1517: .executeUpdate("DROP TABLE IF EXISTS testBug3804");
1518: this .stmt
1519: .executeUpdate("CREATE TABLE testBug3804 (field1 VARCHAR(5))");
1520:
1521: boolean caughtTruncation = false;
1522:
1523: try {
1524: this .stmt
1525: .executeUpdate("INSERT INTO testBug3804 VALUES ('1234567')");
1526: } catch (DataTruncation truncationEx) {
1527: caughtTruncation = true;
1528: System.out.println(truncationEx);
1529: }
1530:
1531: assertTrue(
1532: "Data truncation exception should've been thrown",
1533: caughtTruncation);
1534: } finally {
1535: this .stmt
1536: .executeUpdate("DROP TABLE IF EXISTS testBug3804");
1537: }
1538: }
1539: }
1540:
1541: /**
1542: * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
1543: * generated keys (even though that's not JDBC compliant).
1544: *
1545: * @throws Exception
1546: * if the test fails
1547: */
1548: public void testBug3873() throws Exception {
1549: if (isRunningOnJdk131()) {
1550: return; // test not valid on JDK-1.3.1
1551: }
1552:
1553: PreparedStatement batchStmt = null;
1554:
1555: try {
1556: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
1557: this .stmt
1558: .executeUpdate("CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
1559: batchStmt = this .conn.prepareStatement(
1560: "INSERT INTO testBug3873 (dataField) VALUES (?)",
1561: Statement.RETURN_GENERATED_KEYS);
1562: batchStmt.setString(1, "abc");
1563: batchStmt.addBatch();
1564: batchStmt.setString(1, "def");
1565: batchStmt.addBatch();
1566: batchStmt.setString(1, "ghi");
1567: batchStmt.addBatch();
1568:
1569: int[] updateCounts = batchStmt.executeBatch();
1570:
1571: this .rs = batchStmt.getGeneratedKeys();
1572:
1573: while (this .rs.next()) {
1574: System.out.println(this .rs.getInt(1));
1575: }
1576:
1577: this .rs = batchStmt.getGeneratedKeys();
1578: assertTrue(this .rs.next());
1579: assertTrue(1 == this .rs.getInt(1));
1580: assertTrue(this .rs.next());
1581: assertTrue(2 == this .rs.getInt(1));
1582: assertTrue(this .rs.next());
1583: assertTrue(3 == this .rs.getInt(1));
1584: assertTrue(!this .rs.next());
1585: } finally {
1586: if (batchStmt != null) {
1587: batchStmt.close();
1588: }
1589:
1590: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
1591: }
1592: }
1593:
1594: /**
1595: * Tests fix for BUG#4119 -- misbehavior in a managed environment from
1596: * MVCSoft JDO
1597: *
1598: * @throws Exception
1599: * if the test fails.
1600: */
1601: public void testBug4119() throws Exception {
1602: try {
1603: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
1604: this .stmt.executeUpdate("CREATE TABLE `testBug4119` ("
1605: + "`field1` varchar(255) NOT NULL default '',"
1606: + "`field2` bigint(20) default NULL,"
1607: + "`field3` int(11) default NULL,"
1608: + "`field4` datetime default NULL,"
1609: + "`field5` varchar(75) default NULL,"
1610: + "`field6` varchar(75) default NULL,"
1611: + "`field7` varchar(75) default NULL,"
1612: + "`field8` datetime default NULL,"
1613: + " PRIMARY KEY (`field1`)" + ")");
1614:
1615: PreparedStatement pStmt = this .conn
1616: .prepareStatement("insert into testBug4119 (field2, field3,"
1617: + "field4, field5, field6, field7, field8, field1) values (?, ?,"
1618: + "?, ?, ?, ?, ?, ?)");
1619:
1620: pStmt.setString(1, "0");
1621: pStmt.setString(2, "0");
1622: pStmt.setTimestamp(3, new java.sql.Timestamp(System
1623: .currentTimeMillis()));
1624: pStmt.setString(4, "ABC");
1625: pStmt.setString(5, "DEF");
1626: pStmt.setString(6, "AA");
1627: pStmt.setTimestamp(7, new java.sql.Timestamp(System
1628: .currentTimeMillis()));
1629: pStmt.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1630: pStmt.executeUpdate();
1631: } finally {
1632: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
1633: }
1634: }
1635:
1636: /**
1637: * Tests fix for BUG#4311 - Error in JDBC retrieval of mediumint column when
1638: * using prepared statements and binary result sets.
1639: *
1640: * @throws Exception
1641: * if the test fails.
1642: */
1643: public void testBug4311() throws Exception {
1644: try {
1645: int lowValue = -8388608;
1646: int highValue = 8388607;
1647:
1648: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
1649: this .stmt
1650: .executeUpdate("CREATE TABLE testBug4311 (low MEDIUMINT, high MEDIUMINT)");
1651: this .stmt.executeUpdate("INSERT INTO testBug4311 VALUES ("
1652: + lowValue + ", " + highValue + ")");
1653:
1654: PreparedStatement pStmt = this .conn
1655: .prepareStatement("SELECT low, high FROM testBug4311");
1656: this .rs = pStmt.executeQuery();
1657: assertTrue(this .rs.next());
1658: assertTrue(this .rs.getInt(1) == lowValue);
1659: assertTrue(this .rs.getInt(2) == highValue);
1660: } finally {
1661: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
1662: }
1663: }
1664:
1665: /**
1666: * Tests fix for BUG#4510 -- Statement.getGeneratedKeys() fails when key >
1667: * 32767
1668: *
1669: * @throws Exception
1670: * if the test fails
1671: */
1672: public void testBug4510() throws Exception {
1673: if (isRunningOnJdk131()) {
1674: return; // test not valid on JDK-1.3.1
1675: }
1676:
1677: try {
1678: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
1679: this .stmt.executeUpdate("CREATE TABLE testBug4510 ("
1680: + "field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
1681: + "field2 VARCHAR(100))");
1682: this .stmt
1683: .executeUpdate("INSERT INTO testBug4510 (field1, field2) VALUES (32767, 'bar')");
1684:
1685: PreparedStatement p = this .conn.prepareStatement(
1686: "insert into testBug4510 (field2) values (?)",
1687: Statement.RETURN_GENERATED_KEYS);
1688:
1689: p.setString(1, "blah");
1690:
1691: p.executeUpdate();
1692:
1693: ResultSet rs = p.getGeneratedKeys();
1694: rs.next();
1695: System.out.println("Id: " + rs.getInt(1));
1696: rs.close();
1697: } finally {
1698: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
1699: }
1700: }
1701:
1702: /**
1703: * Server doesn't accept everything as a server-side prepared statement, so
1704: * by default we scan for stuff it can't handle.
1705: *
1706: * @throws SQLException
1707: */
1708: public void testBug4718() throws SQLException {
1709: if (versionMeetsMinimum(4, 1, 0)
1710: && ((com.mysql.jdbc.Connection) this .conn)
1711: .getUseServerPreparedStmts()) {
1712: this .pstmt = this .conn.prepareStatement("SELECT 1 LIMIT ?");
1713: assertTrue(this .pstmt instanceof com.mysql.jdbc.PreparedStatement);
1714:
1715: this .pstmt = this .conn.prepareStatement("SELECT 1 LIMIT 1");
1716: assertTrue(this .pstmt instanceof com.mysql.jdbc.ServerPreparedStatement);
1717:
1718: this .pstmt = this .conn
1719: .prepareStatement("SELECT 1 LIMIT 1, ?");
1720: assertTrue(this .pstmt instanceof com.mysql.jdbc.PreparedStatement);
1721:
1722: try {
1723: this .stmt
1724: .executeUpdate("DROP TABLE IF EXISTS testBug4718");
1725: this .stmt
1726: .executeUpdate("CREATE TABLE testBug4718 (field1 char(32))");
1727:
1728: this .pstmt = this .conn
1729: .prepareStatement("ALTER TABLE testBug4718 ADD INDEX (field1)");
1730: assertTrue(this .pstmt instanceof com.mysql.jdbc.PreparedStatement);
1731:
1732: this .pstmt = this .conn.prepareStatement("SELECT 1");
1733: assertTrue(this .pstmt instanceof ServerPreparedStatement);
1734:
1735: this .pstmt = this .conn
1736: .prepareStatement("UPDATE testBug4718 SET field1=1");
1737: assertTrue(this .pstmt instanceof ServerPreparedStatement);
1738:
1739: this .pstmt = this .conn
1740: .prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT 1");
1741: assertTrue(this .pstmt instanceof ServerPreparedStatement);
1742:
1743: this .pstmt = this .conn
1744: .prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT ?");
1745: assertTrue(this .pstmt instanceof com.mysql.jdbc.PreparedStatement);
1746:
1747: this .pstmt = this .conn
1748: .prepareStatement("UPDATE testBug4718 SET field1='Will we ignore LIMIT ?,?'");
1749: assertTrue(this .pstmt instanceof ServerPreparedStatement);
1750:
1751: } finally {
1752: this .stmt
1753: .executeUpdate("DROP TABLE IF EXISTS testBug4718");
1754: }
1755: }
1756: }
1757:
1758: /**
1759: * Tests fix for BUG#5012 -- ServerPreparedStatements dealing with return of
1760: * DECIMAL type don't work.
1761: *
1762: * @throws Exception
1763: * if the test fails.
1764: */
1765: public void testBug5012() throws Exception {
1766: PreparedStatement pStmt = null;
1767: String valueAsString = "12345.12";
1768:
1769: try {
1770: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
1771: this .stmt
1772: .executeUpdate("CREATE TABLE testBug5012(field1 DECIMAL(10,2))");
1773: this .stmt.executeUpdate("INSERT INTO testBug5012 VALUES ("
1774: + valueAsString + ")");
1775:
1776: pStmt = this .conn
1777: .prepareStatement("SELECT field1 FROM testBug5012");
1778: this .rs = pStmt.executeQuery();
1779: assertTrue(this .rs.next());
1780: assertEquals(new BigDecimal(valueAsString), this .rs
1781: .getBigDecimal(1));
1782: } finally {
1783: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
1784:
1785: if (pStmt != null) {
1786: pStmt.close();
1787: }
1788: }
1789: }
1790:
1791: /**
1792: * Tests fix for BUG#5133 -- PreparedStatement.toString() doesn't return
1793: * correct value if no parameters are present in statement.
1794: *
1795: * @throws Exception
1796: */
1797: public void testBug5133() throws Exception {
1798: String query = "SELECT 1";
1799: String output = this .conn.prepareStatement(query).toString();
1800: System.out.println(output);
1801:
1802: assertTrue(output.indexOf(query) != -1);
1803: }
1804:
1805: /**
1806: * Tests for BUG#5191 -- PreparedStatement.executeQuery() gives
1807: * OutOfMemoryError
1808: *
1809: * @throws Exception
1810: * if the test fails.
1811: */
1812: public void testBug5191() throws Exception {
1813: PreparedStatement pStmt = null;
1814:
1815: try {
1816: this .stmt
1817: .executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
1818: this .stmt
1819: .executeUpdate("DROP TABLE IF EXISTS testBug5191C");
1820:
1821: this .stmt.executeUpdate("CREATE TABLE testBug5191Q"
1822: + "(QuestionId int NOT NULL AUTO_INCREMENT, "
1823: + "Text VARCHAR(200), "
1824: + "PRIMARY KEY(QuestionId))");
1825:
1826: this .stmt.executeUpdate("CREATE TABLE testBug5191C"
1827: + "(CategoryId int, " + "QuestionId int)");
1828:
1829: String[] questions = new String[] {
1830: "What is your name?",
1831: "What is your quest?",
1832: "What is the airspeed velocity of an unladen swollow?",
1833: "How many roads must a man walk?",
1834: "Where's the tea?", };
1835:
1836: for (int i = 0; i < questions.length; i++) {
1837: this .stmt
1838: .executeUpdate("INSERT INTO testBug5191Q(Text)"
1839: + " VALUES (\"" + questions[i] + "\")");
1840: int catagory = (i < 3) ? 0 : i;
1841:
1842: this .stmt.executeUpdate("INSERT INTO testBug5191C"
1843: + "(CategoryId, QuestionId) VALUES ("
1844: + catagory + ", " + i + ")");
1845: /*
1846: * this.stmt.executeUpdate("INSERT INTO testBug5191C" +
1847: * "(CategoryId, QuestionId) VALUES (" + catagory + ", (SELECT
1848: * testBug5191Q.QuestionId" + " FROM testBug5191Q " + "WHERE
1849: * testBug5191Q.Text LIKE '" + questions[i] + "'))");
1850: */
1851: }
1852:
1853: pStmt = this .conn
1854: .prepareStatement("SELECT qc.QuestionId, q.Text "
1855: + "FROM testBug5191Q q, testBug5191C qc "
1856: + "WHERE qc.CategoryId = ? "
1857: + " AND q.QuestionId = qc.QuestionId");
1858:
1859: int catId = 0;
1860: for (int i = 0; i < 100; i++) {
1861: execQueryBug5191(pStmt, catId);
1862: }
1863:
1864: } finally {
1865: this .stmt
1866: .executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
1867: this .stmt
1868: .executeUpdate("DROP TABLE IF EXISTS testBug5191C");
1869:
1870: if (pStmt != null) {
1871: pStmt.close();
1872: }
1873: }
1874: }
1875:
1876: public void testBug5235() throws Exception {
1877: Properties props = new Properties();
1878: props.setProperty("zeroDateTimeBehavior", "convertToNull");
1879:
1880: Connection convertToNullConn = getConnectionWithProps(props);
1881: Statement convertToNullStmt = convertToNullConn
1882: .createStatement();
1883: try {
1884: convertToNullStmt
1885: .executeUpdate("DROP TABLE IF EXISTS testBug5235");
1886: convertToNullStmt
1887: .executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
1888: convertToNullStmt
1889: .executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");
1890:
1891: PreparedStatement ps = convertToNullConn
1892: .prepareStatement("SELECT field1 FROM testBug5235");
1893: this .rs = ps.executeQuery();
1894:
1895: if (this .rs.next()) {
1896: Date d = (Date) this .rs.getObject("field1");
1897: System.out.println("date: " + d);
1898: }
1899: } finally {
1900: convertToNullStmt
1901: .executeUpdate("DROP TABLE IF EXISTS testBug5235");
1902: }
1903: }
1904:
1905: public void testBug5450() throws Exception {
1906: if (versionMeetsMinimum(4, 1)) {
1907: String table = "testBug5450";
1908: String column = "policyname";
1909:
1910: try {
1911: Properties props = new Properties();
1912: props.setProperty("characterEncoding", "utf-8");
1913:
1914: Connection utf8Conn = getConnectionWithProps(props);
1915: Statement utfStmt = utf8Conn.createStatement();
1916:
1917: this .stmt
1918: .executeUpdate("DROP TABLE IF EXISTS " + table);
1919:
1920: this .stmt
1921: .executeUpdate("CREATE TABLE "
1922: + table
1923: + "(policyid int NOT NULL AUTO_INCREMENT, "
1924: + column
1925: + " VARCHAR(200), "
1926: + "PRIMARY KEY(policyid)) DEFAULT CHARACTER SET utf8");
1927:
1928: String pname0 = "inserted \uac00 - foo - \u4e00";
1929:
1930: utfStmt
1931: .executeUpdate("INSERT INTO " + table + "("
1932: + column + ")" + " VALUES (\"" + pname0
1933: + "\")");
1934:
1935: this .rs = utfStmt.executeQuery("SELECT " + column
1936: + " FROM " + table);
1937:
1938: this .rs.first();
1939: String pname1 = this .rs.getString(column);
1940:
1941: assertEquals(pname0, pname1);
1942: byte[] bytes = this .rs.getBytes(column);
1943:
1944: String pname2 = new String(bytes, "utf-8");
1945: assertEquals(pname1, pname2);
1946:
1947: utfStmt.executeUpdate("delete from " + table
1948: + " where " + column + " like 'insert%'");
1949:
1950: PreparedStatement s1 = utf8Conn
1951: .prepareStatement("insert into " + table + "("
1952: + column + ") values (?)");
1953:
1954: s1.setString(1, pname0);
1955: s1.executeUpdate();
1956:
1957: String byteesque = "byte " + pname0;
1958: byte[] newbytes = byteesque.getBytes("utf-8");
1959:
1960: s1.setBytes(1, newbytes);
1961: s1.executeUpdate();
1962:
1963: this .rs = utfStmt.executeQuery("select " + column
1964: + " from " + table + " where " + column
1965: + " like 'insert%'");
1966: this .rs.first();
1967: String pname3 = this .rs.getString(column);
1968: assertEquals(pname0, pname3);
1969:
1970: this .rs = utfStmt.executeQuery("select " + column
1971: + " from " + table + " where " + column
1972: + " like 'byte insert%'");
1973: this .rs.first();
1974:
1975: String pname4 = this .rs.getString(column);
1976: assertEquals(byteesque, pname4);
1977:
1978: } finally {
1979: this .stmt
1980: .executeUpdate("DROP TABLE IF EXISTS " + table);
1981: }
1982: }
1983: }
1984:
1985: public void testBug5510() throws Exception {
1986: // This is a server bug that should be fixed by 4.1.6
1987: if (versionMeetsMinimum(4, 1, 6)) {
1988: try {
1989: this .stmt
1990: .executeUpdate("DROP TABLE IF EXISTS testBug5510");
1991:
1992: this .stmt
1993: .executeUpdate("CREATE TABLE `testBug5510` ("
1994: + "`a` bigint(20) NOT NULL auto_increment,"
1995: + "`b` varchar(64) default NULL,"
1996: + "`c` varchar(64) default NULL,"
1997: + "`d` varchar(255) default NULL,"
1998: + "`e` int(11) default NULL,"
1999: + "`f` varchar(32) default NULL,"
2000: + "`g` varchar(32) default NULL,"
2001: + "`h` varchar(80) default NULL,"
2002: + "`i` varchar(255) default NULL,"
2003: + "`j` varchar(255) default NULL,"
2004: + "`k` varchar(255) default NULL,"
2005: + "`l` varchar(32) default NULL,"
2006: + "`m` varchar(32) default NULL,"
2007: + "`n` timestamp NOT NULL default CURRENT_TIMESTAMP on update"
2008: + " CURRENT_TIMESTAMP,"
2009: + "`o` int(11) default NULL,"
2010: + "`p` int(11) default NULL,"
2011: + "PRIMARY KEY (`a`)"
2012: + ") ENGINE=InnoDB DEFAULT CHARSET=latin1");
2013: PreparedStatement pStmt = this .conn
2014: .prepareStatement("INSERT INTO testBug5510 (a) VALUES (?)");
2015: pStmt.setNull(1, 0);
2016: pStmt.executeUpdate();
2017:
2018: } finally {
2019: this .stmt
2020: .executeUpdate("DROP TABLE IF EXISTS testBug5510");
2021: }
2022: }
2023: }
2024:
2025: /**
2026: * Tests fix for BUG#5874, timezone correction goes in wrong 'direction'
2027: * when useTimezone=true and server timezone differs from client timezone.
2028: *
2029: * @throws Exception
2030: * if the test fails.
2031: */
2032: public void testBug5874() throws Exception {
2033: /*
2034: try {
2035: String clientTimezoneName = "America/Los_Angeles";
2036: String serverTimezoneName = "America/Chicago";
2037:
2038: TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));
2039:
2040: long epsillon = 3000; // 3 seconds difference
2041:
2042: long clientTimezoneOffsetMillis = TimeZone.getDefault()
2043: .getRawOffset();
2044: long serverTimezoneOffsetMillis = TimeZone.getTimeZone(
2045: serverTimezoneName).getRawOffset();
2046:
2047: long offsetDifference = clientTimezoneOffsetMillis
2048: - serverTimezoneOffsetMillis;
2049:
2050: Properties props = new Properties();
2051: props.put("useTimezone", "true");
2052: props.put("serverTimezone", serverTimezoneName);
2053:
2054: Connection tzConn = getConnectionWithProps(props);
2055: Statement tzStmt = tzConn.createStatement();
2056: tzStmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
2057: tzStmt
2058: .executeUpdate("CREATE TABLE timeTest (tstamp DATETIME, t TIME)");
2059:
2060: PreparedStatement pstmt = tzConn
2061: .prepareStatement("INSERT INTO timeTest VALUES (?, ?)");
2062:
2063: long now = System.currentTimeMillis(); // Time in milliseconds
2064: // since 1/1/1970 GMT
2065:
2066: Timestamp nowTstamp = new Timestamp(now);
2067: Time nowTime = new Time(now);
2068:
2069: pstmt.setTimestamp(1, nowTstamp);
2070: pstmt.setTime(2, nowTime);
2071: pstmt.executeUpdate();
2072:
2073: this.rs = tzStmt.executeQuery("SELECT * from timeTest");
2074:
2075: // Timestamps look like this: 2004-11-29 13:43:21
2076: SimpleDateFormat timestampFormat = new SimpleDateFormat(
2077: "yyyy-MM-dd HH:mm:ss");
2078: SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
2079:
2080: while (this.rs.next()) {
2081: // Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types
2082: // when calling getString()...
2083: String retrTimestampString = new String(this.rs.getBytes(1));
2084: Timestamp retrTimestamp = this.rs.getTimestamp(1);
2085:
2086: java.util.Date timestampOnServer = timestampFormat
2087: .parse(retrTimestampString);
2088:
2089: long retrievedOffsetForTimestamp = retrTimestamp.getTime()
2090: - timestampOnServer.getTime();
2091:
2092: assertTrue(
2093: "Difference between original timestamp and timestamp retrieved using client timezone is not "
2094: + offsetDifference, (Math
2095: .abs(retrievedOffsetForTimestamp
2096: - offsetDifference) < epsillon));
2097:
2098: String retrTimeString = new String(this.rs.getBytes(2));
2099: Time retrTime = this.rs.getTime(2);
2100:
2101: java.util.Date timeOnServerAsDate = timeFormat
2102: .parse(retrTimeString);
2103: Time timeOnServer = new Time(timeOnServerAsDate.getTime());
2104:
2105: long retrievedOffsetForTime = retrTime.getTime()
2106: - timeOnServer.getTime();
2107:
2108: assertTrue(
2109: "Difference between original times and time retrieved using client timezone is not "
2110: + offsetDifference,
2111: (Math.abs(retrievedOffsetForTime - offsetDifference) < epsillon));
2112: }
2113: } finally {
2114: this.stmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
2115: } */
2116: }
2117:
2118: public void testBug6823() throws SQLException {
2119: innerBug6823(true);
2120: innerBug6823(false);
2121: }
2122:
2123: public void testBug7461() throws Exception {
2124: String tableName = "testBug7461";
2125:
2126: try {
2127: createTable(tableName, "(field1 varchar(4))");
2128: File tempFile = File.createTempFile("mysql-test", ".txt");
2129: tempFile.deleteOnExit();
2130:
2131: FileOutputStream fOut = new FileOutputStream(tempFile);
2132: fOut.write("abcdefghijklmnop".getBytes());
2133: fOut.close();
2134:
2135: try {
2136: this .stmt.executeQuery("LOAD DATA LOCAL INFILE '"
2137: + tempFile.toString() + "' INTO TABLE "
2138: + tableName);
2139: } catch (SQLException sqlEx) {
2140: this .stmt.getWarnings();
2141: }
2142:
2143: } finally {
2144: dropTable(tableName);
2145: }
2146:
2147: }
2148:
2149: public void testBug8181() throws Exception {
2150:
2151: try {
2152: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
2153: this .stmt
2154: .executeUpdate("CREATE TABLE testBug8181(col1 VARCHAR(20),col2 INT)");
2155:
2156: this .pstmt = this .conn
2157: .prepareStatement("INSERT INTO testBug8181(col1,col2) VALUES(?,?)");
2158:
2159: for (int i = 0; i < 20; i++) {
2160: this .pstmt.setString(1, "Test " + i);
2161: this .pstmt.setInt(2, i);
2162: this .pstmt.addBatch();
2163: }
2164:
2165: this .pstmt.executeBatch();
2166:
2167: } finally {
2168: this .stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
2169:
2170: if (this .pstmt != null) {
2171: this .pstmt.close();
2172: }
2173: }
2174: }
2175:
2176: /**
2177: * Tests fix for BUG#8487 - PreparedStatements not creating streaming result
2178: * sets.
2179: *
2180: * @throws Exception
2181: * if the test fails.
2182: */
2183: public void testBug8487() throws Exception {
2184: try {
2185: this .pstmt = this .conn.prepareStatement("SELECT 1",
2186: ResultSet.TYPE_FORWARD_ONLY,
2187: ResultSet.CONCUR_READ_ONLY);
2188:
2189: this .pstmt.setFetchSize(Integer.MIN_VALUE);
2190: this .rs = this .pstmt.executeQuery();
2191: try {
2192: this .conn.createStatement().executeQuery("SELECT 2");
2193: fail("Should have caught a streaming exception here");
2194: } catch (SQLException sqlEx) {
2195: assertTrue(sqlEx.getMessage() != null
2196: && sqlEx.getMessage().indexOf("Streaming") != -1);
2197: }
2198:
2199: } finally {
2200: if (this .rs != null) {
2201: while (this .rs.next())
2202: ;
2203:
2204: this .rs.close();
2205: }
2206:
2207: if (this .pstmt != null) {
2208: this .pstmt.close();
2209: }
2210: }
2211: }
2212:
2213: /**
2214: * Tests multiple statement support with fix for BUG#9704.
2215: *
2216: * @throws Exception
2217: * DOCUMENT ME!
2218: */
2219: public void testBug9704() throws Exception {
2220: if (versionMeetsMinimum(4, 1)) {
2221: Connection multiStmtConn = null;
2222: Statement multiStmt = null;
2223:
2224: try {
2225: Properties props = new Properties();
2226: props.setProperty("allowMultiQueries", "true");
2227:
2228: multiStmtConn = getConnectionWithProps(props);
2229:
2230: multiStmt = multiStmtConn.createStatement();
2231:
2232: multiStmt
2233: .executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
2234: multiStmt
2235: .executeUpdate("CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
2236: multiStmt
2237: .executeUpdate("INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");
2238:
2239: multiStmt
2240: .execute("SELECT field1 FROM testMultiStatements WHERE field1='abcd';"
2241: + "UPDATE testMultiStatements SET field3=3;"
2242: + "SELECT field3 FROM testMultiStatements WHERE field3=3");
2243:
2244: this .rs = multiStmt.getResultSet();
2245:
2246: assertTrue(this .rs.next());
2247:
2248: assertTrue("abcd".equals(this .rs.getString(1)));
2249: this .rs.close();
2250:
2251: // Next should be an update count...
2252: assertTrue(!multiStmt.getMoreResults());
2253:
2254: assertTrue("Update count was "
2255: + multiStmt.getUpdateCount() + ", expected 1",
2256: multiStmt.getUpdateCount() == 1);
2257:
2258: assertTrue(multiStmt.getMoreResults());
2259:
2260: this .rs = multiStmt.getResultSet();
2261:
2262: assertTrue(this .rs.next());
2263:
2264: assertTrue(this .rs.getDouble(1) == 3);
2265:
2266: // End of multi results
2267: assertTrue(!multiStmt.getMoreResults());
2268: assertTrue(multiStmt.getUpdateCount() == -1);
2269: } finally {
2270: if (multiStmt != null) {
2271: multiStmt
2272: .executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
2273:
2274: multiStmt.close();
2275: }
2276:
2277: if (multiStmtConn != null) {
2278: multiStmtConn.close();
2279: }
2280: }
2281: }
2282: }
2283:
2284: /**
2285: * Tests that you can close a statement twice without an NPE.
2286: *
2287: * @throws Exception
2288: * if an error occurs.
2289: */
2290: public void testCloseTwice() throws Exception {
2291: Statement closeMe = this .conn.createStatement();
2292: closeMe.close();
2293: closeMe.close();
2294: }
2295:
2296: public void testCsc4194() throws Exception {
2297: if (isRunningOnJdk131()) {
2298: return; // test not valid on JDK-1.3.1
2299: }
2300:
2301: Connection sjisConn = null;
2302: Connection windows31JConn = null;
2303:
2304: try {
2305: String tableNameText = "testCsc4194Text";
2306: String tableNameBlob = "testCsc4194Blob";
2307:
2308: createTable(tableNameBlob, "(field1 BLOB)");
2309: String charset = "";
2310:
2311: if (versionMeetsMinimum(5, 0, 3)
2312: || versionMeetsMinimum(4, 1, 12)) {
2313: charset = " CHARACTER SET cp932";
2314: } else if (versionMeetsMinimum(4, 1, 0)) {
2315: charset = " CHARACTER SET sjis";
2316: }
2317:
2318: createTable(tableNameText, "(field1 TEXT)" + charset);
2319:
2320: Properties windows31JProps = new Properties();
2321: windows31JProps.setProperty("useUnicode", "true");
2322: windows31JProps.setProperty("characterEncoding",
2323: "Windows-31J");
2324:
2325: windows31JConn = getConnectionWithProps(windows31JProps);
2326: testCsc4194InsertCheckBlob(windows31JConn, tableNameBlob);
2327:
2328: if (versionMeetsMinimum(4, 1, 0)) {
2329: testCsc4194InsertCheckText(windows31JConn,
2330: tableNameText, "Windows-31J");
2331: }
2332:
2333: Properties sjisProps = new Properties();
2334: sjisProps.setProperty("useUnicode", "true");
2335: sjisProps.setProperty("characterEncoding", "sjis");
2336:
2337: sjisConn = getConnectionWithProps(sjisProps);
2338: testCsc4194InsertCheckBlob(sjisConn, tableNameBlob);
2339:
2340: if (versionMeetsMinimum(5, 0, 3)) {
2341: testCsc4194InsertCheckText(sjisConn, tableNameText,
2342: "Windows-31J");
2343: }
2344:
2345: } finally {
2346:
2347: if (windows31JConn != null) {
2348: windows31JConn.close();
2349: }
2350:
2351: if (sjisConn != null) {
2352: sjisConn.close();
2353: }
2354: }
2355: }
2356:
2357: private void testCsc4194InsertCheckBlob(Connection c,
2358: String tableName) throws Exception {
2359: byte[] bArray = new byte[] { (byte) 0xac, (byte) 0xed,
2360: (byte) 0x00, (byte) 0x05 };
2361:
2362: PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
2363: + tableName + " VALUES (?)");
2364: testStmt.setBytes(1, bArray);
2365: testStmt.executeUpdate();
2366:
2367: this .rs = c.createStatement().executeQuery(
2368: "SELECT field1 FROM " + tableName);
2369: assertTrue(this .rs.next());
2370: assertEquals(getByteArrayString(bArray),
2371: getByteArrayString(this .rs.getBytes(1)));
2372: this .rs.close();
2373: }
2374:
2375: private void testCsc4194InsertCheckText(Connection c,
2376: String tableName, String encoding) throws Exception {
2377: byte[] kabuInShiftJIS = { (byte) 0x87, // a double-byte
2378: // charater("kabu") in Shift JIS
2379: (byte) 0x8a, };
2380:
2381: String expected = new String(kabuInShiftJIS, encoding);
2382: PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
2383: + tableName + " VALUES (?)");
2384: testStmt.setString(1, expected);
2385: testStmt.executeUpdate();
2386:
2387: this .rs = c.createStatement().executeQuery(
2388: "SELECT field1 FROM " + tableName);
2389: assertTrue(this .rs.next());
2390: assertEquals(expected, this .rs.getString(1));
2391: this .rs.close();
2392: }
2393:
2394: /**
2395: * Tests all forms of statements influencing getGeneratedKeys().
2396: *
2397: * @throws Exception
2398: * if the test fails.
2399: */
2400: public void testGetGeneratedKeysAllCases() throws Exception {
2401: if (isRunningOnJdk131()) {
2402: return; // test not valid on JDK-1.3.1
2403: }
2404:
2405: System.out.println("Using Statement.executeUpdate()\n");
2406:
2407: try {
2408: createGGKTables();
2409:
2410: // Do the tests
2411: for (int i = 0; i < tests.length; i++) {
2412: doGGKTestStatement(tests[i], true);
2413: }
2414: } finally {
2415: dropGGKTables();
2416: }
2417:
2418: nextID = 1;
2419: count = 0;
2420:
2421: System.out.println("Using Statement.execute()\n");
2422:
2423: try {
2424: createGGKTables();
2425:
2426: // Do the tests
2427: for (int i = 0; i < tests.length; i++) {
2428: doGGKTestStatement(tests[i], false);
2429: }
2430: } finally {
2431: dropGGKTables();
2432: }
2433:
2434: nextID = 1;
2435: count = 0;
2436:
2437: System.out.println("Using PreparedStatement.executeUpdate()\n");
2438:
2439: try {
2440: createGGKTables();
2441:
2442: // Do the tests
2443: for (int i = 0; i < tests.length; i++) {
2444: doGGKTestPreparedStatement(tests[i], true);
2445: }
2446: } finally {
2447: dropGGKTables();
2448: }
2449:
2450: nextID = 1;
2451: count = 0;
2452:
2453: System.out.println("Using PreparedStatement.execute()\n");
2454:
2455: try {
2456: createGGKTables();
2457:
2458: // Do the tests
2459: for (int i = 0; i < tests.length; i++) {
2460: doGGKTestPreparedStatement(tests[i], false);
2461: }
2462: } finally {
2463: dropGGKTables();
2464: }
2465: }
2466:
2467: /**
2468: * Tests that max_rows and 'limit' don't cause exceptions to be thrown.
2469: *
2470: * @throws Exception
2471: * if the test fails.
2472: */
2473: public void testLimitAndMaxRows() throws Exception {
2474: try {
2475: this .stmt
2476: .executeUpdate("DROP TABLE IF EXISTS testMaxRowsAndLimit");
2477: this .stmt
2478: .executeUpdate("CREATE TABLE testMaxRowsAndLimit(limitField INT)");
2479:
2480: for (int i = 0; i < 500; i++) {
2481: this .stmt
2482: .executeUpdate("INSERT INTO testMaxRowsAndLimit VALUES ("
2483: + i + ")");
2484: }
2485:
2486: this .stmt.setMaxRows(250);
2487: this .stmt
2488: .executeQuery("SELECT limitField FROM testMaxRowsAndLimit");
2489: } finally {
2490: this .stmt.setMaxRows(0);
2491:
2492: this .stmt
2493: .executeUpdate("DROP TABLE IF EXISTS testMaxRowsAndLimit");
2494: }
2495: }
2496:
2497: /*
2498: * public void testBug9595() throws Exception { double[] vals = new double[]
2499: * {52.21, 52.22, 52.23, 52.24};
2500: *
2501: * createTable("testBug9595", "(field1 DECIMAL(10,2), sortField INT)");
2502: *
2503: * this.pstmt = this.conn.prepareStatement("INSERT INTO testBug9595 VALUES
2504: * (?, ?)"); // Try setting as doubles for (int i = 0; i < vals.length; i++) {
2505: * this.pstmt.setDouble(1, vals[i]); this.pstmt.setInt(2, i);
2506: * this.pstmt.executeUpdate(); }
2507: *
2508: * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug9595
2509: * ORDER BY sortField"); this.rs = this.pstmt.executeQuery();
2510: *
2511: * int i = 0;
2512: *
2513: * while (this.rs.next()) { double valToTest = vals[i++];
2514: *
2515: * assertEquals(this.rs.getDouble(1), valToTest, 0.001);
2516: * assertEquals(this.rs.getBigDecimal(1).doubleValue(), valToTest, 0.001); }
2517: *
2518: * this.pstmt = this.conn.prepareStatement("INSERT INTO testBug9595 VALUES
2519: * (?, ?)");
2520: *
2521: * this.stmt.executeUpdate("TRUNCATE TABLE testBug9595"); // Now, as
2522: * BigDecimals for (i = 0; i < vals.length; i++) { BigDecimal foo = new
2523: * BigDecimal(vals[i]);
2524: *
2525: * this.pstmt.setObject(1, foo, Types.DECIMAL, 2); this.pstmt.setInt(2, i);
2526: * this.pstmt.executeUpdate(); }
2527: *
2528: * this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug9595
2529: * ORDER BY sortField"); this.rs = this.pstmt.executeQuery();
2530: *
2531: * i = 0;
2532: *
2533: * while (this.rs.next()) { double valToTest = vals[i++];
2534: * System.out.println(this.rs.getString(1));
2535: * assertEquals(this.rs.getDouble(1), valToTest, 0.001);
2536: * assertEquals(this.rs.getBigDecimal(1).doubleValue(), valToTest, 0.001); } }
2537: */
2538:
2539: /**
2540: * Tests that 'LOAD DATA LOCAL INFILE' works
2541: *
2542: * @throws Exception
2543: * if any errors occur
2544: */
2545: public void testLoadData() throws Exception {
2546: try {
2547: int maxAllowedPacket = 1048576;
2548:
2549: this .stmt
2550: .executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
2551: this .stmt
2552: .executeUpdate("CREATE TABLE loadDataRegress (field1 int, field2 int)");
2553:
2554: File tempFile = File.createTempFile("mysql", ".txt");
2555:
2556: // tempFile.deleteOnExit();
2557: System.out.println(tempFile);
2558:
2559: Writer out = new FileWriter(tempFile);
2560:
2561: int localCount = 0;
2562: int rowCount = 128; // maxAllowedPacket * 4;
2563:
2564: for (int i = 0; i < rowCount; i++) {
2565: out
2566: .write((localCount++) + "\t" + (localCount++)
2567: + "\n");
2568: }
2569:
2570: out.close();
2571:
2572: StringBuffer fileNameBuf = null;
2573:
2574: if (File.separatorChar == '\\') {
2575: fileNameBuf = new StringBuffer();
2576:
2577: String fileName = tempFile.getAbsolutePath();
2578: int fileNameLength = fileName.length();
2579:
2580: for (int i = 0; i < fileNameLength; i++) {
2581: char c = fileName.charAt(i);
2582:
2583: if (c == '\\') {
2584: fileNameBuf.append("/");
2585: } else {
2586: fileNameBuf.append(c);
2587: }
2588: }
2589: } else {
2590: fileNameBuf = new StringBuffer(tempFile
2591: .getAbsolutePath());
2592: }
2593:
2594: int updateCount = this .stmt
2595: .executeUpdate("LOAD DATA LOCAL INFILE '"
2596: + fileNameBuf.toString()
2597: + "' INTO TABLE loadDataRegress");
2598: assertTrue(updateCount == rowCount);
2599: } finally {
2600: this .stmt
2601: .executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
2602: }
2603: }
2604:
2605: public void testNullClob() throws Exception {
2606: createTable("testNullClob", "(field1 TEXT NULL)");
2607:
2608: PreparedStatement pStmt = null;
2609:
2610: try {
2611: pStmt = this .conn
2612: .prepareStatement("INSERT INTO testNullClob VALUES (?)");
2613: pStmt.setClob(1, null);
2614: pStmt.executeUpdate();
2615: } finally {
2616: if (pStmt != null) {
2617: pStmt.close();
2618: }
2619: }
2620: }
2621:
2622: /**
2623: * Tests fix for BUG#1658
2624: *
2625: * @throws Exception
2626: * if the fix for parameter bounds checking doesn't work.
2627: */
2628: public void testParameterBoundsCheck() throws Exception {
2629: try {
2630: this .stmt
2631: .executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
2632: this .stmt
2633: .executeUpdate("CREATE TABLE testParameterBoundsCheck(f1 int, f2 int, f3 int, f4 int, f5 int)");
2634:
2635: PreparedStatement pstmt = this .conn
2636: .prepareStatement("UPDATE testParameterBoundsCheck SET f1=?, f2=?,f3=?,f4=? WHERE f5=?");
2637:
2638: pstmt.setString(1, "");
2639: pstmt.setString(2, "");
2640:
2641: try {
2642: pstmt.setString(25, "");
2643: } catch (SQLException sqlEx) {
2644: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
2645: .equals(sqlEx.getSQLState()));
2646: }
2647: } finally {
2648: this .stmt
2649: .executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
2650: }
2651: }
2652:
2653: public void testPStmtTypesBug() throws Exception {
2654: try {
2655: this .stmt
2656: .executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
2657: this .stmt
2658: .executeUpdate("CREATE TABLE testPStmtTypesBug(field1 INT)");
2659: this .pstmt = this .conn
2660: .prepareStatement("INSERT INTO testPStmtTypesBug VALUES (?)");
2661: this .pstmt.setObject(1, null, Types.INTEGER);
2662: this .pstmt.addBatch();
2663: this .pstmt.setInt(1, 1);
2664: this .pstmt.addBatch();
2665: this .pstmt.executeBatch();
2666:
2667: } finally {
2668: this .stmt
2669: .executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
2670: }
2671: }
2672:
2673: /**
2674: * Tests fix for BUG#1511
2675: *
2676: * @throws Exception
2677: * if the quoteid parsing fix in PreparedStatement doesn't work.
2678: */
2679: public void testQuotedIdRecognition() throws Exception {
2680: if (!this .versionMeetsMinimum(4, 1)) {
2681: try {
2682: this .stmt
2683: .executeUpdate("DROP TABLE IF EXISTS testQuotedId");
2684: this .stmt
2685: .executeUpdate("CREATE TABLE testQuotedId (col1 VARCHAR(32))");
2686:
2687: PreparedStatement pStmt = this .conn
2688: .prepareStatement("SELECT * FROM testQuotedId WHERE col1='ABC`DEF' or col1=?");
2689: pStmt.setString(1, "foo");
2690: pStmt.execute();
2691:
2692: this .stmt
2693: .executeUpdate("DROP TABLE IF EXISTS testQuotedId2");
2694: this .stmt
2695: .executeUpdate("CREATE TABLE testQuotedId2 (`Works?` INT)");
2696: pStmt = this .conn
2697: .prepareStatement("INSERT INTO testQuotedId2 (`Works?`) VALUES (?)");
2698: pStmt.setInt(1, 1);
2699: pStmt.executeUpdate();
2700: } finally {
2701: this .stmt
2702: .executeUpdate("DROP TABLE IF EXISTS testQuotedId");
2703: this .stmt
2704: .executeUpdate("DROP TABLE IF EXISTS testQuotedId2");
2705: }
2706: }
2707: }
2708:
2709: /**
2710: * Tests for BUG#9288, parameter index out of range if LIKE, ESCAPE '\'
2711: * present in query.
2712: *
2713: * @throws Exception
2714: * if the test fails.
2715: */
2716: /*
2717: * public void testBug9288() throws Exception { String tableName =
2718: * "testBug9288"; PreparedStatement pStmt = null;
2719: *
2720: * try { createTable(tableName, "(field1 VARCHAR(32), field2 INT)"); pStmt =
2721: * ((com.mysql.jdbc.Connection)this.conn).clientPrepareStatement( "SELECT
2722: * COUNT(1) FROM " + tableName + " WHERE " + "field1 LIKE '%' ESCAPE '\\'
2723: * AND " + "field2 > ?"); pStmt.setInt(1, 0);
2724: *
2725: * this.rs = pStmt.executeQuery(); } finally { if (this.rs != null) {
2726: * this.rs.close(); this.rs = null; }
2727: *
2728: * if (pStmt != null) { pStmt.close(); } } }
2729: */
2730:
2731: /*
2732: * public void testBug10999() throws Exception { if (versionMeetsMinimum(5,
2733: * 0, 5)) {
2734: *
2735: * String tableName = "testBug10999"; String updateTrigName =
2736: * "testBug10999Update"; String insertTrigName = "testBug10999Insert"; try {
2737: * createTable(tableName, "(pkfield INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
2738: * field1 VARCHAR(32))");
2739: *
2740: * try { this.stmt.executeUpdate("DROP TRIGGER " + updateTrigName); } catch
2741: * (SQLException sqlEx) { // ignore for now }
2742: *
2743: * this.stmt.executeUpdate("CREATE TRIGGER " + updateTrigName + " AFTER
2744: * UPDATE ON " + tableName + " FOR EACH ROW " + "BEGIN " + "END");
2745: *
2746: * try { this.stmt.executeUpdate("DROP TRIGGER " + insertTrigName); } catch
2747: * (SQLException sqlEx) { // ignore }
2748: *
2749: * this.stmt.executeUpdate("CREATE TRIGGER " + insertTrigName + " AFTER
2750: * INSERT ON " + tableName + " FOR EACH ROW " + " BEGIN " + "END");
2751: *
2752: * this.conn.setAutoCommit(false);
2753: *
2754: * String updateSQL = "INSERT INTO " + tableName + " (field1) VALUES
2755: * ('abcdefg')"; int rowCount = this.stmt.executeUpdate(updateSQL,
2756: * Statement.RETURN_GENERATED_KEYS);
2757: *
2758: * this.rs = stmt.getGeneratedKeys(); if (rs.next()) {
2759: * System.out.println(rs.getInt(1)); int id = rs.getInt(1); //if
2760: * (log.isDebugEnabled()) // log.debug("Retrieved ID = " + id); } //else {
2761: * //log.error("Can't retrieve ID with getGeneratedKeys."); // Retrieve ID
2762: * using a SELECT statement instead. // querySQL = "SELECT id from tab1
2763: * WHERE ...";
2764: *
2765: * //if (log.isDebugEnabled()) // log.debug(querySQL);
2766: *
2767: * //rs = stmt.executeQuery(querySQL); this.rs =
2768: * this.stmt.executeQuery("SELECT pkfield FROM " + tableName); } finally {
2769: * this.conn.setAutoCommit(true);
2770: *
2771: * try { this.stmt.executeUpdate("DROP TRIGGER IF EXISTS " +
2772: * insertTrigName); } catch (SQLException sqlEx) { // ignore }
2773: *
2774: * try { this.stmt.executeUpdate("DROP TRIGGER IF EXISTS " +
2775: * updateTrigName); } catch (SQLException sqlEx) { // ignore } } } }
2776: */
2777:
2778: /**
2779: * Tests that binary dates/times are encoded/decoded correctly.
2780: *
2781: * @throws Exception
2782: * if the test fails.
2783: *
2784: * @deprecated because we need to use this particular constructor for the
2785: * date class, as Calendar-constructed dates don't pass the
2786: * .equals() test :(
2787: */
2788: public void testServerPrepStmtAndDate() throws Exception {
2789: try {
2790: this .stmt
2791: .executeUpdate("DROP TABLE IF EXISTS testServerPrepStmtAndDate");
2792: this .stmt
2793: .executeUpdate("CREATE TABLE testServerPrepStmtAndDate("
2794: + "`P_ID` int(10) NOT NULL default '0',"
2795: + "`H_ID` int(10) NOT NULL default '0',"
2796: + "`R_ID` int(10) NOT NULL default '0',"
2797: + "`H_Age` int(10) default NULL,"
2798: + "`R_Date` date NOT NULL default '0000-00-00',"
2799: + "`Comments` varchar(255) default NULL,"
2800: + "`Weight` int(10) default NULL,"
2801: + "`HeadGear` char(1) NOT NULL default '',"
2802: + "`FinPos` int(10) default NULL,"
2803: + "`Jock_ID` int(10) default NULL,"
2804: + "`BtnByPrev` double default NULL,"
2805: + "`BtnByWinner` double default NULL,"
2806: + "`Jock_All` int(10) default NULL,"
2807: + "`Draw` int(10) default NULL,"
2808: + "`SF` int(10) default NULL,"
2809: + "`RHR` int(10) default NULL,"
2810: + "`ORating` int(10) default NULL,"
2811: + "`Odds` double default NULL,"
2812: + "`RaceFormPlus` int(10) default NULL,"
2813: + "`PrevPerform` int(10) default NULL,"
2814: + "`TrainerID` int(10) NOT NULL default '0',"
2815: + "`DaysSinceRun` int(10) default NULL,"
2816: + "UNIQUE KEY `P_ID` (`P_ID`),"
2817: + "UNIQUE KEY `R_H_ID` (`R_ID`,`H_ID`),"
2818: + "KEY `R_Date` (`R_Date`),"
2819: + "KEY `H_Age` (`H_Age`),"
2820: + "KEY `TrainerID` (`TrainerID`),"
2821: + "KEY `H_ID` (`H_ID`)" + ")");
2822:
2823: Date dt = new java.sql.Date(102, 1, 2); // Note, this represents the
2824: // date 2002-02-02
2825:
2826: PreparedStatement pStmt2 = this .conn
2827: .prepareStatement("INSERT INTO testServerPrepStmtAndDate (P_ID, R_Date) VALUES (171576, ?)");
2828: pStmt2.setDate(1, dt);
2829: pStmt2.executeUpdate();
2830: pStmt2.close();
2831:
2832: this .rs = this .stmt
2833: .executeQuery("SELECT R_Date FROM testServerPrepStmtAndDate");
2834: this .rs.next();
2835:
2836: System.out.println("Date that was stored (as String) "
2837: + this .rs.getString(1)); // comes back as 2002-02-02
2838:
2839: PreparedStatement pStmt = this .conn
2840: .prepareStatement("Select P_ID,R_Date from testServerPrepStmtAndDate Where R_Date = ? and P_ID = 171576");
2841: pStmt.setDate(1, dt);
2842:
2843: this .rs = pStmt.executeQuery();
2844:
2845: assertTrue(this .rs.next());
2846:
2847: assertEquals("171576", this .rs.getString(1));
2848:
2849: assertEquals(dt, this .rs.getDate(2));
2850: } finally {
2851: this .stmt
2852: .executeUpdate("DROP TABLE IF EXISTS testServerPrepStmtAndDate");
2853: }
2854: }
2855:
2856: public void testServerPrepStmtDeadlock() throws Exception {
2857:
2858: Connection c = getConnectionWithProps((Properties) null);
2859:
2860: Thread testThread1 = new PrepareThread(c);
2861: Thread testThread2 = new PrepareThread(c);
2862: testThread1.start();
2863: testThread2.start();
2864: Thread.sleep(30000);
2865: assertTrue(this .testServerPrepStmtDeadlockCounter >= 10);
2866: }
2867:
2868: /**
2869: * Tests PreparedStatement.setCharacterStream() to ensure it accepts > 4K
2870: * streams
2871: *
2872: * @throws Exception
2873: * if an error occurs.
2874: */
2875: public void testSetCharacterStream() throws Exception {
2876: try {
2877: ((com.mysql.jdbc.Connection) this .conn)
2878: .setTraceProtocol(true);
2879:
2880: this .stmt
2881: .executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
2882: this .stmt
2883: .executeUpdate("CREATE TABLE charStreamRegressTest(field1 text)");
2884:
2885: this .pstmt = this .conn
2886: .prepareStatement("INSERT INTO charStreamRegressTest VALUES (?)");
2887:
2888: // char[] charBuf = new char[16384];
2889: char[] charBuf = new char[32];
2890:
2891: for (int i = 0; i < charBuf.length; i++) {
2892: charBuf[i] = 'A';
2893: }
2894:
2895: CharArrayReader reader = new CharArrayReader(charBuf);
2896:
2897: this .pstmt.setCharacterStream(1, reader, charBuf.length);
2898: this .pstmt.executeUpdate();
2899:
2900: this .rs = this .stmt
2901: .executeQuery("SELECT LENGTH(field1) FROM charStreamRegressTest");
2902:
2903: this .rs.next();
2904:
2905: System.out.println("Character stream length: "
2906: + this .rs.getString(1));
2907:
2908: this .rs = this .stmt
2909: .executeQuery("SELECT field1 FROM charStreamRegressTest");
2910:
2911: this .rs.next();
2912:
2913: String result = this .rs.getString(1);
2914:
2915: assertTrue(result.length() == charBuf.length);
2916:
2917: this .stmt.execute("TRUNCATE TABLE charStreamRegressTest");
2918:
2919: // Test that EOF is not thrown
2920: reader = new CharArrayReader(charBuf);
2921: this .pstmt.clearParameters();
2922: this .pstmt.setCharacterStream(1, reader, charBuf.length);
2923: this .pstmt.executeUpdate();
2924:
2925: this .rs = this .stmt
2926: .executeQuery("SELECT LENGTH(field1) FROM charStreamRegressTest");
2927:
2928: this .rs.next();
2929:
2930: System.out.println("Character stream length: "
2931: + this .rs.getString(1));
2932:
2933: this .rs = this .stmt
2934: .executeQuery("SELECT field1 FROM charStreamRegressTest");
2935:
2936: this .rs.next();
2937:
2938: result = this .rs.getString(1);
2939:
2940: assertTrue("Retrieved value of length " + result.length()
2941: + " != length of inserted value " + charBuf.length,
2942: result.length() == charBuf.length);
2943:
2944: // Test single quotes inside identifers
2945: this .stmt
2946: .executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
2947: this .stmt
2948: .executeUpdate("CREATE TABLE `charStream'RegressTest`(field1 text)");
2949:
2950: this .pstmt = this .conn
2951: .prepareStatement("INSERT INTO `charStream'RegressTest` VALUES (?)");
2952:
2953: reader = new CharArrayReader(charBuf);
2954: this .pstmt.setCharacterStream(1, reader,
2955: (charBuf.length * 2));
2956: this .pstmt.executeUpdate();
2957:
2958: this .rs = this .stmt
2959: .executeQuery("SELECT field1 FROM `charStream'RegressTest`");
2960:
2961: this .rs.next();
2962:
2963: result = this .rs.getString(1);
2964:
2965: assertTrue("Retrieved value of length " + result.length()
2966: + " != length of inserted value " + charBuf.length,
2967: result.length() == charBuf.length);
2968: } finally {
2969: ((com.mysql.jdbc.Connection) this .conn)
2970: .setTraceProtocol(false);
2971:
2972: if (this .rs != null) {
2973: try {
2974: this .rs.close();
2975: } catch (Exception ex) {
2976: // ignore
2977: }
2978:
2979: this .rs = null;
2980: }
2981:
2982: this .stmt
2983: .executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
2984: this .stmt
2985: .executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
2986: }
2987: }
2988:
2989: /**
2990: * Tests a bug where Statement.setFetchSize() does not work for values other
2991: * than 0 or Integer.MIN_VALUE
2992: *
2993: * @throws Exception
2994: * if any errors occur
2995: */
2996: public void testSetFetchSize() throws Exception {
2997: int oldFetchSize = this .stmt.getFetchSize();
2998:
2999: try {
3000: this .stmt.setFetchSize(10);
3001: } finally {
3002: this .stmt.setFetchSize(oldFetchSize);
3003: }
3004: }
3005:
3006: /**
3007: * Tests fix for BUG#907
3008: *
3009: * @throws Exception
3010: * if an error occurs
3011: */
3012: public void testSetMaxRows() throws Exception {
3013: Statement maxRowsStmt = null;
3014:
3015: try {
3016: maxRowsStmt = this .conn.createStatement();
3017: maxRowsStmt.setMaxRows(1);
3018: maxRowsStmt.executeQuery("SELECT 1");
3019: } finally {
3020: if (maxRowsStmt != null) {
3021: maxRowsStmt.close();
3022: }
3023: }
3024: }
3025:
3026: /**
3027: * Tests for timestamp NPEs occuring in binary-format timestamps.
3028: *
3029: * @throws Exception
3030: * DOCUMENT ME!
3031: *
3032: * @deprecated yes, we know we are using deprecated methods here :)
3033: */
3034: public void testTimestampNPE() throws Exception {
3035: try {
3036: Timestamp ts = new Timestamp(System.currentTimeMillis());
3037:
3038: this .stmt
3039: .executeUpdate("DROP TABLE IF EXISTS testTimestampNPE");
3040: this .stmt
3041: .executeUpdate("CREATE TABLE testTimestampNPE (field1 TIMESTAMP)");
3042:
3043: this .pstmt = this .conn
3044: .prepareStatement("INSERT INTO testTimestampNPE VALUES (?)");
3045: this .pstmt.setTimestamp(1, ts);
3046: this .pstmt.executeUpdate();
3047:
3048: this .pstmt = this .conn
3049: .prepareStatement("SELECT field1 FROM testTimestampNPE");
3050:
3051: this .rs = this .pstmt.executeQuery();
3052:
3053: this .rs.next();
3054:
3055: System.out.println(this .rs.getString(1));
3056:
3057: this .rs.getDate(1);
3058:
3059: Timestamp rTs = this .rs.getTimestamp(1);
3060: assertTrue("Retrieved year of " + rTs.getYear()
3061: + " does not match " + ts.getYear(),
3062: rTs.getYear() == ts.getYear());
3063: assertTrue("Retrieved month of " + rTs.getMonth()
3064: + " does not match " + ts.getMonth(), rTs
3065: .getMonth() == ts.getMonth());
3066: assertTrue("Retrieved date of " + rTs.getDate()
3067: + " does not match " + ts.getDate(),
3068: rTs.getDate() == ts.getDate());
3069: } finally {
3070: }
3071: }
3072:
3073: public void testTruncationWithChar() throws Exception {
3074: try {
3075: this .stmt
3076: .executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
3077: this .stmt
3078: .executeUpdate("CREATE TABLE testTruncationWithChar (field1 char(2))");
3079:
3080: this .pstmt = this .conn
3081: .prepareStatement("INSERT INTO testTruncationWithChar VALUES (?)");
3082: this .pstmt.setString(1, "00");
3083: this .pstmt.executeUpdate();
3084: } finally {
3085: this .stmt
3086: .executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
3087: }
3088: }
3089:
3090: /**
3091: * Tests fix for updatable streams being supported in updatable result sets.
3092: *
3093: * @throws Exception
3094: * if the test fails.
3095: */
3096: public void testUpdatableStream() throws Exception {
3097: try {
3098: this .stmt
3099: .executeUpdate("DROP TABLE IF EXISTS updateStreamTest");
3100: this .stmt
3101: .executeUpdate("CREATE TABLE updateStreamTest (keyField INT NOT NULL AUTO_INCREMENT PRIMARY KEY, field1 BLOB)");
3102:
3103: int streamLength = 16385;
3104: byte[] streamData = new byte[streamLength];
3105:
3106: /* create an updatable statement */
3107: Statement updStmt = this .conn.createStatement(
3108: ResultSet.TYPE_SCROLL_INSENSITIVE,
3109: ResultSet.CONCUR_UPDATABLE);
3110:
3111: /* fill the resultset with some values */
3112: ResultSet updRs = updStmt
3113: .executeQuery("SELECT * FROM updateStreamTest");
3114:
3115: /* move to insertRow */
3116: updRs.moveToInsertRow();
3117:
3118: /* update the table */
3119: updRs.updateBinaryStream("field1",
3120: new ByteArrayInputStream(streamData), streamLength);
3121:
3122: updRs.insertRow();
3123: } finally {
3124: this .stmt
3125: .executeUpdate("DROP TABLE IF EXISTS updateStreamTest");
3126: }
3127: }
3128:
3129: /**
3130: * Tests fix for BUG#15383 - PreparedStatement.setObject() serializes
3131: * BigInteger as object, rather than sending as numeric value (and is thus
3132: * not complementary to .getObject() on an UNSIGNED LONG type).
3133: *
3134: * @throws Exception
3135: * if the test fails.
3136: */
3137: public void testBug15383() throws Exception {
3138: createTable(
3139: "testBug15383",
3140: "(id INTEGER UNSIGNED NOT NULL "
3141: + "AUTO_INCREMENT,value BIGINT UNSIGNED NULL DEFAULT 0,PRIMARY "
3142: + "KEY(id))ENGINE=InnoDB;");
3143:
3144: this .stmt
3145: .executeUpdate("INSERT INTO testBug15383(value) VALUES(1)");
3146:
3147: Statement updatableStmt = this .conn
3148: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
3149: ResultSet.CONCUR_UPDATABLE);
3150:
3151: try {
3152: this .rs = updatableStmt
3153: .executeQuery("SELECT * from testBug15383");
3154:
3155: assertTrue(this .rs.next());
3156:
3157: Object bigIntObj = this .rs.getObject("value");
3158: assertEquals("java.math.BigInteger", bigIntObj.getClass()
3159: .getName());
3160:
3161: this .rs.updateObject("value", new BigInteger("3"));
3162: this .rs.updateRow();
3163:
3164: assertEquals("3", this .rs.getString("value"));
3165: } finally {
3166: if (this .rs != null) {
3167: ResultSet toClose = this .rs;
3168: this .rs = null;
3169: toClose.close();
3170: }
3171:
3172: if (updatableStmt != null) {
3173: updatableStmt.close();
3174: }
3175: }
3176: }
3177:
3178: /**
3179: * Tests fix for BUG#17099 - Statement.getGeneratedKeys() throws NPE when no
3180: * query has been processed.
3181: *
3182: * @throws Exception
3183: * if the test fails
3184: */
3185: public void testBug17099() throws Exception {
3186: if (isRunningOnJdk131()) {
3187: return; // test not valid
3188: }
3189:
3190: Statement newStmt = this .conn.createStatement();
3191: assertNotNull(newStmt.getGeneratedKeys());
3192:
3193: PreparedStatement pStmt = this .conn
3194: .prepareStatement("SELECT 1");
3195: assertNotNull(pStmt.getGeneratedKeys());
3196:
3197: if (versionMeetsMinimum(4, 1)) {
3198: pStmt = ((com.mysql.jdbc.Connection) this .conn)
3199: .clientPrepareStatement("SELECT 1");
3200: assertNotNull(pStmt.getGeneratedKeys());
3201: }
3202: }
3203:
3204: /**
3205: * Tests fix for BUG#17587 - clearParameters() on a closed prepared
3206: * statement causes NPE.
3207: *
3208: * @throws Exception
3209: * if the test fails.
3210: */
3211: public void testBug17587() throws Exception {
3212: createTable("testBug17857", "(field1 int)");
3213: PreparedStatement pStmt = null;
3214:
3215: try {
3216: pStmt = this .conn
3217: .prepareStatement("INSERT INTO testBug17857 VALUES (?)");
3218: pStmt.close();
3219: try {
3220: pStmt.clearParameters();
3221: } catch (SQLException sqlEx) {
3222: assertEquals("08003", sqlEx.getSQLState());
3223: }
3224:
3225: pStmt = ((com.mysql.jdbc.Connection) this .conn)
3226: .clientPrepareStatement("INSERT INTO testBug17857 VALUES (?)");
3227: pStmt.close();
3228: try {
3229: pStmt.clearParameters();
3230: } catch (SQLException sqlEx) {
3231: assertEquals("08003", sqlEx.getSQLState());
3232: }
3233:
3234: } finally {
3235: if (pStmt != null) {
3236: pStmt.close();
3237: }
3238: }
3239: }
3240:
3241: /**
3242: * Tests fix for BUG#18740 - Data truncation and getWarnings() only returns
3243: * last warning in set.
3244: *
3245: * @throws Exception
3246: * if the test fails.
3247: */
3248: public void testBug18740() throws Exception {
3249: if (!versionMeetsMinimum(5, 0, 2)) {
3250: createTable("testWarnings", "(field1 smallint(6),"
3251: + "field2 varchar(6),"
3252: + "UNIQUE KEY field1(field1))");
3253:
3254: try {
3255: this .stmt
3256: .executeUpdate("INSERT INTO testWarnings VALUES "
3257: + "(10001, 'data1'),"
3258: + "(10002, 'data2 foo'),"
3259: + "(10003, 'data3'),"
3260: + "(10004999, 'data4'),"
3261: + "(10005, 'data5')");
3262: } catch (SQLException sqlEx) {
3263: String sqlStateToCompare = "01004";
3264:
3265: if (isJdbc4()) {
3266: sqlStateToCompare = "22001";
3267: }
3268:
3269: assertEquals(sqlStateToCompare, sqlEx.getSQLState());
3270: assertEquals(sqlStateToCompare, sqlEx
3271: .getNextException().getSQLState());
3272:
3273: SQLWarning sqlWarn = this .stmt.getWarnings();
3274: assertEquals("01000", sqlWarn.getSQLState());
3275: assertEquals("01000", sqlWarn.getNextWarning()
3276: .getSQLState());
3277: }
3278: }
3279: }
3280:
3281: protected boolean isJdbc4() {
3282: boolean isJdbc4;
3283:
3284: try {
3285: Class.forName("java.sql.Wrapper");
3286: isJdbc4 = true;
3287: } catch (Throwable t) {
3288: isJdbc4 = false;
3289: }
3290:
3291: return isJdbc4;
3292: }
3293:
3294: /**
3295: * Tests fix for BUG#19615, PreparedStatement.setObject(int, Object, int)
3296: * doesn't respect scale of BigDecimals.
3297: *
3298: * @throws Exception
3299: * if the test fails.
3300: */
3301: public void testBug19615() throws Exception {
3302: createTable("testBug19615", "(field1 DECIMAL(19, 12))");
3303:
3304: try {
3305: BigDecimal dec = new BigDecimal("1.234567");
3306:
3307: this .pstmt = this .conn
3308: .prepareStatement("INSERT INTO testBug19615 VALUES (?)");
3309: this .pstmt.setObject(1, dec, Types.DECIMAL);
3310: this .pstmt.executeUpdate();
3311: this .pstmt.close();
3312:
3313: this .rs = this .stmt
3314: .executeQuery("SELECT field1 FROM testBug19615");
3315: this .rs.next();
3316: assertEquals(dec, this .rs.getBigDecimal(1).setScale(6));
3317: this .rs.close();
3318: this .stmt.executeUpdate("TRUNCATE TABLE testBug19615");
3319:
3320: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
3321: .clientPrepareStatement("INSERT INTO testBug19615 VALUES (?)");
3322: this .pstmt.setObject(1, dec, Types.DECIMAL);
3323: this .pstmt.executeUpdate();
3324: this .pstmt.close();
3325:
3326: this .rs = this .stmt
3327: .executeQuery("SELECT field1 FROM testBug19615");
3328: this .rs.next();
3329: assertEquals(dec, this .rs.getBigDecimal(1).setScale(6));
3330: this .rs.close();
3331: } finally {
3332: closeMemberJDBCResources();
3333: }
3334: }
3335:
3336: /**
3337: * Tests fix for BUG#20029 - NPE thrown from executeBatch().
3338: *
3339: * @throws Exception
3340: */
3341: public void testBug20029() throws Exception {
3342: createTable("testBug20029", ("(field1 int)"));
3343:
3344: long initialTimeout = 20; // may need to raise this depending on environment
3345: // we try and do this automatically in this testcase
3346:
3347: for (int i = 0; i < 10; i++) {
3348: final Connection toBeKilledConn = getConnectionWithProps(new Properties());
3349: final long timeout = initialTimeout;
3350: PreparedStatement toBeKilledPstmt = null;
3351:
3352: try {
3353: toBeKilledPstmt = ((com.mysql.jdbc.Connection) toBeKilledConn)
3354: .clientPrepareStatement("INSERT INTO testBug20029 VALUES (?)");
3355:
3356: for (int j = 0; j < 1000; j++) {
3357: toBeKilledPstmt.setInt(1, j);
3358: toBeKilledPstmt.addBatch();
3359: }
3360:
3361: Thread t = new Thread() {
3362: public void run() {
3363: try {
3364: sleep(timeout);
3365: toBeKilledConn.close();
3366: } catch (Throwable t) {
3367:
3368: }
3369: }
3370: };
3371:
3372: t.start();
3373:
3374: try {
3375: if (toBeKilledConn.isClosed()) {
3376: initialTimeout *= 2;
3377: continue;
3378: }
3379:
3380: toBeKilledPstmt.executeBatch();
3381: fail("Should've caught a SQLException for the statement being closed here");
3382: } catch (BatchUpdateException batchEx) {
3383: assertEquals("08003", batchEx.getSQLState());
3384: break;
3385: }
3386:
3387: fail("Connection didn't close while in the middle of PreparedStatement.executeBatch()");
3388: } finally {
3389: if (toBeKilledPstmt != null) {
3390: toBeKilledPstmt.close();
3391: }
3392:
3393: if (toBeKilledConn != null) {
3394: toBeKilledConn.close();
3395: }
3396: }
3397: }
3398: }
3399:
3400: /**
3401: * Fixes BUG#20687 - Can't pool server-side prepared statements, exception
3402: * raised when re-using them.
3403: *
3404: * @throws Exception if the test fails.
3405: */
3406: public void testBug20687() throws Exception {
3407: if (!isRunningOnJdk131() && versionMeetsMinimum(5, 0)) {
3408: createTable("testBug20687", "(field1 int)");
3409: Connection poolingConn = null;
3410:
3411: Properties props = new Properties();
3412: props.setProperty("cachePrepStmts", "true");
3413: props.setProperty("useServerPrepStmts", "true");
3414: PreparedStatement pstmt1 = null;
3415: PreparedStatement pstmt2 = null;
3416:
3417: try {
3418: poolingConn = getConnectionWithProps(props);
3419: pstmt1 = poolingConn
3420: .prepareStatement("SELECT field1 FROM testBug20687");
3421: pstmt1.executeQuery();
3422: pstmt1.close();
3423:
3424: pstmt2 = poolingConn
3425: .prepareStatement("SELECT field1 FROM testBug20687");
3426: pstmt2.executeQuery();
3427: assertTrue(pstmt1 == pstmt2);
3428: pstmt2.close();
3429: } finally {
3430: if (pstmt1 != null) {
3431: pstmt1.close();
3432: }
3433:
3434: if (pstmt2 != null) {
3435: pstmt2.close();
3436: }
3437:
3438: if (poolingConn != null) {
3439: poolingConn.close();
3440: }
3441: }
3442: }
3443: }
3444:
3445: public void testLikeWithBackslashes() throws Exception {
3446: if (!versionMeetsMinimum(5, 0, 0)) {
3447: return;
3448: }
3449:
3450: Connection noBackslashEscapesConn = null;
3451:
3452: try {
3453: Properties props = new Properties();
3454: props.setProperty("sessionVariables",
3455: "sql_mode=NO_BACKSLASH_ESCAPES");
3456:
3457: noBackslashEscapesConn = getConnectionWithProps(props);
3458:
3459: createTable(
3460: "X_TEST",
3461: "(userName varchar(32) not null, ivalue integer, CNAME varchar(255), bvalue CHAR(1), svalue varchar(255), ACTIVE CHAR(1), primary key (userName))");
3462:
3463: String insert_sql = "insert into X_TEST (ivalue, CNAME, bvalue, svalue, ACTIVE, userName) values (?, ?, ?, ?, ?, ?)";
3464:
3465: this .pstmt = noBackslashEscapesConn
3466: .prepareStatement(insert_sql);
3467: this .pstmt.setInt(1, 0);
3468: this .pstmt.setString(2, "c:\\jetson");
3469: this .pstmt.setInt(3, 1);
3470: this .pstmt.setString(4, "c:\\jetson");
3471: this .pstmt.setInt(5, 1);
3472: this .pstmt.setString(6, "c:\\jetson");
3473: this .pstmt.execute();
3474:
3475: String select_sql = "select user0_.userName as userName0_0_, user0_.ivalue as ivalue0_0_, user0_.CNAME as CNAME0_0_, user0_.bvalue as bvalue0_0_, user0_.svalue as svalue0_0_, user0_.ACTIVE as ACTIVE0_0_ from X_TEST user0_ where user0_.userName like ?";
3476: this .pstmt = noBackslashEscapesConn
3477: .prepareStatement(select_sql);
3478: this .pstmt.setString(1, "c:\\j%");
3479: // if we comment out the previous line and uncomment the following, the like clause matches
3480: // stmt.setString(1,"c:\\\\j%");
3481: System.out.println("about to execute query " + select_sql);
3482: this .rs = this .pstmt.executeQuery();
3483: assertTrue(this .rs.next());
3484: } finally {
3485: closeMemberJDBCResources();
3486:
3487: if (noBackslashEscapesConn != null) {
3488: noBackslashEscapesConn.close();
3489: }
3490: }
3491: }
3492:
3493: /**
3494: * Tests fix for BUG#20650 - Statement.cancel() causes NullPointerException
3495: * if underlying connection has been closed due to server failure.
3496: *
3497: * @throws Exception if the test fails.
3498: */
3499: public void testBug20650() throws Exception {
3500: Connection closedConn = null;
3501: Statement cancelStmt = null;
3502:
3503: try {
3504: closedConn = getConnectionWithProps((String) null);
3505: cancelStmt = closedConn.createStatement();
3506:
3507: closedConn.close();
3508:
3509: cancelStmt.cancel();
3510: } finally {
3511: if (cancelStmt != null) {
3512: cancelStmt.close();
3513: }
3514:
3515: if (closedConn != null && !closedConn.isClosed()) {
3516: closedConn.close();
3517: }
3518: }
3519: }
3520:
3521: /**
3522: * Tests fix for BUG#20888 - escape of quotes in client-side prepared
3523: * statements parsing not respected.
3524: *
3525: * @throws Exception if the test fails.
3526: */
3527: public void testBug20888() throws Exception {
3528:
3529: try {
3530: String s = "SELECT 'What do you think about D\\'Artanian''?', \"What do you think about D\\\"Artanian\"\"?\"";
3531: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
3532: .clientPrepareStatement(s);
3533:
3534: this .rs = this .pstmt.executeQuery();
3535: this .rs.next();
3536: assertEquals(this .rs.getString(1),
3537: "What do you think about D'Artanian'?");
3538: assertEquals(this .rs.getString(2),
3539: "What do you think about D\"Artanian\"?");
3540: } finally {
3541: closeMemberJDBCResources();
3542: }
3543: }
3544:
3545: /**
3546: * Tests Bug#21207 - Driver throws NPE when tracing prepared statements that
3547: * have been closed (in asSQL()).
3548: *
3549: * @throws Exception if the test fails
3550: */
3551: public void testBug21207() throws Exception {
3552: try {
3553: this .pstmt = this .conn.prepareStatement("SELECT 1");
3554: this .pstmt.close();
3555: this .pstmt.toString(); // this used to cause an NPE
3556: } finally {
3557: closeMemberJDBCResources();
3558: }
3559: }
3560:
3561: /**
3562: * Tests BUG#21438, server-side PS fails when using jdbcCompliantTruncation.
3563: * If either is set to FALSE (&useServerPrepStmts=false or
3564: * &jdbcCompliantTruncation=false) test succedes.
3565: *
3566: * @throws Exception
3567: * if the test fails.
3568: */
3569:
3570: public void testBug21438() throws Exception {
3571: createTable("testBug21438",
3572: "(t_id int(10), test_date timestamp(30) NOT NULL,primary key t_pk (t_id));");
3573:
3574: assertEquals(
3575: 1,
3576: this .stmt
3577: .executeUpdate("insert into testBug21438 values (1,NOW());"));
3578:
3579: if (this .versionMeetsMinimum(4, 1)) {
3580: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
3581: .serverPrepareStatement("UPDATE testBug21438 SET test_date=ADDDATE(?,INTERVAL 1 YEAR) WHERE t_id=1;");
3582:
3583: try {
3584: Timestamp ts = new Timestamp(System.currentTimeMillis());
3585: ts.setNanos(999999999);
3586:
3587: this .pstmt.setTimestamp(1, ts);
3588:
3589: assertEquals(1, this .pstmt.executeUpdate());
3590:
3591: Timestamp future = (Timestamp) getSingleIndexedValueWithQuery(
3592: 1, "SELECT test_date FROM testBug21438");
3593: assertEquals(future.getYear() - ts.getYear(), 1);
3594:
3595: } finally {
3596: closeMemberJDBCResources();
3597: }
3598: }
3599: }
3600:
3601: /**
3602: * Tests fix for BUG#22359 - Driver was using millis for
3603: * Statement.setQueryTimeout() when spec says argument is
3604: * seconds.
3605: *
3606: * @throws Exception if the test fails.
3607: */
3608: public void testBug22359() throws Exception {
3609: if (versionMeetsMinimum(5, 0)) {
3610: Statement timeoutStmt = null;
3611:
3612: try {
3613: timeoutStmt = this .conn.createStatement();
3614: timeoutStmt.setQueryTimeout(2);
3615:
3616: long begin = System.currentTimeMillis();
3617:
3618: try {
3619: timeoutStmt.execute("SELECT SLEEP(30)");
3620: } catch (MySQLTimeoutException timeoutEx) {
3621: long end = System.currentTimeMillis();
3622:
3623: assertTrue((end - begin) > 1000);
3624: }
3625: } finally {
3626: if (timeoutStmt != null) {
3627: timeoutStmt.close();
3628: }
3629: }
3630: }
3631: }
3632:
3633: /**
3634: * Tests fix for BUG#22290 - Driver issues truncation on write exception when
3635: * it shouldn't (due to sending big decimal incorrectly to server with
3636: * server-side prepared statement).
3637: *
3638: * @throws Exception if the test fails.
3639: */
3640: public void testBug22290() throws Exception {
3641: if (!versionMeetsMinimum(5, 0)) {
3642: return;
3643: }
3644:
3645: createTable(
3646: "testbug22290",
3647: "(`id` int(11) NOT NULL default '1',`cost` decimal(10,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
3648: assertEquals(
3649: this .stmt
3650: .executeUpdate("INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,'1.00')"),
3651: 1);
3652:
3653: Connection configuredConn = null;
3654:
3655: try {
3656: Properties props = new Properties();
3657: props.setProperty("sessionVariables",
3658: "sql_mode='STRICT_TRANS_TABLES'");
3659:
3660: configuredConn = getConnectionWithProps(props);
3661:
3662: this .pstmt = configuredConn
3663: .prepareStatement("update testbug22290 set cost = cost + ? where id = 1");
3664: this .pstmt.setBigDecimal(1, new BigDecimal("1.11"));
3665: assertEquals(this .pstmt.executeUpdate(), 1);
3666:
3667: assertEquals(
3668: this .stmt
3669: .executeUpdate("UPDATE testbug22290 SET cost='1.00'"),
3670: 1);
3671: this .pstmt = ((com.mysql.jdbc.Connection) configuredConn)
3672: .clientPrepareStatement("update testbug22290 set cost = cost + ? where id = 1");
3673: this .pstmt.setBigDecimal(1, new BigDecimal("1.11"));
3674: assertEquals(this .pstmt.executeUpdate(), 1);
3675: } finally {
3676: closeMemberJDBCResources();
3677:
3678: if (configuredConn != null) {
3679: configuredConn.close();
3680: }
3681: }
3682: }
3683:
3684: public void testClientPreparedSetBoolean() throws Exception {
3685: try {
3686: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
3687: .clientPrepareStatement("SELECT ?");
3688: this .pstmt.setBoolean(1, false);
3689: assertEquals("SELECT 0", this .pstmt.toString().substring(
3690: this .pstmt.toString().indexOf("SELECT")));
3691: this .pstmt.setBoolean(1, true);
3692: assertEquals("SELECT 1", this .pstmt.toString().substring(
3693: this .pstmt.toString().indexOf("SELECT")));
3694: } finally {
3695: closeMemberJDBCResources();
3696: }
3697: }
3698:
3699: /**
3700: * Tests fix for BUG#24360 .setFetchSize() breaks prepared
3701: * SHOW and other commands.
3702: *
3703: * @throws Exception if the test fails
3704: */
3705: public void testBug24360() throws Exception {
3706: if (!versionMeetsMinimum(5, 0)) {
3707: return;
3708: }
3709:
3710: Connection c = null;
3711:
3712: Properties props = new Properties();
3713: props.setProperty("useServerPrepStmts", "true");
3714:
3715: try {
3716: c = getConnectionWithProps(props);
3717:
3718: this .pstmt = c.prepareStatement("SHOW PROCESSLIST");
3719: this .pstmt.setFetchSize(5);
3720: this .pstmt.execute();
3721: } finally {
3722: closeMemberJDBCResources();
3723:
3724: if (c != null) {
3725: c.close();
3726: }
3727: }
3728: }
3729:
3730: /**
3731: * Tests fix for BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
3732: * statements gives different behavior than when using client-side prepared
3733: * statements. (this is now fixed if moving from server-side prepared statements
3734: * to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
3735: * "true", as the driver can't tell if this is a new deployment that never used
3736: * server-side prepared statements, or if it is an existing deployment that is
3737: * switching to client-side prepared statements from server-side prepared statements.
3738: *
3739: * @throws Exception if the test fails
3740: */
3741: public void testBug24344() throws Exception {
3742:
3743: if (!versionMeetsMinimum(4, 1)) {
3744: return; // need SSPS
3745: }
3746:
3747: super
3748: .createTable("testBug24344",
3749: "(i INT AUTO_INCREMENT, t1 DATETIME, PRIMARY KEY (i)) ENGINE = MyISAM");
3750:
3751: Connection conn2 = null;
3752:
3753: try {
3754: Properties props = new Properties();
3755: props.setProperty("useServerPrepStmts", "true");
3756: props.setProperty("useJDBCCompliantTimezoneShift", "true");
3757: conn2 = super .getConnectionWithProps(props);
3758: this .pstmt = conn2
3759: .prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
3760: Calendar c = Calendar.getInstance();
3761: this .pstmt.setTimestamp(1, new Timestamp(c.getTime()
3762: .getTime()));
3763: this .pstmt.execute();
3764: this .pstmt.close();
3765: conn2.close();
3766:
3767: props.setProperty("useServerPrepStmts", "false");
3768: props.setProperty("useJDBCCompliantTimezoneShift", "true");
3769: props.setProperty("useSSPSCompatibleTimezoneShift", "true");
3770:
3771: conn2 = super .getConnectionWithProps(props);
3772: this .pstmt = conn2
3773: .prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
3774: this .pstmt.setTimestamp(1, new Timestamp(c.getTime()
3775: .getTime()));
3776: this .pstmt.execute();
3777: this .pstmt.close();
3778: conn2.close();
3779:
3780: props.setProperty("useServerPrepStmts", "false");
3781: props.setProperty("useJDBCCompliantTimezoneShift", "false");
3782: props
3783: .setProperty("useSSPSCompatibleTimezoneShift",
3784: "false");
3785: conn2 = super .getConnectionWithProps(props);
3786: this .pstmt = conn2
3787: .prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
3788: this .pstmt.setTimestamp(1, new Timestamp(c.getTime()
3789: .getTime()));
3790: this .pstmt.execute();
3791: this .pstmt.close();
3792:
3793: Statement s = conn2.createStatement();
3794: this .rs = s
3795: .executeQuery("SELECT t1 FROM testBug24344 ORDER BY i ASC");
3796:
3797: Timestamp[] dates = new Timestamp[3];
3798:
3799: int i = 0;
3800:
3801: while (rs.next()) {
3802: dates[i++] = rs.getTimestamp(1);
3803: }
3804:
3805: assertEquals("Number of rows should be 3.", 3, i);
3806: assertEquals(dates[0], dates[1]);
3807: assertTrue(!dates[1].equals(dates[2]));
3808: } finally {
3809: closeMemberJDBCResources();
3810:
3811: if (conn2 != null) {
3812: conn2.close();
3813: }
3814: }
3815: }
3816:
3817: /**
3818: * Tests fix for BUG#25073 - rewriting batched statements leaks internal statement
3819: * instances, and causes a memory leak.
3820: *
3821: * @throws Exception if the test fails.
3822: */
3823: public void testBug25073() throws Exception {
3824: if (isRunningOnJdk131()) {
3825: return;
3826: }
3827:
3828: Properties props = new Properties();
3829: props.setProperty("rewriteBatchedStatements", "true");
3830: Connection multiConn = getConnectionWithProps(props);
3831: createTable("testBug25073",
3832: "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3833: Statement multiStmt = multiConn.createStatement();
3834: multiStmt
3835: .addBatch("INSERT INTO testBug25073(field1) VALUES (1)");
3836: multiStmt
3837: .addBatch("INSERT INTO testBug25073(field1) VALUES (2)");
3838: multiStmt
3839: .addBatch("INSERT INTO testBug25073(field1) VALUES (3)");
3840: multiStmt
3841: .addBatch("INSERT INTO testBug25073(field1) VALUES (4)");
3842: multiStmt
3843: .addBatch("UPDATE testBug25073 SET field1=5 WHERE field1=1");
3844: multiStmt
3845: .addBatch("UPDATE testBug25073 SET field1=6 WHERE field1=2 OR field1=3");
3846:
3847: int beforeOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3848: .getActiveStatementCount();
3849:
3850: multiStmt.executeBatch();
3851:
3852: int afterOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3853: .getActiveStatementCount();
3854:
3855: assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3856:
3857: createTable("testBug25073",
3858: "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3859: props.clear();
3860: props.setProperty("rewriteBatchedStatements", "true");
3861: props
3862: .setProperty("sessionVariables",
3863: "max_allowed_packet=1024");
3864: multiConn = getConnectionWithProps(props);
3865: multiStmt = multiConn.createStatement();
3866:
3867: for (int i = 0; i < 1000; i++) {
3868: multiStmt
3869: .addBatch("INSERT INTO testBug25073(field1) VALUES ("
3870: + i + ")");
3871: }
3872:
3873: beforeOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3874: .getActiveStatementCount();
3875:
3876: multiStmt.executeBatch();
3877:
3878: afterOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3879: .getActiveStatementCount();
3880:
3881: assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3882:
3883: createTable("testBug25073",
3884: "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3885:
3886: props.clear();
3887: props.setProperty("useServerPrepStmts", "false");
3888: props.setProperty("rewriteBatchedStatements", "true");
3889: multiConn = getConnectionWithProps(props);
3890: PreparedStatement pStmt = multiConn.prepareStatement(
3891: "INSERT INTO testBug25073(field1) VALUES (?)",
3892: Statement.RETURN_GENERATED_KEYS);
3893:
3894: for (int i = 0; i < 1000; i++) {
3895: pStmt.setInt(1, i);
3896: pStmt.addBatch();
3897: }
3898:
3899: beforeOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3900: .getActiveStatementCount();
3901:
3902: pStmt.executeBatch();
3903:
3904: afterOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3905: .getActiveStatementCount();
3906:
3907: assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3908:
3909: createTable("testBug25073",
3910: "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3911: props.setProperty("useServerPrepStmts", "false");
3912: props.setProperty("rewriteBatchedStatements", "true");
3913: props
3914: .setProperty("sessionVariables",
3915: "max_allowed_packet=1024");
3916: multiConn = getConnectionWithProps(props);
3917: pStmt = multiConn.prepareStatement(
3918: "INSERT INTO testBug25073(field1) VALUES (?)",
3919: Statement.RETURN_GENERATED_KEYS);
3920:
3921: for (int i = 0; i < 1000; i++) {
3922: pStmt.setInt(1, i);
3923: pStmt.addBatch();
3924: }
3925:
3926: beforeOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3927: .getActiveStatementCount();
3928:
3929: pStmt.executeBatch();
3930:
3931: afterOpenStatementCount = ((com.mysql.jdbc.Connection) multiConn)
3932: .getActiveStatementCount();
3933:
3934: assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3935: }
3936:
3937: /**
3938: * Tests fix for BUG#25009 - Results from updates not handled correctly in multi-statement
3939: * queries.
3940: *
3941: * @throws Exception if the test fails.
3942: */
3943: public void testBug25009() throws Exception {
3944: if (!versionMeetsMinimum(4, 1)) {
3945: return;
3946: }
3947:
3948: Properties props = new Properties();
3949: props.setProperty("allowMultiQueries", "true");
3950:
3951: Connection multiConn = getConnectionWithProps(props);
3952: createTable("testBug25009", "(field1 INT)");
3953:
3954: try {
3955: Statement multiStmt = multiConn.createStatement();
3956: multiStmt
3957: .execute("SELECT 1;SET @a=1; SET @b=2; SET @c=3; INSERT INTO testBug25009 VALUES (1)");
3958:
3959: assertEquals(-1, multiStmt.getUpdateCount());
3960:
3961: this .rs = multiStmt.getResultSet();
3962: assertTrue(this .rs.next());
3963: assertEquals(multiStmt.getMoreResults(), false);
3964:
3965: for (int i = 0; i < 3; i++) {
3966: assertEquals(0, multiStmt.getUpdateCount());
3967: assertEquals(multiStmt.getMoreResults(), false);
3968: }
3969:
3970: assertEquals(1, multiStmt.getUpdateCount());
3971:
3972: this .rs = multiStmt
3973: .executeQuery("SELECT field1 FROM testBug25009");
3974: assertTrue(this .rs.next());
3975: assertEquals(1, this .rs.getInt(1));
3976:
3977: } finally {
3978: closeMemberJDBCResources();
3979:
3980: if (multiConn != null) {
3981: multiConn.close();
3982: }
3983: }
3984: }
3985:
3986: /**
3987: * Tests fix for BUG#25025 - Client-side prepared statement parser gets confused by
3988: * in-line (slash-star) comments and therefore can't rewrite batched statements or
3989: * reliably detect type of statements when they're used.
3990: *
3991: * @throws Exception if the test fails.
3992: */
3993: public void testBug25025() throws Exception {
3994:
3995: Connection multiConn = null;
3996:
3997: createTable("testBug25025", "(field1 INT)");
3998:
3999: try {
4000: Properties props = new Properties();
4001: props.setProperty("rewriteBatchedStatements", "true");
4002: props.setProperty("useServerPrepStmts", "false");
4003:
4004: multiConn = getConnectionWithProps(props);
4005:
4006: this .pstmt = multiConn
4007: .prepareStatement("/* insert foo.bar.baz INSERT INTO foo VALUES (?,?,?,?) to trick parser */ INSERT into testBug25025 VALUES (?)");
4008: this .pstmt.setInt(1, 1);
4009: this .pstmt.addBatch();
4010: this .pstmt.setInt(1, 2);
4011: this .pstmt.addBatch();
4012: this .pstmt.setInt(1, 3);
4013: this .pstmt.addBatch();
4014:
4015: int[] counts = this .pstmt.executeBatch();
4016:
4017: assertEquals(3, counts.length);
4018: assertEquals(1, counts[0]);
4019: assertEquals(1, counts[1]);
4020: assertEquals(1, counts[2]);
4021: assertEquals(true,
4022: ((com.mysql.jdbc.PreparedStatement) this .pstmt)
4023: .canRewriteAsMultivalueInsertStatement());
4024: } finally {
4025: closeMemberJDBCResources();
4026:
4027: if (multiConn != null) {
4028: multiConn.close();
4029: }
4030: }
4031: }
4032:
4033: public void testBustedGGKWithPSExecute() throws Exception {
4034: createTable("sequence",
4035: "(sequence_name VARCHAR(255) NOT NULL PRIMARY KEY, next_val BIGINT NOT NULL)");
4036:
4037: // Populate with the initial value
4038: stmt
4039: .executeUpdate("INSERT INTO sequence VALUES ('test-sequence', 1234)");
4040:
4041: // Atomic operation to increment and return next value
4042: PreparedStatement pStmt = null;
4043:
4044: try {
4045: pStmt = this .conn
4046: .prepareStatement(
4047: "UPDATE sequence SET next_val=LAST_INSERT_ID(next_val + ?) WHERE sequence_name = ?",
4048: Statement.RETURN_GENERATED_KEYS);
4049:
4050: pStmt.setInt(1, 4);
4051: pStmt.setString(2, "test-sequence");
4052: pStmt.execute();
4053:
4054: this .rs = pStmt.getGeneratedKeys();
4055: this .rs.next();
4056: assertEquals(1238, this .rs.getLong(1));
4057: } finally {
4058: closeMemberJDBCResources();
4059:
4060: if (pStmt != null) {
4061: pStmt.close();
4062: }
4063: }
4064: }
4065:
4066: /**
4067: * Tests fix for BUG#28469 - PreparedStatement.getMetaData()
4068: * for statements containing leading one-line comments
4069: * is not returned correctly.
4070: *
4071: * As part of this fix, we also overhauled detection of
4072: * DML for executeQuery() and SELECTs for executeUpdate() in
4073: * plain and prepared statements to be aware of the same
4074: * types of comments.
4075: *
4076: * @throws Exception
4077: */
4078: public void testBug28469() throws Exception {
4079: PreparedStatement commentStmt = null;
4080:
4081: try {
4082: String[] statementsToTest = { "-- COMMENT\nSELECT 1",
4083: "# COMMENT\nSELECT 1", "/* comment */ SELECT 1" };
4084:
4085: for (int i = 0; i < statementsToTest.length; i++) {
4086: commentStmt = this .conn
4087: .prepareStatement(statementsToTest[i]);
4088:
4089: assertNotNull(commentStmt.getMetaData());
4090:
4091: try {
4092: commentStmt.executeUpdate();
4093: fail("Should not be able to call executeUpdate() on a SELECT statement!");
4094: } catch (SQLException sqlEx) {
4095: // expected
4096: }
4097:
4098: this .rs = commentStmt.executeQuery();
4099: this .rs.next();
4100: assertEquals(1, this .rs.getInt(1));
4101: }
4102:
4103: createTable("testBug28469", "(field1 INT)");
4104:
4105: String[] updatesToTest = {
4106: "-- COMMENT\nUPDATE testBug28469 SET field1 = 2",
4107: "# COMMENT\nUPDATE testBug28469 SET field1 = 2",
4108: "/* comment */ UPDATE testBug28469 SET field1 = 2" };
4109:
4110: for (int i = 0; i < updatesToTest.length; i++) {
4111: commentStmt = this .conn
4112: .prepareStatement(updatesToTest[i]);
4113:
4114: assertNull(commentStmt.getMetaData());
4115:
4116: try {
4117: commentStmt.executeQuery();
4118: fail("Should not be able to call executeQuery() on a SELECT statement!");
4119: } catch (SQLException sqlEx) {
4120: // expected
4121: }
4122:
4123: try {
4124: this .stmt.executeQuery(updatesToTest[i]);
4125: fail("Should not be able to call executeQuery() on a SELECT statement!");
4126: } catch (SQLException sqlEx) {
4127: // expected
4128: }
4129: }
4130: } finally {
4131: closeMemberJDBCResources();
4132:
4133: if (commentStmt != null) {
4134: commentStmt.close();
4135: }
4136: }
4137: }
4138:
4139: /**
4140: * Tests error with slash-star comment at EOL
4141: *
4142: * @throws Exception if the test fails.
4143: */
4144: public void testCommentParsing() throws Exception {
4145: createTable("PERSON", "(NAME VARCHAR(32), PERID VARCHAR(32))");
4146:
4147: try {
4148: this .pstmt = this .conn
4149: .prepareStatement("SELECT NAME AS name2749_0_, PERID AS perid2749_0_ FROM PERSON WHERE PERID=? /*FOR UPDATE*/");
4150: } finally {
4151: closeMemberJDBCResources();
4152: }
4153: }
4154:
4155: /**
4156: * Tests fix for BUG#28851 - parser in client-side prepared statements
4157: * eats character following '/' if it's not a multi-line comment.
4158: *
4159: * @throws Exception if the test fails.
4160: */
4161: public void testBug28851() throws Exception {
4162:
4163: try {
4164: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
4165: .clientPrepareStatement("SELECT 1/?");
4166: this .pstmt.setInt(1, 1);
4167: this .rs = this .pstmt.executeQuery();
4168:
4169: assertTrue(this .rs.next());
4170:
4171: assertEquals(1, this .rs.getInt(1));
4172: } finally {
4173: closeMemberJDBCResources();
4174: }
4175: }
4176:
4177: /**
4178: * Tests fix for BUG#28596 - parser in client-side prepared statements
4179: * runs to end of statement, rather than end-of-line for '#' comments.
4180: *
4181: * Also added support for '--' single-line comments
4182: *
4183: * @throws Exception if the test fails.
4184: */
4185: public void testBug28596() throws Exception {
4186: String query = "SELECT #\n" + "?, #\n" + "? #?\r\n"
4187: + ",-- abcdefg \n" + "?";
4188:
4189: try {
4190: this .pstmt = ((com.mysql.jdbc.Connection) this .conn)
4191: .clientPrepareStatement(query);
4192: this .pstmt.setInt(1, 1);
4193: this .pstmt.setInt(2, 2);
4194: this .pstmt.setInt(3, 3);
4195:
4196: assertEquals(3, this .pstmt.getParameterMetaData()
4197: .getParameterCount());
4198: this .rs = this .pstmt.executeQuery();
4199:
4200: assertTrue(this .rs.next());
4201:
4202: assertEquals(1, this .rs.getInt(1));
4203: assertEquals(2, this .rs.getInt(2));
4204: assertEquals(3, this .rs.getInt(3));
4205: } finally {
4206: closeMemberJDBCResources();
4207: }
4208: }
4209:
4210: /**
4211: * Tests fix for BUG#30550 - executeBatch() on an empty
4212: * batch when there are no elements in the batch causes a
4213: * divide-by-zero error when rewriting is enabled.
4214: *
4215: * @throws Exception if the test fails
4216: */
4217: public void testBug30550() throws Exception {
4218: createTable("testBug30550", "(field1 int)");
4219:
4220: Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
4221: PreparedStatement batchPStmt = null;
4222: Statement batchStmt = null;
4223:
4224: try {
4225: batchStmt = rewriteConn.createStatement();
4226: assertEquals(0, batchStmt.executeBatch().length);
4227:
4228: batchStmt.addBatch("INSERT INTO testBug30550 VALUES (1)");
4229: int[] counts = batchStmt.executeBatch();
4230: assertEquals(1, counts.length);
4231: assertEquals(1, counts[0]);
4232: assertEquals(0, batchStmt.executeBatch().length);
4233:
4234: batchPStmt = rewriteConn
4235: .prepareStatement("INSERT INTO testBug30550 VALUES (?)");
4236: batchPStmt.setInt(1, 1);
4237: assertEquals(0, batchPStmt.executeBatch().length);
4238: batchPStmt.addBatch();
4239: counts = batchPStmt.executeBatch();
4240: assertEquals(1, counts.length);
4241: assertEquals(1, counts[0]);
4242: assertEquals(0, batchPStmt.executeBatch().length);
4243: } finally {
4244: if (batchPStmt != null) {
4245: batchPStmt.close();
4246: }
4247:
4248: if (batchStmt != null) {
4249: batchStmt.close();
4250: }
4251: if (rewriteConn != null) {
4252: rewriteConn.close();
4253: }
4254: }
4255: }
4256: }
|