Re: SummaryHelper not setting replacements

[Jay Bourland]

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 ListDog 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 ) );
  ListListOrderedMapCI 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

SummaryHelper not setting replacements

[Jay Bourland]

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 ) );
  ListListOrderedMapCI 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. 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?

Thanks,
Jay Bourland
-
To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org
For additional commands, e-mail: torque-user-h...@db.apache.org