Re: [eclipselink-users] SQL Server 2005 Pagination Support

[sarbx <sarbx@xxxxxxxxxxx>]

In case the attached patch file got stripped off, I'm attaching it again with
.txt extension.

sarbx wrote:
> 
> 
> Hello,
> 
> I have created a patch (attached) which allows eclipse link to use the SQL
> Server 2005 ROW_NUMBER() function for pagination using the JPA queries
> maxRow and firstResult settings. Basically it transforms a SQL from,
> 
>     /**
> +     * Print the SQL representation of the statement on a stream.
> +     * Transform a SQL of the form,
> +     * 
> +     * SELECT    t0.ID, t0.NAME, t1.NAME, t2.NAME, t3.NAME
> +     * FROM DOMAINS t0 LEFT OUTER JOIN TENANT t3
> +     *         ON (t3.ID = t0.tenant_id), UC t2, REALM t1
> +     * WHERE ((t1.ID = t0.realm_id) AND (t2.ID = t0.UC_ID))
> +     * ORDER BY t0.NAME
> +     * 
> +     * TO,
> +     * 
> +     * SELECT    COL1, COL2, COL3, COL4, COL5
> +     * FROM    (SELECT ROW_NUMBER() OVER (ORDER BY t0.NAME) AS Row,
> +     *            t0.ID AS COL1, t0.NAME AS COL2, t1.NAME AS COL3,
> +     *            t2.NAME AS COL4, t3.NAME AS COL5
> +     *        FROM DOMAINS t0 LEFT OUTER JOIN TENANT t3
> +     *            ON (t3.ID = t0.tenant_id), UC t2, REALM t1
> +     *         WHERE ((t1.ID = t0.realm_id) AND (t2.ID = t0.UC_ID))) AS
> Report
> +     * WHERE  Row >= ? AND Row <= ?
> +     * 
> +     */
> 
> The new code will not be triggerd if any of the following is true,
> 
> // if ( !(this.shouldUseRownumFiltering()) || ( !(max>0) && !(firstRow>0)
> ) || !statement.hasOrderByExpressions()){
> 
> it will fallback to the standard way for doing things.
> 
> It has been working on our systems for couple of months now and our
> product has gone through multiple QA cycles.
> 
> I would like if any of the eclipselink commiters take this up and run
> through any tests and promote it.
> 
> Some things which are not so nice but unavoidable are,
> 
> - a new class has been added to the persistence.internal.expressions
> package. This done to access package protected methods. The SQL
> transformation for SQL Server was not as straight forward as the Oracle or
> the other DB's where you just had to append or prepend something to the
> existing SQL to enable paging. 
> - This ROW_NUMBER() functionality is only available in SQL Server 2005 and
> later versions. This might break if used with the previous version. In
> that case, shouldUseRownumFiltering() should be set to FALSE from the
> somewhere, I was not sure from where I could do that.
> 
> Any feedback is appreciated.
> 
> Thanks,
> -Sarva
> 
> 
> _________________________________________________________________
> Windows Live™ Hotmail®…more than just e-mail. 
> http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_012009
>  
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 
http://www.nabble.com/file/p21593720/org.eclipse.persistence.core.patch.txt
org.eclipse.persistence.core.patch.txt 
-- 
View this message in context: http://www.nabble.com/SQL-Server-2005-Pagination-Support-tp21593246p21593720.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.