db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Quinet Jérémie (JIRA) <j...@apache.org>
Subject [jira] Created: (OJB-92) Unneeded query with Composite FK which has null column(s), which also lead to not respecting constraint with some db2 drivers.
Date Thu, 26 Jan 2006 14:59:09 GMT
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: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message