com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list'

Question

I am using play 2.3.8 framework to create an API and access mariaDB. When I ran the query on mariaDB console, it works OK but when I run it from play I get error that the field RECEPTORS.r_name is not available which is not true.

My code is

package models.dao

import anorm._
import models.Profile
import play.api.db.DB
import play.api.Play.current

object ProfileDAO {

def index(r_name: String): List[Profile] = {
  DB.withConnection { implicit c =>
    val results = SQL(
      """
        | SELECT `RECEPTORS.r_name`,`RECEPTORS.pdbCode`, `LIGANDS.l_id`, `LIGANDS.l_score` 
        | FROM `RECEPTORS`
        | INNER JOIN `LIGANDS`
        | WHERE `RECEPTORS.r_name`={r_name};
      """.stripMargin).on(
        "r_name" -> r_name
      ).apply()

    results.map { row =>
      Profile(row[String]("r_name"), row[String]("pdbCode"),row[String]("l_id"),row[Double]("l_score"))
    }.force.toList
  }
 }

}

Query that I ran on mariaDB console is

SELECT RECEPTORS.r_name, pdbCode, l_id, l_score FROM RECEPTORS INNER JOIN LIGANDS WHERE RECEPTORS.r_name="receptor";

Error which running with Play 2.3.8 is as follows

laeeq@optiplex:~/Desktop/Backup/Project5/cpvsAPI$ sbt -jvm-debug 9999 run Listening for transport dt_socket at address: 9999 [info] Loading project definition from /home/laeeq/Desktop/Backup/Project5/cpvsAPI/project [info] Set current project to cpvsAPI (in build file:/home/laeeq/Desktop/Backup/Project5/cpvsAPI/) [info] Updating {file:/home/laeeq/Desktop/Backup/Project5/cpvsAPI/}root... [info] Resolving jline#jline;2.11 ... [info] Done updating.

--- (Running the application, auto-reloading is enabled) ---

[info] play - Listening for HTTP on /0:0:0:0:0:0:0:0:9000

(Server started, use Ctrl+D to stop and go back to the console...)

SLF4J: The following set of substitute loggers may have been accessed SLF4J: during the initialization phase. Logging calls during this SLF4J: phase were not honored. However, subsequent logging calls to these SLF4J: loggers will work as normally expected. SLF4J: See also http://www.slf4j.org/codes.html#substituteLogger SLF4J: org.webjars.WebJarExtractor [info] Compiling 1 Scala source to /home/laeeq/Desktop/Backup/Project5/cpvsAPI/target/scala-2.11/classes... [info] play - database [default] connected at jdbc:mysql://localhost:3306/db_profile [info] play - Application started (Dev) [error] application -

! @766oc7b8l - Internal server error, for (GET) [/profiles/receptor] ->

play.api.Application$$anon$1: Execution exception[[MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list']] at play.api.Application$class.handleError(Application.scala:296) ~[play_2.11-2.3.8.jar:2.3.8] at play.api.DefaultApplication.handleError(Application.scala:402) [play_2.11-2.3.8.jar:2.3.8] at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:205) [play_2.11-2.3.8.jar:2.3.8] at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:202) [play_2.11-2.3.8.jar:2.3.8] at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36) [scala-library-2.11.1.jar:na] Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_151] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_151] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_151] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_151] at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.18.jar:na]

Mark Rotteveel Mark Rotteveel · Accepted Answer · 2017-12-01T12:10:40

You need to quote the table and column individually, so use:

`RECEPTORS`.`r_name`

otherwise MySQL thinks you are trying to reference a column with the name RECEPTORS.r_name in some implicit table.

You need to do this for all your (quoted) column references. Specifically in this case, quoting seems to be unnecessary, so you could also just use RECEPTORS.r_name without backticks.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'RECEPTORS.r_name' in 'field list'

1 Answers