FileDocCategorySizeDatePackage
QueryTimeoutTest.javaAPI DocAndroid 1.5 API27906Wed May 06 22:41:06 BST 2009tests.java.sql

QueryTimeoutTest

public class QueryTimeoutTest extends TestCase
Functional test for the Statement.setQueryTimeout() method. Adopted from Apache Derby project (Apache License 2.0). TODO Test requires transaction isolation to be supported. => Ticket 69 This test consists of four parts: 1. Executes a SELECT query in 4 different threads concurrently. The query calls a user-defined, server-side function which delays the execution, so that it takes several seconds even though the data volume is really low. The fetch operations take longer time than the timeout value set. Hence, this part tests getting timeouts from calls to ResultSet.next(). Two connections are used, two threads execute their statement in the context of one connection, the other two threads in the context of the other connection. Of the 4 threads, only one executes its statement with a timeout value. This way, the test ensures that the correct statement is affected by setQueryTimeout(), regardless of what connection/transaction it and other statements are executed in the context of. 2. Executes an INSERT query in multiple threads. This part tests getting timeouts from calls to Statement.execute(). Each thread executes the query in the context of a separate connection. There is no point in executing multiple statements on the same connection; since only one statement per connection executes at a time, there will be no interleaving of execution between them (contrary to the first part of this test, where calls to ResultSet.next() may be interleaved between the different threads). Half of the threads execute their statement with a timeout value set, this is to verify that the correct statements are affected by the timeout, while the other statements execute to completion. 3. Sets an invalid (negative) timeout. Verifies that the correct exception is thrown. 4. Tests that the query timeout value is not forgotten after the execution of a statement.

Fields Summary
private static Statement
statement
private static final int
TIMEOUT
private static final int
CONNECTIONS
private static Connection[]
connections
Constructors Summary
Methods Summary
private static voiddropTables(java.sql.Connection conn, java.lang.String tablePrefix)

        Collection ignore = new HashSet();
        //ignore.add("42Y55");

        exec(conn, "drop table if exists " + tablePrefix + "_orig;", ignore);
        exec(conn, "drop table if exists " + tablePrefix + "_copy;", ignore);
private static voidexec(java.sql.Connection connection, java.lang.String queryString, java.util.Collection ignoreExceptions)
Used for executing the SQL statements for setting up this test (the preparation phase). The queries testing setQueryTimeout() are run by the StatementExecutor class.

        Statement statement = null;
        try {
            statement = connection.createStatement();
            System.out.println(" Executing "+queryString);
            statement.execute(queryString);
        } catch (SQLException e) {
            String sqlState = e.getSQLState();
            if (!ignoreExceptions.contains(sqlState)) {
                throw new TestFailedException(e); // See finally block below
            }
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ee) {
                    // This will discard an exception possibly thrown above :-(
                    // But we don't worry too much about this, since:
                    // 1. This is just a test
                    // 2. We don't expect close() to throw
                    // 3. If it does, this will be inspected by a developer
                    throw new TestFailedException(ee);
                }
            }
        }
private static voidexec(java.sql.Connection connection, java.lang.String queryString)

        exec(connection, queryString, Collections.EMPTY_SET);
private static voidexpectException(java.lang.String expectSqlState, java.sql.SQLException sqlException, java.lang.String failMsg)
This method compares a thrown SQLException's SQLState value to an expected SQLState. If they do not match, a TestFailedException is thrown with the given message string.

        if (sqlException == null) {
            throw new TestFailedException(failMsg);
        } else {
            String sqlState = sqlException.getSQLState();
            if (!expectSqlState.equals(sqlState)) {
                throw new TestFailedException(sqlException);
            }
        }
private static java.lang.StringgetExecQuery(java.lang.String tablePrefix)

        return "insert into " + tablePrefix + "_copy select a from "
                + tablePrefix + "_orig where DELAY(1,1)=1";
private static java.lang.StringgetFetchQuery(java.lang.String tablePrefix)

        /**
         * The reason for using the mod function here is to force at least one
         * invocation of ResultSet.next() to read more than one row from the
         * table before returning. This is necessary since timeout is checked
         * only when reading rows from base tables, and when the first row is
         * read, the query still has not exceeded the timeout.
         */
        return "select a from " + tablePrefix
                + "_orig where mod(DELAY(1,a),3)=0";
private static java.sql.PreparedStatementprepare(java.sql.Connection conn, java.lang.String query)

        try {
            return conn.prepareStatement(query);
        } catch (SQLException e) {
            throw new TestFailedException(e);
        }
private static voidprepareTables(java.sql.Connection conn, java.lang.String tablePrefix)

        System.out.println("Initializing tables with prefix " + tablePrefix);

        dropTables(conn, tablePrefix);

        exec(conn, "create table " + tablePrefix + "_orig (a int)");

        exec(conn, "create table " + tablePrefix + "_copy (a int)");
        
        for (int i = 0; i < 7; i++) {
        exec(conn, "insert into " + tablePrefix + "_orig"
                + " values ("+i+");");
        }
private static voidprintSQLException(java.sql.SQLException e)


         
        while (e != null) {
            e.printStackTrace();
            e = e.getNextException();
        }
public static voidstatementRemembersTimeout(java.sql.Statement stmt)

        System.out.println("Testing that Statement remembers timeout.");
        stmt.setQueryTimeout(1);
        for (int i = 0; i < 3; i++) {
            try {
                ResultSet rs = stmt.executeQuery(getFetchQuery("t"));
                while (rs.next()) {
                    // do nothing
                }
                throw new TestFailedException("Should have timed out.");
            } catch (SQLException sqle) {
                expectException("XCL52", sqle, "Should have timed out.");
            }
        }
        stmt.close();
private static voidstatementRemembersTimeout(java.sql.PreparedStatement ps)

        String name = (ps instanceof CallableStatement) ? "CallableStatement"
                : "PreparedStatement";
        System.out.println("Testing that " + name + " remembers timeout.");
        ps.setQueryTimeout(1);
        for (int i = 0; i < 3; i++) {
            try {
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    // do nothing
                }
                throw new TestFailedException("Should have timed out.");
            } catch (SQLException sqle) {
                expectException("XCL52", sqle, "Should have timed out.");
            }
        }
        ps.close();
public static junit.framework.Testsuite()
The actual main bulk of this test. Sets up the environment, prepares tables, runs the tests, and shuts down.

        
        TestSetup setup = new TestSetup( new TestSuite (QueryTimeoutTest.class)) {
            public void setUp() {
                
                // Establish connections
                Support_SQL.loadDriver();
                try {

                    for (int i = 0; i < connections.length; ++i) {
                        connections[i] = Support_SQL.getConnection();
                    }

                    for (int i = 0; i < connections.length; ++i) {
                        connections[i]
                                .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                    }
                    
                    // setup Delay function
                    prepare();

                } catch (Throwable e) {
                    fail("Unexpected SQLException " + e.toString());
                }

                System.out.println("Connections set up");
                
            }

            public void tearDown() {
                for (int i = connections.length - 1; i >= 0; --i) {
                    if (connections[i] != null) {
                        try {
                            connections[i].close();
                        } catch (SQLException ex) {
                            printSQLException(ex);
                        }
                    }
                }
                System.out.println("Closed connections");
            }
            
            public void prepare() throws TestFailedException {
                System.out.println("Preparing for testing queries with timeout");
                Database db = new Database();

                Connection conn = connections[0];


                try {
                    db.open(Support_SQL.getFilename(), 1);
                    conn.setAutoCommit(true);
                } catch (Exception e) {
                    throw new TestFailedException("Unexpected Exception", e);
                }

                Function delayFc = new Delay();
                db.create_function("DELAY", 2, delayFc);

                prepareTables(conn, "t");
            }
        };
       
        TestSuite ts = new TestSuite();
        ts.addTestSuite(QueryTimeoutTest.class);

        return setup;
public static voidtestInvalidTimeoutValue(java.sql.Connection conn)

test
{@link java.sql.Statement#setQueryTimeout(int) }

        
        try {
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            throw new TestFailedException("Unexpected Exception", e);
        }

        // Create statement
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("select * from sys.systables");
        } catch (SQLException e) {
            throw new TestFailedException("Unexpected Exception", e);
        }

        // Set (invalid) timeout value - expect exception
        try {
            stmt.setQueryTimeout(-1);
        } catch (SQLException e) {
            expectException("XJ074", e,
                    "negative timeout value should give exception");
        }

        System.out
                .println("Negative timeout value caused exception, as expected");

        // Execute the statement and fetch result
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery();
            System.out.println("Execute returned a ResultSet");
            rs.close();
        } catch (SQLException e) {
            throw new TestFailedException("Unexpected Exception", e);
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                // This will discard an exception possibly thrown above :-(
                // But we don't worry too much about this, since:
                // 1. This is just a test
                // 2. We don't expect close() to throw
                // 3. If it does, this will be inspected by a developer
                throw new TestFailedException("close should not throw", e);
            }
        }
public static voidtestRememberTimeoutValue()
Test for DERBY-1692.

        String sql = getFetchQuery("t");
        try {
            Statement stmt = connections[0].createStatement();
            statementRemembersTimeout(stmt);
            PreparedStatement ps = connections[0].prepareStatement(sql);
            statementRemembersTimeout(ps);
            CallableStatement cs = connections[0].prepareCall(sql);
            statementRemembersTimeout(cs);
        } catch (SQLException sqle) {
            throw new TestFailedException("Unexpected Exception", sqle);
        }
public static voidtestTimeoutWithExec()

test
{@link java.sql.Statement#setQueryTimeout(int) } Part two of this test.

        System.out.println("Testing timeout with an execute operation");

        for (int i = 0; i < connections.length; ++i) {
            try {
                connections[i].setAutoCommit(true);
            } catch (SQLException e) {
                throw new TestFailedException("Unexpected Exception", e);
            }
        }

        PreparedStatement statements[] = new PreparedStatement[connections.length];
        for (int i = 0; i < statements.length; ++i) {
            statements[i] = prepare(connections[i], getExecQuery("t"));
        }

        StatementExecutor[] executors = new StatementExecutor[statements.length];
        for (int i = 0; i < executors.length; ++i) {
            int timeout = (i % 2 == 0) ? TIMEOUT : 0;
            executors[i] = new StatementExecutor(statements[i], false, timeout);
        }

        for (int i = 0; i < executors.length; ++i) {
            executors[i].start();
        }

        for (int i = 0; i < executors.length; ++i) {
            try {
                executors[i].join();
            } catch (InterruptedException e) {
                throw new TestFailedException("Should never happen", e);
            }
        }

        /**
         * Actually, there is no guarantee that setting a query timeout for a
         * statement will actually cause a timeout, even if execution of the
         * statement takes longer than the specified timeout. However, these
         * queries execute significantly longer than the specified query
         * timeout. Also, the cancellation mechanism implemented should be quite
         * responsive. In sum, we expect the statement to always time out. If it
         * does not time out, however, we print the highest execution time for
         * the query, as an assistance in determining why it failed. Compare the
         * number to the TIMEOUT constant in this class (note that the TIMEOUT
         * constant is in seconds, while the execution time is in milliseconds).
         */
        for (int i = 0; i < executors.length; ++i) {
            int timeout = (i % 2 == 0) ? TIMEOUT : 0;
            if (timeout > 0) {
                expectException("XCL52", executors[i].getSQLException(),
                        "exec did not time out. Execution time: "
                                + executors[i].getHighestRunTime() + " ms");
            } else {
                SQLException sqlException = executors[i].getSQLException();
                if (sqlException != null) {
                    throw new TestFailedException(sqlException);
                }
            }
        }

        System.out
                .println("Statements that should time out timed out, and statements that should complete completed");

        for (int i = 0; i < statements.length; ++i) {
            try {
                statements[i].close();
            } catch (SQLException e) {
                throw new TestFailedException(e);
            }
        }
public static voidtestTimeoutWithExecuteUpdate()

test
{@link java.sql.Statement#setQueryTimeout(int) } Part two of this test.

        System.out.println("Testing timeout with executeUpdate call.");
        try {
            Statement stmt = connections[0].createStatement();
            stmt.setQueryTimeout(TIMEOUT);
            stmt.executeUpdate(getExecQuery("t"));
        } catch (SQLException sqle) {
            expectException("XCL52", sqle, "Should have timed out.");
        }
public static voidtestTimeoutWithFetch()
Part 1 of this test.

        System.out.println("Testing timeout with fetch operations");

        Connection conn1 = connections[0];
        Connection conn2 = connections[1];

        try {
            conn1.setAutoCommit(false);
            conn2.setAutoCommit(false);
        } catch (SQLException e) {
            throw new TestFailedException("Unexpected Exception", e);
        }

        // The idea with these 4 statements is as follows:
        // A - should time out
        // B - different stmt on the same connection; should NOT time out
        // C - different stmt on different connection; should NOT time out
        // D - here just to create equal contention on conn1 and conn2

        PreparedStatement statementA = prepare(conn1, getFetchQuery("t"));
        PreparedStatement statementB = prepare(conn1, getFetchQuery("t"));
        PreparedStatement statementC = prepare(conn2, getFetchQuery("t"));
        PreparedStatement statementD = prepare(conn2, getFetchQuery("t"));

        StatementExecutor[] statementExecutor = new StatementExecutor[4];
        statementExecutor[0] = new StatementExecutor(statementA, true, TIMEOUT);
        statementExecutor[1] = new StatementExecutor(statementB, true, 0);
        statementExecutor[2] = new StatementExecutor(statementC, true, 0);
        statementExecutor[3] = new StatementExecutor(statementD, true, 0);

        for (int i = 3; i >= 0; --i) {
            statementExecutor[i].start();
        }

        for (int i = 0; i < 4; ++i) {
            try {
                statementExecutor[i].join();
            } catch (InterruptedException e) {
                throw new TestFailedException("Should never happen", e);
            }
        }

        /**
         * Actually, there is no guarantee that setting a query timeout for a
         * statement will actually cause a timeout, even if execution of the
         * statement takes longer than the specified timeout. However, these
         * queries execute significantly longer than the specified query
         * timeout. Also, the cancellation mechanism implemented should be quite
         * responsive. In sum, we expect the statement to always time out. If it
         * does not time out, however, we print the highest execution time for
         * the query, as an assistance in determining why it failed. Compare the
         * number to the TIMEOUT constant in this class (note that the TIMEOUT
         * constant is in seconds, while the execution time is in milliseconds).
         */
        expectException("XCL52", statementExecutor[0].getSQLException(),
                "fetch did not time out. Highest execution time: "
                        + statementExecutor[0].getHighestRunTime() + " ms");

        System.out.println("Statement 0 timed out");

        for (int i = 1; i < 4; ++i) {
            SQLException sqlException = statementExecutor[i].getSQLException();
            if (sqlException != null) {
                throw new TestFailedException("Unexpected exception in " + i,
                        sqlException);
            }
            System.out.println("Statement " + i + " completed");
        }

        try {
            statementA.close();
            statementB.close();
            statementC.close();
            statementD.close();
            conn1.commit();
            conn2.commit();
        } catch (SQLException e) {
            throw new TestFailedException(e);
        }