Re: Meaningful Exception handling - Mailing list pgsql-jdbc

Is there a way to catch and ignore this kind of errors in stored procedures
(like an emtpy catch block in java)?

> -----Original Message-----
> From: Barry Lind [mailto:barry@xythos.com]
> Sent: Wednesday, April 17, 2002 6:57 PM
> To: nickf@ontko.com
> Cc: AgentM; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Meaningful Exception handling
>
>
> Nick,
>
> *All* database errors have this behavior.  Most but not all
> SQLExceptions are the result of errors occuring in the database.
>
> A duplicate key in index error will result in the entire transaction
> needing to be rolled back.  So what you are proposing to do can't be
> done with postgres.  However the way I work around this
> problem is to do
> the following:
>
> insert into foo (bar) values (?)
> where not exists select * from foo where bar = ?
>
> Inserts of this format will prevent duplicates from being
> inserted.  You
> can even look at the result of the above statement to see the
> number of
> rows affected, and if it is zero (meaning the row was already
> there and
> you didn't insert), you can branch and do an update instead.
>
> thanks,
> --Barry
>
>
>
> Nick Fankhauser wrote:
> > Barry-
> >
> > Is this true of *all* SQLExceptions, or are some at a lower
> level that won't
> > cause a transaction abort?
> >
> > My concern is that I'm contemplating a load process in
> which some inserts
> > may be duplicated, and I was planning on depending on the
> unique index to
> > kick these out. If I'm committing after every load instead
> of after every
> > insert, would I lose all transactions up to the point where
> the unique
> > constraint stopped an insert? Is there any way to tell PostgreSQL to
> > continue on errors?
> >
> > Thanks
> >
> > -Nick
> >
> >
> >
> >>-----Original Message-----
> >>From: pgsql-jdbc-owner@postgresql.org
> >>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind
> >>Sent: Wednesday, April 17, 2002 12:23 AM
> >>To: AgentM
> >>Cc: pgsql-jdbc@postgresql.org
> >>Subject: Re: [JDBC] Meaningful Exception handling
> >>
> >>
> >>First off you do know that you can't catch an error in postgres and
> >>continue on, don't you?  Any error in postgres aborts the current
> >>transaction.  Thus after an error you must rollback and start a new
> >>transaction, and then you need to redo all the work up to
> the point of
> >>the error.  This isn't generally something you can easily do when
> >>catching an exception.
> >>
> >>Answering your specific question:  comparing the string
> error message is
> >>currently the only way to determine the error.  There is a
> TODO item for
> >>the database to support error codes, and when the database adds that
> >>support the jdbc driver will as well.
> >>
> >>thanks,
> >>--Barry
> >>
> >>
> >>
> >>AgentM wrote:
> >>
> >>>First off, thanks for the quick help with my previous
> simple problem.
> >>>
> >>>Now I'd like to catch SQLExceptions selectively. For
> example, I have a
> >>>uniquely indexed column in Table A. I would like to insert
> into this
> >>>column and I don't care if the insert fails due to
> duplication. But I
> >>>would want to know if something else went wrong. Is there a way to
> >>>specifically catch and ignore this particular instance of the
> >>>SQLException short of comparing a String to the database's returned
> >>>error message? Are there any docs referring to
> SQLException messages
> >>>thrown? Thanks again.
> >>
> >>
> >>
> >>---------------------------(end of
> broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> >>
> >
> >
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>