Re: JDBC SQLCodes or Error Numbers - Or how to handle errors

Henry,

Here is the format I use:

given table foo with two columns (bar, baz) with bar the primary key:

insert into foo (bar, baz)
select ?,?
where not exists (select 1 from foo where bar = ?)

Then I test the number of rows inserted (if 0, then need to do an update
as the row already exists).

thanks,
--Barry

Henry D. wrote:

> Is there a "better" way to test for duplicate rows than by first do a
> select to see if the key already exists?
> Henry D.
>
> Rene Pijlman wrote:
>
>
>>On Sun, 28 Oct 2001 00:33:07 -0700, you wrote:
>>
>>>I'm used to just doing the insert and catching the SQLCODE -803
>>>on DB2 to know that the row I'm trying to insert already exists.
>>>
>>>Is there an easy way to do this using Postgresql and JDBC?
>>>
>>I'm afraid not.
>>
>>"SQLException.getErrorCode() always returns 0. The PostgreSQL
>>backend currently doesn't provide error codes. This issue is on
>>the TODO list (Admin: 'Allow elog() to return error codes').
>>Once it is fixed in the backend and the FE/BE protocol, it can
>>be fixed in the JDBC driver as well."
>>http://lab.applinet.nl/postgresql-jdbc/#SQLException
>>
>>Regards,
>>René Pijlman <rene(at)lab(dot)applinet(dot)nl>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>