[jira] Resolved: (OJB-92) Unneeded query with Composite FK which has null column(s), which also lead to not respecting constraint with some db2 drivers.

["Armin Waibel (JIRA)" <ji...@apache.org>]

     [ 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: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org