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.wall@computer.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
>