db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jay Bourland <jay.bourl...@gmail.com>
Subject Re: SummaryHelper not setting replacements
Date Thu, 20 Jun 2013 23:20:33 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.

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


Mime
View raw message