nonblocking in a TThread

[Guest]

Hi,

We're using ODAC in a multithread application with success, however, now we want to replace our blocking database calls in the threads with non-blocking (we have a realtime kiosk application, if the database does not respond in 10 seconds we have to handle it ).

So my question :
How can I use TOraQuery with nonblocking = true in a TThread ?

Thanks,

[Alex]

There are no any known restrictions. If you encounter any problems inform us.

[Guest]

Thanks, yes it works now.
There was a problem in our test application with the message loop.

[Guest]

Correction, it works not completly correct.

Sometimes, but not always, oracle exceptions are coming in our main application thread when we use non-blocking in a TThread !

We can simulate the problem by sending a wrong sql statement to the oracleserver so that oracle returns ORA-00900.


Here is an example of the thread in which we can simulate the problem.

Is there something we do wrong ?

TTestThread = class(TThread)
protected
procedure Execute; override;
end;

{ TTestThread }

procedure TTestThread.Execute;
var
msg : TMsg;
ok : boolean;
s : TOraSession;
q : TOraQuery;
begin
s := TOraSession.create(nil);
q := TOraQuery.Create(nil);
try
//--> Fill session
with s do
begin
ConnectPrompt := False;
Username := 'username';
Password := 'pwd';
Server := 'test';
Connected := True;
end;

//--> Dummy query with bad SQL syntax
q.SQL.text := 'BAD SQL TEXT TO ORACLE';
q.Session := s;

ok := false;
repeat
try
q.Fetchall := true;
q.NonBlocking := true;
Q.Close;
q.Open;
while (q.active) and (not q.fetched) do
begin
if PeekMessage(msg, 0, 0, 0, PM_REMOVE) then
begin
if msg.message = wm_quit then break;
end;
end;

beep;
ok := true;

except
on e: exception do
begin
// log of hide exception, but it keeps coming in the main application thread and not here !!!!
end;
end;

until ok;
finally
FreeAndNil(s);
FreeAndNil(q);
end;
end;



Example create thread

var
x : TTestThread;
begin
x := TTestThread.Create(true);
x.FreeOnTerminate := true;
x.resume;
end;

[Alex]

There is no wrong behaviour, when you enable NonBlocking mode ODAC starts a separated thread and uses it performs SQL execution using it. You can't intercept exceptions that are rased in another thread using Try ... Except, so ODAC calls ApplicationHandleException system variable to show these execptions.

[Guest]

Ok, I understand but....

1. Sometimes the oracle exceptions are not passed to applicationHandleException, but goes to the try except message handler. You can simulate this using the testprogram above but change the SQL text to "select * from anunknowntable".

So I guess it has something todo with timing.

If ODAC was designed that non-blocking exceptions are raised using applciationhandleexception then there is a bug in ODAC 5.55, or DELPHI 7+update because we were able to catch some exception in the try...except handler from the Thread.

2. If the passing of the exception to the applicationhandleexception is as designed, then it's a really bad design and makes your nonblocking inside a TThread not usefull. Because if the main application thread is buzy, the exception is not thrown until the main application thread gets some time. So it's not possible to use nonblocking in threads for critical application which needs a fast response time even if the databasecalls are unsuccessfull. So how do we get out our " while (q.active) and (not q.fetched) " loop when there was an exception, and the main application thread was buzy doing other stuff ???

If you need more info we are glad to send you more information to solve this problem.

Thanks

[Alex]

1. Sometimes the oracle exceptions are not passed to applicationHandleException, but goes to the try except message handler. You can simulate this using the testprogram above but change the SQL text to "select * from anunknowntable".
So I guess it has something todo with timing.
If ODAC was designed that non-blocking exceptions are raised using applciationhandleexception then there is a bug in ODAC 5.55, or DELPHI 7+update because we were able to catch some exception in the try...except handler from the Thread.

This situation occurs because exception was raised before execute thread started (when ODAC tries to initialize select fields).

2. If the passing of the exception to the applicationhandleexception is as designed, then it's a really bad design and makes your nonblocking inside a TThread not usefull. Because if the main application thread is buzy, the exception is not thrown until the main application thread gets some time. So it's not possible to use nonblocking in threads for critical application which needs a fast response time even if the databasecalls are unsuccessfull. So how do we get out our " while (q.active) and (not q.fetched) " loop when there was an exception, and the main application thread was buzy doing other stuff ???

You are right about such issue with havily loaded main application thread. But you can always define Application.OnException event handler to prevent ApplicationHandleException from accessing main thread and hanging up.

[Guest]

Alex, thanks for your answer.

You're right, with Application.OnException we can hook into the exception even if the main thread is buzy, but it's not very usefull. We have several threads and it would be difficult to determine which odac nonblocking call raised the error. Ok, we can get the getcurrentthreadid in the exception handler, but we would get the threadid of the ODAC thread executing the call.

I think we will stay with our blocking function calls in threads, but use a seperate thread ourself for executing the blocking ODAC function.

Thanks for the help.
Benny,