Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Wed, 10 Dec 2014 14:55:13 +0000 (UTC)
From: "John English (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12760767.1418223140000.120215.1418223313977@Atlassian.JIRA>
In-Reply-To: <JIRA.12760767.1418223140000@Atlassian.JIRA>
References: <JIRA.12760767.1418223140000@Atlassian.JIRA>
 <JIRA.12760767.1418223140729@arcas>
Subject: [jira] [Updated] (DERBY-6779) Provide subclass of SQLException for
 duplicate key insertions
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-6779?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

John English updated DERBY-6779:
--------------------------------
    Description: 
A commonly-occurring situation is to take some special action when an insert fails due to a duplicate key; for example, to update the existing row or to retry the insert with a new key (e.g. mutate "Name" into "Name(1)", "Name(2)" etc. until a unique key is found). At present this requires code similar to:
{noformat}
    try {
       //... insert new row
    }
    catch (SQLException e) {
        if (e.getSQLState().equals(DUPLICATE_KEY)) {
            // ... take recovery action
        }
        else {
            throw e;
        }
    }
{noformat}
It would be more convenient if a subclass of SQLException were used to report this precise error. The SQLIntegrityConstraintViolationException that is currently thrown will also be thrown in other case where a constraint is violated. A new exception subclass for this specific situation would not affect any existing code, and would allow the code above to be simplified to this:
{noformat}
    try {
        //... insert new row
    }
    catch (DuplicateKeyException e) {    // or some suitable name
        // ... take recovery action
    }
{noformat}
This would allow a more elegant, more O-O solution to what is, in my experience, a common use case without having to discriminate based on the value of getSQLState().

  was:
A commonly-occurring situation is to take some special action when an insert fails due to a duplicate key; for example, to update the existing row or to retry the insert with a new key (e.g. mutate "Name" into "Name(1)", "Name(2)" etc. until a unique key is found). At present this requires code similar to:
{noformat}
    try {
       //... insert new row
    }
    catch (SQLException e) {
        if (e.getSQLState().equals(DUPLICATE_KEY)) {
            // ... take recovery action
        }
        else {
            throw e;
        }
    }
{noformat}
It would be more convenient if a subclass of SQLException were used to report this precise error. The SQLIntegrityConstraintViolationException that is currently thrown will also be thrown in other case where a constraint is violated. A new exception subclass for this specific situation would not affect any existing code, and would allow the code above to be simplified to this:
{noformat}
    try {
        //... insert new row
    }
    catch DuplicateKeyException e) {
        // ... take recovery action
    }
{noformat}
This would allow a more elegant, more O-O solution to what is, in my experience, a common use case without having to discriminate based on the value of getSQLState().


> Provide subclass of SQLException for duplicate key insertions
> -------------------------------------------------------------
>
>                 Key: DERBY-6779
>                 URL: https://issues.apache.org/jira/browse/DERBY-6779
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: John English
>            Priority: Minor
>
> A commonly-occurring situation is to take some special action when an insert fails due to a duplicate key; for example, to update the existing row or to retry the insert with a new key (e.g. mutate "Name" into "Name(1)", "Name(2)" etc. until a unique key is found). At present this requires code similar to:
> {noformat}
>     try {
>        //... insert new row
>     }
>     catch (SQLException e) {
>         if (e.getSQLState().equals(DUPLICATE_KEY)) {
>             // ... take recovery action
>         }
>         else {
>             throw e;
>         }
>     }
> {noformat}
> It would be more convenient if a subclass of SQLException were used to report this precise error. The SQLIntegrityConstraintViolationException that is currently thrown will also be thrown in other case where a constraint is violated. A new exception subclass for this specific situation would not affect any existing code, and would allow the code above to be simplified to this:
> {noformat}
>     try {
>         //... insert new row
>     }
>     catch (DuplicateKeyException e) {    // or some suitable name
>         // ... take recovery action
>     }
> {noformat}
> This would allow a more elegant, more O-O solution to what is, in my experience, a common use case without having to discriminate based on the value of getSQLState().


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)