SqlLimiter for "select top N ..." in sql server

[hastebrot]

Hi!

I'm using Ebean 2.7.3 and an SQL Server 2005. A query with
setMaxRows(1)
creates an SQL query using "LIMIT n" syntax. This throws an
SQLException.

I'm wondering why Ebean doesn't create an SQL query with "SELECT TOP
n" syntax.
Do I need to create a customized SqlLimiter to use "SELECT TOP n"
syntax?
Has somebody run into the same Exception?

Greetings
Benjamin

== Appendix: The query and the exception

Ebean.createQuery(MyEntity).setMaxRows(1).findUnique() throws an
SQLException:

javax.persistence.PersistenceException: Query threw SQLException:
Falsche Syntax in der Nähe von 'limit'.
Bind values:[]
Query was:
select ...
from ...
limit 2

"Falsche Syntax in der Nähe von 'limit'." translates into "Wrong
syntax in near of 'limit'.".

== Appendix: SqlLimiter in DatabasePlatform.

MsSqlServer2005Platform uses the LimitOffsetSqlLimiter which creates
SQL queries with "LIMIT n".

public class DatabasePlatform {
// ...
protected SqlLimiter sqlLimiter = new LimitOffsetSqlLimiter();
}

/**
* Microsoft SQL Server 2005 specific platform.
* ...
* <li>Uses LIMIT OFFSET clause</li>
* ...
*/
public class MsSqlServer2005Platform extends DatabasePlatform {
// ...
}

I think I need a customized MsSqlServer2005Platform class.

ServerConfig serverConfig = new ServerConfig()
serverConfig.setDatabasePlatform(new
MsSqlServer2005PlatformWithTopOffset())

[Rob Bygrave]

I have committed up a MsSqlServer2005SqlLimiter and reference it in MsSqlServer2005Platform.

If you can build from source then you can try that out. It is passing my initial tests.

The code for MsSqlServer2005SqlLimiter is:



package com.avaje.ebean.config.dbplatform;

/**
 * Use top and row_number() function to limit sql results.
 */
public class MsSqlServer2005SqlLimiter implements SqlLimiter {

    final String rowNumberWindowAlias;
   
    /**
     * Specify the name of the rowNumberWindowAlias.
     */
    public MsSqlServer2005SqlLimiter(String rowNumberWindowAlias){
        this.rowNumberWindowAlias = rowNumberWindowAlias;
    }
   
    public MsSqlServer2005SqlLimiter(){
        this("as limitresult");
    }
   
    public SqlLimitResponse limit(SqlLimitRequest request) {
       
        StringBuilder sb = new StringBuilder(500);
       
        int firstRow = request.getFirstRow();
       
        int lastRow = request.getMaxRows();
        if (lastRow > 0) {
            // fetch 1 more than we return so that
            // we know if more rows are available
            lastRow = lastRow + firstRow + 1;
        }
       
        if (firstRow < 1){
            // just use top n
            sb.append(" select top ").append(lastRow).append(" ");
            if (request.isDistinct()) {
                sb.append("distinct ");
            }
            sb.append(request.getDbSql());
            return new SqlLimitResponse(sb.toString(), false);
        }
       
        /*
        SELECT  *
        FROM     (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY ...)
                     AS rn, ...)
                    AS limitresult
        WHERE  rn >= 11 AND rn <= 20
        */
       
        sb.append("select * ").append(NEW_LINE).append("from ( ");
       
        sb.append("select ");
        if (request.isDistinct()) {
            sb.append("distinct ");
        }
        sb.append("top ").append(lastRow);
        sb.append(" row_number() over (order by ");
        sb.append(request.getDbOrderBy());
        sb.append(") as rn, ");
        sb.append(request.getDbSql());
       
        sb.append(NEW_LINE).append(") ");
        sb.append(rowNumberWindowAlias);
        sb.append(" where ");
        if (firstRow > 0) {
            sb.append(" rn > ").append(firstRow);
            if (lastRow > 0) {
                sb.append(" and ");
            }
        }
        if (lastRow > 0) {
            sb.append(" rn <= ").append(lastRow);
        }
       
        return new SqlLimitResponse(sb.toString(), true);
    }

}



Thanks, Rob.

[Rob Bygrave]

Are you able to use the code in HEAD or do you want me to make a snapshot available?

Cheers, Rob.

[Benjamin Gudehus]

Hi, Rob!

I've tried your MsSqlServer2005SqlLimiter and it works flawlessly. Thank you very much!

I'm currently investigating some other issues with the distinct-keyword on queries with sqlserver.

Greetings, Benjamin.

2011/8/3 Rob Bygrave <robin....@gmail.com>

[Benjamin Gudehus]

Here are some queries I've tested:

The generated SQL in Query 1 has a syntax error at the "distinct" keyword and "${movies}" should be "u1." (I'm not quite sure about this one, maybe a fetch("movies", "year") is obligatory?).

Query 2 is missing "top 2" and "u1.year" in the where clause must be "t1.year" ("u1.year" does not filter any movies).

I've uploaded an Eclipse-Project including Jar-Dendencies (ebean.sqlserver.test.zip) to 

http://www.mediafire.com/?vdbe0w114cike5t

--Benjamin

== Query 1

Query query = Ebean.createQuery(Director.class)

    .where().eq("movies.year", 2003)

    .order("movies.year");

select top 2 distinct t0.id c0, t0.first_name c1, t0.last_name c2 

from director t0

join movie u1 on u1.director_id = t0.id  

where u1.year = 2003

order by ${movies}year

== Query 2

Query query = Ebean.createQuery(Director.class)

    .fetch("movies", "year")

    .where().eq("movies.year", 2003)

    .order("movies.year");

select distinct t0.id c0, t0.first_name c1, t0.last_name c2

        , t1.id c3, t1.year c4 

from director t0

left outer join movie t1 on t1.director_id = t0.id 

join movie u1 on u1.director_id = t0.id  

where u1.year = 2003

order by t0.id, t1.year

2011/8/3 Benjamin Gudehus <hast...@googlemail.com>

[Benjamin Gudehus]

Sorry I've forgot about this here:

query = query.setDistinct(false).setVanillaMode(false);

Director element = (Director) query.setMaxRows(1).findUnique();

For Query 1 and 2.

2011/8/3 Benjamin Gudehus <hast...@googlemail.com>