You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Ian Hunter <ih...@hunterweb.net> on 2004/09/30 18:30:50 UTC
Simple DISTINCT query
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple DISTINCT query
Posted by Ian Hunter <ih...@hunterweb.net>.
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" <ro...@codepuccino.com>
To: "OJB Users List" <oj...@db.apache.org>
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" <St...@bergland-it.nl>
> > To: "OJB Users List" <oj...@db.apache.org>
> > 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: ojb-user-unsubscribe@db.apache.org
> >> >For additional commands, e-mail: ojb-user-help@db.apache.org
> >> >
> >> >
> >> >
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >> For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple DISTINCT query
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
"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" <St...@bergland-it.nl>
> To: "OJB Users List" <oj...@db.apache.org>
> 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: ojb-user-unsubscribe@db.apache.org
>> >For additional commands, e-mail: ojb-user-help@db.apache.org
>> >
>> >
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple DISTINCT query
Posted by Ian Hunter <ih...@hunterweb.net>.
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" <St...@bergland-it.nl>
To: "OJB Users List" <oj...@db.apache.org>
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: ojb-user-unsubscribe@db.apache.org
> >For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple DISTINCT query
Posted by Stijn de Witt <St...@bergland-it.nl>.
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: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org