[jira] Assigned: (CAY-1244) Apply SQL Server TOP Fetch Limit

["Malcolm Edgar (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CAY-1244?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Malcolm Edgar reassigned CAY-1244:
----------------------------------

    Assignee: Malcolm Edgar

> Apply SQL Server TOP Fetch Limit
> --------------------------------
>
>                 Key: CAY-1244
>                 URL: https://issues.apache.org/jira/browse/CAY-1244
>             Project: Cayenne
>          Issue Type: Improvement
>          Components: Cayenne Core Library
>    Affects Versions: 3.0M5
>            Reporter: Malcolm Edgar
>            Assignee: Malcolm Edgar
>            Priority: Critical
>         Attachments: patch.txt
>
>
> The Cayenne SQL Server adaptor has does not apply the SelectQuery fetch limit using the SQL Server TOP expression.  For example:
>    SELECT TOP 1000 * FROM customer
> See the TOP expression reference at:  http://msdn.microsoft.com/en-us/library/ms189463.aspx
> This can result is very large resultset being loaded into memory by Cayenne and leading to out of memory errors.  This has occurred over the last few days on a JBoss with SQL Server taking out the entire application server.
> While this issue may not technically be a bug, its behaviour is not what you would expect for a top tier database adaptor. 
> A proposed solution is provided below. I will attempt to provide a patch in the next few days.
> public class SQLServerSelectTranslator extends SelectTranslator {
> 	
>     @Override
>     protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
>         QueryMetadata metadata = getQuery().getMetaData(getEntityResolver());
>         
>         int limit = metadata.getFetchLimit();
>         
>         if (limit > 0) {
>         	buffer.replace(0, 6, "SELECT TOP " + limit);
>         }
>     }
> }
> Not the metadata.getFetchOffset() is not applied above, as there is no equivalent function in SQL Server 2000 & 2005.  Note while SQL Server 2005 has a ROW_COUNT() function, the SQL looks a little crazy and I have not experience or confidence in its use:
> http://msdn.microsoft.com/en-us/library/ms186734.aspx

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