Mailing-List: contact commits-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@cayenne.apache.org
Message-ID: <24391401.15751295278784197.JavaMail.jira@thor>
Date: Mon, 17 Jan 2011 10:39:44 -0500 (EST)
From: =?utf-8?Q?=C3=98yvind_Harboe_=28JIRA=29?= <jira@apache.org>
To: commits@cayenne.apache.org
Subject: [jira] Created: (CAY-1523) Paged queries stop working at 2000
 entries
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Paged queries stop working at 2000 entries
------------------------------------------

                 Key: CAY-1523
                 URL: https://issues.apache.org/jira/browse/CAY-1523
             Project: Cayenne
          Issue Type: Bug
          Components: Core Library
    Affects Versions: 3.0.1
            Reporter: =C3=98yvind Harboe


Paged queries will stop working as they rely on extremely long SQL statemen=
ts. There are many places
this break down: Derby runs out of stack, prepared statements fail, does MS=
 SQL have a limit?

-          Caused by: java.sql.SQLException: Prepared or callable statement=
 has more than 2000 parameter markers.
-          net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1139)
-          net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)=20


SELECT t0.message_text, t0.sent, t0.receiver_role, t0.subject, t0.exception=
_message, t0.generator, t0.sender, t0.sent_status, t0.recipient, t0.receive=
r_sysuser, t0.id FROM log_email t0 WHERE (t0.id =3D ?) OR (t0.id =3D ?) OR =
(t0.id =3D ?) OR (t0.id =3D ?) OR (t0.id =3D ?) OR (t0.id =3D ?) ....


The code below needs a kludge where we set a fetch limit of 500 to get to w=
ork
(or rather I'm x'ing my fingers that kludge will work, because I can't easi=
ly test it)

=09/** fetch the newest log entries */
=09protected List fetchSortedList()
=09{
=09=09SelectQuery selectQuery =3D new SelectQuery(getCRUDClass());
=09
=09=09getOrderQuery().setOrdering(selectQuery, getAscending());
=09=09selectQuery.setPageSize(50);
=09=09/* MSSQL dies when we have > 2000 records */
=09=09selectQuery.setFetchLimit(500);
=09=09
=09=09Expression e=3DExpressionFactory.expTrue();
=09=09e=3De.andExp(ExpressionFactory.likeIgnoreCaseExp(LogEmail.RECIPIENT_P=
ROPERTY, "%" + getSearchText() + "%")
=09=09=09=09//.orExp(ExpressionFactory.likeIgnoreCaseExp(LogEmail.TEXT_PROP=
ERTY, "%" + getSearchText() + "%"))
=09=09=09=09.orExp(
=09=09=09=09=09=09ExpressionFactory.likeIgnoreCaseExp(LogEmail.SUBJECT_PROP=
ERTY, "%" + getSearchText() + "%")).orExp(
=09=09=09=09=09=09=09=09ExpressionFactory.likeIgnoreCaseExp(LogEmail.TO_REC=
EIVER_SYS_USER_PROPERTY + "." + SysUser.NAME_PROPERTY,
=09=09=09=09=09=09=09=09=09=09"%" + getSearchText() + "%")));
=09=09e=3De.andExp(getTypeFilter().filterExp());
=09=09selectQuery.setQualifier(e);
=09=09
=09=09List list =3D getDataContext().performQuery(selectQuery);
=09=09
=09=09/* Kludge!!!! Derby will run out of stack due to parsing .orExp() as =
a tree.... */
=09=09((IncrementalFaultList)list).setMaxFetchSize(100);
=09
=09=09return list;
}

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.