You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Günther Wieser <gw...@creative-it.com> on 2005/02/28 16:39:09 UTC
problem with compound primary key when one key=""
hi,
i found a problem with using a compound primary key (two columns). it
appears only if one of the keys is set to "" (an empty string, NOT!!!!
null).
here's my setup:
ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
repository.xml:
<class-descriptor
class="com.creative_it.webcreator.resources.Property" table="properties">
<field-descriptor id="1" name="key" column="id"
jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
<field-descriptor id="2" name="locale" column="locale"
jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
<field-descriptor id="3" name="value" column="value"
jdbc-type="VARCHAR" />
</class-descriptor>
the database layout:
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | varchar(255) | | PRI | | |
| value | text | YES | | | |
| locale | varchar(255) | | PRI | | |
+--------+--------------+------+-----+---------+-------+
example code:
Property prop = new Property();
prop.setKey("whateverKey");
prop.setLocale("");
prop.setValue("some test value");
broker.beginTransaction();
// this one works if table doesn't have one element with same
key and locale
broker.store(prop);
broker.commitTransaction();
prop.setValue("a new value");
broker.beginTransaction();
// this one fails with the following exception
broker.store(prop);
broker.commitTransaction();
the exception raised is:
org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure while
insert object data for class com.creative_it.webcreator.resources.Property,
PK of the given object is [ key=test.property locale=], object was
com.creative_it.webcreator.resources.Property@5dd582, exception message is
[Duplicate key or integrity constraint violation message from server:
"Duplicate entry 'test.property-' for key 1"]
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAccessImp
l.java:239)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(PersistenceBroker
Impl.java:1642)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl
.java:1557)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl
.java:715)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersi
stenceBroker.java:175)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersi
stenceBroker.java:175)
at
com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(Propert
yTest.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
nner.java:421)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
java:305)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
.java:186)
if i update the database entry with native SQL (e.g. UPDATE properties SET
value="another value" WHERE id="whateverKey" AND locale="") everything works
fine, so i'm pretty sure it is not a database problem or database error.
also everything works fine if "locale" is set to e.g. "de_AT" or anything
else except an empty string.
maybe the broker does something unexpected with the empty "" string? any
ideas?
kr,
guenther
Re: AW: problem with compound primary key when one key=""
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi armin,
i found this comment in BrokerHelper#representsNull:
// TODO: Do we need this check?? String could be nullified, why should
we assume
// it's 'null' on empty string?
so it lokks like not everything is clear here.
jakob
Armin Waibel schrieb:
> Günther Wieser wrote:
>
>> hmm, that's bad news for me. to my understanding that means that there
>> won't
>> be a change of this behaviour very soon (let's say: never)?
>
>
> If you know that an object needs 'update' you could use
> PB.store(obj, ObjectModificationDefaultImpl.UPDATE);
>
>
>> i know a workaround for my implementation (using some default
>> identifier for
>> locale, e.g. "DEFAULT") but that doesn't make me happy as i need to
>> change
>> data PLUS my code to work together in changing data, which makes it very
>> error-prone if somebody misuses the classes provided by my applicationen.
>>
>> by the way: from an sql perspective, an empty string (or varchar) is a
>> valid
>> primary key... (i know this is not a very subtile attempt but who
>> knows ;-)
>> )
>>
>
> I know, the problem is that method BrokerHelper#representsNull(...) is
> used to reduce DB calls and OJB use the rules:
> <snip>
> - If given value is 'null' itself, true will be returned
> - If given value is instance of Number with value 0 and the
> field-descriptor represents a primitive field, true will be returned
> - If given value is instance of String with length 0 and the
> field-descriptor is a primary key, true will be returned
> </snip>
>
> Currently it's not possible to bypass this test. Would it be useful to
> make this check configurable/pluggable?
>
> regards,
> Armin
>
>> kr,
>> guenther
>>
>> --
>> Günther Wieser
>>
>> creative-it
>> Guglgasse 6/1/11/1
>> A-1110 Wien
>> Austria
>> http://www.creative-it.com
>>
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Armin Waibel [mailto:arminw@apache.org] Gesendet: Montag, 28.
>> Februar 2005 20:29
>> An: OJB Users List
>> Betreff: Re: problem with compound primary key when one key=""
>>
>> Hi Günther,
>>
>> > maybe the broker does something unexpected with the empty ""
>> string? any
>>
>>> ideas?
>>
>>
>>
>> When storing an object with PB.store(obj), OJB has to decide if an
>> insert or
>> update is required. Thus if the PK field is of type String and the
>> value is
>> 'null' or an empty String, OJB assume that the PK value was not set and
>> declare the object as 'new'.
>>
>> regards,
>> Armin
>>
>> Günther Wieser wrote:
>>
>>> hi,
>>>
>>> i found a problem with using a compound primary key (two columns). it
>>> appears only if one of the keys is set to "" (an empty string, NOT!!!!
>>> null).
>>>
>>> here's my setup:
>>>
>>> ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>>>
>>> repository.xml:
>>> <class-descriptor
>>> class="com.creative_it.webcreator.resources.Property"
>>> table="properties">
>>> <field-descriptor id="1" name="key" column="id"
>>> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>>> <field-descriptor id="2" name="locale" column="locale"
>>> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>>> <field-descriptor id="3" name="value" column="value"
>>> jdbc-type="VARCHAR" />
>>> </class-descriptor>
>>>
>>> the database layout:
>>> +--------+--------------+------+-----+---------+-------+
>>> | Field | Type | Null | Key | Default | Extra |
>>> +--------+--------------+------+-----+---------+-------+
>>> | id | varchar(255) | | PRI | | |
>>> | value | text | YES | | | |
>>> | locale | varchar(255) | | PRI | | |
>>> +--------+--------------+------+-----+---------+-------+
>>>
>>> example code:
>>> Property prop = new Property();
>>> prop.setKey("whateverKey");
>>> prop.setLocale("");
>>> prop.setValue("some test value");
>>> broker.beginTransaction();
>>> // this one works if table doesn't have one element with
>>> same key and locale
>>> broker.store(prop);
>>> broker.commitTransaction();
>>> prop.setValue("a new value");
>>> broker.beginTransaction();
>>> // this one fails with the following exception
>>> broker.store(prop);
>>> broker.commitTransaction();
>>> the exception raised is:
>>> org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure
>>> while insert object data for class
>>> com.creative_it.webcreator.resources.Property,
>>> PK of the given object is [ key=test.property locale=], object was
>>> com.creative_it.webcreator.resources.Property@5dd582, exception
>>> message is [Duplicate key or integrity constraint violation message from
>>
>>
>> server:
>>
>>> "Duplicate entry 'test.property-' for key 1"]
>>> at
>>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAcc
>>> essImp
>>> l.java:239)
>>> at
>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistence
>>> Broker
>>> Impl.java:1642)
>>> at
>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>> erImpl
>>> .java:1557)
>>> at
>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>> erImpl
>>> .java:715)
>>> at
>>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>> gPersi
>>> stenceBroker.java:175)
>>> at
>>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>> gPersi
>>> stenceBroker.java:175)
>>> at
>>> com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(P
>>> ropert
>>> yTest.java:63)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>> at
>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.j
>>> ava:39
>>> )
>>> at
>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccess
>>> orImpl
>>> .java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at junit.framework.TestCase.runTest(TestCase.java:154)
>>> at junit.framework.TestCase.runBare(TestCase.java:127)
>>> at junit.framework.TestResult$1.protect(TestResult.java:106)
>>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>>> at junit.framework.TestResult.run(TestResult.java:109)
>>> at junit.framework.TestCase.run(TestCase.java:118)
>>> at junit.framework.TestSuite.runTest(TestSuite.java:208)
>>> at junit.framework.TestSuite.run(TestSuite.java:203)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(Remote
>>> TestRu
>>> nner.java:421)
>>> at
>>>
>>
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
>>
>>
>>> java:305)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTest
>>> Runner
>>> .java:186)
>>>
>>> if i update the database entry with native SQL (e.g. UPDATE
>>> properties SET value="another value" WHERE id="whateverKey" AND
>>> locale="") everything works fine, so i'm pretty sure it is not a
>>> database problem or
>>
>>
>> database error.
>>
>>> also everything works fine if "locale" is set to e.g. "de_AT" or
>>> anything else except an empty string.
>>> maybe the broker does something unexpected with the empty "" string?
>>> any ideas?
>>>
>>> kr,
>>> guenther
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: AW: problem with compound primary key when one key=""
Posted by Armin Waibel <ar...@apache.org>.
Jakob Braeuchi wrote:
> hi armin,
>
> imo we could make this checkForExistence pluggable. afaik toplink also
> has this feature per class (DoesExistQuery with methods like
> checkDatabaseForDoesExist, checkCacheForDoesExist,
> assumeExistenceForDoesExist etc.)
>
This makes sense! Agree the declaration per class-descriptor will be the
most flexible. How should the interface look liken named?
regards,
Armin
> jakob
>
> Armin Waibel schrieb:
>
>> Günther Wieser wrote:
>>
>>> hmm, that's bad news for me. to my understanding that means that
>>> there won't
>>> be a change of this behaviour very soon (let's say: never)?
>>
>>
>>
>> If you know that an object needs 'update' you could use
>> PB.store(obj, ObjectModificationDefaultImpl.UPDATE);
>>
>>
>>> i know a workaround for my implementation (using some default
>>> identifier for
>>> locale, e.g. "DEFAULT") but that doesn't make me happy as i need to
>>> change
>>> data PLUS my code to work together in changing data, which makes it very
>>> error-prone if somebody misuses the classes provided by my
>>> applicationen.
>>>
>>> by the way: from an sql perspective, an empty string (or varchar) is
>>> a valid
>>> primary key... (i know this is not a very subtile attempt but who
>>> knows ;-)
>>> )
>>>
>>
>> I know, the problem is that method BrokerHelper#representsNull(...) is
>> used to reduce DB calls and OJB use the rules:
>> <snip>
>> - If given value is 'null' itself, true will be returned
>> - If given value is instance of Number with value 0 and the
>> field-descriptor represents a primitive field, true will be returned
>> - If given value is instance of String with length 0 and the
>> field-descriptor is a primary key, true will be returned
>> </snip>
>>
>> Currently it's not possible to bypass this test. Would it be useful to
>> make this check configurable/pluggable?
>>
>> regards,
>> Armin
>>
>>> kr,
>>> guenther
>>>
>>> --
>>> Günther Wieser
>>>
>>> creative-it
>>> Guglgasse 6/1/11/1
>>> A-1110 Wien
>>> Austria
>>> http://www.creative-it.com
>>>
>>>
>>> -----Ursprüngliche Nachricht-----
>>> Von: Armin Waibel [mailto:arminw@apache.org] Gesendet: Montag, 28.
>>> Februar 2005 20:29
>>> An: OJB Users List
>>> Betreff: Re: problem with compound primary key when one key=""
>>>
>>> Hi Günther,
>>>
>>> > maybe the broker does something unexpected with the empty ""
>>> string? any
>>>
>>>> ideas?
>>>
>>>
>>>
>>>
>>> When storing an object with PB.store(obj), OJB has to decide if an
>>> insert or
>>> update is required. Thus if the PK field is of type String and the
>>> value is
>>> 'null' or an empty String, OJB assume that the PK value was not set and
>>> declare the object as 'new'.
>>>
>>> regards,
>>> Armin
>>>
>>> Günther Wieser wrote:
>>>
>>>> hi,
>>>>
>>>> i found a problem with using a compound primary key (two columns).
>>>> it appears only if one of the keys is set to "" (an empty string,
>>>> NOT!!!!
>>>> null).
>>>>
>>>> here's my setup:
>>>>
>>>> ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>>>>
>>>> repository.xml:
>>>> <class-descriptor
>>>> class="com.creative_it.webcreator.resources.Property"
>>>> table="properties">
>>>> <field-descriptor id="1" name="key" column="id"
>>>> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>>>> <field-descriptor id="2" name="locale" column="locale"
>>>> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>>>> <field-descriptor id="3" name="value" column="value"
>>>> jdbc-type="VARCHAR" /> </class-descriptor>
>>>>
>>>> the database layout:
>>>> +--------+--------------+------+-----+---------+-------+
>>>> | Field | Type | Null | Key | Default | Extra |
>>>> +--------+--------------+------+-----+---------+-------+
>>>> | id | varchar(255) | | PRI | | |
>>>> | value | text | YES | | | |
>>>> | locale | varchar(255) | | PRI | | |
>>>> +--------+--------------+------+-----+---------+-------+
>>>>
>>>> example code:
>>>> Property prop = new Property();
>>>> prop.setKey("whateverKey");
>>>> prop.setLocale("");
>>>> prop.setValue("some test value");
>>>> broker.beginTransaction();
>>>> // this one works if table doesn't have one element with
>>>> same key and locale
>>>> broker.store(prop);
>>>> broker.commitTransaction();
>>>> prop.setValue("a new value");
>>>> broker.beginTransaction();
>>>> // this one fails with the following exception
>>>> broker.store(prop);
>>>> broker.commitTransaction(); the exception
>>>> raised is:
>>>> org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure
>>>> while insert object data for class
>>>> com.creative_it.webcreator.resources.Property,
>>>> PK of the given object is [ key=test.property locale=], object was
>>>> com.creative_it.webcreator.resources.Property@5dd582, exception
>>>> message is [Duplicate key or integrity constraint violation message
>>>> from
>>>
>>>
>>>
>>> server:
>>>
>>>> "Duplicate entry 'test.property-' for key 1"]
>>>> at
>>>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAcc
>>>> essImp
>>>> l.java:239)
>>>> at
>>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistence
>>>> Broker
>>>> Impl.java:1642)
>>>> at
>>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>>> erImpl
>>>> .java:1557)
>>>> at
>>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>>> erImpl
>>>> .java:715)
>>>> at
>>>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>>> gPersi
>>>> stenceBroker.java:175)
>>>> at
>>>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>>> gPersi
>>>> stenceBroker.java:175)
>>>> at
>>>> com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(P
>>>> ropert
>>>> yTest.java:63)
>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>> at
>>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.j
>>>> ava:39
>>>> )
>>>> at
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccess
>>>> orImpl
>>>> .java:25)
>>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>>> at junit.framework.TestCase.runTest(TestCase.java:154)
>>>> at junit.framework.TestCase.runBare(TestCase.java:127)
>>>> at junit.framework.TestResult$1.protect(TestResult.java:106)
>>>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>>>> at junit.framework.TestResult.run(TestResult.java:109)
>>>> at junit.framework.TestCase.run(TestCase.java:118)
>>>> at junit.framework.TestSuite.runTest(TestSuite.java:208)
>>>> at junit.framework.TestSuite.run(TestSuite.java:203)
>>>> at
>>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(Remote
>>>> TestRu
>>>> nner.java:421)
>>>> at
>>>>
>>>
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
>>>
>>>
>>>> java:305)
>>>> at
>>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTest
>>>> Runner
>>>> .java:186)
>>>>
>>>> if i update the database entry with native SQL (e.g. UPDATE
>>>> properties SET value="another value" WHERE id="whateverKey" AND
>>>> locale="") everything works fine, so i'm pretty sure it is not a
>>>> database problem or
>>>
>>>
>>>
>>> database error.
>>>
>>>> also everything works fine if "locale" is set to e.g. "de_AT" or
>>>> anything else except an empty string.
>>>> maybe the broker does something unexpected with the empty "" string?
>>>> any ideas?
>>>>
>>>> kr,
>>>> guenther
>>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: AW: problem with compound primary key when one key=""
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi armin,
imo we could make this checkForExistence pluggable. afaik toplink also
has this feature per class (DoesExistQuery with methods like
checkDatabaseForDoesExist, checkCacheForDoesExist,
assumeExistenceForDoesExist etc.)
jakob
Armin Waibel schrieb:
> Günther Wieser wrote:
>
>> hmm, that's bad news for me. to my understanding that means that there
>> won't
>> be a change of this behaviour very soon (let's say: never)?
>
>
> If you know that an object needs 'update' you could use
> PB.store(obj, ObjectModificationDefaultImpl.UPDATE);
>
>
>> i know a workaround for my implementation (using some default
>> identifier for
>> locale, e.g. "DEFAULT") but that doesn't make me happy as i need to
>> change
>> data PLUS my code to work together in changing data, which makes it very
>> error-prone if somebody misuses the classes provided by my applicationen.
>>
>> by the way: from an sql perspective, an empty string (or varchar) is a
>> valid
>> primary key... (i know this is not a very subtile attempt but who
>> knows ;-)
>> )
>>
>
> I know, the problem is that method BrokerHelper#representsNull(...) is
> used to reduce DB calls and OJB use the rules:
> <snip>
> - If given value is 'null' itself, true will be returned
> - If given value is instance of Number with value 0 and the
> field-descriptor represents a primitive field, true will be returned
> - If given value is instance of String with length 0 and the
> field-descriptor is a primary key, true will be returned
> </snip>
>
> Currently it's not possible to bypass this test. Would it be useful to
> make this check configurable/pluggable?
>
> regards,
> Armin
>
>> kr,
>> guenther
>>
>> --
>> Günther Wieser
>>
>> creative-it
>> Guglgasse 6/1/11/1
>> A-1110 Wien
>> Austria
>> http://www.creative-it.com
>>
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Armin Waibel [mailto:arminw@apache.org] Gesendet: Montag, 28.
>> Februar 2005 20:29
>> An: OJB Users List
>> Betreff: Re: problem with compound primary key when one key=""
>>
>> Hi Günther,
>>
>> > maybe the broker does something unexpected with the empty ""
>> string? any
>>
>>> ideas?
>>
>>
>>
>> When storing an object with PB.store(obj), OJB has to decide if an
>> insert or
>> update is required. Thus if the PK field is of type String and the
>> value is
>> 'null' or an empty String, OJB assume that the PK value was not set and
>> declare the object as 'new'.
>>
>> regards,
>> Armin
>>
>> Günther Wieser wrote:
>>
>>> hi,
>>>
>>> i found a problem with using a compound primary key (two columns). it
>>> appears only if one of the keys is set to "" (an empty string, NOT!!!!
>>> null).
>>>
>>> here's my setup:
>>>
>>> ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>>>
>>> repository.xml:
>>> <class-descriptor
>>> class="com.creative_it.webcreator.resources.Property"
>>> table="properties">
>>> <field-descriptor id="1" name="key" column="id"
>>> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>>> <field-descriptor id="2" name="locale" column="locale"
>>> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>>> <field-descriptor id="3" name="value" column="value"
>>> jdbc-type="VARCHAR" />
>>> </class-descriptor>
>>>
>>> the database layout:
>>> +--------+--------------+------+-----+---------+-------+
>>> | Field | Type | Null | Key | Default | Extra |
>>> +--------+--------------+------+-----+---------+-------+
>>> | id | varchar(255) | | PRI | | |
>>> | value | text | YES | | | |
>>> | locale | varchar(255) | | PRI | | |
>>> +--------+--------------+------+-----+---------+-------+
>>>
>>> example code:
>>> Property prop = new Property();
>>> prop.setKey("whateverKey");
>>> prop.setLocale("");
>>> prop.setValue("some test value");
>>> broker.beginTransaction();
>>> // this one works if table doesn't have one element with
>>> same key and locale
>>> broker.store(prop);
>>> broker.commitTransaction();
>>> prop.setValue("a new value");
>>> broker.beginTransaction();
>>> // this one fails with the following exception
>>> broker.store(prop);
>>> broker.commitTransaction();
>>> the exception raised is:
>>> org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure
>>> while insert object data for class
>>> com.creative_it.webcreator.resources.Property,
>>> PK of the given object is [ key=test.property locale=], object was
>>> com.creative_it.webcreator.resources.Property@5dd582, exception
>>> message is [Duplicate key or integrity constraint violation message from
>>
>>
>> server:
>>
>>> "Duplicate entry 'test.property-' for key 1"]
>>> at
>>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAcc
>>> essImp
>>> l.java:239)
>>> at
>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistence
>>> Broker
>>> Impl.java:1642)
>>> at
>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>> erImpl
>>> .java:1557)
>>> at
>>> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>> erImpl
>>> .java:715)
>>> at
>>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>> gPersi
>>> stenceBroker.java:175)
>>> at
>>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>> gPersi
>>> stenceBroker.java:175)
>>> at
>>> com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(P
>>> ropert
>>> yTest.java:63)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>> at
>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.j
>>> ava:39
>>> )
>>> at
>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccess
>>> orImpl
>>> .java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at junit.framework.TestCase.runTest(TestCase.java:154)
>>> at junit.framework.TestCase.runBare(TestCase.java:127)
>>> at junit.framework.TestResult$1.protect(TestResult.java:106)
>>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>>> at junit.framework.TestResult.run(TestResult.java:109)
>>> at junit.framework.TestCase.run(TestCase.java:118)
>>> at junit.framework.TestSuite.runTest(TestSuite.java:208)
>>> at junit.framework.TestSuite.run(TestSuite.java:203)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(Remote
>>> TestRu
>>> nner.java:421)
>>> at
>>>
>>
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
>>
>>
>>> java:305)
>>> at
>>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTest
>>> Runner
>>> .java:186)
>>>
>>> if i update the database entry with native SQL (e.g. UPDATE
>>> properties SET value="another value" WHERE id="whateverKey" AND
>>> locale="") everything works fine, so i'm pretty sure it is not a
>>> database problem or
>>
>>
>> database error.
>>
>>> also everything works fine if "locale" is set to e.g. "de_AT" or
>>> anything else except an empty string.
>>> maybe the broker does something unexpected with the empty "" string?
>>> any ideas?
>>>
>>> kr,
>>> guenther
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
AW: AW: problem with compound primary key when one key=""
Posted by Günther Wieser <gw...@creative-it.com>.
> If you know that an object needs 'update' you could use PB.store(obj,
ObjectModificationDefaultImpl.UPDATE);
this solution works fine for me as i know if i need to update or not.
it's good to see that you've found a solution (as proposed in the discussion
in the other mails sent today about this topic), seems very reasonable.
kr and many thanks,
guenther
-----Ursprüngliche Nachricht-----
Von: Armin Waibel [mailto:arminw@apache.org]
Gesendet: Montag, 28. Februar 2005 20:56
An: OJB Users List
Betreff: Re: AW: problem with compound primary key when one key=""
Günther Wieser wrote:
> hmm, that's bad news for me. to my understanding that means that there
> won't be a change of this behaviour very soon (let's say: never)?
>
If you know that an object needs 'update' you could use PB.store(obj,
ObjectModificationDefaultImpl.UPDATE);
> i know a workaround for my implementation (using some default
> identifier for locale, e.g. "DEFAULT") but that doesn't make me happy
> as i need to change data PLUS my code to work together in changing
> data, which makes it very error-prone if somebody misuses the classes
provided by my applicationen.
>
> by the way: from an sql perspective, an empty string (or varchar) is a
> valid primary key... (i know this is not a very subtile attempt but
> who knows ;-)
> )
>
I know, the problem is that method BrokerHelper#representsNull(...) is used
to reduce DB calls and OJB use the rules:
<snip>
- If given value is 'null' itself, true will be returned
- If given value is instance of Number with value 0 and the field-descriptor
represents a primitive field, true will be returned
- If given value is instance of String with length 0 and the
field-descriptor is a primary key, true will be returned </snip>
Currently it's not possible to bypass this test. Would it be useful to make
this check configurable/pluggable?
regards,
Armin
> kr,
> guenther
>
> --
> Günther Wieser
>
> creative-it
> Guglgasse 6/1/11/1
> A-1110 Wien
> Austria
> http://www.creative-it.com
>
>
> -----Ursprüngliche Nachricht-----
> Von: Armin Waibel [mailto:arminw@apache.org]
> Gesendet: Montag, 28. Februar 2005 20:29
> An: OJB Users List
> Betreff: Re: problem with compound primary key when one key=""
>
> Hi Günther,
>
> > maybe the broker does something unexpected with the empty ""
> string? any
>
>>ideas?
>
>
> When storing an object with PB.store(obj), OJB has to decide if an
> insert or update is required. Thus if the PK field is of type String
> and the value is 'null' or an empty String, OJB assume that the PK
> value was not set and declare the object as 'new'.
>
> regards,
> Armin
>
> Günther Wieser wrote:
>
>>hi,
>>
>>i found a problem with using a compound primary key (two columns). it
>>appears only if one of the keys is set to "" (an empty string, NOT!!!!
>>null).
>>
>>here's my setup:
>>
>>ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>>
>>repository.xml:
>> <class-descriptor
>>class="com.creative_it.webcreator.resources.Property" table="properties">
>> <field-descriptor id="1" name="key" column="id"
>>jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>> <field-descriptor id="2" name="locale" column="locale"
>>jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>> <field-descriptor id="3" name="value" column="value"
>>jdbc-type="VARCHAR" />
>> </class-descriptor>
>>
>>the database layout:
>>+--------+--------------+------+-----+---------+-------+
>>| Field | Type | Null | Key | Default | Extra |
>>+--------+--------------+------+-----+---------+-------+
>>| id | varchar(255) | | PRI | | |
>>| value | text | YES | | | |
>>| locale | varchar(255) | | PRI | | |
>>+--------+--------------+------+-----+---------+-------+
>>
>>example code:
>> Property prop = new Property();
>> prop.setKey("whateverKey");
>> prop.setLocale("");
>> prop.setValue("some test value");
>> broker.beginTransaction();
>> // this one works if table doesn't have one element with
>>same key and locale
>> broker.store(prop);
>> broker.commitTransaction();
>> prop.setValue("a new value");
>> broker.beginTransaction();
>> // this one fails with the following exception
>> broker.store(prop);
>> broker.commitTransaction();
>>
>>the exception raised is:
>>org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure
>>while insert object data for class
>>com.creative_it.webcreator.resources.Property,
>>PK of the given object is [ key=test.property locale=], object was
>>com.creative_it.webcreator.resources.Property@5dd582, exception
>>message is [Duplicate key or integrity constraint violation message
>>from
>
> server:
>
>>"Duplicate entry 'test.property-' for key 1"]
>> at
>>org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAcc
>>essImp
>>l.java:239)
>> at
>>org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistence
>>Broker
>>Impl.java:1642)
>> at
>>org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>erImpl
>>.java:1557)
>> at
>>org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>erImpl
>>.java:715)
>> at
>>org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>gPersi
>>stenceBroker.java:175)
>> at
>>org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>gPersi
>>stenceBroker.java:175)
>> at
>>com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(P
>>ropert
>>yTest.java:63)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>>sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.j
>>ava:39
>>)
>> at
>>sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccess
>>orImpl
>>.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:324)
>> at junit.framework.TestCase.runTest(TestCase.java:154)
>> at junit.framework.TestCase.runBare(TestCase.java:127)
>> at junit.framework.TestResult$1.protect(TestResult.java:106)
>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>> at junit.framework.TestResult.run(TestResult.java:109)
>> at junit.framework.TestCase.run(TestCase.java:118)
>> at junit.framework.TestSuite.runTest(TestSuite.java:208)
>> at junit.framework.TestSuite.run(TestSuite.java:203)
>> at
>>org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(Remote
>>TestRu
>>nner.java:421)
>> at
>>
>
>
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
>
>>java:305)
>> at
>>org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTest
>>Runner
>>.java:186)
>>
>>if i update the database entry with native SQL (e.g. UPDATE properties
>>SET value="another value" WHERE id="whateverKey" AND locale="")
>>everything works fine, so i'm pretty sure it is not a database problem
>>or
>
> database error.
>
>>also everything works fine if "locale" is set to e.g. "de_AT" or
>>anything else except an empty string.
>>maybe the broker does something unexpected with the empty "" string?
>>any ideas?
>>
>>kr,
>>guenther
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: AW: problem with compound primary key when one key=""
Posted by Armin Waibel <ar...@apache.org>.
Günther Wieser wrote:
> hmm, that's bad news for me. to my understanding that means that there won't
> be a change of this behaviour very soon (let's say: never)?
>
If you know that an object needs 'update' you could use
PB.store(obj, ObjectModificationDefaultImpl.UPDATE);
> i know a workaround for my implementation (using some default identifier for
> locale, e.g. "DEFAULT") but that doesn't make me happy as i need to change
> data PLUS my code to work together in changing data, which makes it very
> error-prone if somebody misuses the classes provided by my applicationen.
>
> by the way: from an sql perspective, an empty string (or varchar) is a valid
> primary key... (i know this is not a very subtile attempt but who knows ;-)
> )
>
I know, the problem is that method BrokerHelper#representsNull(...) is
used to reduce DB calls and OJB use the rules:
<snip>
- If given value is 'null' itself, true will be returned
- If given value is instance of Number with value 0 and the
field-descriptor represents a primitive field, true will be returned
- If given value is instance of String with length 0 and the
field-descriptor is a primary key, true will be returned
</snip>
Currently it's not possible to bypass this test. Would it be useful to
make this check configurable/pluggable?
regards,
Armin
> kr,
> guenther
>
> --
> Günther Wieser
>
> creative-it
> Guglgasse 6/1/11/1
> A-1110 Wien
> Austria
> http://www.creative-it.com
>
>
> -----Ursprüngliche Nachricht-----
> Von: Armin Waibel [mailto:arminw@apache.org]
> Gesendet: Montag, 28. Februar 2005 20:29
> An: OJB Users List
> Betreff: Re: problem with compound primary key when one key=""
>
> Hi Günther,
>
> > maybe the broker does something unexpected with the empty "" string? any
>
>>ideas?
>
>
> When storing an object with PB.store(obj), OJB has to decide if an insert or
> update is required. Thus if the PK field is of type String and the value is
> 'null' or an empty String, OJB assume that the PK value was not set and
> declare the object as 'new'.
>
> regards,
> Armin
>
> Günther Wieser wrote:
>
>>hi,
>>
>>i found a problem with using a compound primary key (two columns). it
>>appears only if one of the keys is set to "" (an empty string, NOT!!!!
>>null).
>>
>>here's my setup:
>>
>>ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>>
>>repository.xml:
>> <class-descriptor
>>class="com.creative_it.webcreator.resources.Property" table="properties">
>> <field-descriptor id="1" name="key" column="id"
>>jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>> <field-descriptor id="2" name="locale" column="locale"
>>jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
>> <field-descriptor id="3" name="value" column="value"
>>jdbc-type="VARCHAR" />
>> </class-descriptor>
>>
>>the database layout:
>>+--------+--------------+------+-----+---------+-------+
>>| Field | Type | Null | Key | Default | Extra |
>>+--------+--------------+------+-----+---------+-------+
>>| id | varchar(255) | | PRI | | |
>>| value | text | YES | | | |
>>| locale | varchar(255) | | PRI | | |
>>+--------+--------------+------+-----+---------+-------+
>>
>>example code:
>> Property prop = new Property();
>> prop.setKey("whateverKey");
>> prop.setLocale("");
>> prop.setValue("some test value");
>> broker.beginTransaction();
>> // this one works if table doesn't have one element with
>>same key and locale
>> broker.store(prop);
>> broker.commitTransaction();
>> prop.setValue("a new value");
>> broker.beginTransaction();
>> // this one fails with the following exception
>> broker.store(prop);
>> broker.commitTransaction();
>>
>>the exception raised is:
>>org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure
>>while insert object data for class
>>com.creative_it.webcreator.resources.Property,
>>PK of the given object is [ key=test.property locale=], object was
>>com.creative_it.webcreator.resources.Property@5dd582, exception
>>message is [Duplicate key or integrity constraint violation message from
>
> server:
>
>>"Duplicate entry 'test.property-' for key 1"]
>> at
>>org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAcc
>>essImp
>>l.java:239)
>> at
>>org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistence
>>Broker
>>Impl.java:1642)
>> at
>>org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>erImpl
>>.java:1557)
>> at
>>org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
>>erImpl
>>.java:715)
>> at
>>org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>gPersi
>>stenceBroker.java:175)
>> at
>>org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
>>gPersi
>>stenceBroker.java:175)
>> at
>>com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(P
>>ropert
>>yTest.java:63)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>>sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.j
>>ava:39
>>)
>> at
>>sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccess
>>orImpl
>>.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:324)
>> at junit.framework.TestCase.runTest(TestCase.java:154)
>> at junit.framework.TestCase.runBare(TestCase.java:127)
>> at junit.framework.TestResult$1.protect(TestResult.java:106)
>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>> at junit.framework.TestResult.run(TestResult.java:109)
>> at junit.framework.TestCase.run(TestCase.java:118)
>> at junit.framework.TestSuite.runTest(TestSuite.java:208)
>> at junit.framework.TestSuite.run(TestSuite.java:203)
>> at
>>org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(Remote
>>TestRu
>>nner.java:421)
>> at
>>
>
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
>
>>java:305)
>> at
>>org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTest
>>Runner
>>.java:186)
>>
>>if i update the database entry with native SQL (e.g. UPDATE properties
>>SET value="another value" WHERE id="whateverKey" AND locale="")
>>everything works fine, so i'm pretty sure it is not a database problem or
>
> database error.
>
>>also everything works fine if "locale" is set to e.g. "de_AT" or
>>anything else except an empty string.
>>maybe the broker does something unexpected with the empty "" string?
>>any ideas?
>>
>>kr,
>>guenther
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
AW: problem with compound primary key when one key=""
Posted by Günther Wieser <gw...@creative-it.com>.
hmm, that's bad news for me. to my understanding that means that there won't
be a change of this behaviour very soon (let's say: never)?
i know a workaround for my implementation (using some default identifier for
locale, e.g. "DEFAULT") but that doesn't make me happy as i need to change
data PLUS my code to work together in changing data, which makes it very
error-prone if somebody misuses the classes provided by my applicationen.
by the way: from an sql perspective, an empty string (or varchar) is a valid
primary key... (i know this is not a very subtile attempt but who knows ;-)
)
kr,
guenther
--
Günther Wieser
creative-it
Guglgasse 6/1/11/1
A-1110 Wien
Austria
http://www.creative-it.com
-----Ursprüngliche Nachricht-----
Von: Armin Waibel [mailto:arminw@apache.org]
Gesendet: Montag, 28. Februar 2005 20:29
An: OJB Users List
Betreff: Re: problem with compound primary key when one key=""
Hi Günther,
> maybe the broker does something unexpected with the empty "" string? any
> ideas?
When storing an object with PB.store(obj), OJB has to decide if an insert or
update is required. Thus if the PK field is of type String and the value is
'null' or an empty String, OJB assume that the PK value was not set and
declare the object as 'new'.
regards,
Armin
Günther Wieser wrote:
> hi,
>
> i found a problem with using a compound primary key (two columns). it
> appears only if one of the keys is set to "" (an empty string, NOT!!!!
> null).
>
> here's my setup:
>
> ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>
> repository.xml:
> <class-descriptor
> class="com.creative_it.webcreator.resources.Property" table="properties">
> <field-descriptor id="1" name="key" column="id"
> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
> <field-descriptor id="2" name="locale" column="locale"
> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
> <field-descriptor id="3" name="value" column="value"
> jdbc-type="VARCHAR" />
> </class-descriptor>
>
> the database layout:
> +--------+--------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+--------------+------+-----+---------+-------+
> | id | varchar(255) | | PRI | | |
> | value | text | YES | | | |
> | locale | varchar(255) | | PRI | | |
> +--------+--------------+------+-----+---------+-------+
>
> example code:
> Property prop = new Property();
> prop.setKey("whateverKey");
> prop.setLocale("");
> prop.setValue("some test value");
> broker.beginTransaction();
> // this one works if table doesn't have one element with
> same key and locale
> broker.store(prop);
> broker.commitTransaction();
> prop.setValue("a new value");
> broker.beginTransaction();
> // this one fails with the following exception
> broker.store(prop);
> broker.commitTransaction();
>
> the exception raised is:
> org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure
> while insert object data for class
> com.creative_it.webcreator.resources.Property,
> PK of the given object is [ key=test.property locale=], object was
> com.creative_it.webcreator.resources.Property@5dd582, exception
> message is [Duplicate key or integrity constraint violation message from
server:
> "Duplicate entry 'test.property-' for key 1"]
> at
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAcc
> essImp
> l.java:239)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistence
> Broker
> Impl.java:1642)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
> erImpl
> .java:1557)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrok
> erImpl
> .java:715)
> at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
> gPersi
> stenceBroker.java:175)
> at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegatin
> gPersi
> stenceBroker.java:175)
> at
> com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(P
> ropert
> yTest.java:63)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.j
> ava:39
> )
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccess
> orImpl
> .java:25)
> at java.lang.reflect.Method.invoke(Method.java:324)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(Remote
> TestRu
> nner.java:421)
> at
>
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
> java:305)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTest
> Runner
> .java:186)
>
> if i update the database entry with native SQL (e.g. UPDATE properties
> SET value="another value" WHERE id="whateverKey" AND locale="")
> everything works fine, so i'm pretty sure it is not a database problem or
database error.
> also everything works fine if "locale" is set to e.g. "de_AT" or
> anything else except an empty string.
> maybe the broker does something unexpected with the empty "" string?
> any ideas?
>
> kr,
> guenther
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: problem with compound primary key when one key=""
Posted by Armin Waibel <ar...@apache.org>.
Hi Günther,
> maybe the broker does something unexpected with the empty "" string? any
> ideas?
When storing an object with PB.store(obj), OJB has to decide if an
insert or update is required. Thus if the PK field is of type String and
the value is 'null' or an empty String, OJB assume that the PK value was
not set and declare the object as 'new'.
regards,
Armin
Günther Wieser wrote:
> hi,
>
> i found a problem with using a compound primary key (two columns). it
> appears only if one of the keys is set to "" (an empty string, NOT!!!!
> null).
>
> here's my setup:
>
> ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>
> repository.xml:
> <class-descriptor
> class="com.creative_it.webcreator.resources.Property" table="properties">
> <field-descriptor id="1" name="key" column="id"
> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
> <field-descriptor id="2" name="locale" column="locale"
> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
> <field-descriptor id="3" name="value" column="value"
> jdbc-type="VARCHAR" />
> </class-descriptor>
>
> the database layout:
> +--------+--------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+--------------+------+-----+---------+-------+
> | id | varchar(255) | | PRI | | |
> | value | text | YES | | | |
> | locale | varchar(255) | | PRI | | |
> +--------+--------------+------+-----+---------+-------+
>
> example code:
> Property prop = new Property();
> prop.setKey("whateverKey");
> prop.setLocale("");
> prop.setValue("some test value");
> broker.beginTransaction();
> // this one works if table doesn't have one element with same
> key and locale
> broker.store(prop);
> broker.commitTransaction();
> prop.setValue("a new value");
> broker.beginTransaction();
> // this one fails with the following exception
> broker.store(prop);
> broker.commitTransaction();
>
> the exception raised is:
> org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure while
> insert object data for class com.creative_it.webcreator.resources.Property,
> PK of the given object is [ key=test.property locale=], object was
> com.creative_it.webcreator.resources.Property@5dd582, exception message is
> [Duplicate key or integrity constraint violation message from server:
> "Duplicate entry 'test.property-' for key 1"]
> at
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAccessImp
> l.java:239)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(PersistenceBroker
> Impl.java:1642)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl
> .java:1557)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl
> .java:715)
> at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersi
> stenceBroker.java:175)
> at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersi
> stenceBroker.java:175)
> at
> com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(Propert
> yTest.java:63)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
> )
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
> .java:25)
> at java.lang.reflect.Method.invoke(Method.java:324)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:421)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
> java:305)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
> .java:186)
>
> if i update the database entry with native SQL (e.g. UPDATE properties SET
> value="another value" WHERE id="whateverKey" AND locale="") everything works
> fine, so i'm pretty sure it is not a database problem or database error.
> also everything works fine if "locale" is set to e.g. "de_AT" or anything
> else except an empty string.
> maybe the broker does something unexpected with the empty "" string? any
> ideas?
>
> kr,
> guenther
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: problem with compound primary key when one key=""
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi günter,
could you please post the sql ?
is there a row in the database with locale="" ? i'm asking this because
you say that you can insert it manually.
btw. it's no longer necessary to use ids in field-descriptors.
jakob
Günther Wieser schrieb:
> hi,
>
> i found a problem with using a compound primary key (two columns). it
> appears only if one of the keys is set to "" (an empty string, NOT!!!!
> null).
>
> here's my setup:
>
> ojb 1.0.1, mysql 4.0.23 on XP prof., jdk 1.4.2_07
>
> repository.xml:
> <class-descriptor
> class="com.creative_it.webcreator.resources.Property" table="properties">
> <field-descriptor id="1" name="key" column="id"
> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
> <field-descriptor id="2" name="locale" column="locale"
> jdbc-type="VARCHAR" primarykey="true" indexed="true"/>
> <field-descriptor id="3" name="value" column="value"
> jdbc-type="VARCHAR" />
> </class-descriptor>
>
> the database layout:
> +--------+--------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+--------------+------+-----+---------+-------+
> | id | varchar(255) | | PRI | | |
> | value | text | YES | | | |
> | locale | varchar(255) | | PRI | | |
> +--------+--------------+------+-----+---------+-------+
>
> example code:
> Property prop = new Property();
> prop.setKey("whateverKey");
> prop.setLocale("");
> prop.setValue("some test value");
> broker.beginTransaction();
> // this one works if table doesn't have one element with same
> key and locale
> broker.store(prop);
> broker.commitTransaction();
> prop.setValue("a new value");
> broker.beginTransaction();
> // this one fails with the following exception
> broker.store(prop);
> broker.commitTransaction();
>
> the exception raised is:
> org.apache.ojb.broker.KeyConstraintViolatedException: SQL failure while
> insert object data for class com.creative_it.webcreator.resources.Property,
> PK of the given object is [ key=test.property locale=], object was
> com.creative_it.webcreator.resources.Property@5dd582, exception message is
> [Duplicate key or integrity constraint violation message from server:
> "Duplicate entry 'test.property-' for key 1"]
> at
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAccessImp
> l.java:239)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(PersistenceBroker
> Impl.java:1642)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl
> .java:1557)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl
> .java:715)
> at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersi
> stenceBroker.java:175)
> at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersi
> stenceBroker.java:175)
> at
> com.creative_it.webcreator.junit.resources.PropertyTest.testProperty(Propert
> yTest.java:63)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
> )
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
> .java:25)
> at java.lang.reflect.Method.invoke(Method.java:324)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:421)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
> java:305)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
> .java:186)
>
> if i update the database entry with native SQL (e.g. UPDATE properties SET
> value="another value" WHERE id="whateverKey" AND locale="") everything works
> fine, so i'm pretty sure it is not a database problem or database error.
> also everything works fine if "locale" is set to e.g. "de_AT" or anything
> else except an empty string.
> maybe the broker does something unexpected with the empty "" string? any
> ideas?
>
> kr,
> guenther
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org