BDE multi-thread error: when encounter ora-12560, cannot restart TDatabase

Question

We have a big program, using BDE + oracle, developed by delphi, We use 3 threads to do DB operations, they are using separated TSession. (1 deamon thread, 1 data collect thread, 1 checking thread)

We use this structure:

TQuery -> TDatabase -> TSession

We use this method to do query:

try
  qu.close;  //TQuery
  qu.sql.clear;
  qu.sql.add('select foo from db_foos');
  qu.open;
except
  on e:Exception do
  begin
    db.close; // db is TDatabase, We use this to auto restart connection when network fail.
    error(e.message);
  end;
end;

the problem is, when our program run more than 24 hours, ora-12560 arise, and our program cannot restart connection, and keep report this error when execute queries.

We had reviewed code, debugged all week long, and still cannot solve it, do you guys encounter the same problem?

edit:

We have this test case:

3 program, each run one thread(with 1 second sleep), and between 0.5 - 1 hour, They all failed at the same time. (3 DB operation, and 1 Indy9 ftp client...)

So I think there is something wrong in BDE or Windows socket... Current I am working on a auto restart program machanism as a workaround.

Answer 1

Apparently it's a common problem where the Oracle client forgets where it's supposed to be connecting. Perhaps one of these will help:

http://www.cryer.co.uk/brian/oracle/ORA12560.htm

http://www.dba-oracle.com/t_ora_12560_tns_protocol_adapter_error.htm

Answer 2

Have a seperate timer-based routine to keep the DB open at all times - that's what we do per my comment above. IE when the Database disconnects, then re-open it immediately, do not rely on the query to auto-open it for you. Use a timer to check periodically if the database is still (really) open. In this way we do not use a seperate thread and avoid cross-thread problems, but in our case we have one connection per client. Each client PC collects data and there are many client PC's in the factory.

In your case you may need to have the DB checking routine within every thread.