getCollectionByQuery(q) is not the correct method call for a Report Query. Try broker.getReportQueryIteratorByQuery(q); which returns an Iterator of Object[].
-----Original Message----- From: Ian Hunter [mailto:[EMAIL PROTECTED] Sent: Thursday, September 30, 2004 12:17 PM To: [EMAIL PROTECTED]; OJB Users List Subject: Re: Simple DISTINCT query Like this? (This is what failed) public static Collection getAllBillDates (AuthorizedUser user) throws PersistenceException, PermissionDeniedException { Criteria c = new Criteria(); ReportQueryByCriteria q = QueryFactory.newReportQuery (BillItem.class, c, true); q.setAttributes(new String[] { "billingRun" }); q.addOrderByDescending("billingRun"); DataStore ds = new DataStore (user); Collection result = ds.getCollectionByQuery(q); // <--- exception is thrown here ArrayList dates = new ArrayList(); Iterator i = result.iterator(); while (i.hasNext()) { BillItem bi = (BillItem) i.next(); dates.add (bi.getBillingRun()); } return dates; } The "datastore" object wraps calls to the PB API. You can see a call to "getCollectionByQuery" which basically calls "getCollectionByQuery" and catches PersistenceBrokerExceptions and redefines them as generic-to-application "PersistenceException" objects. The actual object definition is this: <class-descriptor class="BillItem" table="BILLITEM"> <field-descriptor name="id" column="ID_BILLITEM" jdbc-type="INTEGER" primarykey="true" autoincrement="true" /> <field-descriptor name="fkService" column="FK_SERVICE" jdbc-type="INTEGER" nullable="false"/> <reference-descriptor name="service" class-ref="Service" auto-retrieve="true" auto-update="none" auto-delete="none"> <foreignkey field-ref="fkService"/> </reference-descriptor> <field-descriptor name="billingRun" column="BILLING_RUN" jdbc-type="TIMESTAMP" conversion="org.apache.ojb.broker.accesslayer.conversions.JavaDate2SqlTi mest ampFieldConversion" /> <field-descriptor name="amount" column="AMOUNT" jdbc-type="FLOAT" nullable="false" /> <field-descriptor name="notes" column="NOTES" jdbc-type="VARCHAR" nullable="false" /> <field-descriptor name="category" column="CATEGORY" jdbc-type="VARCHAR" nullable="false" /> <field-descriptor name="locked" column="LOCKED" jdbc-type="BIT" conversion="org.apache.ojb.broker.accesslayer.conversions.Boolean2IntFie ldCo nversion" nullable="false" /> </class-descriptor> The error I got was: Caused by: org.apache.ojb.broker.PersistenceBrokerException: Error reading class type: BillItem from result set, current read field was amount at org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readValuesFrom(Ro wRea derDefaultImpl.java:205) at org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readObjectArrayFr om(R owReaderDefaultImpl.java:176) at org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIt erat or.java:427) at org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:265) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Que ryRe ferenceBroker.java:121) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Que ryRe ferenceBroker.java:232) ... 89 more Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid column name: AMOUNT at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.base.BaseResultSet.getColumnOrdinal(Unknown Source) at com.microsoft.jdbc.base.BaseResultSet.getDouble(Unknown Source) at org.apache.ojb.broker.util.JdbcTypesHelper$T_Float.readValueFromResultSe t(Jd bcTypesHelper.java:773) at org.apache.ojb.broker.util.JdbcTypesHelper$BaseType.getObjectFromColumn( Jdbc TypesHelper.java:302) at org.apache.ojb.broker.util.JdbcTypesHelper$BaseType.getObjectFromColumn( Jdbc TypesHelper.java:281) at org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readValuesFrom(Ro wRea derDefaultImpl.java:199) ... 94 more So why is it referring to "amount" at all? ----- Original Message ----- From: "Stijn de Witt" <[EMAIL PROTECTED]> To: "OJB Users List" <[EMAIL PROTECTED]> Sent: Thursday, September 30, 2004 12:33 PM Subject: Re: Simple DISTINCT query > Look at ReportQueries, they are part of the PersistenceBroker interface > and let you perform selects where the result is not a complete object. > > -Stijn > > Ian Hunter wrote: > > >I've been pulling my ahir out trying to accomplish something simple. Say I > >have a table called FOO, with fields FOOID (int), FOODATE (datetime), and > >FOOAMOUNT (double). Some process writes data to this table, where FOOID is > >a autoincrement field, FOODATE is a datestamp, for which there can be > >duplicates, and FOOFOO which represents some number. > > > >I want to generate this query: SELECT DISTINCT FOODATE FROM FOO; -- I can't > >figure out how to do this, because it seems like OBJ is wanting to load all > >the fields from the resulting query, including ones I don't need, such as > >FOOAMOUNT. > > > >How do I do this? > > > >--- > >beati pacifici quoniam filii Dei vocabuntur > > > > > >--------------------------------------------------------------------- > >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]
