Repost: Cancel stored procedure using JDBC

Discussion in 'Java' started by nkunkov@escholar.com, Nov 2, 2006.

  1. Guest

    Hello,
    I'll try my luck one more time by posting this again.
    I do hope somebody has already solved my problem.
    I have a Java application that spawns 4 different threads. Each thread
    will perform it's own task which is calling a stored procedure. Each
    stored procedure could possibly run a very long time since it will be
    processing millions of rows. The user should
    be able to stop any of the tasks he started. Stopping a task means
    stopping a stored procedure and doing the clean up of the affected
    tables.
    What are my options in terms of stopping/cancelling a running stored
    procedure from Java code?
    I would appreciate any help on this matter.
    Thanks in advance.
    NK
    , Nov 2, 2006
    #1
    1. Advertising

  2. Wesley Hall Guest

    wrote:
    > Hello,
    > I'll try my luck one more time by posting this again.
    > I do hope somebody has already solved my problem.
    > I have a Java application that spawns 4 different threads. Each thread
    > will perform it's own task which is calling a stored procedure. Each
    > stored procedure could possibly run a very long time since it will be
    > processing millions of rows. The user should
    > be able to stop any of the tasks he started. Stopping a task means
    > stopping a stored procedure and doing the clean up of the affected
    > tables.
    > What are my options in terms of stopping/cancelling a running stored
    > procedure from Java code?
    > I would appreciate any help on this matter.
    > Thanks in advance.
    > NK
    >


    As far as I am aware, there is no database independent way to abort a
    stored procedure. You could try closing the database connection but even
    if this works (and I am not certain it will) it will probably be
    database dependent.

    I have used databases that do not end a query when you control-C from
    the command line client. There are probably databases out there that
    would continue to run the procedure for a good long time (perhaps to
    completion) even if you killed the VM.
    Wesley Hall, Nov 2, 2006
    #2
    1. Advertising

  3. Chris Uppal Guest

    Wesley Hall wrote:

    > There are probably databases out there that
    > would continue to run the procedure for a good long time (perhaps to
    > completion) even if you killed the VM.


    There may even be ones (distributed, high availaibilty) which would run to
    completion even if you power down the machine where the stored procedure was
    running...

    (I don't know whether there are any such super-robust DBs, but I'd like to
    think they exist ;-)

    -- chris
    Chris Uppal, Nov 3, 2006
    #3
  4. John Maline Guest

    wrote:
    > What are my options in terms of stopping/cancelling a running stored
    > procedure from Java code?


    Have you tried CallableStatement.cancel()? Whether it works or not is a
    matter of the quality of the DB and JDBC driver. But that's the JDBC
    API for canceling Statement execution (and subclasses).

    I don't have experience using this on CallableStatement, but it worked
    as advertised on Statement/PreparedStatement on various Oracle JDBC drivers.


    void cancel()
    throws SQLException

    Cancels this Statement object if both the DBMS and driver support
    aborting an SQL statement. This method can be used by one thread to
    cancel a statement that is being executed by another thread.

    Throws:
    SQLException - if a database access error occurs


    Good luck!
    John
    John Maline, Nov 3, 2006
    #4
  5. steve Guest

    On Thu, 2 Nov 2006 22:54:45 +0800, wrote
    (in article <>):

    > Hello,
    > I'll try my luck one more time by posting this again.
    > I do hope somebody has already solved my problem.
    > I have a Java application that spawns 4 different threads. Each thread
    > will perform it's own task which is calling a stored procedure. Each
    > stored procedure could possibly run a very long time since it will be
    > processing millions of rows. The user should
    > be able to stop any of the tasks he started. Stopping a task means
    > stopping a stored procedure and doing the clean up of the affected
    > tables.
    > What are my options in terms of stopping/cancelling a running stored
    > procedure from Java code?
    > I would appreciate any help on this matter.
    > Thanks in advance.
    > NK
    >


    basically a stored procedure is usually called , in a standard sql statement.

    there is provision for cancelling a statement.

    I.E

    String The_qry = "{?=call
    external_user.PACKAGE_02.RETURN_reporttablearray(?,?,?)}";

    cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);

    cstmt.execute();
    cstmt.cancel();


    notice that the use of cancel relies on you writing your SQL so that it is in
    callback mode, I.E you submit sql , then go away & come back to check it has
    finished.
    Or that you correctly thread your sql calls. (after the .execute() the thread
    is tied up , until completion)

    heres what oracle say about this

    Using statement.cancel
    The JDBC standard method Statement.cancel attempts to cleanly stop the
    execution of a SQL statement by sending a message to the database. In
    response, the database stops execution and replies with an error message. The
    Java thread that invoked Statement.execute waits on the server, and continues
    execution only when it receives the error reply message invoked by the other
    thread's call to Statement.cancel.
    As a result, Statement.cancel relies on the correct functioning of the
    network and the database. If either the network connection is broken or the
    database server is hung, the client does not receive the error reply to the
    cancel message. Frequently, when the server process dies, JDBC receives an
    IOException that frees the thread that invoked Statement.execute. In some
    circumstances, the server is hung, but JDBC does not receive an IOException.
    Statement.cancel does not free the thread that initiated the
    Statement.execute.
    When JDBC does not receive an IOException, Oracle Net may eventually time out
    and close the connection. This causes an IOException and frees the thread.
    This process can take many minutes. For information on how to control this
    time-out, see the description of the readTimeout property for
    OracleDatasource.setConnectionProperties. You can also tune this time-out
    with certain Oracle Net settings. See the Oracle Database Net Services
    Administrator's Guide for more information.
    The JDBC standard method Statement.setQueryTimeout relies on
    Statement.cancel. If execution continues longer than the specified time-out
    interval, then the monitor thread calls Statement.cancel. This is subject to
    all the same limitations described previously. As a result, there are cases
    when the time-out does not free the thread that invoked Statement.execute.
    The length of time between execution and cancellation is not precise. This
    interval is no less than the specified time-out interval but can be several
    seconds longer. If the application has active threads running at high
    priority, then the interval can be arbitrarily longer. The monitor thread
    runs at high priority, but other high priority threads may keep it from
    running indefinitely. Note that the monitor thread is started only if there
    are statements executed with non zero time-out. There is only one monitor
    thread that monitors all Oracle JDBC statement execution.
    Statement.cancel and Statement.setQueryTimeout are not supported in the
    server-side internal driver. The server-side internal driver runs in the
    single-threaded server process; the Oracle JVM implements Java threads within
    this single-threaded process. If the server-side internal driver is executing
    a SQL statement, then no Java thread can call Statement.cancel. This also
    applies to the Oracle JDBC monitor thread.


    notice the words "attempts to cleanly stop" , and "This process can take many
    minutes".

    Personally I think you will have to be very careful how you use this
    function.


    steve
    steve, Nov 3, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    728
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,839
    Juha Laiho
    May 21, 2005
  3. Sumukh
    Replies:
    1
    Views:
    2,035
    steve
    Oct 4, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,267
    Mike P
    Jun 19, 2006
  5. Kermit Piper
    Replies:
    1
    Views:
    585
    Chris Uppal
    Jun 13, 2006
Loading...

Share This Page