>> Hi. I've created several applications in Delphi that access data on
>> Oracle7 tables scattered across several servers. It works wonderfully
>> and I've managed to impress everyone.
>> Does anybody know how I would go about allowing the user to "Cancel"
>> the execution of the query, like SQL Plus does?
>You could at least Close the Database Component the TQuery belongs to.
>That means in the OnClick of the button
>you code
>Database->Close();
>Database->Open();
I don't think this will work. First, you can not cancel a TQUERY in progress
from Delphi as the BDE does not support the OCI call to cancel the query.
The BDE is a generic bit of software (much like ODBC, only better in some
respects I think). Therefore it does not support API calls that are specific
to a certain database only (which is a pity and yes I would like to have my
whiskey and drink it too).
But there is a way. First, make the Query Tool a MDI window with its own
TSession, TDatabase and TQuery components. When the window is created, it
inherits the database connection parameters (userid, password and db alias)
from the main window and opens a brand new connection to the database. First
thing the MDI child does is to get its session and serial# from Oracle and
save it for future use.
When the user runs a query, create a TThread and activate the TQuery in the
thread. The reason for having a seperate TSession and TDatabase is that even
though you can multithread queries on a single TSession and TDatabase, the
TQuery's will block one another, i.e. the 2nd threaded TQuery will wait for
the 1st one to complete before it can run. It's like having a single pipe to
the database. To run more than one query at a time, you need more than one
pipe.
OK, now when the TQuery is happily running in a thread, the user decides to
can it. Windows has an API call to cancel or kill a thread. The cancel call
won't work because the Thread is not "active" as it is waiting for a return
from the database (in fact, the BDE and/or SQL*Net dll's have the exec
pointer for the thread at that time). So, the kill thread api call is all
that's left - pity that it GPF's the application though because while BDE is
happily waiting/working in that thread, you kick the chair from under it and
destroy the thread. Not good.
So what's left? The MDI window uses the main application thread and it's
TSession, TDatabase, and TQuery components to send a ALTER SYSTEM KILL
SESSION command with the session and serial# as parameters. And this works
fine as the Oracle session is killed and control returned by SQL*Net and BDE
back to the thread, which then terminates. Only problem is now that you have
killed the connection for that MDI child to Oracle. So you will have to
reconnect to Oracle and get your session and serial# again for just in
case... :-)
regards,
Billy
by