Unsupported length() function generated for SQLite in scalaquery

Question

When trying to use scalaquery to retrieve the length of a text column in a SQLite database it generates the wrong SQL. I get this:

SELECT "t1"."title" FROM "GoodPages" "t1" WHERE ({fn length("t1"."title")} > 65)

when the query should really be

SELECT "t1"."title" FROM "GoodPages" "t1" WHERE length("t1"."title") > 65

The for comp I use for getting this query is

for (f <- Foo if f.title.length > 65) yield f.title

And the Table def I have is

object Foo extends Table[(Int,String)]("Foo") {
  def id = column[Int]("id")
  def title = column[String]("title")
  def * = id ~ title
}

It seems like scalaquery is just generating the wrong length() function, but I can't find where in the code this happens nor have I found anything on the Internet about this.

score 1 · Answer 1 · answered Feb 23 '13 at 19:25

The generated SQL you see uses JDBC escape syntax, more precisely JDBC escape syntax for fn keyword. This syntax can be used by JDBC drivers but unfortunately is not supported by the SQLite driver.

Slick 1.0.0 knows this as the following snippet from SQLiteDriver suggests

case Apply(j: Library.JdbcFunction, ch) if j != Library.Concat =>
  /* The SQLite JDBC driver does not support ODBC {fn ...} escapes, so we try
   * unescaped function calls by default */
  b"${j.name}("
  b.sep(ch, ",")(expr(_, true))
  b")"
case s: SimpleFunction if s.scalar =>
  /* The SQLite JDBC driver does not support ODBC {fn ...} escapes, so we try
   * unescaped function calls by default */
  b"${s.name}("
  b.sep(s.nodeChildren, ",")(expr(_, true))
  b")"

If Slick 1.0.0 doesn't work for you we can probably find another solution. Let me know.

Unsupported length() function generated for SQLite in scalaquery

1 Answers1