2

I was working on a Quill project and suddenly I wanted to execute raw SQL. I referred documentation and learn how to do it. When I execute below lines it worked greatly...

def getProjectsFromSql: Future[List[(Index, String)]] ={
    val rawQuery = quote(
        infix"""SELECT * FROM Project""".as[Query[Project_True]]
    )

    val result = ctx.run(rawQuery)
    result
  }

But when I execute below lines, it gives error...

def getProjectsFromSql: Future[List[(Index, String)]] ={
    val rawQuery = quote(
        infix"""SELECT project_id, name FROM Project""".as[Query[(Int, String)]]
    )

    val result = ctx.run(rawQuery)
    result
  }

error

com.github.mauricio.async.db.mysql.exceptions.MySQLException: Error 1054 - #42S22 - Unknown column 'x._1' in 'field list'

I couldn't identify what was the reason for the error. I want to solve this quickly. (My database is mysql)

Dushyantha Wijesinghe
  • 190
  • 15
  • Hi Dushyantha Wijesinghe, I have a related problem as above. Instead of putting `SELECT project_id, name FROM Project` into Infix, is it okay If I assign it to val say `myQuery` , and then replace the the query inside the infix by that val ? because my query is going to be dynamic so I want to generate it first as string and then give it to the infix. However it does not work. Could you please help on it? I have asked it here. https://stackoverflow.com/questions/60200859/running-plain-sql-dynamically-in-quill-using-infix-fails Thanks in advance. – user9920500 Feb 13 '20 at 08:57
  • I also find similar error. If I paste the same query from documentation, I get this error – user9920500 Mar 09 '20 at 07:02

1 Answers1

1

The reason behind the error is that Quill determines the table column names from the field name of the class being read. The field names for a tuple are _1 and _2, so this is what Quill uses for the column names are called.

You could fix this my defining a new case class instead of using a tuple, and name the fields the same as the column. Or you could change the sql to set the column names as _1 and _2 - that's a fine approach since we know that _1 and _2 are the expected column names for a tuple.

omar
  • 86
  • 1
  • 4