Connector/J commit: r6127 - branches/branch_5_0/connector-j branches/branch_5_0/connector-j/src/com/mysql/jdbc branches/branch_5_0/connector-j/src/tes...

[mmatthews]

Modified:
   branches/branch_5_0/connector-j/CHANGES
   branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
   branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java
   branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java
   trunk/connector-j/CHANGES
   trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
   trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java
   trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java
Log:
Fixed BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
	  statements gives different behavior than when using client-side prepared
	  statements. (this is now fixed if moving from server-side prepared statements
	  to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
	  true", as the driver can't tell if this is a new deployment that never used 
	  server-side prepared statements, or if it is an existing deployment that is
	  switching to client-side prepared statements from server-side prepared statements.
	   

Modified: branches/branch_5_0/connector-j/CHANGES
===================================================================
--- branches/branch_5_0/connector-j/CHANGES	2006-12-06 01:01:36 UTC (rev 6126)
+++ branches/branch_5_0/connector-j/CHANGES	2006-12-06 21:48:40 UTC (rev 6127)
@@ -16,7 +16,16 @@
       columns not referenced in them.
       
     - Fixed BUG#24360 .setFetchSize() breaks prepared SHOW and other commands.
-      
+
+          
+    - Fixed BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
+	  statements gives different behavior than when using client-side prepared
+	  statements. (this is now fixed if moving from server-side prepared statements
+	  to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
+	  true", as the driver can't tell if this is a new deployment that never used 
+	  server-side prepared statements, or if it is an existing deployment that is
+	  switching to client-side prepared statements from server-side prepared statements.
+	         
 10-20-06 - Version 5.0.4
 
     - Fixed BUG#21379 - column names don't match metadata in cases 

Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
===================================================================
---
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java	2006-12-06
01:01:36 UTC (rev 6126)
+++
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java	2006-12-06
21:48:40 UTC (rev 6127)
@@ -1354,6 +1354,14 @@
 			"Use SSL when communicating with the server (true/false), defaults to 'false'",
 			"3.0.2", SECURITY_CATEGORY, 2);
 
+	private BooleanConnectionProperty useSSPSCompatibleTimezoneShift = new
BooleanConnectionProperty(
+			"useSSPSCompatibleTimezoneShift",
+			false,
+			"If migrating from an environment that was using server-side prepared statements, and
the"
+			+ " configuration property \"useJDBCCompliantTimeZoneShift\" set to \"true\", use
compatible behavior"
+			+ " when not using server-side prepared statements when sending TIMESTAMP values to
the MySQL server.",
+			"5.0.5", MISC_CATEGORY, Integer.MIN_VALUE);
+	
 	private BooleanConnectionProperty useStreamLengthsInPrepStmts = new
BooleanConnectionProperty(
 			"useStreamLengthsInPrepStmts",
 			true,
@@ -3717,4 +3725,12 @@
 	public void setUseOldAliasMetadataBehavior(boolean flag) {
 		this.useOldAliasMetadataBehavior.setValue(flag);
 	}
+
+	public boolean getUseSSPSCompatibleTimezoneShift() {
+		return this.useSSPSCompatibleTimezoneShift.getValueAsBoolean();
+	}
+
+	public void setUseSSPSCompatibleTimezoneShift(boolean flag) {
+		this.useSSPSCompatibleTimezoneShift.setValue(flag);
+	}
 }

Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java
===================================================================
--- branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java	2006-12-06
01:01:36 UTC (rev 6126)
+++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java	2006-12-06
21:48:40 UTC (rev 6127)
@@ -3543,15 +3543,95 @@
 					.getServerTimezoneTZ(), rollForward);
 			}
 
-			if (this.tsdf == null) {
-				this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$
+			
+			if (this.connection.getUseSSPSCompatibleTimezoneShift()) {
+				doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar);
+			} else {
+				
+				if (this.tsdf == null) {
+					this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US);
//$NON-NLS-1$
+				}
+				
+				timestampString = this.tsdf.format(x);
+
+				setInternal(parameterIndex, timestampString); // SimpleDateFormat is not
+															  // thread-safe
 			}
+		}
+	}
 
-			timestampString = this.tsdf.format(x);
+	private void doSSPSCompatibleTimezoneShift(int parameterIndex, Timestamp x, Calendar
sessionCalendar) throws SQLException {
+		Calendar sessionCalendar2 = (this.connection
+				.getUseJDBCCompliantTimezoneShift()) ? this.connection
+				.getUtcCalendar()
+				: getCalendarInstanceForSessionOrNew();
 
-			setInternal(parameterIndex, timestampString); // SimpleDateFormat
-			// is not
-			// thread-safe
+		synchronized (sessionCalendar2) {
+			java.util.Date oldTime = sessionCalendar2.getTime();
+
+			try {
+				sessionCalendar2.setTime(x);
+
+				int year = sessionCalendar2.get(Calendar.YEAR);
+				int month = sessionCalendar2.get(Calendar.MONTH) + 1;
+				int date = sessionCalendar2.get(Calendar.DAY_OF_MONTH);
+
+				int hour = sessionCalendar2.get(Calendar.HOUR_OF_DAY);
+				int minute = sessionCalendar2.get(Calendar.MINUTE);
+				int seconds = sessionCalendar2.get(Calendar.SECOND);
+
+				StringBuffer tsBuf = new StringBuffer();
+
+				tsBuf.append('\'');
+				tsBuf.append(year);
+
+				tsBuf.append("-");
+
+				if (month < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(month);
+
+				tsBuf.append('-');
+
+				if (date < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(date);
+
+				tsBuf.append(' ');
+
+				if (hour < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(hour);
+
+				tsBuf.append(':');
+
+				if (minute < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(minute);
+
+				tsBuf.append(':');
+
+				if (seconds < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(seconds);
+
+				tsBuf.append('\'');
+
+				setInternal(parameterIndex, tsBuf.toString());
+
+			} finally {
+				sessionCalendar.setTime(oldTime);
+			}
 		}
 	}
 

Modified:
branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java
===================================================================
---
branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java	2006-12-06
01:01:36 UTC (rev 6126)
+++
branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java	2006-12-06
21:48:40 UTC (rev 6127)
@@ -3525,4 +3525,77 @@
 			}
 		}
 	}
+	
+	/**
+	 * Tests fix for BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
+	 * statements gives different behavior than when using client-side prepared
+	 * statements. (this is now fixed if moving from server-side prepared statements
+	 * to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
+	 * "true", as the driver can't tell if this is a new deployment that never used 
+	 * server-side prepared statements, or if it is an existing deployment that is
+	 * switching to client-side prepared statements from server-side prepared statements.
+	 * 
+	 * @throws Exception if the test fails
+	 */
+	public void testBug24344() throws Exception {
+		
+		super.createTable("testBug24344", 
+				"(i INT AUTO_INCREMENT, t1 DATETIME, PRIMARY KEY (i)) ENGINE = MyISAM");
+		
+		Connection conn2 = null;
+		
+		try {
+			Properties props = new Properties();
+			props.setProperty("useServerPrepStmts", "true");
+			props.setProperty("useJDBCCompliantTimezoneShift", "true");
+			conn2 = super.getConnectionWithProps(props);
+			this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
+			Calendar c = Calendar.getInstance();
+			this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
+			this.pstmt.execute();
+			this.pstmt.close();
+			conn2.close();
+			
+			props.setProperty("useServerPrepStmts", "false");
+			props.setProperty("useJDBCCompliantTimezoneShift", "true");
+			props.setProperty("useSSPSCompatibleTimezoneShift", "true");
+			
+			conn2 = super.getConnectionWithProps(props);
+			this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
+			this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
+			this.pstmt.execute();
+			this.pstmt.close();
+			conn2.close();
+			
+			props.setProperty("useServerPrepStmts", "false");
+			props.setProperty("useJDBCCompliantTimezoneShift", "false");
+			props.setProperty("useSSPSCompatibleTimezoneShift", "false");
+			conn2 = super.getConnectionWithProps(props);
+			this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
+			this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
+			this.pstmt.execute();
+			this.pstmt.close();
+			
+			Statement s = conn2.createStatement();
+			 this.rs = s.executeQuery("SELECT t1 FROM testBug24344 ORDER BY i ASC");
+			
+			 Timestamp[] dates = new Timestamp[3];
+			
+			int i = 0;
+			
+			while(rs.next()){
+				dates[i++] = rs.getTimestamp(1);
+			}
+			
+			assertEquals( "Number of rows should be 3.", 3, i);
+			assertEquals(dates[0], dates[1]);
+			assertTrue(!dates[1].equals(dates[2]));
+		} finally {
+			closeMemberJDBCResources();
+			
+			if (conn2 != null) {
+				conn2.close();
+			}
+		}
+	}
 }

Modified: trunk/connector-j/CHANGES
===================================================================
--- trunk/connector-j/CHANGES	2006-12-06 01:01:36 UTC (rev 6126)
+++ trunk/connector-j/CHANGES	2006-12-06 21:48:40 UTC (rev 6127)
@@ -13,7 +13,15 @@
       what is required.
       
     - Fixed BUG#24360 .setFetchSize() breaks prepared SHOW and other commands.
-            
+          
+    - Fixed BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
+	  statements gives different behavior than when using client-side prepared
+	  statements. (this is now fixed if moving from server-side prepared statements
+	  to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
+	  true", as the driver can't tell if this is a new deployment that never used 
+	  server-side prepared statements, or if it is an existing deployment that is
+	  switching to client-side prepared statements from server-side prepared statements.
+	   
 10-20-06 - Version 5.0.4
 
     - Fixed BUG#21379 - column names don't match metadata in cases 

Modified: trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java
===================================================================
--- trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java	2006-12-06 01:01:36 UTC
(rev 6126)
+++ trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java	2006-12-06 21:48:40 UTC
(rev 6127)
@@ -1357,6 +1357,14 @@
 			"Use SSL when communicating with the server (true/false), defaults to 'false'",
 			"3.0.2", SECURITY_CATEGORY, 2);
 
+	private BooleanConnectionProperty useSSPSCompatibleTimezoneShift = new
BooleanConnectionProperty(
+			"useSSPSCompatibleTimezoneShift",
+			false,
+			"If migrating from an environment that was using server-side prepared statements, and
the"
+			+ " configuration property \"useJDBCCompliantTimeZoneShift\" set to \"true\", use
compatible behavior"
+			+ " when not using server-side prepared statements when sending TIMESTAMP values to
the MySQL server.",
+			"5.0.5", MISC_CATEGORY, Integer.MIN_VALUE);
+	
 	private BooleanConnectionProperty useStreamLengthsInPrepStmts = new
BooleanConnectionProperty(
 			"useStreamLengthsInPrepStmts",
 			true,
@@ -3812,4 +3820,12 @@
 			String value) {
 		this.trustCertificateKeyStoreUrl.setValue(value);
 	}
+	
+	public boolean getUseSSPSCompatibleTimezoneShift() {
+		return this.useSSPSCompatibleTimezoneShift.getValueAsBoolean();
+	}
+
+	public void setUseSSPSCompatibleTimezoneShift(boolean flag) {
+		this.useSSPSCompatibleTimezoneShift.setValue(flag);
+	}
 }

Modified: trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java
===================================================================
--- trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java	2006-12-06 01:01:36 UTC
(rev 6126)
+++ trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java	2006-12-06 21:48:40 UTC
(rev 6127)
@@ -3774,15 +3774,94 @@
 					.getServerTimezoneTZ(), rollForward);
 			}
 
-			if (this.tsdf == null) {
-				this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$
+			if (this.connection.getUseSSPSCompatibleTimezoneShift()) {
+				doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar);
+			} else {
+				
+				if (this.tsdf == null) {
+					this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US);
//$NON-NLS-1$
+				}
+				
+				timestampString = this.tsdf.format(x);
+
+				setInternal(parameterIndex, timestampString); // SimpleDateFormat is not
+															  // thread-safe
 			}
+		}
+	}
 
-			timestampString = this.tsdf.format(x);
+	private void doSSPSCompatibleTimezoneShift(int parameterIndex, Timestamp x, Calendar
sessionCalendar) throws SQLException {
+		Calendar sessionCalendar2 = (this.connection
+				.getUseJDBCCompliantTimezoneShift()) ? this.connection
+				.getUtcCalendar()
+				: getCalendarInstanceForSessionOrNew();
 
-			setInternal(parameterIndex, timestampString); // SimpleDateFormat
-			// is not
-			// thread-safe
+		synchronized (sessionCalendar2) {
+			java.util.Date oldTime = sessionCalendar2.getTime();
+
+			try {
+				sessionCalendar2.setTime(x);
+
+				int year = sessionCalendar2.get(Calendar.YEAR);
+				int month = sessionCalendar2.get(Calendar.MONTH) + 1;
+				int date = sessionCalendar2.get(Calendar.DAY_OF_MONTH);
+
+				int hour = sessionCalendar2.get(Calendar.HOUR_OF_DAY);
+				int minute = sessionCalendar2.get(Calendar.MINUTE);
+				int seconds = sessionCalendar2.get(Calendar.SECOND);
+
+				StringBuffer tsBuf = new StringBuffer();
+
+				tsBuf.append('\'');
+				tsBuf.append(year);
+
+				tsBuf.append("-");
+
+				if (month < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(month);
+
+				tsBuf.append('-');
+
+				if (date < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(date);
+
+				tsBuf.append(' ');
+
+				if (hour < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(hour);
+
+				tsBuf.append(':');
+
+				if (minute < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(minute);
+
+				tsBuf.append(':');
+
+				if (seconds < 10) {
+					tsBuf.append('0');
+				}
+
+				tsBuf.append(seconds);
+
+				tsBuf.append('\'');
+
+				setInternal(parameterIndex, tsBuf.toString());
+
+			} finally {
+				sessionCalendar.setTime(oldTime);
+			}
 		}
 	}
 

Modified: trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java
===================================================================
--- trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java	2006-12-06
01:01:36 UTC (rev 6126)
+++ trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java	2006-12-06
21:48:40 UTC (rev 6127)
@@ -3529,4 +3529,77 @@
 			}
 		}
 	}
+	
+	/**
+	 * Tests fix for BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
+	 * statements gives different behavior than when using client-side prepared
+	 * statements. (this is now fixed if moving from server-side prepared statements
+	 * to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
+	 * "true", as the driver can't tell if this is a new deployment that never used 
+	 * server-side prepared statements, or if it is an existing deployment that is
+	 * switching to client-side prepared statements from server-side prepared statements.
+	 * 
+	 * @throws Exception if the test fails
+	 */
+	public void testBug24344() throws Exception {
+		
+		super.createTable("testBug24344", 
+				"(i INT AUTO_INCREMENT, t1 DATETIME, PRIMARY KEY (i)) ENGINE = MyISAM");
+		
+		Connection conn2 = null;
+		
+		try {
+			Properties props = new Properties();
+			props.setProperty("useServerPrepStmts", "true");
+			props.setProperty("useJDBCCompliantTimezoneShift", "true");
+			conn2 = super.getConnectionWithProps(props);
+			this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
+			Calendar c = Calendar.getInstance();
+			this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
+			this.pstmt.execute();
+			this.pstmt.close();
+			conn2.close();
+			
+			props.setProperty("useServerPrepStmts", "false");
+			props.setProperty("useJDBCCompliantTimezoneShift", "true");
+			props.setProperty("useSSPSCompatibleTimezoneShift", "true");
+			
+			conn2 = super.getConnectionWithProps(props);
+			this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
+			this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
+			this.pstmt.execute();
+			this.pstmt.close();
+			conn2.close();
+			
+			props.setProperty("useServerPrepStmts", "false");
+			props.setProperty("useJDBCCompliantTimezoneShift", "false");
+			props.setProperty("useSSPSCompatibleTimezoneShift", "false");
+			conn2 = super.getConnectionWithProps(props);
+			this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
+			this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
+			this.pstmt.execute();
+			this.pstmt.close();
+			
+			Statement s = conn2.createStatement();
+			 this.rs = s.executeQuery("SELECT t1 FROM testBug24344 ORDER BY i ASC");
+			
+			 Timestamp[] dates = new Timestamp[3];
+			
+			int i = 0;
+			
+			while(rs.next()){
+				dates[i++] = rs.getTimestamp(1);
+			}
+			
+			assertEquals( "Number of rows should be 3.", 3, i);
+			assertEquals(dates[0], dates[1]);
+			assertTrue(!dates[1].equals(dates[2]));
+		} finally {
+			closeMemberJDBCResources();
+			
+			if (conn2 != null) {
+				conn2.close();
+			}
+		}
+	}
 }