Re: SummaryHelper not setting replacements
Thomas - I have verified that the code now runs clean. Thank you for fixing it.
And thank you for reviving this project. I looked at migrating to Hibernate or
one of the other ORM's but I really like Torque much better.
Jay
On Jun 24, 2013, at 1:30 AM, Thomas Fox wrote:
>> 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 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 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 );
>> }
>>
Re: SummaryHelper not setting replacements
> 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 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 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
wrote:
>
> >
>
|>
> > |Jay Bourland wrote:
|
> >
>
|-
Re: SummaryHelper not setting replacements
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 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 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 wrote:
> |>
> |Jay Bourland wrote:
>|
> |>
>> ---|
> | An: |
>> ---|
>
>
>
>> I'm
RE: SummaryHelper not setting replacements
|>
|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 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 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
