You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Deyan Tsvetanov <de...@flexbrix.com> on 2010/07/19 14:43:59 UTC
Entity Engine: GenericDuplicateKeyException
Hi guys,
there is a problem in entity engine which I would like to fix.
Reproduction:
I am trying to create a party with a duplicate partyId.
In general I thought I have to expect a GenericDuplicateKeyException.
However the exception that I get is a regular GenericEntityException
with a nested GenericDateSourceException one with nested PSQLException
inside. The exception message is: ERROR: duplicate key value violates
unique constraint "pk_party"
I believe it should be expected that a
GenericDuplicateKeyException will be thrown in case of a duplicate
primary key.
The solution for that would be to handle not only SQLException in org.ofbiz.entity.jdbc.SQLProcessor
but it's subclasses as well and throw the corresponding entity engine exceptions.
So the code:
} catch (SQLException sqle) {
throw new GenericDataSourceException("SQL Exception while executing the following:" + sql, sqle);
}
would be changed to :
} catch ( SQLIntegrityConstraintViolationException icvEx ) {
throw new GenericDuplicateKeyException( "Duplicate key exception while executing the following: " + sql , icvEx );
} catch (SQLException sqle) {
throw new GenericDataSourceException("SQL Exception while executing the following:" + sql, sqle);
}
on all occurances of catching a SQLException where insert or update is executed.
-- deyan
Re: Entity Engine: GenericDuplicateKeyException
Posted by Deyan Tsvetanov <de...@flexbrix.com>.
https://issues.apache.org/jira/browse/OFBIZ-3870
On Tue, 2010-07-20 at 09:06 +1200, Scott Gray wrote:
> Also worth noting is that a SQLIntegrityConstraintViolationException is not necessarily indicative of a duplicate primary key error, it could be also be a foreign or unique key violation.
>
> Regards
> Scott
>
> HotWax Media
> http://www.hotwaxmedia.com
>
> On 20/07/2010, at 1:46 AM, Adrian Crum wrote:
>
> > PreparedStatement.execute throws SQLException:
> >
> > http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
> >
> > If a database vendor chooses to add meaning to that exception by using a subclass of SQLException, then they are welcome to do so - but they are not *required* to do so.
> >
> > SQLIntegrityConstraintViolationException was added in Java 6. Any JDBC drivers written before Java 6 will not use it.
> >
> > -Adrian
> >
> > --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
> >
> >> From: Deyan Tsvetanov <de...@flexbrix.com>
> >> Subject: Re: Entity Engine: GenericDuplicateKeyException
> >> To: user@ofbiz.apache.org
> >> Date: Monday, July 19, 2010, 6:22 AM
> >> The benefit here would be that
> >> developers will not have to invoke
> >> delegator.findOne() to check if a record with the same ID
> >> exists before
> >> trying create a new one. Saves one database call,
> >> postresql, mysql,
> >> oracle and mssql support it. If other databases or drivers
> >> do not than
> >> their drivers should not be labeled as JDBC compatible :)
> >>
> >> Anyway if the code bellow will catch the SQLException and
> >> re-throw
> >> GenericEntityException if the current DB driver is buggy
> >> and does not
> >> throw SQLIntegrityConstraintViolationException.
> >>
> >> -- Deyan
> >>
> >> On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
> >>> I agree that better mapping of SQL exception causes
> >> would be helpful in debugging and it could help in certain
> >> algorithms. For example, testing for a primary key violation
> >> could be used as an alternate way to get the next invoice
> >> number.
> >>>
> >>> The problem with using
> >> SQLIntegrityConstraintViolationException is that not all
> >> JDBC drivers will support it, so it might not get caught.
> >>>
> >>> -Adrian
> >>>
> >>> --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com>
> >> wrote:
> >>>
> >>>> From: Deyan Tsvetanov <de...@flexbrix.com>
> >>>> Subject: Entity Engine:
> >> GenericDuplicateKeyException
> >>>> To: user@ofbiz.apache.org
> >>>> Date: Monday, July 19, 2010, 5:43 AM
> >>>>
> >>>> Hi guys,
> >>>>
> >>>> there is a problem in entity engine which I would
> >> like to
> >>>> fix.
> >>>>
> >>>> Reproduction:
> >>>>
> >>>> I am trying to create a party with a duplicate
> >> partyId.
> >>>>
> >>>> In general I thought I have to expect a
> >>>> GenericDuplicateKeyException.
> >>>>
> >>>> However the exception that I get is a regular
> >>>> GenericEntityException
> >>>> with a nested GenericDateSourceException one with
> >> nested
> >>>> PSQLException
> >>>> inside. The exception message is: ERROR:
> >> duplicate
> >>>> key value violates
> >>>> unique constraint "pk_party"
> >>>>
> >>>> I believe it should be expected that a
> >>>> GenericDuplicateKeyException will be thrown in
> >> case of a
> >>>> duplicate
> >>>> primary key.
> >>>>
> >>>>
> >>>>
> >>>> The solution for that would be to handle not
> >> only
> >>>> SQLException in
> >> org.ofbiz.entity.jdbc.SQLProcessor
> >>>> but it's subclasses as well and throw the
> >> corresponding
> >>>> entity engine exceptions.
> >>>>
> >>>> So the code:
> >>>>
> >>>> } catch (SQLException sqle) {
> >>>> throw
> >> new
> >>>> GenericDataSourceException("SQL Exception while
> >> executing
> >>>> the following:" + sql, sqle);
> >>>> }
> >>>>
> >>>> would be changed to :
> >>>>
> >>>>
> >>>> } catch (
> >> SQLIntegrityConstraintViolationException icvEx )
> >>>> {
> >>>> throw new
> >> GenericDuplicateKeyException(
> >>>> "Duplicate key exception while executing the
> >> following: " +
> >>>> sql , icvEx );
> >>>> } catch (SQLException sqle) {
> >>>> throw
> >> new
> >>>> GenericDataSourceException("SQL Exception while
> >> executing
> >>>> the following:" + sql, sqle);
> >>>> }
> >>>>
> >>>> on all occurances of catching a SQLException
> >> where insert
> >>>> or update is executed.
> >>>>
> >>>> -- deyan
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >
> >
> >
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Scott Gray <sc...@hotwaxmedia.com>.
Also worth noting is that a SQLIntegrityConstraintViolationException is not necessarily indicative of a duplicate primary key error, it could be also be a foreign or unique key violation.
Regards
Scott
HotWax Media
http://www.hotwaxmedia.com
On 20/07/2010, at 1:46 AM, Adrian Crum wrote:
> PreparedStatement.execute throws SQLException:
>
> http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
>
> If a database vendor chooses to add meaning to that exception by using a subclass of SQLException, then they are welcome to do so - but they are not *required* to do so.
>
> SQLIntegrityConstraintViolationException was added in Java 6. Any JDBC drivers written before Java 6 will not use it.
>
> -Adrian
>
> --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
>
>> From: Deyan Tsvetanov <de...@flexbrix.com>
>> Subject: Re: Entity Engine: GenericDuplicateKeyException
>> To: user@ofbiz.apache.org
>> Date: Monday, July 19, 2010, 6:22 AM
>> The benefit here would be that
>> developers will not have to invoke
>> delegator.findOne() to check if a record with the same ID
>> exists before
>> trying create a new one. Saves one database call,
>> postresql, mysql,
>> oracle and mssql support it. If other databases or drivers
>> do not than
>> their drivers should not be labeled as JDBC compatible :)
>>
>> Anyway if the code bellow will catch the SQLException and
>> re-throw
>> GenericEntityException if the current DB driver is buggy
>> and does not
>> throw SQLIntegrityConstraintViolationException.
>>
>> -- Deyan
>>
>> On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
>>> I agree that better mapping of SQL exception causes
>> would be helpful in debugging and it could help in certain
>> algorithms. For example, testing for a primary key violation
>> could be used as an alternate way to get the next invoice
>> number.
>>>
>>> The problem with using
>> SQLIntegrityConstraintViolationException is that not all
>> JDBC drivers will support it, so it might not get caught.
>>>
>>> -Adrian
>>>
>>> --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com>
>> wrote:
>>>
>>>> From: Deyan Tsvetanov <de...@flexbrix.com>
>>>> Subject: Entity Engine:
>> GenericDuplicateKeyException
>>>> To: user@ofbiz.apache.org
>>>> Date: Monday, July 19, 2010, 5:43 AM
>>>>
>>>> Hi guys,
>>>>
>>>> there is a problem in entity engine which I would
>> like to
>>>> fix.
>>>>
>>>> Reproduction:
>>>>
>>>> I am trying to create a party with a duplicate
>> partyId.
>>>>
>>>> In general I thought I have to expect a
>>>> GenericDuplicateKeyException.
>>>>
>>>> However the exception that I get is a regular
>>>> GenericEntityException
>>>> with a nested GenericDateSourceException one with
>> nested
>>>> PSQLException
>>>> inside. The exception message is: ERROR:
>> duplicate
>>>> key value violates
>>>> unique constraint "pk_party"
>>>>
>>>> I believe it should be expected that a
>>>> GenericDuplicateKeyException will be thrown in
>> case of a
>>>> duplicate
>>>> primary key.
>>>>
>>>>
>>>>
>>>> The solution for that would be to handle not
>> only
>>>> SQLException in
>> org.ofbiz.entity.jdbc.SQLProcessor
>>>> but it's subclasses as well and throw the
>> corresponding
>>>> entity engine exceptions.
>>>>
>>>> So the code:
>>>>
>>>> } catch (SQLException sqle) {
>>>> throw
>> new
>>>> GenericDataSourceException("SQL Exception while
>> executing
>>>> the following:" + sql, sqle);
>>>> }
>>>>
>>>> would be changed to :
>>>>
>>>>
>>>> } catch (
>> SQLIntegrityConstraintViolationException icvEx )
>>>> {
>>>> throw new
>> GenericDuplicateKeyException(
>>>> "Duplicate key exception while executing the
>> following: " +
>>>> sql , icvEx );
>>>> } catch (SQLException sqle) {
>>>> throw
>> new
>>>> GenericDataSourceException("SQL Exception while
>> executing
>>>> the following:" + sql, sqle);
>>>> }
>>>>
>>>> on all occurances of catching a SQLException
>> where insert
>>>> or update is executed.
>>>>
>>>> -- deyan
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Deyan Tsvetanov <de...@flexbrix.com>.
ok ,
thanks :)
On Mon, 2010-07-19 at 08:27 -0700, Adrian Crum wrote:
> Then create a Jira issue and attach a patch.
>
> We might need to support both specs by having them specified in the
> entitytengine datasource element.
>
> -Adrian
>
> On 7/19/2010 8:19 AM, Deyan Tsvetanov wrote:
> > Right,
> >
> > before that we had to use the sql state to check the type of the
> > SQLException.
> >
> > Still sql state has 2 specs - xopen and sql2003 identifying the type of
> > the error. Entity Engine should support that level of abstraction and
> > throw the DuplicateKeyException.
> >
> > I am willing to implement it because I actually need it :)
> >
> >
> > -- deyan
> >
> > On Mon, 2010-07-19 at 06:46 -0700, Adrian Crum wrote:
> >> PreparedStatement.execute throws SQLException:
> >>
> >> http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
> >>
> >> If a database vendor chooses to add meaning to that exception by using a subclass of SQLException, then they are welcome to do so - but they are not *required* to do so.
> >>
> >> SQLIntegrityConstraintViolationException was added in Java 6. Any JDBC drivers written before Java 6 will not use it.
> >>
> >> -Adrian
> >>
> >> --- On Mon, 7/19/10, Deyan Tsvetanov<de...@flexbrix.com> wrote:
> >>
> >>> From: Deyan Tsvetanov<de...@flexbrix.com>
> >>> Subject: Re: Entity Engine: GenericDuplicateKeyException
> >>> To: user@ofbiz.apache.org
> >>> Date: Monday, July 19, 2010, 6:22 AM
> >>> The benefit here would be that
> >>> developers will not have to invoke
> >>> delegator.findOne() to check if a record with the same ID
> >>> exists before
> >>> trying create a new one. Saves one database call,
> >>> postresql, mysql,
> >>> oracle and mssql support it. If other databases or drivers
> >>> do not than
> >>> their drivers should not be labeled as JDBC compatible :)
> >>>
> >>> Anyway if the code bellow will catch the SQLException and
> >>> re-throw
> >>> GenericEntityException if the current DB driver is buggy
> >>> and does not
> >>> throw SQLIntegrityConstraintViolationException.
> >>>
> >>> -- Deyan
> >>>
> >>> On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
> >>>> I agree that better mapping of SQL exception causes
> >>> would be helpful in debugging and it could help in certain
> >>> algorithms. For example, testing for a primary key violation
> >>> could be used as an alternate way to get the next invoice
> >>> number.
> >>>>
> >>>> The problem with using
> >>> SQLIntegrityConstraintViolationException is that not all
> >>> JDBC drivers will support it, so it might not get caught.
> >>>>
> >>>> -Adrian
> >>>>
> >>>> --- On Mon, 7/19/10, Deyan Tsvetanov<de...@flexbrix.com>
> >>> wrote:
> >>>>
> >>>>> From: Deyan Tsvetanov<de...@flexbrix.com>
> >>>>> Subject: Entity Engine:
> >>> GenericDuplicateKeyException
> >>>>> To: user@ofbiz.apache.org
> >>>>> Date: Monday, July 19, 2010, 5:43 AM
> >>>>>
> >>>>> Hi guys,
> >>>>>
> >>>>> there is a problem in entity engine which I would
> >>> like to
> >>>>> fix.
> >>>>>
> >>>>> Reproduction:
> >>>>>
> >>>>> I am trying to create a party with a duplicate
> >>> partyId.
> >>>>>
> >>>>> In general I thought I have to expect a
> >>>>> GenericDuplicateKeyException.
> >>>>>
> >>>>> However the exception that I get is a regular
> >>>>> GenericEntityException
> >>>>> with a nested GenericDateSourceException one with
> >>> nested
> >>>>> PSQLException
> >>>>> inside. The exception message is: ERROR:
> >>> duplicate
> >>>>> key value violates
> >>>>> unique constraint "pk_party"
> >>>>>
> >>>>> I believe it should be expected that a
> >>>>> GenericDuplicateKeyException will be thrown in
> >>> case of a
> >>>>> duplicate
> >>>>> primary key.
> >>>>>
> >>>>>
> >>>>>
> >>>>> The solution for that would be to handle not
> >>> only
> >>>>> SQLException in
> >>> org.ofbiz.entity.jdbc.SQLProcessor
> >>>>> but it's subclasses as well and throw the
> >>> corresponding
> >>>>> entity engine exceptions.
> >>>>>
> >>>>> So the code:
> >>>>>
> >>>>> } catch (SQLException sqle) {
> >>>>> throw
> >>> new
> >>>>> GenericDataSourceException("SQL Exception while
> >>> executing
> >>>>> the following:" + sql, sqle);
> >>>>> }
> >>>>>
> >>>>> would be changed to :
> >>>>>
> >>>>>
> >>>>> } catch (
> >>> SQLIntegrityConstraintViolationException icvEx )
> >>>>> {
> >>>>> throw new
> >>> GenericDuplicateKeyException(
> >>>>> "Duplicate key exception while executing the
> >>> following: " +
> >>>>> sql , icvEx );
> >>>>> } catch (SQLException sqle) {
> >>>>> throw
> >>> new
> >>>>> GenericDataSourceException("SQL Exception while
> >>> executing
> >>>>> the following:" + sql, sqle);
> >>>>> }
> >>>>>
> >>>>> on all occurances of catching a SQLException
> >>> where insert
> >>>>> or update is executed.
> >>>>>
> >>>>> -- deyan
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >
> >
> >
Re: Entity Engine: GenericDuplicateKeyException
Posted by Adrian Crum <ad...@hlmksw.com>.
Then create a Jira issue and attach a patch.
We might need to support both specs by having them specified in the
entitytengine datasource element.
-Adrian
On 7/19/2010 8:19 AM, Deyan Tsvetanov wrote:
> Right,
>
> before that we had to use the sql state to check the type of the
> SQLException.
>
> Still sql state has 2 specs - xopen and sql2003 identifying the type of
> the error. Entity Engine should support that level of abstraction and
> throw the DuplicateKeyException.
>
> I am willing to implement it because I actually need it :)
>
>
> -- deyan
>
> On Mon, 2010-07-19 at 06:46 -0700, Adrian Crum wrote:
>> PreparedStatement.execute throws SQLException:
>>
>> http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
>>
>> If a database vendor chooses to add meaning to that exception by using a subclass of SQLException, then they are welcome to do so - but they are not *required* to do so.
>>
>> SQLIntegrityConstraintViolationException was added in Java 6. Any JDBC drivers written before Java 6 will not use it.
>>
>> -Adrian
>>
>> --- On Mon, 7/19/10, Deyan Tsvetanov<de...@flexbrix.com> wrote:
>>
>>> From: Deyan Tsvetanov<de...@flexbrix.com>
>>> Subject: Re: Entity Engine: GenericDuplicateKeyException
>>> To: user@ofbiz.apache.org
>>> Date: Monday, July 19, 2010, 6:22 AM
>>> The benefit here would be that
>>> developers will not have to invoke
>>> delegator.findOne() to check if a record with the same ID
>>> exists before
>>> trying create a new one. Saves one database call,
>>> postresql, mysql,
>>> oracle and mssql support it. If other databases or drivers
>>> do not than
>>> their drivers should not be labeled as JDBC compatible :)
>>>
>>> Anyway if the code bellow will catch the SQLException and
>>> re-throw
>>> GenericEntityException if the current DB driver is buggy
>>> and does not
>>> throw SQLIntegrityConstraintViolationException.
>>>
>>> -- Deyan
>>>
>>> On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
>>>> I agree that better mapping of SQL exception causes
>>> would be helpful in debugging and it could help in certain
>>> algorithms. For example, testing for a primary key violation
>>> could be used as an alternate way to get the next invoice
>>> number.
>>>>
>>>> The problem with using
>>> SQLIntegrityConstraintViolationException is that not all
>>> JDBC drivers will support it, so it might not get caught.
>>>>
>>>> -Adrian
>>>>
>>>> --- On Mon, 7/19/10, Deyan Tsvetanov<de...@flexbrix.com>
>>> wrote:
>>>>
>>>>> From: Deyan Tsvetanov<de...@flexbrix.com>
>>>>> Subject: Entity Engine:
>>> GenericDuplicateKeyException
>>>>> To: user@ofbiz.apache.org
>>>>> Date: Monday, July 19, 2010, 5:43 AM
>>>>>
>>>>> Hi guys,
>>>>>
>>>>> there is a problem in entity engine which I would
>>> like to
>>>>> fix.
>>>>>
>>>>> Reproduction:
>>>>>
>>>>> I am trying to create a party with a duplicate
>>> partyId.
>>>>>
>>>>> In general I thought I have to expect a
>>>>> GenericDuplicateKeyException.
>>>>>
>>>>> However the exception that I get is a regular
>>>>> GenericEntityException
>>>>> with a nested GenericDateSourceException one with
>>> nested
>>>>> PSQLException
>>>>> inside. The exception message is: ERROR:
>>> duplicate
>>>>> key value violates
>>>>> unique constraint "pk_party"
>>>>>
>>>>> I believe it should be expected that a
>>>>> GenericDuplicateKeyException will be thrown in
>>> case of a
>>>>> duplicate
>>>>> primary key.
>>>>>
>>>>>
>>>>>
>>>>> The solution for that would be to handle not
>>> only
>>>>> SQLException in
>>> org.ofbiz.entity.jdbc.SQLProcessor
>>>>> but it's subclasses as well and throw the
>>> corresponding
>>>>> entity engine exceptions.
>>>>>
>>>>> So the code:
>>>>>
>>>>> } catch (SQLException sqle) {
>>>>> throw
>>> new
>>>>> GenericDataSourceException("SQL Exception while
>>> executing
>>>>> the following:" + sql, sqle);
>>>>> }
>>>>>
>>>>> would be changed to :
>>>>>
>>>>>
>>>>> } catch (
>>> SQLIntegrityConstraintViolationException icvEx )
>>>>> {
>>>>> throw new
>>> GenericDuplicateKeyException(
>>>>> "Duplicate key exception while executing the
>>> following: " +
>>>>> sql , icvEx );
>>>>> } catch (SQLException sqle) {
>>>>> throw
>>> new
>>>>> GenericDataSourceException("SQL Exception while
>>> executing
>>>>> the following:" + sql, sqle);
>>>>> }
>>>>>
>>>>> on all occurances of catching a SQLException
>>> where insert
>>>>> or update is executed.
>>>>>
>>>>> -- deyan
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Deyan Tsvetanov <de...@flexbrix.com>.
Right,
before that we had to use the sql state to check the type of the
SQLException.
Still sql state has 2 specs - xopen and sql2003 identifying the type of
the error. Entity Engine should support that level of abstraction and
throw the DuplicateKeyException.
I am willing to implement it because I actually need it :)
-- deyan
On Mon, 2010-07-19 at 06:46 -0700, Adrian Crum wrote:
> PreparedStatement.execute throws SQLException:
>
> http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
>
> If a database vendor chooses to add meaning to that exception by using a subclass of SQLException, then they are welcome to do so - but they are not *required* to do so.
>
> SQLIntegrityConstraintViolationException was added in Java 6. Any JDBC drivers written before Java 6 will not use it.
>
> -Adrian
>
> --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
>
> > From: Deyan Tsvetanov <de...@flexbrix.com>
> > Subject: Re: Entity Engine: GenericDuplicateKeyException
> > To: user@ofbiz.apache.org
> > Date: Monday, July 19, 2010, 6:22 AM
> > The benefit here would be that
> > developers will not have to invoke
> > delegator.findOne() to check if a record with the same ID
> > exists before
> > trying create a new one. Saves one database call,
> > postresql, mysql,
> > oracle and mssql support it. If other databases or drivers
> > do not than
> > their drivers should not be labeled as JDBC compatible :)
> >
> > Anyway if the code bellow will catch the SQLException and
> > re-throw
> > GenericEntityException if the current DB driver is buggy
> > and does not
> > throw SQLIntegrityConstraintViolationException.
> >
> > -- Deyan
> >
> > On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
> > > I agree that better mapping of SQL exception causes
> > would be helpful in debugging and it could help in certain
> > algorithms. For example, testing for a primary key violation
> > could be used as an alternate way to get the next invoice
> > number.
> > >
> > > The problem with using
> > SQLIntegrityConstraintViolationException is that not all
> > JDBC drivers will support it, so it might not get caught.
> > >
> > > -Adrian
> > >
> > > --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com>
> > wrote:
> > >
> > > > From: Deyan Tsvetanov <de...@flexbrix.com>
> > > > Subject: Entity Engine:
> > GenericDuplicateKeyException
> > > > To: user@ofbiz.apache.org
> > > > Date: Monday, July 19, 2010, 5:43 AM
> > > >
> > > > Hi guys,
> > > >
> > > > there is a problem in entity engine which I would
> > like to
> > > > fix.
> > > >
> > > > Reproduction:
> > > >
> > > > I am trying to create a party with a duplicate
> > partyId.
> > > >
> > > > In general I thought I have to expect a
> > > > GenericDuplicateKeyException.
> > > >
> > > > However the exception that I get is a regular
> > > > GenericEntityException
> > > > with a nested GenericDateSourceException one with
> > nested
> > > > PSQLException
> > > > inside. The exception message is: ERROR:
> > duplicate
> > > > key value violates
> > > > unique constraint "pk_party"
> > > >
> > > > I believe it should be expected that a
> > > > GenericDuplicateKeyException will be thrown in
> > case of a
> > > > duplicate
> > > > primary key.
> > > >
> > > >
> > > >
> > > > The solution for that would be to handle not
> > only
> > > > SQLException in
> > org.ofbiz.entity.jdbc.SQLProcessor
> > > > but it's subclasses as well and throw the
> > corresponding
> > > > entity engine exceptions.
> > > >
> > > > So the code:
> > > >
> > > > } catch (SQLException sqle) {
> > > > throw
> > new
> > > > GenericDataSourceException("SQL Exception while
> > executing
> > > > the following:" + sql, sqle);
> > > > }
> > > >
> > > > would be changed to :
> > > >
> > > >
> > > > } catch (
> > SQLIntegrityConstraintViolationException icvEx )
> > > > {
> > > > throw new
> > GenericDuplicateKeyException(
> > > > "Duplicate key exception while executing the
> > following: " +
> > > > sql , icvEx );
> > > > } catch (SQLException sqle) {
> > > > throw
> > new
> > > > GenericDataSourceException("SQL Exception while
> > executing
> > > > the following:" + sql, sqle);
> > > > }
> > > >
> > > > on all occurances of catching a SQLException
> > where insert
> > > > or update is executed.
> > > >
> > > > -- deyan
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Adrian Crum <ad...@yahoo.com>.
PreparedStatement.execute throws SQLException:
http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/api/java/sql/PreparedStatement.html#execute()
If a database vendor chooses to add meaning to that exception by using a subclass of SQLException, then they are welcome to do so - but they are not *required* to do so.
SQLIntegrityConstraintViolationException was added in Java 6. Any JDBC drivers written before Java 6 will not use it.
-Adrian
--- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
> From: Deyan Tsvetanov <de...@flexbrix.com>
> Subject: Re: Entity Engine: GenericDuplicateKeyException
> To: user@ofbiz.apache.org
> Date: Monday, July 19, 2010, 6:22 AM
> The benefit here would be that
> developers will not have to invoke
> delegator.findOne() to check if a record with the same ID
> exists before
> trying create a new one. Saves one database call,
> postresql, mysql,
> oracle and mssql support it. If other databases or drivers
> do not than
> their drivers should not be labeled as JDBC compatible :)
>
> Anyway if the code bellow will catch the SQLException and
> re-throw
> GenericEntityException if the current DB driver is buggy
> and does not
> throw SQLIntegrityConstraintViolationException.
>
> -- Deyan
>
> On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
> > I agree that better mapping of SQL exception causes
> would be helpful in debugging and it could help in certain
> algorithms. For example, testing for a primary key violation
> could be used as an alternate way to get the next invoice
> number.
> >
> > The problem with using
> SQLIntegrityConstraintViolationException is that not all
> JDBC drivers will support it, so it might not get caught.
> >
> > -Adrian
> >
> > --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com>
> wrote:
> >
> > > From: Deyan Tsvetanov <de...@flexbrix.com>
> > > Subject: Entity Engine:
> GenericDuplicateKeyException
> > > To: user@ofbiz.apache.org
> > > Date: Monday, July 19, 2010, 5:43 AM
> > >
> > > Hi guys,
> > >
> > > there is a problem in entity engine which I would
> like to
> > > fix.
> > >
> > > Reproduction:
> > >
> > > I am trying to create a party with a duplicate
> partyId.
> > >
> > > In general I thought I have to expect a
> > > GenericDuplicateKeyException.
> > >
> > > However the exception that I get is a regular
> > > GenericEntityException
> > > with a nested GenericDateSourceException one with
> nested
> > > PSQLException
> > > inside. The exception message is: ERROR:
> duplicate
> > > key value violates
> > > unique constraint "pk_party"
> > >
> > > I believe it should be expected that a
> > > GenericDuplicateKeyException will be thrown in
> case of a
> > > duplicate
> > > primary key.
> > >
> > >
> > >
> > > The solution for that would be to handle not
> only
> > > SQLException in
> org.ofbiz.entity.jdbc.SQLProcessor
> > > but it's subclasses as well and throw the
> corresponding
> > > entity engine exceptions.
> > >
> > > So the code:
> > >
> > > } catch (SQLException sqle) {
> > > throw
> new
> > > GenericDataSourceException("SQL Exception while
> executing
> > > the following:" + sql, sqle);
> > > }
> > >
> > > would be changed to :
> > >
> > >
> > > } catch (
> SQLIntegrityConstraintViolationException icvEx )
> > > {
> > > throw new
> GenericDuplicateKeyException(
> > > "Duplicate key exception while executing the
> following: " +
> > > sql , icvEx );
> > > } catch (SQLException sqle) {
> > > throw
> new
> > > GenericDataSourceException("SQL Exception while
> executing
> > > the following:" + sql, sqle);
> > > }
> > >
> > > on all occurances of catching a SQLException
> where insert
> > > or update is executed.
> > >
> > > -- deyan
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Deyan Tsvetanov <de...@flexbrix.com>.
The benefit here would be that developers will not have to invoke
delegator.findOne() to check if a record with the same ID exists before
trying create a new one. Saves one database call, postresql, mysql,
oracle and mssql support it. If other databases or drivers do not than
their drivers should not be labeled as JDBC compatible :)
Anyway if the code bellow will catch the SQLException and re-throw
GenericEntityException if the current DB driver is buggy and does not
throw SQLIntegrityConstraintViolationException.
-- Deyan
On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
> I agree that better mapping of SQL exception causes would be helpful in debugging and it could help in certain algorithms. For example, testing for a primary key violation could be used as an alternate way to get the next invoice number.
>
> The problem with using SQLIntegrityConstraintViolationException is that not all JDBC drivers will support it, so it might not get caught.
>
> -Adrian
>
> --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
>
> > From: Deyan Tsvetanov <de...@flexbrix.com>
> > Subject: Entity Engine: GenericDuplicateKeyException
> > To: user@ofbiz.apache.org
> > Date: Monday, July 19, 2010, 5:43 AM
> >
> > Hi guys,
> >
> > there is a problem in entity engine which I would like to
> > fix.
> >
> > Reproduction:
> >
> > I am trying to create a party with a duplicate partyId.
> >
> > In general I thought I have to expect a
> > GenericDuplicateKeyException.
> >
> > However the exception that I get is a regular
> > GenericEntityException
> > with a nested GenericDateSourceException one with nested
> > PSQLException
> > inside. The exception message is: ERROR: duplicate
> > key value violates
> > unique constraint "pk_party"
> >
> > I believe it should be expected that a
> > GenericDuplicateKeyException will be thrown in case of a
> > duplicate
> > primary key.
> >
> >
> >
> > The solution for that would be to handle not only
> > SQLException in org.ofbiz.entity.jdbc.SQLProcessor
> > but it's subclasses as well and throw the corresponding
> > entity engine exceptions.
> >
> > So the code:
> >
> > } catch (SQLException sqle) {
> > throw new
> > GenericDataSourceException("SQL Exception while executing
> > the following:" + sql, sqle);
> > }
> >
> > would be changed to :
> >
> >
> > } catch ( SQLIntegrityConstraintViolationException icvEx )
> > {
> > throw new GenericDuplicateKeyException(
> > "Duplicate key exception while executing the following: " +
> > sql , icvEx );
> > } catch (SQLException sqle) {
> > throw new
> > GenericDataSourceException("SQL Exception while executing
> > the following:" + sql, sqle);
> > }
> >
> > on all occurances of catching a SQLException where insert
> > or update is executed.
> >
> > -- deyan
> >
> >
> >
> >
> >
> >
>
>
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Deyan Tsvetanov <de...@flexbrix.com>.
Do you know a certain jdbc driver which does not support it ?
-- deyan
On Mon, 2010-07-19 at 06:15 -0700, Adrian Crum wrote:
> I agree that better mapping of SQL exception causes would be helpful in debugging and it could help in certain algorithms. For example, testing for a primary key violation could be used as an alternate way to get the next invoice number.
>
> The problem with using SQLIntegrityConstraintViolationException is that not all JDBC drivers will support it, so it might not get caught.
>
> -Adrian
>
> --- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
>
> > From: Deyan Tsvetanov <de...@flexbrix.com>
> > Subject: Entity Engine: GenericDuplicateKeyException
> > To: user@ofbiz.apache.org
> > Date: Monday, July 19, 2010, 5:43 AM
> >
> > Hi guys,
> >
> > there is a problem in entity engine which I would like to
> > fix.
> >
> > Reproduction:
> >
> > I am trying to create a party with a duplicate partyId.
> >
> > In general I thought I have to expect a
> > GenericDuplicateKeyException.
> >
> > However the exception that I get is a regular
> > GenericEntityException
> > with a nested GenericDateSourceException one with nested
> > PSQLException
> > inside. The exception message is: ERROR: duplicate
> > key value violates
> > unique constraint "pk_party"
> >
> > I believe it should be expected that a
> > GenericDuplicateKeyException will be thrown in case of a
> > duplicate
> > primary key.
> >
> >
> >
> > The solution for that would be to handle not only
> > SQLException in org.ofbiz.entity.jdbc.SQLProcessor
> > but it's subclasses as well and throw the corresponding
> > entity engine exceptions.
> >
> > So the code:
> >
> > } catch (SQLException sqle) {
> > throw new
> > GenericDataSourceException("SQL Exception while executing
> > the following:" + sql, sqle);
> > }
> >
> > would be changed to :
> >
> >
> > } catch ( SQLIntegrityConstraintViolationException icvEx )
> > {
> > throw new GenericDuplicateKeyException(
> > "Duplicate key exception while executing the following: " +
> > sql , icvEx );
> > } catch (SQLException sqle) {
> > throw new
> > GenericDataSourceException("SQL Exception while executing
> > the following:" + sql, sqle);
> > }
> >
> > on all occurances of catching a SQLException where insert
> > or update is executed.
> >
> > -- deyan
> >
> >
> >
> >
> >
> >
>
>
>
Re: Entity Engine: GenericDuplicateKeyException
Posted by Adrian Crum <ad...@yahoo.com>.
I agree that better mapping of SQL exception causes would be helpful in debugging and it could help in certain algorithms. For example, testing for a primary key violation could be used as an alternate way to get the next invoice number.
The problem with using SQLIntegrityConstraintViolationException is that not all JDBC drivers will support it, so it might not get caught.
-Adrian
--- On Mon, 7/19/10, Deyan Tsvetanov <de...@flexbrix.com> wrote:
> From: Deyan Tsvetanov <de...@flexbrix.com>
> Subject: Entity Engine: GenericDuplicateKeyException
> To: user@ofbiz.apache.org
> Date: Monday, July 19, 2010, 5:43 AM
>
> Hi guys,
>
> there is a problem in entity engine which I would like to
> fix.
>
> Reproduction:
>
> I am trying to create a party with a duplicate partyId.
>
> In general I thought I have to expect a
> GenericDuplicateKeyException.
>
> However the exception that I get is a regular
> GenericEntityException
> with a nested GenericDateSourceException one with nested
> PSQLException
> inside. The exception message is: ERROR: duplicate
> key value violates
> unique constraint "pk_party"
>
> I believe it should be expected that a
> GenericDuplicateKeyException will be thrown in case of a
> duplicate
> primary key.
>
>
>
> The solution for that would be to handle not only
> SQLException in org.ofbiz.entity.jdbc.SQLProcessor
> but it's subclasses as well and throw the corresponding
> entity engine exceptions.
>
> So the code:
>
> } catch (SQLException sqle) {
> throw new
> GenericDataSourceException("SQL Exception while executing
> the following:" + sql, sqle);
> }
>
> would be changed to :
>
>
> } catch ( SQLIntegrityConstraintViolationException icvEx )
> {
> throw new GenericDuplicateKeyException(
> "Duplicate key exception while executing the following: " +
> sql , icvEx );
> } catch (SQLException sqle) {
> throw new
> GenericDataSourceException("SQL Exception while executing
> the following:" + sql, sqle);
> }
>
> on all occurances of catching a SQLException where insert
> or update is executed.
>
> -- deyan
>
>
>
>
>
>