Re: OJB 1.0.4, Oracle 10g and LONGVARCHAR column issue
Actually with dbcp it doesn't work either. Any thoughts? 2008/5/5 Vasily Ivanov <[EMAIL PROTECTED]>: > Hello, > > I'm using Oracle 10.2.0.4.0 thin Jdbc driver, Oracle 10g db, Ojb > 1.0.4, Tomcat 4, c3p0 connection pooling, Spring/Spring Modules. > > I'm experiencing a problem materializing an object with LONGVARCHAR > field (LONG datatype in Oracle). > > Caused by: java.sql.SQLException: Stream has already been closed > at > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) > at > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) > at > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) > at oracle.jdbc.driver.LongAccessor.getBytes(LongAccessor.java:151) > at oracle.jdbc.driver.LongAccessor.getString(LongAccessor.java:193) > at > oracle.jdbc.driver.T4CLongAccessor.getString(T4CLongAccessor.java:426) > at > oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:403) > at > com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3316) > at > org.apache.ojb.broker.metadata.JdbcTypesHelper$T_LongVarChar.readValueFromResultSet(JdbcTypesHelper.java:449) > at > org.apache.ojb.broker.metadata.JdbcTypesHelper$BaseType.getObjectFromColumn(JdbcTypesHelper.java:331) > at > org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readValuesFrom(RowReaderDefaultImpl.java:246) > at > org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readObjectArrayFrom(RowReaderDefaultImpl.java:208) > at > org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIterator.java:457) > at > org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:278) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:131) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:263) > at > org.apache.ojb.broker.core.QueryReferenceBroker.doRetrieveCollection(QueryReferenceBroker.java:764) > at > org.apache.ojb.broker.core.QueryReferenceBroker.retrieveCollection(QueryReferenceBroker.java:722) > at > org.apache.ojb.broker.core.QueryReferenceBroker.doRetrieveCollections(QueryReferenceBroker.java:973) > at > org.apache.ojb.broker.core.QueryReferenceBroker.retrieveCollections(QueryReferenceBroker.java:940) > at > org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIterator.java:488) > at > org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:278) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getObjectByQuery(PersistenceBrokerImpl.java:1770) > at > org.apache.ojb.broker.core.DelegatingPersistenceBroker.getObjectByQuery(DelegatingPersistenceBroker.java:352) > at > org.apache.ojb.broker.core.DelegatingPersistenceBroker.getObjectByQuery(DelegatingPersistenceBroker.java:352) > at > org.springmodules.orm.ojb.PersistenceBrokerTemplate$2.doInPersistenceBroker(PersistenceBrokerTemplate.java:187) > at > org.springmodules.orm.ojb.PersistenceBrokerTemplate.execute(PersistenceBrokerTemplate.java:141) > > Oracle documentation reads that: > "If you try to access the data for the none-stream column before > reading the data from the streaming column the JDBC driver discards > the streaming data automatically. You cannot access that data any > more. If you try to get a stream for the LONG column the driver raises > a "Stream Closed" error." > > Even if LONGVARCHAR field is specified first in the list of > field-descriptors OJB ignores that and tries to get none-stream PK > fields first (see method RsIterator#getObjectFromResultSet() which > calls RowReaderDefaultImpl#readPkValuesFrom()) and only than it tries > to get LONGVARCHAR column and fails because stream has already been > invalidated by Oracle driver at that point. > > I have no idea how to get around this problem. The only solution I can > think about is providing my own implementation of RowReaderDefaultImpl > for that particular class. > > Surprisingly enough it works fine if I change connection pooling from > c3p0 to commons dbcp... Maybe DBCP silently swallows "Stream has > already been closed" SQLException? I haven't looked into there yet. > > Thank you, > Vasily > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
OJB 1.0.4, Oracle 10g and LONGVARCHAR column issue
Hello, I'm using Oracle 10.2.0.4.0 thin Jdbc driver, Oracle 10g db, Ojb 1.0.4, Tomcat 4, c3p0 connection pooling, Spring/Spring Modules. I'm experiencing a problem materializing an object with LONGVARCHAR field (LONG datatype in Oracle). Caused by: java.sql.SQLException: Stream has already been closed at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.LongAccessor.getBytes(LongAccessor.java:151) at oracle.jdbc.driver.LongAccessor.getString(LongAccessor.java:193) at oracle.jdbc.driver.T4CLongAccessor.getString(T4CLongAccessor.java:426) at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:403) at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3316) at org.apache.ojb.broker.metadata.JdbcTypesHelper$T_LongVarChar.readValueFromResultSet(JdbcTypesHelper.java:449) at org.apache.ojb.broker.metadata.JdbcTypesHelper$BaseType.getObjectFromColumn(JdbcTypesHelper.java:331) at org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readValuesFrom(RowReaderDefaultImpl.java:246) at org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readObjectArrayFrom(RowReaderDefaultImpl.java:208) at org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIterator.java:457) at org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:278) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:131) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:263) at org.apache.ojb.broker.core.QueryReferenceBroker.doRetrieveCollection(QueryReferenceBroker.java:764) at org.apache.ojb.broker.core.QueryReferenceBroker.retrieveCollection(QueryReferenceBroker.java:722) at org.apache.ojb.broker.core.QueryReferenceBroker.doRetrieveCollections(QueryReferenceBroker.java:973) at org.apache.ojb.broker.core.QueryReferenceBroker.retrieveCollections(QueryReferenceBroker.java:940) at org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIterator.java:488) at org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:278) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getObjectByQuery(PersistenceBrokerImpl.java:1770) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getObjectByQuery(DelegatingPersistenceBroker.java:352) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getObjectByQuery(DelegatingPersistenceBroker.java:352) at org.springmodules.orm.ojb.PersistenceBrokerTemplate$2.doInPersistenceBroker(PersistenceBrokerTemplate.java:187) at org.springmodules.orm.ojb.PersistenceBrokerTemplate.execute(PersistenceBrokerTemplate.java:141) Oracle documentation reads that: "If you try to access the data for the none-stream column before reading the data from the streaming column the JDBC driver discards the streaming data automatically. You cannot access that data any more. If you try to get a stream for the LONG column the driver raises a "Stream Closed" error." Even if LONGVARCHAR field is specified first in the list of field-descriptors OJB ignores that and tries to get none-stream PK fields first (see method RsIterator#getObjectFromResultSet() which calls RowReaderDefaultImpl#readPkValuesFrom()) and only than it tries to get LONGVARCHAR column and fails because stream has already been invalidated by Oracle driver at that point. I have no idea how to get around this problem. The only solution I can think about is providing my own implementation of RowReaderDefaultImpl for that particular class. Surprisingly enough it works fine if I change connection pooling from c3p0 to commons dbcp... Maybe DBCP silently swallows "Stream has already been closed" SQLException? I haven't looked into there yet. Thank you, Vasily - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: getIteratorByQuery + thousands of objects = OutOfMemoryError
We are using Ojb 1.0.4. On 16/11/2007, Vasily Ivanov <[EMAIL PROTECTED]> wrote: > Hello, > > We've got a system that needs to update thousands (possibly hundreds > of thousands) of Objects. > > After reading OJB FAQ I thought that > PersistenceBroker.getIteratorByQuery(query) would be the best solution > in this case. Query runs fine and returns RsIterator. However the > problems start when I try to iterate and process few thousands of > records eventually I get OutOfMemoryError (out of java heap). I did > profiling and found that QueryReferenceBroker object holds a big > ArrayList called prefetchingListeners that grows rapidly while > iterating. I think that's because OJB keeps all objects to commit when > PersistenceBroker is closed. > > What I do is I call the following statement after each iteration: > ((PersistenceBrokerInternal) > pb).getReferenceBroker().removePrefetchingListeners(); > > That helped but that's obviously not the best way of doing it and I > don't know how thread-safe it is. > > Is there any more appropriate way of doing that? > > Thank you, > Vasily > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
getIteratorByQuery + thousands of objects = OutOfMemoryError
Hello, We've got a system that needs to update thousands (possibly hundreds of thousands) of Objects. After reading OJB FAQ I thought that PersistenceBroker.getIteratorByQuery(query) would be the best solution in this case. Query runs fine and returns RsIterator. However the problems start when I try to iterate and process few thousands of records eventually I get OutOfMemoryError (out of java heap). I did profiling and found that QueryReferenceBroker object holds a big ArrayList called prefetchingListeners that grows rapidly while iterating. I think that's because OJB keeps all objects to commit when PersistenceBroker is closed. What I do is I call the following statement after each iteration: ((PersistenceBrokerInternal) pb).getReferenceBroker().removePrefetchingListeners(); That helped but that's obviously not the best way of doing it and I don't know how thread-safe it is. Is there any more appropriate way of doing that? Thank you, Vasily - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: best solution for updating many objects ?
Sorry, can't help. I'm using Spring Jdbc abstraction to do plain sql calls. Cheers, Vasily On 4/19/07, Björn Agel <[EMAIL PROTECTED]> wrote: can you give a code example pls ? im sticking in how to execute plain sql with ojb ... thank, Björn Vasily Ivanov wrote: > Hi Björn, > > I usually do retrieve list of IDs via OJB's > getReportQueryIteratorByQuery and then run simple plain SQL to bulk > update one field in each record by id. > > Cheers, > Vasily > > On 4/17/07, Björn Agel <[EMAIL PROTECTED]> wrote: >> hi, >> >> say we have a table, where many thousands objects with about 30 fields >> are stored. >> Now I want to update only 1 of these 30 colums for a few hundred >> objects. >> >> Atm I retrieve every single object, change 1 value and store it ... >> >> What is the best solution for this task ? >> Is there anything similar to the "ALTER TABLE" statement ? >> >> with best regards, >> Björn Agel > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: best solution for updating many objects ?
Hi Björn, I usually do retrieve list of IDs via OJB's getReportQueryIteratorByQuery and then run simple plain SQL to bulk update one field in each record by id. Cheers, Vasily On 4/17/07, Björn Agel <[EMAIL PROTECTED]> wrote: hi, say we have a table, where many thousands objects with about 30 fields are stored. Now I want to update only 1 of these 30 colums for a few hundred objects. Atm I retrieve every single object, change 1 value and store it ... What is the best solution for this task ? Is there anything similar to the "ALTER TABLE" statement ? with best regards, Björn Agel - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Want do query Object width no 1-n reference
Hello,
I usually do criteria.addNotExists(subQuery) with
ReportQueryByCriteria subQuery and set subQuery.setAttributes(new
String[] { "1" }), it should be even faster than "anything ligth of
Group (key field by example)".
Cheers,
Vasily
On 12/7/06, Bruno CROS <[EMAIL PROTECTED]> wrote:
Well, try notExists criteria and a subquery on Group returning anything
ligth of Group (key field by example)
Bruno.
On 12/6/06, Josef Wagner <[EMAIL PROTECTED]> wrote:
>
> Hello List,
>
> I want to have all "Users", which are not in a "Group"
>
> Here my repository:
>
>
> jdbc-type="INTEGER"primarykey="true"
> autoincrement="true">
> jdbc-type="VARCHAR" length="100" >
>
> name="groups"
>
> collection-class="
> org.apache.ojb.broker.util.collections.ManageableArrayList"
> element-class-ref="de.on_ergy.lakon.data.model.Group"
> auto-retrieve="true"
> auto-update="false"
> auto-delete="link"
> proxy="true"
> indirection-table="user_group"
> >
>
>
>
>
>
> How can I get all Users, which currently not in a Group?
> Is there a way like this?
> Criteria crit = new Criteria();
> crit.addIsNull("groups");
>
> Thanks a lot for your help!
>
> regards
>
> --
> Josef Wagner
>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: How-To select on a "group" function
Hi Francesco,
You can do the following:
ReportQueryByCriteria query = QueryFactory.newReportQuery(Clazz.class,
new Criteria());
query.setAttributes(new String[] { "max(value1)", "value2" });
query.addOrderByAscending("max(value1)");
query.addGroupBy("value2");
Iterator itr = pb.getReportQueryIteratorByQuery(query);
I hope that helps.
Regards,
Vasily
On 11/22/06, Francesco Martinelli <[EMAIL PROTECTED]> wrote:
Hi,
I would like to retrieve a record using the following SQL-Like statement:
"select max(value) from " + className
Does anyone know how to "convert" this to OQL?
I know it would be possible to retrieve all the records, then sort them
out, and pick up the first, but I would prefer to reduce the amount of
data retrieved from the database.
I also wonder if it were possible to order the "resultset" according to
one field, e.g. of SQL-like would be:
"select xxx from " + className + " order by value".
Thenk you,
Francesco.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: report query in order by
Hi Abid, It was me who posted that question. I also posted an improvement request into Jira: https://issues.apache.org/jira/browse/OJB-104 There is a workaround in there. Have a look. Regards, Vasily On 10/23/06, Abid Hussain <[EMAIL PROTECTED]> wrote: Hi everybody, the following problem was already posted in the mailinglist in March 06. <-- I've got two classes: class Parent: int id String data Collection children class Child: int id String data int parentId Parent parent I need to get collection of parents sorted by the number of children, but I didn't find any way to put ReportQuery to "ORDER BY" statement. --> The problem is that there is no method in QueryByCriteria like QueryByCriteria.addOrderBy(ReportQuery), so there wasn't found a proper solution in the mailinglist when the problem was posted. I've got the same problem and am asking if there is a solution maybe in ojb 1.0.5? Regards, Abid Hussain -- Abid Hussain Mail: [EMAIL PROTECTED] Web: http://www.abid76.de - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Clear cache issue!
Hi Vagula, Consider this one: broker.removeFromCache(entityOrId); Cheers, Vasily On 10/16/06, Vagula <[EMAIL PROTECTED]> wrote: Hi All, I'm using the following code: QueryByCriteria queryByCriteria = new QueryByCriteria(RefInfoData.class, criteria, true); broker.clearCache(); Collection refInfo = (Collection) broker.getCollectionByQuery(queryByCriteria); For the class 'RefInfoData' we have set the cache level as default implementation. When we use the method "broker.clearCache()" we are facing performance issues. But when we don't clear cache there is dirty read. We are maintaining the cache for reference tables. Is there a way to clear cache for the specific object instead of whole cache? Regards, Vagula CAUTION - Disclaimer * This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Support of database views
Yeah, I'm a bit late here. We do not use cache for objects retrieved from views, so the suggestion was correct. Also be aware that on big views (that join lots of table for instance) retrieving objects could be quite expensive. The thing is that db refreshes view each time you call it, unless you are using materialised views which is not the case in dynamic apps. We had some problems because of that. We had to get rid of views and connect to tables directly. Cheers, Vasily On 9/20/06, Hiller, Frank RD-AS2 <[EMAIL PROTECTED]> wrote: Yes, it works. Thank youFrank -Ursprüngliche Nachricht- Von: Charles Anthony [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 19. September 2006 16:09 An: OJB Users List Betreff: RE: Support of database views Well, that's my guess... > -Original Message- > From: Hiller, Frank RD-AS2 [mailto:[EMAIL PROTECTED] > Sent: 19 September 2006 15:08 > To: OJB Users List > Subject: AW: Support of database views > > Ok, I see. > Setting > class="org.apache.ojb.broker.cache.ObjectCacheEmptyImpl"/> > on the specific table (that actually is a view) resolves the problem? > > Frank > > -Ursprüngliche Nachricht- > Von: Charles Anthony [mailto:[EMAIL PROTECTED] > Gesendet: Dienstag, 19. September 2006 16:04 > An: OJB Users List > Betreff: RE: Support of database views > > Yes, but all (by default) all reads ARE transparently cached (to > improve performance). > > So if you've read C, then you do an update of A or B, then you re-read > C - you will not see the updates but the OLD version of C. > > It's a bit difficult to explain, and I'm obviously not being very > clear. > > > > -Original Message- > > From: Hiller, Frank RD-AS2 [mailto:[EMAIL PROTECTED] > > Sent: 19 September 2006 14:52 > > To: OJB Users List > > Subject: AW: Support of database views > > > > I do not expect to automatically update the object in cache > > representing the view. > > After the update of A or B I explicitely read the view. At > that point > > my expactation was to retrieve the new value. The SQL statement > > returns the correct (new) value, but ojb uses the object from the > > cache with the old value. This confuses me a little. > > > > Best regards, > > Frank > > > > > > -Ursprüngliche Nachricht- > > Von: Charles Anthony [mailto:[EMAIL PROTECTED] > > Gesendet: Dienstag, 19. September 2006 15:23 > > An: OJB Users List > > Betreff: RE: Support of database views > > > > No, it's not really a bug; > > > > Let's say you have objects A, B and C where C is an objject mapped > > onto a view joining A & B. > > > > OJB Caches objects on their Class + PK; when you update an > object, it > > is ejected from the cache based on it's class and PK. > > > > So, let's say C with a PK of one is references A with a PK of 2 > > > > You read from C -> C[1] is inserted in the cache You read A > > -> A[2] is inserted in the cache You update A -> A[2] is > > dropped from the cache but C[1] is still in the cache. > > > > OJB doesn't (and can't) know about the relationship between A & C > > > > If all updates are done from OJB, and if you can derive the PK of C > > (in the above example) from A, then you could implement > some kind of > > listener that says "whenever A is updated, drop C from the cache" > > > > You might also be able to specify that C should never be cached (by > > specifying the org.apache.ojb.broker.cache.ObjectCacheEmptyImpl in > > object-cache element for the class in the repostory.xml) > > > > > > Cheers, > > Charles. > > > > > > > -Original Message- > > > From: Hiller, Frank RD-AS2 [mailto:[EMAIL PROTECTED] > > > Sent: 19 September 2006 14:09 > > > To: OJB Users List > > > Subject: AW: Support of database views > > > > > > Additional information: When I explicitely remove the > > objects from the > > > brokers internal cache (broker.removeFromCache(obj)) it works. > > > Is it a bug? > > > > > > Frank > > > > > > -Ursprüngliche Nachricht- > > > Von: Hiller, Frank RD-AS2 [mailto:[EMAIL PROTECTED] > > > Gesendet: Dienstag, 19. September 2006 13:56 > > > An: OJB Users List > > > Betreff: AW: Support of database views > > > > > > Hi Vasily, > > > > > > using the view as a table (following your recommendation) shows a > > > strange behaviour. >
Re: Support of database views
Hi Frank, We used to work with views without any issue, just put view's name as "table" attribute in "class-descriptor". However, you will only be able to read objects from that view. Regards, Vasily On 9/11/06, Hiller, Frank RD-AS2 <[EMAIL PROTECTED]> wrote: Short question: I couldn't find a functionality to deal with database views (create view...) within OJB. Is there a reason for that? Is the answer "use ReportQuery". Thank you, Frank Confidentiality note: The information in this email and any attachment may contain confidential and proprietary information of Heidelberger Druckmaschinen AG and/or its affiliates and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you are hereby notified that any review, reliance or distribution by others or forwarding without express permission is strictly prohibited and may cause liability. In case you have received this message due to an error in transmission, we kindly ask you to notify the sender immediately and to delete this email and any attachment from your system. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: addExists(subQuery) builds invalid SQL if subquery has orderby
Hi Jakob,
We are using Oralce 10g. It has problems with order by in sub queries.
Cheers,
Vasily
On 8/25/06, Jakob Braeuchi <[EMAIL PROTECTED]> wrote:
hi vasily,
forgot to post the sql and the testcase.
SELECT A0.Kategorie_Nr
FROM Kategorien A0
WHERE EXISTS (
SELECT B0.Artikel_Nr
FROM Artikel B0
WHERE (B0.Kategorie_Nr = A0.Kategorie_Nr) AND
B0.Artikelname LIKE '%Tofu%' ORDER BY 1)
this sql breaks in hsqldb.
public void testSubQueryExists()
{
ReportQueryByCriteria subQuery;
Criteria subCrit = new Criteria();
Criteria crit = new Criteria();
subCrit.addEqualToField("productGroupId",
Criteria.PARENT_QUERY_PREFIX + "groupId");
subCrit.addLike("articleName", "%Tofu%");
subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
subQuery.setAttributes(new String[]{"articleId"});
subQuery.addOrderByAscending("articleId"); // PROBLEMS in hsqldb
crit.addExists(subQuery);
Query q = QueryFactory.newQuery(ProductGroup.class, crit);
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
assertEquals(1, results.size());
}
jakob
Jakob Braeuchi schrieb:
> hi vasily,
>
> order by in the subquery seems to be a problem for hsqldb. it works in
> mysql.
>
> jakob
>
> Vasily Ivanov schrieb:
>> Hi All,
>>
>> I've got the following code:
>> ===Code===
>> //build sub query
>> Criteria subCriteria = new Criteria();
>> subCriteria.addEqualToField("parentId", Criteria.PARENT_QUERY_PREFIX +
>> "id");
>> subCriteria.addIn("someChildFeild", someCollection);
>>
>> ReportQueryByCriteria subQuery =
>> QueryFactory.newReportQuery(Child.class, subCriteria);
>> subQuery.setAttributes(new String[] { "1" });
>> subQuery.addOrderByDescending("someChildFeild"); //**
>>
>> //build main query
>> Criteria mainCriteria = new Criteria();
>> mainCriteria.addExists(subQuery);
>>
>> ReportQueryByCriteria mainQuery =
>> QueryFactory.newReportQuery(Parent.class, mainCriteria);
>> mainQuery.setAttributes(new String[] { "id", "someParentFeild1",
>> "someParentFeild2" });
>> mainQuery.addOrderByDescending("someParentFeild2");
>>
>> ===Generated SQL===
>> SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
>> FROM PARENT A0
>> WHERE EXISTS (SELECT 1, B0.SOME_CHILD_FEILD as ojb_col_2
>> FROM CHILD B0
>> WHERE (B0.PARENT_ID = A0.ID)
>> AND (B0.SOME_CHILD_FEILD IN (?, ?))
>> ORDER BY 2 DESC)
>> ORDER BY 3 DESC
>> =
>> This SQL throws "ORA-00907: missing right parenthesis".
>>
>> If we remove line marked with //** we'll get:
>> ===Generated SQL===
>> SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
>> FROM PARENT A0
>> WHERE EXISTS (SELECT 1
>> FROM CHILD B0
>> WHERE (B0.PARENT_ID = A0.ID)
>> AND (B0.SOME_CHILD_FEILD IN (?, ?)))
>> ORDER BY 3 DESC
>> =
>> ...which works fine.
>>
>> Question: Should addExists(subQuery) check that subQuery doesn't have
>> any orderby added or it's up to developer?
>>
>> Cheers,
>> Vasily
>>
>> -
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Deleting items in collection?
Hi Sebastian,
if I delete an ClassB object from the collection in
ClassA and store the ClassA object again, this
ClassB object is not removed from the database
It depends on how you delete this object from collection. You should
get object (aObject) with all references using OJB query, first. Than
remove object (bObject) from collection (by calling
aObject.getBs().remove(bObject)) and than call broker.store(aObject).
That should remove bObject from db.
Cheers,
Vasily
On 8/21/06, Sebastian Stein <[EMAIL PROTECTED]> wrote:
Hi,
this might be an easy question, I'm quite new to OJB. I have a question
concerning the following example:
Class A has a primary key field "PKEY" with type int and other elements in
addition.
Class B has "PKEY" as a foreign key field plus another additional field.
Together, both entries are the primary key for Class B.
Now, if I query Class A in source code, I want to get a Collection of objects
of Class B belonging to Class A. So in repository.xml I did the following:
And here for ClassB:
Now, if I create in my source a class A instance and add a class B collection
to it, everything goes fine. Class A is stored in the DB as well as all
instances of class B.
On deletion of class A, I also start a query to delete all B connected to it,
so something like:
broker = PersistenceBrokerFactory.createPersistenceBroker(pbkey);
Criteria crit = new Criteria();
crit.addEqualTo("pkey", new Integer(pkey));
Query query_A = new QueryByCriteria(namespace.ClassA.class, crit);
Query query_B = new QueryByCriteria(namespace.ClassB.class, crit);
broker.deleteByQuery(queryA);
broker.deleteByQuery(queryA);
If I insert a new instance of ClassB to the collection of ClassB objects in
ClassA, this new instance is also automatically added to the DB. However, and
now we get to the problem, if I delete an ClassB object from the collection in
ClassA and store the ClassA object again, this ClassB object is not removed
from the database.
What do I have to do to automatically trigger the deletion of this object?
Thanks for your help,
Sebastian
--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
addExists(subQuery) builds invalid SQL if subquery has orderby
Hi All,
I've got the following code:
===Code===
//build sub query
Criteria subCriteria = new Criteria();
subCriteria.addEqualToField("parentId", Criteria.PARENT_QUERY_PREFIX + "id");
subCriteria.addIn("someChildFeild", someCollection);
ReportQueryByCriteria subQuery =
QueryFactory.newReportQuery(Child.class, subCriteria);
subQuery.setAttributes(new String[] { "1" });
subQuery.addOrderByDescending("someChildFeild"); //**
//build main query
Criteria mainCriteria = new Criteria();
mainCriteria.addExists(subQuery);
ReportQueryByCriteria mainQuery =
QueryFactory.newReportQuery(Parent.class, mainCriteria);
mainQuery.setAttributes(new String[] { "id", "someParentFeild1",
"someParentFeild2" });
mainQuery.addOrderByDescending("someParentFeild2");
===Generated SQL===
SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
FROM PARENT A0
WHERE EXISTS (SELECT 1, B0.SOME_CHILD_FEILD as ojb_col_2
FROM CHILD B0
WHERE (B0.PARENT_ID = A0.ID)
AND (B0.SOME_CHILD_FEILD IN (?, ?))
ORDER BY 2 DESC)
ORDER BY 3 DESC
=
This SQL throws "ORA-00907: missing right parenthesis".
If we remove line marked with //** we'll get:
===Generated SQL===
SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
FROM PARENT A0
WHERE EXISTS (SELECT 1
FROM CHILD B0
WHERE (B0.PARENT_ID = A0.ID)
AND (B0.SOME_CHILD_FEILD IN (?, ?)))
ORDER BY 3 DESC
=
...which works fine.
Question: Should addExists(subQuery) check that subQuery doesn't have
any orderby added or it's up to developer?
Cheers,
Vasily
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Count a Subquery with group clause
Sorry again, :)
Your query will run, but it will not make any sence. It is the same as write:
SELECT count(*) FROM
FROM people
Cheers,
Vasily
On 8/9/06, Vasily Ivanov <[EMAIL PROTECTED]> wrote:
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> How can I generate the following SQL statement using
> OJB?
>
> SELECT count(*) FROM (
> SELECT lastname, age, city
> FROM people
> GROUP BY lastname, age, city
> );
>
> Thanks for your help,
>
> Bin
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Count a Subquery with group clause
Sorry,
Read this:
subQuery.addGroupBy(new String[] { "lastname, age, city" });
as this:
query.addGroupBy(new String[] { "lastname, age, city" });
Cheers,
Vasily
On 8/9/06, Vasily Ivanov <[EMAIL PROTECTED]> wrote:
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> How can I generate the following SQL statement using
> OJB?
>
> SELECT count(*) FROM (
> SELECT lastname, age, city
> FROM people
> GROUP BY lastname, age, city
> );
>
> Thanks for your help,
>
> Bin
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Count a Subquery with group clause
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao <[EMAIL PROTECTED]> wrote:
Hello All,
How can I generate the following SQL statement using
OJB?
SELECT count(*) FROM (
SELECT lastname, age, city
FROM people
GROUP BY lastname, age, city
);
Thanks for your help,
Bin
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Ability to access primary key prior to Store of Object
Hi Gary, Have a look here: http://db.apache.org/ojb/docu/guides/basic-technique.html#Link+references Cheers, Vasily On 7/30/06, Gary Bartlett <[EMAIL PROTECTED]> wrote: Greetings I am working on upgrading an application that was built using OJB 0.94 to release 1.0.4. I am running into some difficulties getting a primary key assigned for a newly created persistent object before the object is actually stored into the database. Under 0.94 it seemed I could just create a new instance of my persistent object and then call Identity id = new Identity(object); passing the newly created object. I did not have to do anything with the returned Identity (id) --- but a side effect of calling this was that the primaryKey of the newly created object was populated. In 1.04 the above call is not available - so I changed it to Identity id = new IdentityFactoryImpl(getBroker()).buildIdentity(object); This does not seem to have the same side effect - and I am unsure if I can even get the primaryKey from the returned Identity. So I guess my question is whether or not there is a way to get a primaryKey for a newly created object prior to the object being stored into the database. Thanks, Gary Bartlett __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: OJB/Oracle/BLOBs + "Consider using Oracle9i as OJB platform" error
I've just solved this problem by changing platform="Oracle" to platform="Oracle9i" in our "jdbc-connection-descriptor"s It even works with Oracle 9 thin driver, no need to upgrade to 10g driver. Thanks, Vasily On 7/7/06, Vasily Ivanov <[EMAIL PROTECTED]> wrote: Hi all, We are using OJB 1.0.3 with Oracle 9 thin driver. We've just started to persist BLOBs in DB and we keep getting an error "Oracle thin driver cannot update BLOB values with length>2000. (Consider using Oracle9i as OJB platform.)" when trying to persist something >2k. :( It doesn't work even when we moved to Oracle 10g driver. Do you have any idea how could we solve this problem? Thank you very much for your time. Regards, Vasily Ivanov - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
OJB/Oracle/BLOBs + "Consider using Oracle9i as OJB platform" error
Hi all, We are using OJB 1.0.3 with Oracle 9 thin driver. We've just started to persist BLOBs in DB and we keep getting an error "Oracle thin driver cannot update BLOB values with length>2000. (Consider using Oracle9i as OJB platform.)" when trying to persist something >2k. :( It doesn't work even when we moved to Oracle 10g driver. Do you have any idea how could we solve this problem? Thank you very much for your time. Regards, Vasily Ivanov - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Order in ReportQueryByCriteria
Hi Serge,
I didn't try that, but it should work:
query.setAttributes(new String[] {"id","count(*)"});
query.addOrderByAscending("count(*)");
query.addGroupBy("id");
If it doesn't work try changing "count(*)" to "count(1)".
Cheers,
Vasily
On 6/8/06, Serge78 <[EMAIL PROTECTED]> wrote:
Hi!
I'm trying to sort results of a ReportQueryByCriteria by calculated field:
Criteria criteria = ...;
ReportQueryByCriteria query = new ReportQueryByCriteria(AClass.class,
criteria);
query.setAttributes(new String[] {"id","count(*)"});
query.addGroupBy("id");
Now i want sort by second field (count)
How can I do that?
query.setAttributes(new String[] {"id","count(*) AS aa"});
query.addOrderBy("aa", false);
Doesn't work.
Thank in advance.
--
View this message in context:
http://www.nabble.com/Order-in-ReportQueryByCriteria-t1751968.html#a4763152
Sent from the Apache DB - ObjectRelationalBridge Users forum at Nabble.com.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: OJB DB2 UDB, retrieving DATE ????
Hi,
Try this:
ReportQueryByCriteria query =
QueryFactory.newReportQuery(YourClass.class, criteria);
query.setAttributes(new String[] { "char(dateField, 'ISO')", "anotherField" });
yourPersistenceManager.getBroker().getReportQueryIteratorByQuery(query);
I didn't test, but it should work.
On 3/31/06, ABOU LINA <[EMAIL PROTECTED]> wrote:
> Hi;
>
> AS400 as server.
> DB2 UDB as database .
>
> in a table we have a record whith the date "2999-12-31".
> when using ojb to retreive this record, the value of date is returned null.
>
> with an sql tool we have be able to get this date with the instruction
> select char(datetoshow, "ISO")...
> we have also verify that ojb generate select datetoshow without char(...,
> "ISO").
>
> best regards,
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Subqueries in ODMG query
http://db.apache.org/ojb/docu/guides/query.html#subqueries On 3/24/06, Eric Kelm <[EMAIL PROTECTED]> wrote: > Is it possible to use a subquery inside of an ODMG query e.g. > > select jobs from " + JobBO.class.getName(); > where employees_idemployee = " + employee.getIdEmployee(); > idjob like $1 or idtask_code like >(select task_codes from " + TaskCodeBO.class.getName(); > where task_code_name like $2 >) > > If this is not correct syntax (seems not to be) can someone point me in the > right direction? Thanks > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Report Query in ORDER BY
ok. I have no idea what "jira" is. Could you give me the link?
On 3/10/06, Jakob Braeuchi <[EMAIL PROTECTED]> wrote:
> hi vasily,
>
> you're right. we do not support ordering by subquery.
> you can open a feature request on jira.
>
> jakob
>
> Vasily Ivanov schrieb:
> > Hi Jakob,
> >
> > As I understand, report subquery (with Criteria.PARENT_QUERY_PREFIX +
> > "id") can be used only in WHERE clause (Criteria class), but not in
> > ORDER BY clause. Have a look:
> > QueryByCriteria.addOrderBy(String, boolean)
> > QueryByCriteria.addOrderBy(FieldHelper)
> > QueryByCriteria.addOrderByAscending(String)
> > QueryByCriteria.addOrderByDescending(String)
> >
> > ... no methods to add report subquery. :(
> >
> > On 3/9/06, Jakob Braeuchi <[EMAIL PROTECTED]> wrote:
> >
> >>hi vasily,
> >>
> >>you can use Criteria.PARENT_QUERY_PREFIX to prefix an attribute of the
> >>subquery. see QueryTest#testSubQuery3 and testSubQuery4.
> >>
> >>but i've to admit i nver tried it with addOrderBy().
> >>
> >>hth
> >>jakob
> >>
> >>
> >>Vasily Ivanov schrieb:
> >>
> >>>Hi All,
> >>>
> >>>I've got two classes:
> >>>class Parent:
> >>> int id
> >>> String data
> >>> Collection children
> >>>class Child:
> >>> int id
> >>> String data
> >>> int parentId
> >>> Parent parent
> >>>
> >>>I need to get collection of parents sorted by the number of children,
> >>>but I didn't find any way to put Report Query to "ORDER BY" statement.
> >>>Here is the code that works:
> >>>
> >>>
> >>>>QueryByCriteria query = QueryFactory.newQuery(Parent.class, new
> >>>>Criteria());
> >>>>query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
> >>>
> >>>AAA.PARENT_ID = A0.ID)");
> >>>
> >>>>persistenceManager.getBroker().getCollectionByQuery(query);
> >>>
> >>>This is generated sql:
> >>>
> >>>
> >>>>SELECT A0.ID, A0.DATA, (SELECT count(1)
> >>>
> >>> FROM CHILD AAA
> >>> WHERE AAA.PARENT_ID =
> >>>A0.ID) as ojb_col_3
> >>>
> >>>>FROM PARENT A0
> >>>>ORDER BY 3 DESC
> >>>
> >>>It works, but I don't like "addOrderByDescending" hard coded table
> >>>alias "A0". There should be the way to set report subquery as OrderBy
> >>>which should solve the problem.
> >>>I changed code to:
> >>>
> >>>
> >>>>query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
> >>>>AAA.PARENT_ID = (id))");
> >>>
> >>>But it doesn't work because of "SqlHelper.splitPath()" I guess, it
> >>>doesn't substitute "(id)" by column name "A0.ID".
> >>>
> >>>Any help will be greatly appreciated.
> >>>
> >>>Regards,
> >>> Vasily Ivanov
> >>>
> >>>-
> >>>To unsubscribe, e-mail: [EMAIL PROTECTED]
> >>>For additional commands, e-mail: [EMAIL PROTECTED]
> >>>
> >>>
> >>>
> >>
> >>-
> >>To unsubscribe, e-mail: [EMAIL PROTECTED]
> >>For additional commands, e-mail: [EMAIL PROTECTED]
> >>
> >>
> >
> >
> > -
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Sub classes and QueryByCriteria
Sorry, I've got it.
I've just removed from Class1
. :)
Thanks.
On 3/9/06, Vasily Ivanov <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> Are there any way to ask OJB not to query for subclasses when querying
> for parent class.
>
> For example, I've got:
> Class1
> Class2 extends Class1
> Class3 extends Class2
>
> When I want to get the collection of Class1 objects I don't want to
> get all Class2 and Class3 objects, but OJB does it. I have to manualy
> put:
> > criteria.addEqualTo("ojbConcreteClass", Class1.class.getName());
> It works, but OJB still runs additional queries for sub classes which
> return nothing.
>
> It should be the way to do it via some method or something like that.
> Does someone know how to do it?
>
> Thanks.
>
> Regards,
> Vasily
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Sub classes and QueryByCriteria
Hi All,
Are there any way to ask OJB not to query for subclasses when querying
for parent class.
For example, I've got:
Class1
Class2 extends Class1
Class3 extends Class2
When I want to get the collection of Class1 objects I don't want to
get all Class2 and Class3 objects, but OJB does it. I have to manualy
put:
> criteria.addEqualTo("ojbConcreteClass", Class1.class.getName());
It works, but OJB still runs additional queries for sub classes which
return nothing.
It should be the way to do it via some method or something like that.
Does someone know how to do it?
Thanks.
Regards,
Vasily
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Report Query in ORDER BY
Hi Jakob,
As I understand, report subquery (with Criteria.PARENT_QUERY_PREFIX +
"id") can be used only in WHERE clause (Criteria class), but not in
ORDER BY clause. Have a look:
QueryByCriteria.addOrderBy(String, boolean)
QueryByCriteria.addOrderBy(FieldHelper)
QueryByCriteria.addOrderByAscending(String)
QueryByCriteria.addOrderByDescending(String)
... no methods to add report subquery. :(
On 3/9/06, Jakob Braeuchi <[EMAIL PROTECTED]> wrote:
> hi vasily,
>
> you can use Criteria.PARENT_QUERY_PREFIX to prefix an attribute of the
> subquery. see QueryTest#testSubQuery3 and testSubQuery4.
>
> but i've to admit i nver tried it with addOrderBy().
>
> hth
> jakob
>
>
> Vasily Ivanov schrieb:
> > Hi All,
> >
> > I've got two classes:
> > class Parent:
> > int id
> > String data
> > Collection children
> > class Child:
> > int id
> > String data
> > int parentId
> > Parent parent
> >
> > I need to get collection of parents sorted by the number of children,
> > but I didn't find any way to put Report Query to "ORDER BY" statement.
> > Here is the code that works:
> >
> >> QueryByCriteria query = QueryFactory.newQuery(Parent.class, new
> >> Criteria());
> >> query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
> > AAA.PARENT_ID = A0.ID)");
> >> persistenceManager.getBroker().getCollectionByQuery(query);
> >
> > This is generated sql:
> >
> >> SELECT A0.ID, A0.DATA, (SELECT count(1)
> >FROM CHILD AAA
> >WHERE AAA.PARENT_ID =
> > A0.ID) as ojb_col_3
> >> FROM PARENT A0
> >> ORDER BY 3 DESC
> >
> > It works, but I don't like "addOrderByDescending" hard coded table
> > alias "A0". There should be the way to set report subquery as OrderBy
> > which should solve the problem.
> > I changed code to:
> >
> >> query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
> >> AAA.PARENT_ID = (id))");
> >
> > But it doesn't work because of "SqlHelper.splitPath()" I guess, it
> > doesn't substitute "(id)" by column name "A0.ID".
> >
> > Any help will be greatly appreciated.
> >
> > Regards,
> > Vasily Ivanov
> >
> > -
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Report Query in ORDER BY
Hi All,
I've got two classes:
class Parent:
int id
String data
Collection children
class Child:
int id
String data
int parentId
Parent parent
I need to get collection of parents sorted by the number of children,
but I didn't find any way to put Report Query to "ORDER BY" statement.
Here is the code that works:
>QueryByCriteria query = QueryFactory.newQuery(Parent.class, new Criteria());
>query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
AAA.PARENT_ID = A0.ID)");
>persistenceManager.getBroker().getCollectionByQuery(query);
This is generated sql:
> SELECT A0.ID, A0.DATA, (SELECT count(1)
FROM CHILD AAA
WHERE AAA.PARENT_ID =
A0.ID) as ojb_col_3
> FROM PARENT A0
> ORDER BY 3 DESC
It works, but I don't like "addOrderByDescending" hard coded table
alias "A0". There should be the way to set report subquery as OrderBy
which should solve the problem.
I changed code to:
> query.addOrderByDescending("(SELECT count(1) FROM CHILD AAA WHERE
> AAA.PARENT_ID = (id))");
But it doesn't work because of "SqlHelper.splitPath()" I guess, it
doesn't substitute "(id)" by column name "A0.ID".
Any help will be greatly appreciated.
Regards,
Vasily Ivanov
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
