Delphi 2.0 and Oracle

Delphi 2.0 and Oracle

Post by Elie Tordjma » Sat, 28 Feb 1998 04:00:00



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.

All these applications also come with a generic "Query Tool" that allows
users to do their own queries. Sometimes, though, they will experiment
with a Select statement that can take forever.

Does anybody know how I would go about allowing the user to "Cancel" the
execution of the query, like SQL Plus does?

Query objects in Delphi have an event called OnServerYield, but it never
gets triggered. Does something need to be done on the server side?

Any help would be great. Thanks.

 
 
 

Delphi 2.0 and Oracle

Post by Helmut Hah » Tue, 03 Mar 1998 04:00:00



> 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.

> All these applications also come with a generic "Query Tool" that
> allows
> users to do their own queries. Sometimes, though, they will experiment

> with a Select statement that can take forever.

> Does anybody know how I would go about allowing the user to "Cancel"
> the
> execution of the query, like SQL Plus does?

> Query objects in Delphi have an event called OnServerYield, but it
> never
> gets triggered. Does something need to be done on the server side?

> Any help would be great. Thanks.

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();

Thus you should provide a new TDatabase Component only for the SQL-Tool.

Hope this helps!

Helmut

 
 
 

Delphi 2.0 and Oracle

Post by Billy Verreynn » Thu, 05 Mar 1998 04:00:00




>> 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