how to trap logon error

[indmolding]

I am trying to detect, via code, whether or not I have been successful logging onto an Oracle database. In general, I've been trying:

try
.Execute
except
on e:Exception do
begin

end;
end;

There is never an exception raised, yet there is an error dialog which rquires human input to make go away. What I am trying to accomplish is to write a program that, in the event we lose a connection to a database, can just spin in a loop, trying to log back on, until the connection returns. The particular computer is unattended, polling our time clocks every minute, and storing the data. It is very important that I be able to restore a lost connection AUTOMATICALLY and without human intervention.

Any ideas?

Thanks

Bill Weaks

 

[ltd]

Hi,

the solution to your problem looks something like that in c++

bool bFinished=false;

while(bFinished==false)
{
try
{
SQLVar.Execute();
// some more code
bFinished=true;
}
catch( Exception &E)
{
// Some error loggin first
//
if(SQLSession->Connected==false)
{
SQLSession->Connected=true;
}
}
}

In the code we use in a real production scenario the while loop is also stopped when a configurable number of reconnets failed.
Depending if the code is used in the main application or in a thread you should also think about implementing another flag that signals that an application shutdown is in progress. And finaly in the real code we make some difference between "normal" exceptions and "orcale" exceptions. Be carefull, at least in BCB 5 "not logged on" is an normal exception and not an oracle exception.

Hope this helps,

Lars

 

[Marco Kalter]

How about this?

procedure Reconnect(Session: TOracleSession);
begin
  while Session.CheckConnection(True) = ccError do Sleep(10000);
end;

This tries to reconnect a session every 10 seconds if the connection was lost.

------------------
Marco Kalter
Allround Automations

 

[indmolding]

Thanks for the replies!

Basically, what I'm doing is making a connection, then unplugging the computer from the network. If I just leave the computer unplugged, and try CheckConnection(true), the computer hangs forever (or at least 10 minutes, which is all the patience I had).

If I plug it back in after just a few seconds, it seems to reconnect just fine. Where is this hanging? In Oracle or DOA?

 

[Marco Kalter]

It's probably hanging in Oracle Net code. What happens if you simply execute a dummy select statement when the network is unplugged? Does it also hang?

------------------
Marco Kalter
Allround Automations

 

[indmolding]

Originally posted by mkalter:
It's probably hanging in Oracle Net code. What happens if you simply execute a dummy select statement when the network is unplugged? Does it also hang?

Actually, it looks like its in the Oracle Net code... If I wait until after I'm logged in, I get an External Exception c0000006 error, which is more than I get with the logon. Perhaps they are trapping this error, and never coming back? In any event, it doesn't seem that I can successfully trap that error at logon, but can at query time.

Thanks for your help!

BW

 

[monitorwa]

Hi,
I was having a similar problem where my application would simply stop and display and error message if it failed to logon. As my application was scheduled I didnt want this to happen so by adding a timer which starts just before I try and logon. I allow 20 seconds or so for the logon. If it does logon it dissables the timer otherwise the timer will activate its ontime event which in my case simply closes the program so the schedule will start it next time.
dont know if this helps but thought I would throw it in there

regards

Dave