Re: Meaningful Exception handling

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
>