[2.0] Anorm MS SQL too many rows single query

[Jared Armstrong]

With Anorm on the latest RC2 release I am seeing in a very repeatable
manner "SqlMappingError(too many rows when expecting a single one)".
I thought since this is mssql maybe the mapping should be by Int but
that did not change the result. My code example is below:

def findExists(login: String) = {
DB.withConnection {
implicit connection =>
SQL("SELECT TOP 1 [users].id FROM [users] WHERE username =
{login}").on(
'login -> login
).as(scalar[Long].single)

}
}

Database Server: MS SQL Server 2005

Can anyone lend a suggestion on how to resolve this?


=== stacktrace ===

java.lang.RuntimeException: SqlMappingError(too many rows when
expecting a single one)
at scala.sys.package$.error(package.scala:27)
at scala.Predef$.error(Predef.scala:66)
at anorm.Sql$.as(Anorm.scala:511)
at anorm.Sql$class.as(Anorm.scala:440)
at anorm.SimpleSql.as(Anorm.scala:367)

[Guillaume Bort]

Are you sure that your query returns a single row?

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>

[Jared Armstrong]

It has "TOP 1" in it. This is the direct equivalent of "Limit 1".
Could this be somehow related to the messages MS SQL chooses to
return? (The record count messages?) I know VBScript/ADO sometimes had
a problem with that. However, I also can not find the Anorm Specs2/etc
test cases to run against a MS SQL database to verify any of this.

On Feb 15, 8:04 am, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> Are you sure that your query returns a single row?
>

[Guillaume Bort]

Try:

SQL("...").apply().toList

to check the returned ResultSet

--
Guillaume Bort

[Jared Armstrong]

I get List() as a result

[Jared Armstrong]

So If NO Rows are returned I get the exception. And I have to use
"NOCOUNT ON" for all MSSQL queries with a single row or it throws an
exception. So... is there some way I can tell SQL() to return as
Option[Long] so it wont fault if there are no rows?

[Guillaume Bort]

Yes the error message is wrong, we will fix it.

Now you need to use singleOpt instead of single to get an Option[Long]