2 Replies Latest reply: May 31, 2011 5:43 AM by 864796 RSS

    Cancel long running statement in Oracle Lite (OLITE_10.3.0.3.0 olite40.jar)

    864796
      On JDBC statement, there is the method 'cancel' to instruct the database to cancel an executing statement. This works fine on Oracle server database, but not on Oracle lite database. The method call 'cancel' just blocks and the running statement is never interrupted.

      The example I tried is very simple. There is a thread started which executes a long running statement. I noticed, that when moving the cursor forward by calling rs.next(), it just blocks. That would be ok, if the statement could be canceled from within the main thread by stmt.cancel. But this call blocks as well with no implact on the running statement. Why is that? Do I miss something or is it not possible to cancel a long running statements in Oracle Lite?

      In the following my code snipped:
      public class CancelStatement {
      
           private static final String PATH_DB = "XX";
           private static final String PATH_LIB = "XX";
      
           private static final String CON_STRING = "jdbc:polite:whatever;DataDirectory=" + PATH_DB + ";Database=XX;IsolationLevel=Read Committed;Autocommit=Off;CursorType=Forward Only";
           private static final String USER = "XX";
           private static final String PASSWORD = "XX";
      
           public static void main(String args[]) throws Exception {
                System.setProperty("java.library.path", PATH_LIB);
                Class.forName("oracle.lite.poljdbc.POLJDBCDriver");
      
                Connection con = DriverManager.getConnection(CON_STRING, USER, PASSWORD);
                Statement stmt = con.createStatement();
                Thread thread = new Thread(new LongStatementRunnable(con, stmt));
                thread.start();
                Thread.sleep(3000);
      
                // stop long running statement
                System.out.println("cancel long running statement");
                stmt.cancel(); // XXX does not work, as call is blocked until out of memory
                System.out.println("statement canceled");
           }
      
           private static class LongStatementRunnable implements Runnable {
      
                private Connection con;
                private Statement stmt;
      
                public LongStatementRunnable(Connection con, Statement stmt) {
                     this.con = con;
                     this.stmt = stmt;
                }
      
                @Override
                public void run() {
                     try {
                          System.out.println("start long running statement...");
      
                          // execute long running statement
                          ResultSet rs = stmt.executeQuery("SELECT * FROM PERSON P1, PERSON P2");
                          while (rs.next()) { // here the execution gets blocked
                               System.out.println("row"); // is never entered
                          }
                          rs.close();
                          stmt.close();
                          con.close();
                          System.out.println("long running statement finished...");
                     } catch (Exception e) {
                          e.printStackTrace();
                     }
                }
           }
      }
      I would be very glad if you could help me.
      Thanks a lot
      Daniel

      Edited by: 861793 on 26.05.2011 14:29