> Hi Thomas - Here's the problem code. The plain select works fine, > but asking for the count causes an error to be thrown if I use the > non-hack method.
I think I have found and fixed the problem. If you want you can try the current trunk. Thanks for reporting the error ! Thomas > > /** > * Get a List of the dogs that earned all of a list of titles in > a given year > * @param titles array of titles to be searched. > * @param year four digit year to be searched > * @return a List of dogs that earned the titles > * @throws TorqueException if something goes wrong > */ > public static List<Dog> doFindDogsWithAllTitles( String[] titles, > String year ) throws TorqueException { > Criteria crit = getCriteriaDogsWithAllTitles( titles, year ); > crit.addAscendingOrderByColumn( REG_NAME ); > > return doSelect( crit ); > } > > /** > * Get a count of the dogs that earned all of a list of titles in > a given year > * @param titles array of titles to be searched. > * @param year four digit year to be searched > * @return number of dogs that earned the titles > * @throws TorqueException if something goes wrong > */ > public static int doFindCountWithAllTitles( String[] titles, > String year ) throws TorqueException { > //TODO: Remove Hack once issue fixed > Criteria crit = getCriteriaDogsWithAllTitlesHack( titles, year ); > > SummaryHelper summary = new SummaryHelper(); > > summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) ); > List<ListOrderedMapCI> results = summary.summarize( crit ); > return Integer.parseInt( results.get( 0 ).get( "count" ).toString () ); > } > > /** > * Create a criteria to search for the dogs that earned all of an > array of titles in a given year > * @param titles array of titles to be searched. > * @param year four digit year to be searched > * @return the Criteria to be used for the request > */ > private static Criteria getCriteriaDogsWithAllTitles( String[] > titles, String year ) { > String startDate = null, endDate = null; > try{ > int yr = Integer.parseInt( year )+1; > //They year is valid if we get here. > startDate = year+"-01-01"; > endDate = Integer.toString( yr ) + "-01-01"; > } catch( NumberFormatException nfe ) { > //ignore > } > > Criteria crit = new Criteria(); > int idx = 1; > for( String str : titles ) { > String alias = "t"+Integer.toString( idx ); > crit.addAlias( alias, "title" ); > crit.addJoin( DogPeer.DOG_ID, new ColumnImpl( alias + "." + > TitlePeer.DOG_ID.getColumnName() ) ); > crit.where( new ColumnImpl( alias + "." + > TitlePeer.TITLE.getColumnName() ), str ); > if( startDate != null ) { > Column aliasDate = new ColumnImpl( alias + "." + > TitlePeer.TITLE_DATE.getColumnName() ); > crit.where( aliasDate, startDate, Criteria.GREATER_EQUAL ); > crit.where( aliasDate, endDate, Criteria.LESS_THAN ); > } > ++idx; > } > return crit; > } > > /** > * forces a non-prepared statement to get around SummaryHelper bug > * @param titles array of titles that the dog must have > * @param year year to be searched > * @return a Criteria object with the appropriate query > */ > private static Criteria getCriteriaDogsWithAllTitlesHack( String > [] titles, String year ) { > String startDate = null, endDate = null; > try{ > int yr = Integer.parseInt( year )+1; > //They year is valid if we get here. > startDate = year+"-01-01"; > endDate = Integer.toString( yr ) + "-01-01"; > } catch( NumberFormatException nfe ) { > //ignore > } > > Criteria crit = new Criteria(); > int idx = 1; > for( String str : titles ) { > String alias = "t"+Integer.toString( idx ); > crit.addAlias( alias, "title" ); > crit.addJoin( DogPeer.DOG_ID, new ColumnImpl( alias + "." + > TitlePeer.DOG_ID.getColumnName() ) ); > crit.whereVerbatimSql( alias + "." + > TitlePeer.TITLE.getColumnName() + "='" + str + "'", null ); > if( startDate != null ) { > Column aliasDate = new ColumnImpl( alias + "." + > TitlePeer.TITLE_DATE.getColumnName() ); > crit.whereVerbatimSql( alias + "." + > TitlePeer.TITLE_DATE.getColumnName() + ">='" + startDate + "'", null ); > crit.whereVerbatimSql( alias + "." + > TitlePeer.TITLE_DATE.getColumnName() + "<'" + endDate + "'", null ); > } > ++idx; > } > return crit; > } > > > On Jun 12, 2013, at 12:59 AM, Thomas Fox <thomas....@seitenbau.net> wrote: > > > > |----------------------------------------------------------------------------------------------------------------------------------------------------> > > |Jay Bourland wrote: | > > > |----------------------------------------------------------------------------------------------------------------------------------------------------> > >> ---------------------------| > > | An: | > >> ---------------------------| > > > > > > > >> I'm trying to convert an old Torque site to version 4. I'm running > >> into a problem with using a count() function. The code looks like this: > >> > >> Criteria crit = getCriteriaDogsWithAllTitles( titles, year ); > >> > >> SummaryHelper summary = new SummaryHelper(); > >> > >> summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) ); > >> List<ListOrderedMapCI> results = summary.summarize( crit ); > >> > >> When summarize() is executed, I get a > >> "jdbc4.MySQLSyntaxErrorException: You have an error in your SQL > >> syntax" exception. The Criteria is good and works fine with a > >> doSelect(). It appears that the summarize converts the Criteria to a > >> string without adding in the replacements for the parameters in the > >> prepared statement. > > > > Can you please provide an example how you construct a crit which fails ? > > > >> Also, if I take the string from the > >> queryStatement and replace the '?' with values, the statement runs > >> fine from an interactive MySQL session. When I compare the code in > >> SummaryHelper.summarize() to BasePeerImpl.doSelect() the code to set > >> the replacements is present in doSelect but not in summarize. > >> > >> What's the best way to report this? > > > > Please file a jira issue at > > > > https://issues.apache.org/jira/browse/TORQUE/ > > > > As a workaround, you can try > > crit.addSelectColumn(new org.apache.torque.util.functions.Count > > ("*")); > > int count = SomePeer.doSelectSingleRecord(crit, new > > org.apache.torque.om.mapper.IntegerMapper()); > > > > instead of > > summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) ); > > List<ListOrderedMapCI> results = summary.summarize( crit ); > > > > Thomas > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org > > For additional commands, e-mail: torque-user-h...@db.apache.org > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org > For additional commands, e-mail: torque-user-h...@db.apache.org > --------------------------------------------------------------------- To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org