You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by "Øyvind Harboe (JIRA)" <ji...@apache.org> on 2011/01/17 16:39:44 UTC
[jira] Created: (CAY-1523) Paged queries stop working at 2000
entries
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.
[jira] Commented: (CAY-1523) Paged queries stop working at 2000
entries
Posted by "Andrus Adamchik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12984103#action_12984103 ]
Andrus Adamchik commented on CAY-1523:
--------------------------------------
So is 'setMaxFetchSize' sufficient to solve the problem?
> 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.
[jira] Commented: (CAY-1523) Paged queries stop working at 2000
entries
Posted by "Øyvind Harboe (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12984105#action_12984105 ]
Øyvind Harboe commented on CAY-1523:
------------------------------------
No.
I'm hoping the below will be, but I haven't had a chance to test.
/* MSSQL dies when we have > 2000 records */
selectQuery.setFetchLimit(500);
Non-sequitor: I'd like to switch to Cayenne 3.1 and give it a go, but I
haven't been able to figure out how to replace the code that uses Configuration
(or even create a DataContext) yet. I'm going to give 3.1 a new
go once I find an example on how to load cayenne.xml and create a
datacontext.
> 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.