Why is this Anorm Result an empty List? (Play 2.1.0)

Question

Scala noob here; for the life of me cannot see why I am not getting a result for this Anorm SQL call. When I run the SQL debug output it returns a result just fine but when run through the code I end up with an empty List().

Is there an issue with my RowParser? Why am I seeing good SQL in the debug output but it's not being collected by my result val?

Am I missing something in my SQL .as() to properly map the result rows to the Parser? When I remove the last result line my result val evaluates to a Unit, which is definitely suspicious.

// Case class - SQL results rows go into List of these
case class PerformanceData(
    date: String, 
    kwh: String
)

// RowParser
val perfData = {
    get[String]("reading_date") ~ get[String]("kwh") map{ 
        case reading_date~kwh => PerformanceData(reading_date, kwh) 
    }
}

// SQL Call - function ret type is Seq[PerformanceData]
DB.withConnection("performance") { implicit connection => 

    val result: Seq[PerformanceData] = SQL(
    """
        SELECT CONCAT(reading_date) AS reading_date,
           CONCAT(SUM(reading)) AS kwh
        FROM perf
        WHERE reading_date >= DATE_SUB(NOW(), INTERVAL 45 DAY)
        AND sfoid IN ({sf_account_ids})
        GROUP BY reading_date
        ORDER BY reading_date DESC
        LIMIT 30
    """
    ).on(
        'sf_account_ids -> getSQLInValues(SFAccountIDs)
    ).as(
        User.perfData *
    )

//  Logger.debug(result.toString) -> EMPTY LIST!??
    result // Why is this necessary to return proper type?

}

See [this answer of mine](http://stackoverflow.com/questions/15864564/play-framework-2-0-correct-way-to-represent-a-set-in-a-query-using-anorm) regarding `IN` clauses in Anorm. — maba, Apr 10 '13 at 06:21

score 2 · Accepted Answer · edited May 23 '17 at 10:25

2

Unfortunately, you need to use not bind variables but replacing in string value for IN clause.

see also: "In" clause in anorm?

Edit: I meant that sf_account_ids will be a single bind varibale. Maybe sfoid IN (?, ?, ?) is expected but the statement will be sfoid IN (?).

edited May 23 '17 at 10:25

Community

1
1

answered Apr 10 '13 at 01:46

Kazuhiro Sera

1,822
12
15

that's not my issue. the SQL renders fine, the call to get the list of ids prevents bad values – notbrain Apr 10 '13 at 02:33
or are you saying that the SQL won't run if I'm simply constructing SQL as a string using mkstring? the debug out SQL runs fine manually. – notbrain Apr 10 '13 at 02:39
I meant that sf_account_ids will be a single bind varibale. Maybe `sfoid IN (?, ?, ?)` is expected but the statement will be `sfoid IN (?)`. – Kazuhiro Sera Apr 10 '13 at 09:55
Can you edit your answer so I can upvote and mark it as the answer? I was mistaken in that the logger was producing perfectly valid SQL but silently failing to send the query and bind it. The fix was to construct the SQL query beforehand and not use binding at all. It was also masking an issue with my assumption that CONCAT() would also allow me to bind to strings in the RowParser. – notbrain Apr 10 '13 at 15:28

score 0 · Answer 2 · answered Apr 10 '13 at 06:54

For the first question id suggest you check your case statement in perDataand ensure that it's accurate. The function getSQLInValues(...) may also be the cause.

For the question as to why you need the last result and that is because scala uses the last statement in a closure to infer the return type when not explicitly defined. So val result = SQ(...) being an assignment would return Unit

To avoid this you can do something like:

DB.withConnection("performance") { implicit connection =>
  SQL(...).on(...).as(...)
}

By not assigning a return from SQL it is used to infer the type.

Why is this Anorm Result an empty List? (Play 2.1.0)

2 Answers2