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: http://issues.apache.org/jira/browse/OJB-92
Project: OJB
Type: Bug
Components: PB-API, RDBMS platform-specific support
Versions: 1.0.x CVS, 1.0.3, 1.0.4
Reporter: Quinet Jérémie
Priority: Minor
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.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
