Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of jay.bourland@gmail.com
 designates 209.85.223.170 as permitted sender)
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 6.5 \(1508\))
Subject: Re: SummaryHelper not setting replacements
From: Jay Bourland <jay.bourland@gmail.com>
In-Reply-To: 
 <OF102A4B89.B2E5361E-ONC1257B94.00291BC5-C1257B94.0029415A@seitenbau.net>
Date: Mon, 24 Jun 2013 18:45:10 -0600
Content-Transfer-Encoding: quoted-printable
Message-Id: <ECBCAB9A-2ED7-4B9F-9165-42FD2400F0A2@gmail.com>
References: <53D7A1AD-3700-4399-9684-A7729EFAFB81@gmail.com>
 <OF1FEFE5BE.D4454834-ONC1257B88.0025A542-C1257B88.00266665@seitenbau.net>
 <B8BDF392-F214-4020-8F7B-2A354FD963FE@gmail.com>
 <OF102A4B89.B2E5361E-ONC1257B94.00291BC5-C1257B94.0029415A@seitenbau.net>
To: "Apache Torque Users List" <torque-user@db.apache.org>

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 <Thomas.Fox@seitenbau.net> =
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.
>=20
> I think I have found and fixed the problem. If you want you can try =
the
> current trunk.
> Thanks for reporting the error !
>=20
>    Thomas
>=20
>>=20
>>   /**
>>    * 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 =3D getCriteriaDogsWithAllTitles( titles, year );
>>      crit.addAscendingOrderByColumn( REG_NAME );
>>=20
>>      return doSelect( crit );
>>   }
>>=20
>>   /**
>>    * 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 =3D getCriteriaDogsWithAllTitlesHack( titles, year =
);
>>=20
>>      SummaryHelper summary =3D new SummaryHelper();
>>=20
>>      summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) );
>>      List<ListOrderedMapCI> results =3D summary.summarize( crit );
>>      return Integer.parseInt( results.get( 0 ).get( "count" =
).toString
> () );
>>   }
>>=20
>>   /**
>>    * 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 =3D null, endDate =3D null;
>>      try{
>>         int yr =3D Integer.parseInt( year )+1;
>>         //They year is valid if we get here.
>>         startDate =3D year+"-01-01";
>>         endDate =3D Integer.toString( yr ) + "-01-01";
>>      } catch( NumberFormatException nfe ) {
>>         //ignore
>>      }
>>=20
>>      Criteria crit =3D new Criteria();
>>      int idx =3D 1;
>>      for( String str : titles ) {
>>         String alias =3D "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 !=3D null ) {
>>            Column aliasDate =3D new ColumnImpl( alias + "." +
>> TitlePeer.TITLE_DATE.getColumnName() );
>>            crit.where( aliasDate, startDate, Criteria.GREATER_EQUAL =
);
>>            crit.where( aliasDate, endDate, Criteria.LESS_THAN );
>>         }
>>         ++idx;
>>      }
>>      return crit;
>>   }
>>=20
>>   /**
>>    * 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 =3D null, endDate =3D null;
>>      try{
>>         int yr =3D Integer.parseInt( year )+1;
>>         //They year is valid if we get here.
>>         startDate =3D year+"-01-01";
>>         endDate =3D Integer.toString( yr ) + "-01-01";
>>      } catch( NumberFormatException nfe ) {
>>         //ignore
>>      }
>>=20
>>      Criteria crit =3D new Criteria();
>>      int idx =3D 1;
>>      for( String str : titles ) {
>>         String alias =3D "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() + "=3D'" + str + "'", null );
>>         if( startDate !=3D null ) {
>>            Column aliasDate =3D new ColumnImpl( alias + "." +
>> TitlePeer.TITLE_DATE.getColumnName() );
>>            crit.whereVerbatimSql( alias + "." +
>> TitlePeer.TITLE_DATE.getColumnName() + ">=3D'" + startDate + "'", =
null );
>>            crit.whereVerbatimSql( alias + "." +
>> TitlePeer.TITLE_DATE.getColumnName() + "<'" + endDate + "'", null );
>>         }
>>         ++idx;
>>      }
>>      return crit;
>>   }
>>=20
>>=20
>> On Jun 12, 2013, at 12:59 AM, Thomas Fox <Thomas.Fox@seitenbau.net>
> wrote:
>>=20
>>>=20
>>=20
> =
|-------------------------------------------------------------------------=
--------------------------------------------------------------------------=
->
>=20
>>> |Jay Bourland wrote:
> |
>>>=20
>>=20
> =
|-------------------------------------------------------------------------=
--------------------------------------------------------------------------=
->
>=20
>>>> ---------------------------|
>>> | An:                       |
>>>> ---------------------------|
>>>=20
>>>=20
>>>=20
>>>> 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:
>>>>=20
>>>>     Criteria crit =3D getCriteriaDogsWithAllTitles( titles, year );
>>>>=20
>>>>     SummaryHelper summary =3D new SummaryHelper();
>>>>=20
>>>>     summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) );
>>>>     List<ListOrderedMapCI> results =3D summary.summarize( crit );
>>>>=20
>>>> 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.
>>>=20
>>> Can you please provide an example how you construct a crit which
> fails ?
>>>=20
>>>> 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.
>>>>=20
>>>> What's the best way to report this?
>>>=20
>>> Please file a jira issue at
>>>=20
>>> https://issues.apache.org/jira/browse/TORQUE/
>>>=20
>>> As a workaround, you can try
>>>      crit.addSelectColumn(new org.apache.torque.util.functions.Count
>>> ("*"));
>>>      int count =3D SomePeer.doSelectSingleRecord(crit, new
>>> org.apache.torque.om.mapper.IntegerMapper());
>>>=20
>>> instead of
>>>      summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) );
>>>      List<ListOrderedMapCI> results =3D summary.summarize( crit );
>>>=20
>>>   Thomas
>>>=20
>>>=20
>>> =
---------------------------------------------------------------------
>>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: torque-user-help@db.apache.org
>>>=20
>>=20
>>=20
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: torque-user-help@db.apache.org
>>=20
>=20
>=20
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>=20


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org