SQL Server 2014 MaxRows

[alexmnyc]

Guys,

using Mapper.findAll against lift 3-0 M1 with the following queries parameters yield SQL Server Exception

>net.liftweb.mapper.NotIn$$anon$6@104f65b, StartAt(0), MaxRows(25), PreCache(NULL,true), OrderBy(NULL,Descending,Empty)

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified

I saw something about "brokenLimit_?" and attempted creating a custom driver

object LoadplainSqlServerDriver extends SqlServerBaseDriver {
  override def brokenLimit_? : Boolean = true
}

val vendor = new StandardDBVendor("code.config.LoadplainSqlServerDriver",
        Props.get("db.url") openOr "jdbc:h2:lift_proto.db;AUTO_SERVER=TRUE",
        Props.get("db.user"), Props.get("db.password"))
      LiftRules.unloadHooks.append(vendor.closeAllConnections_! _)

      DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)

That yields the same result. Is MaxRows broken in SQL Server 2014?

Thank you.

[Diego Medina]

don't you also need to do this:

DriverType.calcDriver = _ => LoadplainSqlServerDriver

in boot to get it all working?

Thanks

Diego

--
--
Lift, the simply functional web framework: http://liftweb.net
Code: http://github.com/lift
Discussion: http://groups.google.com/group/liftweb
Stuck? Help us help you: https://www.assembla.com/wiki/show/liftweb/Posting_example_code

---
You received this message because you are subscribed to the Google Groups "Lift" group.
To unsubscribe from this group and stop receiving emails from it, send an email to liftweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Diego Medina
Lift/Scala consultant
di...@fmpwizard.com
http://fmpwizard.telegr.am

[a.mik...@gmail.com]

I tried doing both, the issue with MaxRows is not fixed.

I saw this ticket https://github.com/lift/framework/pull/1473. which implies that by specifying SQLServerDriver, it should resolve the issue but it didn’t work.

I used both in default.props

db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

db.driver=net.liftweb.db.SqlServerBaseDriver

with

 if (!DB.jndiJdbcConnAvailable_?) {
      val vendor = new StandardDBVendor(Props.get("db.driver") openOr "org.h2.Driver",

                   Props.get("db.url") openOr "jdbc:h2:lift_proto.db;AUTO_SERVER=TRUE",
                   Props.get("db.user"), Props.get("db.password"))

      LiftRules.unloadHooks.append(vendor.closeAllConnections_! _)

      DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)
    }

[Diego Medina]

in the code snippet you posted, I don't see you setting 

DriverType.calcDriver = _ => LoadplainSqlServerDriver

was that just an oversight or you didn't add it to your app?

I don't have sqlServer to try this out, otherwise I would ask for a sample project :(

--
--
Lift, the simply functional web framework: http://liftweb.net
Code: http://github.com/lift
Discussion: http://groups.google.com/group/liftweb
Stuck? Help us help you: https://www.assembla.com/wiki/show/liftweb/Posting_example_code

---
You received this message because you are subscribed to the Google Groups "Lift" group.
To unsubscribe from this group and stop receiving emails from it, send an email to liftweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Peter Petersson]

In case this turns out to be a driver error/bug you should consider using the jtds driver [1] I found it being way more stable and usable (I have been using it for several years) but then again It has now been more than 3 years sins I needed a mssql driver ;).  

[1] http://jtds.sourceforge.net/

best regards Peter Petersson

--

[alexmnyc]

Same results.

DriverType.calcDriver = _ => LoadPlainSqlServerDriver

      val vendor = new StandardDBVendor(

                    "code.config.LoadPlainSqlServerDriver",

                   Props.get("db.url") openOr "jdbc:h2:lift_proto.db;AUTO_SERVER=TRUE",

                   Props.get("db.user"), Props.get("db.password"))

 LiftRules.unloadHooks.append(vendor.closeAllConnections_! _)

 DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)

com.microsoft.sqlserver.jdbc.SQLServerException: The ORDER BY clause is invalid

in views, inline functions, derived tables, subqueries, and common table express

ions, unless TOP, OFFSET or FOR XML is also specified.

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError

(SQLServerException.java:216)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServ

erStatement.java:1515)

This happens with a mapper model A

class A private() extends LongKeyedMapper[A] with IdPK {

    object b extends MappedLongForeignKey(this, B)

 object name extends MappedString(this.asInstanceOf[T], 256)

}

class B private() extends LongKeyedMapper[A] with IdPK

Using    PreCache(A.b), StartAt(0), MaxRows(25), OrderBy(A.b, Ascending), OrderBy(A.name, Ascending) in findAll

Also, am I supposed to see TOP 25 (or LIMIT 25) when using DB log function? I don't see any LIMIT or TOP with MaxRows.

 DB.addLogFunc {

      case (log, duration) => {

        logger.debug("Total query time : %d ms".format(duration))

        log.allEntries.foreach {

          case DBLogEntry(stmt,duration) =>

            logger.debug("  %s in %d ms".format(stmt, duration))

        }

      }

    }

[alexmnyc]

With JTDS

Message: java.sql.SQLException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. 

 net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) 

[alexmnyc]

Looking at the source code of MetaMapper.scala lines 405-413

DB.prepareStatement(query, conn) {

        st =>

        setStatementFields(st, bl, 1, conn)

        DB.exec(st)(createInstances(dbId, _, start, max, f))

      }

MawRows QueryParam  is never serialized into SQL Statement TOP parameter (SQLServer equivalent of LIMIT parameter).

Can somebody please tell me if this is expected behaviour or it is a bug?

Thank you.

[alexmnyc]

Also, if you do a search for "TOP" inside MetaMapper.scala there is not even a mentioning of it.

[Diego Medina]

Hi,

I just went through the source code and when brokenLimit_? is set to true, it doesn't mean we use TOP, it simply means we don't add the text LIMIT to the query. then, seeing your error, the problem is that you cannot use ORDER BY unless you use TOP.

I have only used mapper with MySQL and derby, so never run into this issue myself.

I guess at this point you would have to go and type the SQL in plain string and use ... let me see if I find that method to enter plain SQL ... I think it's this one

https://github.com/lift/framework/blob/master/persistence/mapper/src/main/scala/net/liftweb/mapper/MetaMapper.scala#L198

Thanks

Diego

On Thu, Oct 16, 2014 at 9:42 PM, alexmnyc <a.mik...@gmail.com> wrote:

Also, if you do a search for "TOP" inside MetaMapper.scala there is not even a mentioning of it.

--

--
Lift, the simply functional web framework: http://liftweb.net
Code: http://github.com/lift
Discussion: http://groups.google.com/group/liftweb
Stuck? Help us help you: https://www.assembla.com/wiki/show/liftweb/Posting_example_code

---
You received this message because you are subscribed to the Google Groups "Lift" group.
To unsubscribe from this group and stop receiving emails from it, send an email to liftweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[alexmnyc]

Correct. There should be an exception thrown when MaxRows parameter is used in findAll query against SQLServer since it is not implemented against it. Or at least there should be some form of notation added to WIKI perhaps.

I ended up doing 

val tableName = Entity.dbTableName

    val fields = Entity.fieldNamesAsMap.map(x=>x._1).mkString(",")

    val orderBy =  paramsCombined.map(x => x match {

      case OrderBy(field, order, nullOrder) => (MapperRules.quoteColumnName.vend(field._dbColumnNameLC) + " " + order.sql)

      case x => println(x); ""

    }).mkString

    Entity.findAllByPreparedStatement(

    { superconn =>

      superconn.connection.prepareStatement(

        "select "+fields +

          " from " +tableName+

          " ORDER BY "+orderBy+

          " OFFSET "+startAt +" ROWS FETCH NEXT " +max+  " ROWS ONLY"

      )

    })