db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peter S. Hamlen" <pham...@mail.com>
Subject RE: Issues with select distinct
Date Tue, 11 Feb 2003 20:42:55 GMT
Justin is correct about using doSelectVillageRecords.

In case an example would help, here's the following code.  We basically
have a Person table, an Event table, and an Attendee table that contains
a foreign key to both person and event.  

At one point, we wanted only the PersonKeys for everyone who attended
an event, so we did this:

  // get all the PersonKeys for anyone who has attended an event.
  Criteria attendeeCrit = new Criteria();
  attendeeCrit.addSelectColumn(AttendeePeer.PERSONKEY);
  attendeeCrit.add(AttendeePeer.EVENTKEY,eventid.toString());
  attendeeCrit.addJoin(PersonPeer.PERSONID,AttendeePeer.PERSONKEY);
  attendeeCrit.addAscendingOrderByColumn(PersonPeer.LASTNAME);	    
  attendeeCrit.addAscendingOrderByColumn(PersonPeer.FIRSTNAME);
	    
  List attendeeIds = 
		AttendeePeer.doSelectVillageRecords(attendeeCrit,conn);
	    
  Iterator idIter = attendeeIds.iterator();
  Record rec = null;
  ArrayList numberkeyList = new ArrayList();
  NumberKey key = null;
  while(idIter.hasNext())
  {
	rec = (Record)idIter.next();
	key = new NumberKey(rec.getValue(1).asInt());
	numberkeyList.add(key);
   }


Note that what is returned is a Record - and the record contents are
1-based, not zero-based!!! 

Hope this helps.

-Peter

On Tue, 2003-02-11 at 10:56, Campbell, Justin wrote:
> Femi -
> 
> If you're doing a select with a list of select columns (as you are in your
> example), you can not use doSelect (at least not from what I've found).
> Instead, you must use "doSelectVillageRecords". This will return a List of
> objects of type "Record" (found under com.workingdogs.village). You can then
> perform manipulations on your record set from there.
> 
> Hope this helps.
> 
> Justin Campbell
> Project Technical Lead
> Profile Systems, Inc.
> 
> -----Original Message-----
> From: Olufemi Omojola [mailto:kingpin@vehiclesense.com]
> Sent: Tuesday, February 11, 2003 3:13 AM
> To: torque-user@db.apache.org
> Subject: Issues with select distinct
> 
> 
> Hi,
>   I'm getting started with Torque 3.0, and I've had nothing but joy with the
> setup and the basics, such as insertion or selecting all. I'm having a
> nightmare
> of a time (namely 6 hours so far :-)) trying to figure out how to do a
> SELECT
> DISTINCT. I have a database with a VARCHAR column titled part_number, and
> I'm
> using the following code:
> 
> Criteria crit = new Criteria();
>  crit.addSelectColumn(PartPeer.PART_NUMBER);
>  crit.addJoin(PartPeer.PART_NUMBER, PartPeer.PART_NUMBER);
>  crit.setDistinct();
>  List m = PartPeer.doSelect(crit);
> 
> >From the exception below, it looks as if its retrieving the data correctly,
> but
> trying to format it as a number; the column is clearly marked as a VARCHAR
> everywhere. A further check indicates that regardless of the fact I'm only
> asking for one column, the Peer is trying to populate the entire object, and
> crashing once it hits the VARCHAR.
> 
> The query I'm trying to execute is
> 
> select distinct part_number from part;
> 
> (which works in the mysql console). Is there anyway to execute a similar
> query
> in Torque without resorting to retrieving the entire table and sorting it
> manually for unique entries (which kinda voids the purpose of the database)?
> 
> Any help would be appreciated,
> Femi.
> 
> Exception:
> 
> Exception retrieving partslist: Bad conversion:
> java.lang.NumberFormatException:
> RXM-900-HP-II
> com.workingdogs.village.DataSetException: Bad conversion:
> java.lang.NumberFormatException: RXM-900-HP-II
>         at com.workingdogs.village.Value.asInt(Unknown Source)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.populateObject(BasePartPeer
> .jav
> a:329)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.row2Object(BasePartPeer.jav
> a:29
> 7)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.populateObjects(BasePartPee
> r.ja
> va:448)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.doSelect(BasePartPeer.java:
> 360)
>         at
> com.vehiclesense.inventory.database.PartPeer.getPartsList(PartPeer.java:27)
>         at org.apache.jsp.partuse_jsp._jspService(partuse_jsp.java:96)
>         at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
>         at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
>         at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.
> java
> :684)
>         at
> org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatch
> er.j
> ava:575)
>         at
> org.apache.catalina.core.ApplicationDispatcher.access$100(ApplicationDispatc
> her.
> java:116)
>         at
> org.apache.catalina.core.ApplicationDispatcher$PrivilegedInclude.run(Applica
> tion
> Dispatcher.java:147)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at
> org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher
> .jav
> a:489)
>         at
> org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:8
> 22)
>         at
> org.apache.jsp.index_jsp._jspx_meth_logic_equal_1(index_jsp.java:282)
>         at
> org.apache.jsp.index_jsp._jspx_meth_logic_present_1(index_jsp.java:253)
>         at
> org.apache.jsp.index_jsp._jspx_meth_html_html_0(index_jsp.java:127)
>         at org.apache.jsp.index_jsp._jspService(index_jsp.java:70)
>         at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
>         at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
>         at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.
> java
> :684)
>         at
> org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatch
> er.j
> ava:575)
>         at
> org.apache.catalina.core.ApplicationDispatcher.access$100(ApplicationDispatc
> her.
> java:116)
>         at
> org.apache.catalina.core.ApplicationDispatcher$PrivilegedInclude.run(Applica
> tion
> Dispatcher.java:147)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at
> org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher
> .jav
> a:489)
>         at
> org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:8
> 22)
>         at org.apache.jsp.usepart_jsp._jspService(usepart_jsp.java:41)
>         at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
>         at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
>         at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
> Filt
> erChain.java:247)
>         at
> org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilter
> Chai
> n.java:98)
>         at
> org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain
> .jav
> a:176)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
> ain.
> java:172)
>         at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
> va:2
> 60)
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
> va:1
> 91)
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
>         at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
> )
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
> java
> :170)
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:641)
>         at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
> )
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:641)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
> :174
> )
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
>         at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:432)
>         at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
> ctio
> n(Http11Protocol.java:386)
>         at
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:534)
>         at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
> a:53
> 0)
>         at java.lang.Thread.run(Thread.java:484)
> rethrown as org.apache.torque.TorqueException: Bad conversion:
> java.lang.NumberFormatException: RXM-900-HP-II
>         at
> com.vehiclesense.inventory.database.BasePartPeer.populateObject(BasePartPeer
> .jav
> a:346)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.row2Object(BasePartPeer.jav
> a:29
> 7)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.populateObjects(BasePartPee
> r.ja
> va:448)
>         at
> com.vehiclesense.inventory.database.BasePartPeer.doSelect(BasePartPeer.java:
> 360)
>         at
> com.vehiclesense.inventory.database.PartPeer.getPartsList(PartPeer.java:27)
>         at org.apache.jsp.partuse_jsp._jspService(partuse_jsp.java:96)
>         at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
>         at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
>         at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.
> java
> :684)
>         at
> org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatch
> er.j
> ava:575)
>         at
> org.apache.catalina.core.ApplicationDispatcher.access$100(ApplicationDispatc
> her.
> java:116)
>         at
> org.apache.catalina.core.ApplicationDispatcher$PrivilegedInclude.run(Applica
> tion
> Dispatcher.java:147)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at
> org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher
> .jav
> a:489)
>         at
> org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:8
> 22)
>         at
> org.apache.jsp.index_jsp._jspx_meth_logic_equal_1(index_jsp.java:282)
>         at
> org.apache.jsp.index_jsp._jspx_meth_logic_present_1(index_jsp.java:253)
>         at
> org.apache.jsp.index_jsp._jspx_meth_html_html_0(index_jsp.java:127)
>         at org.apache.jsp.index_jsp._jspService(index_jsp.java:70)
>         at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
>         at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
>         at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.
> java
> :684)
>         at
> org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatch
> er.j
> ava:575)
>         at
> org.apache.catalina.core.ApplicationDispatcher.access$100(ApplicationDispatc
> her.
> java:116)
>         at
> org.apache.catalina.core.ApplicationDispatcher$PrivilegedInclude.run(Applica
> tion
> Dispatcher.java:147)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at
> org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher
> .jav
> a:489)
>         at
> org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:8
> 22)
>         at org.apache.jsp.usepart_jsp._jspService(usepart_jsp.java:41)
>         at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
>         at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
>         at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>         at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
> Filt
> erChain.java:247)
>         at
> org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilter
> Chai
> n.java:98)
>         at
> org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain
> .jav
> a:176)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
> ain.
> java:172)
>         at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
> va:2
> 60)
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
> va:1
> 91)
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
>         at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
> )
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
> java
> :170)
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:641)
>         at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
> )
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:641)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
> :174
> )
>         at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNex
> t(StandardPipeline.java:643)
>         at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
>         at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
>         at
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
>         at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:432)
>         at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
> ctio
> n(Http11Protocol.java:386)
>         at
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:534)
>         at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
> a:53
> 0)
>         at java.lang.Thread.run(Thread.java:484)
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 



Mime
View raw message