Newbie problem finding a solution retrieving "orphaned" rows

[Bernd Laengerich <B....@actosoft.de>]

Hi,

First please excuse my english as this is not my native language.
I am new to OJB and I am using the PB-API.

Given
class A with attributes and a foreign key reference to class B (b_ref)
class B with b_id and more attributes
The reference is n to 1, so one instance of B can be referenced by 0 to 
n instances of A

What I want:

Select all instances of B that are not referenced by instances of A.

In plain SQL I would write:
SELECT * FROM B WHERE B_ID NOT IN (SELECT DISTINCT B_REF FROM A)
or
SELECT * FROM B LEFT OUTER JOIN A ON (B.b_id =
A.b_ref) WHERE A.a_id is null

What I must avoid is loading all instances of A into the cache.
I have tried using a subquery and a "dummy class", that only has the
b_ref attribute:

     ReportQueryByCriteria subquery =
	new ReportQueryByCriteria(ADummy.class,null);
     subquery.setAttributes(new String[] { "b_ref" });
     subquery.setDistinct(true);

with class ADummy using b_ref as the primary key,
but

     Collection results = broker.getCollectionByQuery(subquery);

results in:

[org.apache.ojb.broker.core.QueryReferenceBroker] DEBUG:
getCollectionByQuery (class
org.apache.ojb.broker.util.collections.RemovalAwareCollection, class
ADummy, ReportQuery from class ADummy b_ref )
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG: executeQuery :
ReportQuery from class ADummy b_ref
[org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$TableAlias]
DEBUG: TableAlias(): using hints ? false
[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
SQL:SELECT DISTINCT A0.B_REF FROM A A0
[org.apache.ojb.broker.accesslayer.ConnectionFactoryPooledImpl] DEBUG:
makeObject called
[org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] DEBUG:
Create new connection using DriverManager:
com.microsoft.jdbc.sqlserver.SQLServerConnection@9c176c
[org.apache.ojb.broker.accesslayer.ConnectionManagerImpl] DEBUG: Request
new connection from ConnectionFactory:
com.microsoft.jdbc.sqlserver.SQLServerConnection@9c176c
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG: executeQuery:
com.microsoft.jdbc.base.BasePreparedStatement@1c5ddd3
[org.apache.ojb.broker.accesslayer.RsIterator] DEBUG:
RsIterator[org.apache.ojb.broker.accesslayer.RsQueryObject[query:
ReportQuery from class ADummy b_ref , class descriptor: ADummy]] initialized
[org.apache.ojb.broker.accesslayer.RsIterator] DEBUG: hasNext() -> true
org.apache.ojb.broker.PersistenceBrokerException: Error reading class
type: ADummy from result set, current read field was a_attr
	at
org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readValuesFrom(RowReaderDefaultImpl.java:208)
	at
org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readObjectArrayFrom(RowReaderDefaultImpl.java:176)
	at
org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIterator.java:440)
	at org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:265)
	at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:122)
	at
[...]

Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Invalid column name: A_ATTRIBUTE
	at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
[...]



If this query would be successful, my next idea was to use something like:


     Criteria crit = new Criteria();
     crit.addNotIn("b_id",subquery);
     Query query = QueryFactory.newQuery(B.class,crit);
     Collection results = broker.getCollectionByQuery(query);


My concern is that once I have the first step solved, the next will be 
that the attribute b_id does not match the dummy class...


How can I do this with OJB?
Any hints are appreciated.


With kind regards
Bernd


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org