db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fox <Thomas....@seitenbau.net>
Subject Re: SummaryHelper not setting replacements
Date Mon, 24 Jun 2013 07:30:32 GMT
> 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.Fox@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-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
>


---------------------------------------------------------------------
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