Connector/J commit: r5735 - in branches/branch_5_0/connector-j: . src/com/mysql/jdbc src/testsuite/regression

[mmatthews]

Modified:
   branches/branch_5_0/connector-j/CHANGES
   branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java
   branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java
  
branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java
Log:
Workaround for server crash when calling stored procedures
	  via a server-side prepared statement (driver now detects 
	  prepare(stored procedure) and substitutes client-side prepared 
	  statement), addresses BUG#22297.

Modified: branches/branch_5_0/connector-j/CHANGES
===================================================================
--- branches/branch_5_0/connector-j/CHANGES	2006-09-15 00:01:26 UTC (rev 5734)
+++ branches/branch_5_0/connector-j/CHANGES	2006-09-15 16:41:58 UTC (rev 5735)
@@ -29,6 +29,11 @@
 	  Statement.setQueryTimeout() when specification says argument is
 	  to be in seconds.
 	  
+	- Workaround for server crash when calling stored procedures
+	  via a server-side prepared statement (driver now detects 
+	  prepare(stored procedure) and substitutes client-side prepared 
+	  statement), addresses BUG#22297.
+	  
 07-26-06 - Version 5.0.3
 
     - Fixed BUG#20650 - Statement.cancel() causes NullPointerException

Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java
===================================================================
--- branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java	2006-09-15 00:01:26
UTC (rev 5734)
+++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java	2006-09-15 16:41:58
UTC (rev 5735)
@@ -1742,6 +1742,12 @@
 
 	private boolean canHandleAsServerPreparedStatementNoCache(String sql) 
 		throws SQLException {
+		
+		// Can't use server-side prepare for CALL
+		if (StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "CALL")) {
+			return false;
+		}
+		
 		boolean canHandleAsStatement = true;
 		
 		if (!versionMeetsMinimum(5, 0, 7) && 
@@ -2085,18 +2091,25 @@
 	 */
 	public PreparedStatement clientPrepareStatement(String sql,
 			int resultSetType, int resultSetConcurrency) throws SQLException {
+		return clientPrepareStatement(sql, resultSetType, resultSetConcurrency, true);
+	}
+	
+	protected PreparedStatement clientPrepareStatement(String sql,
+			int resultSetType, int resultSetConcurrency, 
+			boolean processEscapeCodesIfNeeded) throws SQLException {
 		checkClosed();
 
+		String nativeSql = processEscapeCodesIfNeeded &&
getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;
+		
 		PreparedStatement pStmt = null;
 
 		if (getCachePreparedStatements()) {
 			synchronized (this.cachedPreparedStatementParams) {
 				PreparedStatement.ParseInfo pStmtInfo = (PreparedStatement.ParseInfo)
this.cachedPreparedStatementParams
-						.get(sql);
+						.get(nativeSql);
 	
 				if (pStmtInfo == null) {
-					pStmt = new com.mysql.jdbc.PreparedStatement(this, 
-							getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql,
+					pStmt = new com.mysql.jdbc.PreparedStatement(this, nativeSql,
 							this.database);
 	
 					PreparedStatement.ParseInfo parseInfo = pStmt.getParseInfo();
@@ -2125,17 +2138,17 @@
 							}
 						}
 	
-						this.cachedPreparedStatementParams.put(sql, pStmt
+						this.cachedPreparedStatementParams.put(nativeSql, pStmt
 								.getParseInfo());
 					}
 				} else {
 					pStmtInfo.lastUsed = System.currentTimeMillis();
-					pStmt = new com.mysql.jdbc.PreparedStatement(this, sql,
+					pStmt = new com.mysql.jdbc.PreparedStatement(this, nativeSql,
 							this.database, pStmtInfo);
 				}
 			}
 		} else {
-			pStmt = new com.mysql.jdbc.PreparedStatement(this, sql,
+			pStmt = new com.mysql.jdbc.PreparedStatement(this, nativeSql,
 					this.database);
 		}
 
@@ -4576,14 +4589,14 @@
 					if (pStmt == null) {
 						try {
 							pStmt = new com.mysql.jdbc.ServerPreparedStatement(this, nativeSql,
-									this.database);
+									this.database, resultSetType, resultSetConcurrency);
 							if (sql.length() < getPreparedStatementCacheSqlLimit()) {
 								((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true;
 							}
 						} catch (SQLException sqlEx) {
 							// Punt, if necessary
 							if (getEmulateUnsupportedPstmts()) {
-								pStmt = clientPrepareStatement(nativeSql);
+								pStmt = clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency,
false);
 								
 								if (sql.length() < getPreparedStatementCacheSqlLimit()) {
 									this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
@@ -4597,24 +4610,20 @@
 			} else {
 				try {
 					pStmt = new com.mysql.jdbc.ServerPreparedStatement(this, nativeSql,
-							this.database);
+							this.database, resultSetType, resultSetConcurrency);
 				} catch (SQLException sqlEx) {
 					// Punt, if necessary
 					if (getEmulateUnsupportedPstmts()) {
-						pStmt = clientPrepareStatement(nativeSql);
+						pStmt = clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency,
false);
 					} else {
 						throw sqlEx;
 					}
 				}
 			}
 		} else {
-			pStmt = clientPrepareStatement(nativeSql);
+			pStmt = clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
 		}
 
-
-		pStmt.setResultSetType(resultSetType);
-		pStmt.setResultSetConcurrency(resultSetConcurrency);
-
 		return pStmt;
 	}
 
@@ -5152,7 +5161,12 @@
 	 */
 	public ServerPreparedStatement serverPrepare(String sql)
 			throws SQLException {
-		return new ServerPreparedStatement(this, sql, this.getCatalog());
+
+		String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;
+		
+		return new ServerPreparedStatement(this, nativeSql, this.getCatalog(),
+				java.sql.ResultSet.TYPE_SCROLL_SENSITIVE,
+				java.sql.ResultSet.CONCUR_READ_ONLY);
 	}
 
 	protected boolean serverSupportsConvertFn() throws SQLException {

Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java
===================================================================
---
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java	2006-09-15
00:01:26 UTC (rev 5734)
+++
branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java	2006-09-15
16:41:58 UTC (rev 5735)
@@ -284,7 +284,8 @@
 	 * @throws SQLException
 	 *             If an error occurs
 	 */
-	public ServerPreparedStatement(Connection conn, String sql, String catalog)
+	public ServerPreparedStatement(Connection conn, String sql, String catalog,
+			int resultSetType, int resultSetConcurrency)
 			throws SQLException {
 		super(conn, catalog);
 
@@ -324,6 +325,9 @@
 			throw SQLError.createSQLException(ex.toString(),
 					SQLError.SQL_STATE_GENERAL_ERROR);
 		}
+		
+		setResultSetType(resultSetType);
+		setResultSetConcurrency(resultSetConcurrency);
 	}
 
 	/**

Modified:
branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java
===================================================================
---
branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java	2006-09-15
00:01:26 UTC (rev 5734)
+++
branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java	2006-09-15
16:41:58 UTC (rev 5735)
@@ -27,6 +27,7 @@
 import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
+import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Types;
 import java.util.Properties;
@@ -712,6 +713,77 @@
 			}
 		}
 	}
+	
+	/**
+	 * Tests workaround for server crash when calling stored procedures
+	 * via a server-side prepared statement (driver now detects 
+	 * prepare(stored procedure) and substitutes client-side prepared statement).
+	 * 
+	 * @throws Exception if the test fails
+	 */
+	public void testBug22297() throws Exception {
+		if (versionMeetsMinimum(5, 0)) {
+			this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22297");
+			
+			createTable("tblTestBug2297_1", "("
+					+ "id varchar(20) NOT NULL default '',"
+					+ "Income double(19,2) default NULL)");	
+			
+			createTable("tblTestBug2297_2", "("
+					+ "id varchar(20) NOT NULL default ''," 
+					+ "CreatedOn datetime default NULL)");
+			
+			this.stmt.executeUpdate("CREATE PROCEDURE testBug22297(pcaseid INT)"
+					+ "BEGIN"
+					+ "\nSET @sql = \"DROP TEMPORARY TABLE IF EXISTS tmpOrders\";"
+					+ " PREPARE stmt FROM @sql;"
+					+ " EXECUTE stmt;"
+					+ " DEALLOCATE PREPARE stmt;"
+					+ "\nSET @sql = \"CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM
tblTestBug2297_1 GROUP BY id\";"
+					+ " PREPARE stmt FROM @sql;"
+					+ " EXECUTE stmt;"
+					+ " DEALLOCATE PREPARE stmt;"
+					+ "\n SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income"
+					+ "\n FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id"
+					+ "\n WHERE e.CreatedOn > '2006-08-01') AS Final ORDER BY id;" 
+					+ "\nEND");
+
+			this.stmt.executeUpdate("INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES "
+					+ "('a',4094.00),"
+					+ "('b',500.00),"
+					+ "('c',3462.17),"
+					+ " ('d',500.00),"
+					+ " ('e',600.00)");
+			
+			this.stmt.executeUpdate("INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES "
+					+ "('d','2006-08-31 00:00:00'),"
+					+ "('e','2006-08-31 00:00:00'),"
+				+ "('b','2006-08-31 00:00:00'),"
+				+ "('c','2006-08-31 00:00:00'),"
+				+ "('a','2006-08-31 00:00:00')");
+			
+			try {
+				this.pstmt = this.conn.prepareStatement("{CALL testBug22297(?)}");
+				this.pstmt.setInt(1, 1);
+				this.rs =this.pstmt.executeQuery();
+                
+				String[] ids = new String[] { "a", "b", "c", "d", "e"};
+                int pos = 0;
+                
+                while (this.rs.next()) {
+                	assertEquals(ids[pos++], rs.getString(1));
+                	assertEquals(100, rs.getInt(2));
+                }
+                
+                assertEquals(this.pstmt.getClass().getName(),
+                		com.mysql.jdbc.PreparedStatement.class.getName());
+
+			} finally {
+				closeMemberJDBCResources();
+			}
+		}
+	}
+	
 	public void testHugeNumberOfParameters() throws Exception {
 		if (versionMeetsMinimum(5, 0)) {
 			this.stmt