RE: Bug : duplicate objects in resultset : Why add orderby column s to resultset?
> -Original Message- > From: Thomas Dudziak [mailto:[EMAIL PROTECTED] > Sent: 19 June 2006 22:26 > Subject: Re: Bug : duplicate objects in resultset : Why add orderby > columns to resultset? > > > On 6/19/06, Janssen, Roger <[EMAIL PROTECTED]> wrote: > > > Diving deeper into the OJB code I see that the > ensureColumns method in > > fact does two things: > > (1) add the columns to the returnable set of columns in the > resultset > > (2) adds the columns to a list of columns to be used in force a join > > > > (2) is okay for orderby's, but (1) is not. If for orderby's > (1) was left > > out, would that be a problem? > > As far as I remember, there was a problem a couple of months ago that > for one database all orderby columns had to be selected columns as > well (though I don't remember offhand which database it was, though). I don't recall the recent problem, but certainly that was the case for many years for DB2/400. V5R3 and V5R4 no longer have this problem, though. Cheers, Charles. ___ HPD Software Ltd. - Helping Business Finance Business Email terms and conditions: www.hpdsoftware.com/disclaimer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Bug : duplicate objects in resultset : Why add orderby columns to resultset?
On 6/19/06, Janssen, Roger <[EMAIL PROTECTED]> wrote: Diving deeper into the OJB code I see that the ensureColumns method in fact does two things: (1) add the columns to the returnable set of columns in the resultset (2) adds the columns to a list of columns to be used in force a join (2) is okay for orderby's, but (1) is not. If for orderby's (1) was left out, would that be a problem? As far as I remember, there was a problem a couple of months ago that for one database all orderby columns had to be selected columns as well (though I don't remember offhand which database it was, though). Tom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Bug : duplicate objects in resultset : Why add orderby columns to resultset?
Hi,
Diving deeper into the OJB code I see that the ensureColumns method in
fact does two things:
(1) add the columns to the returnable set of columns in the resultset
(2) adds the columns to a list of columns to be used in force a join
(2) is okay for orderby's, but (1) is not. If for orderby's (1) was left
out, would that be a problem?
Roger Janssen
iBanx
-Original Message-
From: Janssen, Roger [mailto:[EMAIL PROTECTED]
Sent: Monday, June 19, 2006 5:32 PM
To: OJB Users List
Subject: RE: Bug : duplicate objects in resultset : Why add orderby
columns to resultset?
Hi,
I found the following in OJB code:
For Version 1.0.4:
In class SqlSelectStatement in method protected String
buildStatement():
orderByFields = query.getOrderBy();
columnList = ensureColumns(orderByFields, columnList, stmt);
For Version 1.0 RC6 (which we are still using... sorry)
In class SqlSelectStatement in method public String getStatement():
if (orderByFields != null && !orderByFields.isEmpty())
{
orderByColumnNumbers = ensureColumns(orderByFields, columnList,
stmt);
}
The above code raises the question why these columns are added when
these columns are only appearing in the orderby? This is the root of the
problem, that is causing multiple resultsets for one and the same object
to be returned. OJBIterator implementations do not filter out the
duplicates, and I do not think that that is the right way to go, how do
you implement paging for that matter, especially when native SQL will be
used implementing paging?
Is their a specific reason why these columns are added?
Roger Janssen
iBanx
-Original Message-
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, June 16, 2006 9:11 PM
To: OJB Users List
Subject: Re: Bug : duplicate objects in resultset
hi roger,
the following works for me. i use a set instead of a collection:
public void testOrderByJoined2()
{
Criteria crit = new Criteria();
QueryByCriteria q = QueryFactory.newQuery(Project.class, crit,
true);
q.addOrderByAscending("persons.lastname");
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
ManageableHashSet resultsMgmt = (ManageableHashSet)
broker.getCollectionByQuery(RemovalAwareSet.class, q);
assertNotNull(resultsMgmt);
// compare with count
int count = broker.getCount(q);
assertEquals(resultsMgmt.size(), count);
}
hth
jakob
Janssen, Roger schrieb:
> Hi,
>
> Thanx for the confirmation.
>
> Is it necessary to add the orderby column to the returned columns in
> the resultset, when the orderby column is a column of a joined table?
> This is what causes this behaviour. I personnaly do not think so,
> since for instantiating the objects, it is not required to be in the
resultset.
>
> Example:
>
> OJB generated SQL (A1.VALUE is added to columns in resultset):
>
> SELECT DISTINCT A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS,A1.VALUE as
> ojb_col_49
> FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_ABSTRACTATTRIBUTE
> A1 ON A0.ID=A1.OID WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER
> BY 4
>
> What it (could) should look like, and gives the correct results (no
> duplicates in resultset):
>
> SELECT DISTINCT A0.ID, A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS
> FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_AA A1 ON
> A0.ID=A1.OID
>
> WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER BY A1.VALUE
>
> If you agree that this is the problem, is their a quick patch I can
> apply?
>
> Thanx.
>
> Roger Janssen
> iBanx
>
> -Original Message-
> From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 15, 2006 10:13 PM
> To: OJB Users List
> Cc: Selders, Bart
> Subject: Re: Bug : duplicate objects in resultset
>
> hi roger,
>
> i can confirm this problem.
> this simple testcase shows the duplicates:
>
> /**
> * test OrderBy joined column
> */
> public void testOrderByJoined2()
> {
> Criteria crit = new Criteria();
> QueryByCriteria q = QueryFactory.newQuery(Project.class,
crit);
> q.addOrderByAscending("persons.lastname");
>
> Collection results = broker.getCollectionByQuery(q);
> assertNotNull(results);
>
> // compare with count
> int count = broker.getCount(q);
> assertEquals(results.size(), count);
>
> Set resultsSet = new HashSet(results);
> assertEquals(resultsSet.size(), results.size());
> }
>
> jakob
>
>
>
> **
> ***T he information contained in this communication is confidential
> and is intended solely for the use of the individual or entity to whom
> it is addressed.You should not copy, disclose or distribute this
> communication without the authority of iBanx bv. iBanx bv is neither
> liable for the proper and complete transmissi
RE: Bug : duplicate objects in resultset : Why add orderby columns to resultset?
Hi,
I found the following in OJB code:
For Version 1.0.4:
In class SqlSelectStatement in method protected String
buildStatement():
orderByFields = query.getOrderBy();
columnList = ensureColumns(orderByFields, columnList, stmt);
For Version 1.0 RC6 (which we are still using... sorry)
In class SqlSelectStatement in method public String getStatement():
if (orderByFields != null && !orderByFields.isEmpty())
{
orderByColumnNumbers = ensureColumns(orderByFields, columnList,
stmt);
}
The above code raises the question why these columns are added when
these columns are only appearing in the orderby? This is the root of the
problem, that is causing multiple resultsets for one and the same object
to be returned. OJBIterator implementations do not filter out the
duplicates, and I do not think that that is the right way to go, how do
you implement paging for that matter, especially when native SQL will be
used implementing paging?
Is their a specific reason why these columns are added?
Roger Janssen
iBanx
-Original Message-
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, June 16, 2006 9:11 PM
To: OJB Users List
Subject: Re: Bug : duplicate objects in resultset
hi roger,
the following works for me. i use a set instead of a collection:
public void testOrderByJoined2()
{
Criteria crit = new Criteria();
QueryByCriteria q = QueryFactory.newQuery(Project.class, crit,
true);
q.addOrderByAscending("persons.lastname");
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
ManageableHashSet resultsMgmt = (ManageableHashSet)
broker.getCollectionByQuery(RemovalAwareSet.class, q);
assertNotNull(resultsMgmt);
// compare with count
int count = broker.getCount(q);
assertEquals(resultsMgmt.size(), count);
}
hth
jakob
Janssen, Roger schrieb:
> Hi,
>
> Thanx for the confirmation.
>
> Is it necessary to add the orderby column to the returned columns in
> the resultset, when the orderby column is a column of a joined table?
> This is what causes this behaviour. I personnaly do not think so,
> since for instantiating the objects, it is not required to be in the
resultset.
>
> Example:
>
> OJB generated SQL (A1.VALUE is added to columns in resultset):
>
> SELECT DISTINCT A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS,A1.VALUE as
> ojb_col_49
> FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_ABSTRACTATTRIBUTE
> A1 ON A0.ID=A1.OID WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER
> BY 4
>
> What it (could) should look like, and gives the correct results (no
> duplicates in resultset):
>
> SELECT DISTINCT A0.ID, A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS
> FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_AA A1 ON
> A0.ID=A1.OID
>
> WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER BY A1.VALUE
>
> If you agree that this is the problem, is their a quick patch I can
> apply?
>
> Thanx.
>
> Roger Janssen
> iBanx
>
> -Original Message-
> From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 15, 2006 10:13 PM
> To: OJB Users List
> Cc: Selders, Bart
> Subject: Re: Bug : duplicate objects in resultset
>
> hi roger,
>
> i can confirm this problem.
> this simple testcase shows the duplicates:
>
> /**
> * test OrderBy joined column
> */
> public void testOrderByJoined2()
> {
> Criteria crit = new Criteria();
> QueryByCriteria q = QueryFactory.newQuery(Project.class,
crit);
> q.addOrderByAscending("persons.lastname");
>
> Collection results = broker.getCollectionByQuery(q);
> assertNotNull(results);
>
> // compare with count
> int count = broker.getCount(q);
> assertEquals(results.size(), count);
>
> Set resultsSet = new HashSet(results);
> assertEquals(resultsSet.size(), results.size());
> }
>
> jakob
>
>
>
> **
> ***T he information contained in this communication is confidential
> and is intended solely for the use of the individual or entity to
> whom it is addressed.You should not copy, disclose or distribute this
> communication without the authority of iBanx bv. iBanx bv is neither
> liable for the proper and complete transmission of the information has
> been maintainedn or that the communication is free of viruses,
> interceptions or interference.
>
> If you are not the intended recipient of this communication please
> returnt he communication to the sender and delete and destroy all
copies.
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additi
Re: oracle id in m:n tables
Thanks for the link Tom, that information was exactly what i was looking for. 2006/6/19, Thomas Dudziak <[EMAIL PROTECTED]>: On 6/19/06, Dennis Bekkering <[EMAIL PROTECTED]> wrote: > When i add a record to a collection that is of type many 2 many the > following sql is executed > > INSERT INTO m2m_196 (ruleValueListId,ruleValueId) VALUES (?,?) , the id > field is not specified in the query but the table has such a field and > therefore oracle complains about the fact that the id is not there. I know > that i could do without an id field and use a composit id but i rather move > on without having to change all my tables. I dont believe i can let oracle > asign id's automatically like on mysql. Is there a solution to this problem? From what I gather Oracle complains about a missing primary key in the table m2m_196, right ? If that's the case, then there are two easy solutions that I can think of: * If the pair (ruleValueListId,ruleValueId) is unique in the table, then you can generate a composite primary key with these two columns. * Otherwise, you should define a sequence and add a new column to the table for the primary key. Something like this: http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/ In this case, OJB will not have anything to do with the primary key of the m:n table. Tom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- mvg, Dennis
Re: oracle id in m:n tables
On 6/19/06, Dennis Bekkering <[EMAIL PROTECTED]> wrote: When i add a record to a collection that is of type many 2 many the following sql is executed INSERT INTO m2m_196 (ruleValueListId,ruleValueId) VALUES (?,?) , the id field is not specified in the query but the table has such a field and therefore oracle complains about the fact that the id is not there. I know that i could do without an id field and use a composit id but i rather move on without having to change all my tables. I dont believe i can let oracle asign id's automatically like on mysql. Is there a solution to this problem? From what I gather Oracle complains about a missing primary key in the table m2m_196, right ? If that's the case, then there are two easy solutions that I can think of: * If the pair (ruleValueListId,ruleValueId) is unique in the table, then you can generate a composite primary key with these two columns. * Otherwise, you should define a sequence and add a new column to the table for the primary key. Something like this: http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/ In this case, OJB will not have anything to do with the primary key of the m:n table. Tom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Bug : duplicate objects in resultset
Hi,
You're solution only seems to filter out the duplicates based on hashmap
logic, this of course only works when the method hashCode() has properly
been implemented. [And... does this suggested solution support the
paging (pagination) implementation?]
But more critical since the resultset is delivered as a hashset, we
lost all sense of ordering, and that is not what we want here. Hence the
problem remains.
So is their a another way on how to avoid duplicated objects in the
resultset?
Roger Janssen
iBanx
-Original Message-
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, June 16, 2006 9:11 PM
To: OJB Users List
Subject: Re: Bug : duplicate objects in resultset
hi roger,
the following works for me. i use a set instead of a collection:
public void testOrderByJoined2()
{
Criteria crit = new Criteria();
QueryByCriteria q = QueryFactory.newQuery(Project.class, crit,
true);
q.addOrderByAscending("persons.lastname");
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
ManageableHashSet resultsMgmt = (ManageableHashSet)
broker.getCollectionByQuery(RemovalAwareSet.class, q);
assertNotNull(resultsMgmt);
// compare with count
int count = broker.getCount(q);
assertEquals(resultsMgmt.size(), count);
}
hth
jakob
*T
he information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed.You should not copy, disclose or distribute this communication
without the authority of iBanx bv. iBanx bv is neither liable for
the proper and complete transmission of the information has been maintainedn
or that the communication is free of viruses, interceptions or
interference.
If you are not the intended recipient of this communication please returnt
he communication to the sender and delete and destroy all copies.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
oracle id in m:n tables
Hello All, When i add a record to a collection that is of type many 2 many the following sql is executed INSERT INTO m2m_196 (ruleValueListId,ruleValueId) VALUES (?,?) , the id field is not specified in the query but the table has such a field and therefore oracle complains about the fact that the id is not there. I know that i could do without an id field and use a composit id but i rather move on without having to change all my tables. I dont believe i can let oracle asign id's automatically like on mysql. Is there a solution to this problem? Cheers, Dennis
