Oracle 8.1.6 ProC and error codes , C++
Date: Sat, 21 Jul 2001 21:45:14 GMT
Message-ID: <9eu93g$dd5$04$1_at_news.t-online.com>
According to the ProC programmer's guide (chapter on 'Handling runtime errors'), there are three main ways to handle error conditions caused by embedded SQL statements:
- using the SQLSTATE variable
- using the SQLCODE variable
- using the sqlca.sqlcode variable
Furthermore the chapter says that SQLCODE (option 2) is deprecated, which leaves me with option 1 and option 3. On the sqlca.sqlcode it also says
"<0 Means that Oracle did not execute the statement because of a database, system, ........ Such an error can be fatal..."
This statement is a little bit vague and leaves some questions (especially
how
to differentiate between non-fatal and fatal error conditions). Maybe one of
you could enlighten me a little bit on this issue.
My questions
- Does anyone know of a header file that lists all possible error codes (unfortunately such a file is not provided by Oracle in the ProC standard distribution. At least I haven't found it :-)
[Quoted] Reason: In a statement like the following, I want to use predefined named constants instead
of hard coded integral constants, that is to say
EXEC SQL CONNECT :username IDENTIFIED BY :password;
if (SQLCACODE_USER_NAME_OR_PASSWORD_INVALID == sqlca.sqlcode) { /* handle non recoverable error */ }
instead of
EXEC SQL CONNECT :username IDENTIFIED BY :password;
if (-1017 == sqlca.sqlcode) { /* handle non recoverable error */ }
2. Is there a way to figure out whether or not an error condition is indead fatal ? I mean,
usually my program needs to find out
- if it has been disconnected from the database. In which case I simply want to reconnect
- if an error has occured that is temporary (e.g. "database temporarily not available), and this condition can be fixed by simply executing the same statement again later on, e.g. 30 seconds later
- if an error is fatal in that sense that executing the statement can never be successful
- if an error is fatal but disconnecting from and reconnecting to the database can solve the problem
Example:
EXEC SQL INSERT INTO abc ( a, b, c) VALUES (:a, :b, :c)
if (0 > sqlca.sqlcode)
{
/*pseudo code following*/
ERR_COND_DISCON: The program has been disconnected from the database.
Required action: Reconnect to the database (and maybe do an EXEC SQL ROLLBACK WORK RELEASE before that)
ERR_COND_TEMPORARY: A condition like "No more resources. Try again later"
Required action: Sleep 30 seconds and try again (e.g. do this 10 times)
ERR_COND_FATAL1: e.g. the data provided in a, b, or c is no appropriate for the fields (for simplicity reasons
let's assume :a, :b, and :c are varchars) Required action: write an entry to the log file about the failedinsertion and continue with the next set
ERR_COND_FATAL2: for whatever reason the database cannot accept the insert now (but maybe later on)
Required action: disconnect from the database, reconnect to the data base, and re-execute the INSERT
}
Can anyone explain how to find these "error classes" ? (Yes, the SQLSTATE has classes,
but as I see, the mapping from ORACLE messages to SQLSTATEs is many-to-one, that is
to say, e.g. state "72000" (class 72 = SQL phase error) is returned for about 10,000 different
oracle messages).
3. Where can I find a list of error codes that can be returned by the statements
EXEC SQL CONNECT :username IDENTIFIED BY :password; or
EXEC SQL INSERT INTO abc (a, b, c) VALUES (:a, :b, :c); or
EXEC SQL COMMIT WORK; Thanks
Thomas Received on Sat Jul 21 2001 - 23:45:14 CEST