I use the BillItem object all over the place and it's fine -- the problem is
that since I'm doing a DISTINCT query on the billingRun field, the amount
field (mapped to the AMOUNT column) isn't being returned by the database,
and that's what I want. I just want the billingRun field, which is a date.
----- Original Message -----
From: "Robert S. Sfeir" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Thursday, September 30, 2004 1:35 PM
Subject: Re: Simple DISTINCT query
> "JDBC]Invalid column name: AMOUNT"
>
> Seems in your xml you're referring to a column called AMOUNT, but your
> table either doesn't have it, or it's not the right name.
>
> R
>
> You Said Something Like: Ian HunterYou Were Done Here.
> > 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.JavaDate2SqlTimest
> > 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.Boolean2IntFieldCo
> > 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(RowRea
> > derDefaultImpl.java:205)
> > at
> >
org.apache.ojb.broker.accesslayer.RowReaderDefaultImpl.readObjectArrayFrom(R
> > owReaderDefaultImpl.java:176)
> > at
> >
org.apache.ojb.broker.accesslayer.RsIterator.getObjectFromResultSet(RsIterat
> > or.java:427)
> > at
org.apache.ojb.broker.accesslayer.RsIterator.next(RsIterator.java:265)
> > at
> >
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
> > ferenceBroker.java:121)
> > at
> >
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
> > 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.readValueFromResultSet(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(RowRea
> > 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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
