db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6053) Client should use a prepared statement rather than regular statement for Connection.setTransactionIsolation
Date Tue, 29 Jan 2013 03:31:12 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13565040#comment-13565040
] 

Mamta A. Satoor commented on DERBY-6053:
----------------------------------------

I wrote a simple JDBC program outside to see if parameter can be used to set transaction isolation
level and it appears that use of ? inside a prepared statement is not allowed by SET CURRENT
ISOLATION sql. I got following syntax error
$ java org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC
SQLState=42X01Syntax error: Encountered "?" at line 1, column 25.
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 25.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:92)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2400)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:153)
        at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:102)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1725)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1553)
        at org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.main(MamtaJDBC.java:29)
Caused by: java.sql.SQLException: Syntax error: Encountered "?" at line 1, column 25.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71)
        ... 10 more
Caused by: ERROR 42X01: Syntax error: Encountered "?" at line 1, column 25.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278)
        at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:153)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:357)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:134)
        ... 4 more

The JDBC program is as follows
package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.*; 

/** 
 * Sample JDBC program
 * 
 */public class MamtaJDBC { 
     
    public static void main(String[] args) throws Exception { 
        Statement s; 
        PreparedStatement ps; 
        try { 
            // Load the driver.             
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); 
            Connection conn = DriverManager 
                    .getConnection("jdbc:derby:c:/dellater/wombat;create=true"); 
            // clean up from a previous run 
            s = conn.createStatement(); 
            try { 
                s.executeUpdate("DROP TABLE T1"); 
            } catch (SQLException se) { 
                if (!se.getSQLState().equals("42Y55")) 
                    throw se; 
            } 
            //Just a sanity check that ? worked for following select statement
            ps = conn.prepareStatement("select * from sys.systables where tablename=?");
            ps.setString(1, "rr");
            ps.execute();
            ps = conn.prepareStatement("SET CURRENT ISOLATION = ?");
            ps.setString(1, "rr");
            ps.execute();
            ps.close(); 
            s.close();
            conn.close(); 
        } catch (SQLException se) { 
            while (se != null) { 
                System.out.println("SQLState=" + se.getSQLState() 
                        + se.getMessage()); 
                se.printStackTrace(); 
                se = se.getNextException(); 
            } 
        } 
    } 
}

In order to address the performance issues and possible garbage collection issues mentioned
by Kathey above, since there are only 4 isolation levels, I was thinking we could create 4
prepared statements in Client side, one for each of the isolation levels and execute one of
those PreparedStatement when the user asks for change of isolation levels. This way, we will
not be preparing and executing for every isolation level change, instead just execute already
prepared PreparedStatemtn.

                
> Client should use a prepared statement rather than regular statement for Connection.setTransactionIsolation
> -----------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6053
>                 URL: https://issues.apache.org/jira/browse/DERBY-6053
>             Project: Derby
>          Issue Type: Improvement
>          Components: Network Client
>            Reporter: Kathey Marsden
>
> o.a.d.client.am.Connection setTransactionIsolation() uses a Statement which  it builds
up each time for setTransactionIsolation()  is called.
> private Statement setTransactionIsolationStmt = null;
> ...
> setTransactionIsolationStmt =
>                     createStatementX(java.sql.ResultSet.TYPE_FORWARD_ONLY,
>                             java.sql.ResultSet.CONCUR_READ_ONLY,
>                             holdability());
> ....
>  private void setTransactionIsolationX(int level)
> ...
>             setTransactionIsolationStmt.executeUpdate(
>                 "SET CURRENT ISOLATION = " + levelString);
> It would be better for performance and also for avoid possible garbage collection issues,
to have a single prepared statement with a parameter marker. 
> The program below shows repeated calls to setTransactionIsolation.
> import java.sql.*;
> import java.net.*;
> import java.io.*;
> import org.apache.derby.drda.NetworkServerControl;
> /**
>  * Client template starts its own NetworkServer and runs some SQL against it.
>  * The SQL or JDBC API calls can be modified to reproduce issues
>  * 
>  */public class SetTransactionIsolation {
>     public static Statement s;
>     
>     public static void main(String[] args) throws Exception {
>         try {
>             // Load the driver. Not needed for network server.
>             
>             Class.forName("org.apache.derby.jdbc.ClientDriver");
>             // Start Network Server
>             startNetworkServer();
>             // If connecting to a customer database. Change the URL
>             Connection conn = DriverManager
>                     .getConnection("jdbc:derby://localhost:1527/wombat;create=true");
>             // clean up from a previous run
>             s = conn.createStatement();
>             try {
>                 s.executeUpdate("DROP TABLE T");
>             } catch (SQLException se) {
>                 if (!se.getSQLState().equals("42Y55"))
>                     throw se;
>             }
>             for (int i = 0; i < 50000; i++) {
> 		conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
> 		conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
> 	    }
>             
>             // rs.close();
>             // ps.close();
>             runtimeInfo();
>             conn.close();
>             // Shutdown the server
>             shutdownServer();
>         } catch (SQLException se) {
>             while (se != null) {
>                 System.out.println("SQLState=" + se.getSQLState()
>                         + se.getMessage());
>                 se.printStackTrace();
>                 se = se.getNextException();
>             }
>         }
>     }
>     
>     /**
>      * starts the Network server
>      * 
>      */
>     public static void startNetworkServer() throws SQLException {
>         Exception failException = null;
>         try {
>             
>             NetworkServerControl networkServer = new NetworkServerControl(
>                     InetAddress.getByName("localhost"), 1527);
>             
>             networkServer.start(new PrintWriter(System.out));
>             
>             // Wait for the network server to start
>             boolean started = false;
>             int retries = 10; // Max retries = max seconds to wait
>             
>             while (!started && retries > 0) {
>                 try {
>                     // Sleep 1 second and then ping the network server
>                     Thread.sleep(1000);
>                     networkServer.ping();
>                     
>                     // If ping does not throw an exception the server has
>                     // started
>                     started = true;
>                 } catch (Exception e) {
>                     retries--;
>                     failException = e;
>                 }
>                 
>             }
>             
>             // Check if we got a reply on ping
>             if (!started) {
>                 throw failException;
>             }
>         } catch (Exception e) {
>             SQLException se = new SQLException("Error starting network  server");
>             se.initCause(failException);
>             throw se;
>         }
>     }
>     
>     public static void shutdownServer() throws Exception {
>         NetworkServerControl networkServer = new NetworkServerControl(
>                 InetAddress.getByName("localhost"), 1527);
>         networkServer.shutdown();
>     }
>     
>     public static void runtimeInfo() throws Exception {
>         NetworkServerControl networkServer = new NetworkServerControl(
>                 InetAddress.getByName("localhost"), 1527);
>         System.out.println(networkServer.getRuntimeInfo());
>     }
>     
> }

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message