Bug #39649 automaticaly change the isolation

• Submitted: 25 Sep 2008 12:46
• Modified: 20 Apr 2017 23:10
• Reporter: Bogdan Kecman
• Status: Won't fix
• Category: Connector / J
• Severity: S4 (Feature request)
• OS: Any
• Assigned to: Alexander Soklakov
• CPU Architecture: Any
• Tags: migration, MSSQL

[25 Sep 2008 12:46] Bogdan Kecman

Description:
In some of our java API we are not using setTransactionIsolation() on the jdbc connection . We instead encapsulate a DML statement with SET ISOLATION statements separated with delimiters in a unique statement.

This is working fine for MSSQL but not in MySQL.

How to repeat:
.

Suggested fix:
Add a compatibility parameter on the connection that parse the 
SQL statement and automaticaly change the isolation and other 
little Syntax

Other syntax that are working in MSSQL but not in MySQL

Select count(*) will return int vs long
Select function () will work on MSQL and should be changed to function() in MySQL

[20 Apr 2017 23:10] Filipe Silva

Posted by developer:
 
The requested feature can be accomplished by using a statement interceptor, such as the example below.

The other two syntax issues described in this report don't apply anymore:
- Select count(*) are returned as BIGINT
- Functions are allowed with or without spaces between the name and opening parentheses.

Sample of a statement interceptor that sets connection isolation. Innumerable variations of this sample can be made to achieve the expected results:

public class Bug39649 {

    public static void main(String[] args) throws Exception {
        Class.forName(Driver.class.getName());

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?statementInterceptors=" + Bug39649StatementInterceptor.class.getName(),
                "user", "password");
        Statement stmt = conn.createStatement();
        System.out.println(conn.getTransactionIsolation());
        stmt.executeQuery("/* SET-TRANSACTION-ISOLATION-READ-COMMITTED */SELECT 1");
        System.out.println(conn.getTransactionIsolation());
        conn.close();
    }

    public static class Bug39649StatementInterceptor implements StatementInterceptorV2 {
        @Override
        public void init(com.mysql.jdbc.Connection conn, Properties props) throws SQLException {
        }

        @Override
        public ResultSetInternalMethods preProcess(String sql, com.mysql.jdbc.Statement interceptedStatement, com.mysql.jdbc.Connection connection)
                throws SQLException {
            if (sql.contains("SET-TRANSACTION-ISOLATION-READ-COMMITTED")) {
                connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            }
            return null;
        }

        @Override
        public boolean executeTopLevelOnly() {
            return false;
        }

        @Override
        public void destroy() {
        }

        @Override
        public ResultSetInternalMethods postProcess(String sql, com.mysql.jdbc.Statement interceptedStatement, ResultSetInternalMethods originalResultSet,
                com.mysql.jdbc.Connection connection, int warningCount, boolean noIndexUsed, boolean noGoodIndexUsed, SQLException statementException)
                throws SQLException {
            return originalResultSet;
        }
    }
}