[
https://issues.apache.org/jira/browse/OJB-92?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Armin Waibel resolved OJB-92.
-----------------------------
Resolution: Fixed
Fix Version/s: 1.0.5
Hi,
I agree with you, if at least one FK value (of a compounded FK) is null the
reference-query shouldn't be performed - fixed it in
QueryReferenceBroker#hasNullifiedFK(...) method.
Additionally in OJB 1.0.5 the "null check of values" is now pluggable (see
OJB-105).
regards,
Armin
> Unneeded query with Composite FK which has null column(s), which also lead to
> not respecting constraint with some db2 drivers.
> ------------------------------------------------------------------------------------------------------------------------------
>
> Key: OJB-92
> URL: https://issues.apache.org/jira/browse/OJB-92
> Project: OJB
> Issue Type: Bug
> Components: PB-API, RDBMS platform-specific support
> Affects Versions: 1.0.3, 1.0.4, 1.0.x CVS
> Reporter: Quinet Jérémie
> Priority: Minor
> Fix For: 1.0.5
>
>
> Hello,
> i have a problem using composite FK, which may contain null column(s),
> here is a simple description :
> table1
> brand varchar(10) not null
> code varchar(1) not null
> the PK is composite : brand,code
> table2
> brand varchar(10) not null
> country varchar(2) not null
> code varchar(1)
> the PK is composite : brand,country
> and there is a FK (brand,code) pointing to table1
> On db2 (and i think it's not the only rdbms) when you have a composite FK,
> the FK is considered 'null' (and integrity is ok) when at least one of the
> column is null, and so per example we can find in table2 a row with values :
> brand='brand1' country='fr' and a null code.
> We have this kind of mapping :
> <class-descriptor
> class="test.bean.Object1"
> table="TABLE1"
> >
> <field-descriptor
> name="brand"
> column="BRAND"
> jdbc-type="VARCHAR"
> length="10"
> primarykey="true"
> />
> <field-descriptor
> name="code"
> column="CODE"
> jdbc-type="CHAR"
> length="1"
> primarykey="true"
> />
> </class-descriptor>
> <class-descriptor
> class="test.bean.Object2"
> table="TABLE2"
> >
> <field-descriptor
> name="brand"
> column="BRAND"
> jdbc-type="VARCHAR"
> length="10"
> primarykey="true"
> />
> <field-descriptor
> name="country"
> column="COUNTRY"
> jdbc-type="VARCHAR"
> length="2"
> primarykey="true"
> />
> <field-descriptor
> name="code"
> column="CODE"
> jdbc-type="CHAR"
> length="1"
> />
> <reference-descriptor name="referenceToObject1"
> class-ref="test.bean.Object1">
> <foreignkey field-ref="brand"/>
> <foreignkey field-ref="code"/>
> </reference-descriptor>
> </class-descriptor>
> When retrieving an instance of object2 using a QueryByIdentity (or
> QueryByCriteria) with brand='brand1' and country='fr' the generated SQL is
> like that :
> SELECT BRAND,COUNTRY,CODE FROM TABLE2 WHERE BRAND = ? AND COUNTRY = ?
> which point to the row : brand='brand1' country='fr' and a null code
> then OJB try to retrieve the referenceToObject1 and generate this query :
> SELECT BRAND,CODE FROM TABLE1 WHERE BRAND = ? AND CODE = ?
> which is not needed as the FK for the row retrieved in table2 (brand,code) is
> 'null', on the PreparedStatement related to this query it tries a setNull on
> the second parameter but the problem for me is that the Universal DB2 Driver
> we use on Z/OS check constraints when executing setNull and throw an
> exception explaining the parameter can't be null.
> i have quickly look at the source code use to retrieve the references, and
> see on the QueryReferenceBroker class that the getReferencedObjectIdentity
> method was used to get the Identity of the reference object and this method
> use the hasNullifiedFK method to check if the FK is null, couldn't it be
> possible to move the hasNullifiedFK to the Platform class or a mechanism to
> extend it ? (at this time i have made an extension of PersistenceBrokerImpl a
> use a new QueryReferenceBroker class).
> i don't think the default behavior which is to tag a composite FK as null
> only if all the columns are null is correct for all the rdbms (at least for
> db2).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
