Processing SQL (pro*c) errors: connection failures etc.

We've got a multi-threaded process which uses pro*c for doing queries in each thread. Those threads do an EXEC SQL CONNECT, and remain connected to the database throughout the life of the process.

If the connection to the server goes down for some reason, it will generally result in an sql error (through the sqlca). Question is, how can we tell what errors are related to a server-connection faults, and which are due to transaction or data problems?

At the mo, we just use a function that checks whether the sqlcode value is in one of several ranges as being connection faults, derived mainly from rdbms/mesg/oraus.msg file. Is that really the only way to do it? The allocation of error numbers (I realise it's a "defacto standard") seems to be a dictionary definition of
"ad hoc". There aren't classes or categories of errors, just
"parse errors: 10-75, continued at 1710-1899" or some such.

Is there a better way for our threads to determine they need to re-connect to the database? If the server is shutdown and brought back up, there is one or two "internal fault: please report" errors that happen, which we handle by restarting our process.

I suppose handling must be better with OCI, but originally thought there were some better portability issues in using ESQL. (Not really the case, I guess).

--
Ken Tough Received on Sun Jun 13 1999 - 09:08:58 CDT