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: Øyvind Harboe
Paged queries will stop working as they rely on extremely long SQL statements.
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)
SELECT t0.message_text, t0.sent, t0.receiver_role, t0.subject,
t0.exception_message, t0.generator, t0.sender, t0.sent_status, t0.recipient,
t0.receiver_sysuser, t0.id FROM log_email t0 WHERE (t0.id = ?) OR (t0.id = ?)
OR (t0.id = ?) OR (t0.id = ?) OR (t0.id = ?) OR (t0.id = ?) ....
The code below needs a kludge where we set a fetch limit of 500 to get to work
(or rather I'm x'ing my fingers that kludge will work, because I can't easily
test it)
/** fetch the newest log entries */
protected List fetchSortedList()
{
SelectQuery selectQuery = new SelectQuery(getCRUDClass());
getOrderQuery().setOrdering(selectQuery, getAscending());
selectQuery.setPageSize(50);
/* MSSQL dies when we have > 2000 records */
selectQuery.setFetchLimit(500);
Expression e=ExpressionFactory.expTrue();
e=e.andExp(ExpressionFactory.likeIgnoreCaseExp(LogEmail.RECIPIENT_PROPERTY, "%"
+ getSearchText() + "%")
//.orExp(ExpressionFactory.likeIgnoreCaseExp(LogEmail.TEXT_PROPERTY, "%" +
getSearchText() + "%"))
.orExp(
ExpressionFactory.likeIgnoreCaseExp(LogEmail.SUBJECT_PROPERTY, "%" +
getSearchText() + "%")).orExp(
ExpressionFactory.likeIgnoreCaseExp(LogEmail.TO_RECEIVER_SYS_USER_PROPERTY +
"." + SysUser.NAME_PROPERTY,
"%" + getSearchText() + "%")));
e=e.andExp(getTypeFilter().filterExp());
selectQuery.setQualifier(e);
List list = getDataContext().performQuery(selectQuery);
/* Kludge!!!! Derby will run out of stack due to parsing
.orExp() as a tree.... */
((IncrementalFaultList)list).setMaxFetchSize(100);
return list;
}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
