Forum OpenACS Q&A: Obtaining the real DB error

I'm interested in obtaining (programatically) the real reason for a DB insert failure. For example, that a duplicate key insert was attempted. I have named constraints so I can parse the error string to intuit what the error was. Is there a way to obtain this information?

Randy

Thank you, Jade. I am able to see the error. I just need to program responses to DB errors. There must be a method to access the real error. Do folks just throw up a generic "there was an error" message and disregard dealing with it?

For example, there is a table defined with a unique constraint on the name column, which is text. A form is filled out with ad_form and submitted. A record insert is attempted with a duplicate name. An error is generated at the pl/pgsql level  when the insert is performed since the unique constraint is violated. The insert query actually utilizes the xql mechanism and is performed by a call to a defined __new pl/pgsql function, so I do have a function where I can react to errors very close to the DB and possibly pass the error details back to the caller.

In this simple example, without knowing the actual DB error, I might just blindly guess that this particular constraint has been violated and inform the user to change the name. My guess could definitely be wrong. Bad Solution...

Add another constraint to the same table and the guessing approach is not valid at all. Even with just the single constraint, the cause of a DB error could be completely unrelated to it.

I could retrieve all the names of the objects (or cache all the names) and verify the constraint is honored before attempting the __new call. Another (really) Bad Solution...

I know that the cause of a DB error is known at the pl/pgsql level. I'm just looking for a general way to retrieve this information so that I can deal with the cause programatically, in my tcl program... for error control and recovery. Is there no interface or mechanism available to retrieve these details? I have seen no examples of this if it exists.

For what it's worth, the table is a subtyped acs_object and I've followed the standard convention of defining *__{new,delete,name} pl/pgsql functions and used define_function_args on each.

Thanks for taking the time to help.

Randy

Insert this after line 410 in nspostgres.c, i added Ns_DbSetException specifically for this purpose.

nspostgres.c:410
    default:
    Ns_Log(Error, "%s: result status: %d message: %s", asfuncname,
              PQresultStatus(nsConn->res), PQerrorMessage(nsConn->conn));
    Ns_DbSetException(handle,"ERROR",PQerrorMessage(nsConn->conn));
        return NS_ERROR;
    }

Don, are you reading this thread?

I see, from a google search, that you and Lamar had quite a lengthy discussion regarding propagating db error indications and information through the postgres driver (thread in acs-pg-dev in March 2000: http://www.geocrawler.com/mail/thread.php3?subject=%5BAcs-pg-dev%5D+New+nspostgres.c+in+AOLserver+CVS&list=684).
I'm wondering if there are some hooks at the tcl level where I could access this information to use in dealing with exceptions?

Randy