You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2013/01/29 04:31:12 UTC
[jira] [Commented] (DERBY-6053) Client should use a prepared
statement rather than regular statement for
Connection.setTransactionIsolation
[ 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