Re: Is a right behaviour on inserting a duplicate key?

The behavior is not consist across all DBs' JDBC. The reason I posted the question is that I saw some sample code
from Java.Sun site using the result value to detect whether inserting a duplicate key or not.

I guess that I shall a select statement before a insertion one to find out whether the key exists or not.

Thanks David for sharing your strategy.

Vernon

09/02/2003 12:19:20 PM, "David Wall" <d(dot)wall(at)computer(dot)org> wrote:

>> It seems to me that inserting a duplicate user would result an invalide
>retured value. Instead, the action leads to a SQL
>> expection as
>>
>> ERROR: Cannot insert a duplicate key into unique index pk_signon
>>
>> Can some clarify the 7.3.1 JDBC driver specification or implement in this
>regards?
>
>I think throwing an exception is the correct thing to do and is consistent
>with Oracle. The only downside is that detecting a duplicate key, something
>that seems so ordinary in the database world that one has to wonder why a
>special exception was not devised by JDBC to ensure a portable way to detect
>it. We use some hacked code like the following to do so in our connection
>pool code (obviously, only the public method is invoked by our mainline
>code):
>
> protected boolean isPostgresqlDuplicateKey(SQLException e)
> {
> String msg = e.getMessage();
> if ( msg == null )
> return false;
> return msg.indexOf("duplicate key") > 0;
> }
>
> protected boolean isOracleDuplicateKey(SQLException e)
> {
> return e.getErrorCode() == 1;
> }
>
> public boolean isDuplicateKey(SQLException e)
> {
> if ( e == null )
> return false;
> if ( isOracle() )
> return isOracleDuplicateKey(e);
> return isPostgresqlDuplicateKey(e);
> }
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>