[2.2.1 scala] Anorm unable to convert SQLite string to java.util.Date

[Jason Pearson]

I was working through making an example project that used SQLite for development and testing.  I'm using the latest release (3.8.3) of SQLite and v3.7.15-M1 of the sqlite-jdbc lib.

You can find my code with tests here: https://github.com/kaeawc/play-sqlite

My case class is the following:

case class Widget(

  id      : Int,

  name    : String,

  created : DateTime = now

)

I'm encountering a weird error when I create an Anorm row parser

val widgets =

    int("id") ~

    str("name") ~

    date("created") map {

      case     id~name~created =>

        Widget(id,name,asDateTime(created))

    }

That compiles fine (I'm using JodaTime as well), but at runtime or through tests I get the following exception:

[RuntimeException: TypeDoesNotMatch(Cannot convert 2014-02-08T21:15:31.941-05:00:class java.lang.String to Date for column ColumnName(widget.created,Some(created)))]

This seems really odd because my SQL create statement is pretty typical (SQLite doesn't understand BIGINT or AUTO_INCREMENT)

CREATE TABLE widget (

  id        INTEGER      NOT NULL PRIMARY KEY AUTOINCREMENT,

  name      VARCHAR(255) NOT NULL,

  created   DATETIME     NOT NULL

);

The following code is a workaround.  Is the format of the string wrong?  Is this a bug in Anorm?

val widgets =

    int("id") ~

    str("name") ~

    str("created") map {

      case     id~name~created =>

        Widget(id,name,new DateTime(created))

    }

[Byron Weber Becker]

I'm using Postgres, but I'm sure the principles are the same.  Have the following implicit imported into your Anorm parser.

  /**

  * Convert a column value to a joda.time DateTime.

  * http://stackoverflow.com/questions/11388301/joda-datetime-field-on-play-framework-2-0s-anorm has

  * a bit more code that may be valuable for writing DateTime values to the database.

  */

  implicit def rowToDate: Column[DateTime] = Column.nonNull { (value, meta) =>

   val MetaDataItem(qualified, nullable, clazz) = meta

   value match {

     case d: java.sql.Date => Right(new DateTime(d.getTime()))

     case ts: java.sql.Timestamp => Right(new DateTime(ts.getTime()))

     case _ => Left(TypeDoesNotMatch("Cannot convert " + value + ":" + value.asInstanceOf[AnyRef].getClass + " to DateTime for column " + qualified))

   }

 }

I have quite a few of these things to convert from a database type to a scala type as soon as possible in the life-cycle.  For example, I need to store some regular expressions in the DB.  I have a type Regex defined in the database (which is really just an alias for a text).  But code similar to the above lets me convert it to an honest-to-goodness Scala Regex as soon as it's loaded from the DB.

Byron