CODEWITHSUNDEEP
sqlpostgresqlscaladoobie
DenisNovac
DenisNovac

Reputation: 728

Scala Doobie PSQLException: ERROR: syntax error at end of input when use "Like %"

I am using PostgreSQL 12.1 with Scala and Doobie. Getting exception when trying to do query with LIKE % syntax. It works without %.

My code:

implicit val cs = IO.contextShift(ExecutionContexts.synchronous)

val driver = "org.postgresql.Driver"
val connectionString = "jdbc:postgresql:postgres"
val user = "postgres"
val pass = "P@ssw0rd"

lazy val xa = Transactor.fromDriverManager[IO](driver, connectionString, user, pass)

def findNamePref(title: String): Option[Book] = {
    val s = sql"SELECT * FROM books WHERE title LIKE $title%".query[Book].option
    s.transact(xa).unsafeRunSync()
}

Exception:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at end of input Position: 41 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2241) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:158) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108) at doobie.free.KleisliInterpreter$PreparedStatementInterpreter.$anonfun$executeQuery$2(kleisliinterpreter.scala:956) at doobie.free.KleisliInterpreter.$anonfun$primitive$2(kleisliinterpreter.scala:112) at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:87) at cats.effect.internals.IORunLoop$.startCancelable(IORunLoop.scala:41) at cats.effect.internals.IOBracket$BracketStart.run(IOBracket.scala:86) at cats.effect.internals.Trampoline.cats$effect$internals$Trampoline$$immediateLoop(Trampoline.scala:70) at cats.effect.internals.Trampoline.startLoop(Trampoline.scala:36) at cats.effect.internals.TrampolineEC$JVMTrampoline.super$startLoop(TrampolineEC.scala:93) at cats.effect.internals.TrampolineEC$JVMTrampoline.$anonfun$startLoop$1(TrampolineEC.scala:93) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18) at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:94) at cats.effect.internals.TrampolineEC$JVMTrampoline.startLoop(TrampolineEC.scala:93) at cats.effect.internals.Trampoline.execute(Trampoline.scala:43) at cats.effect.internals.TrampolineEC.execute(TrampolineEC.scala:44) at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:72) at cats.effect.internals.IOBracket$BracketStart.apply(IOBracket.scala:52) at cats.effect.internals.IORunLoop$.cats$effect$internals$IORunLoop$$loop(IORunLoop.scala:136) at cats.effect.internals.IORunLoop$RestartCallback.signal(IORunLoop.scala:355) at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:376) at cats.effect.internals.IORunLoop$RestartCallback.apply(IORunLoop.scala:316) at cats.effect.internals.IOShift$Tick.run(IOShift.scala:36) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834)

Dependencies:

scalaVersion := "2.13.1"


lazy val doobieVersion = "0.8.8"

libraryDependencies ++= Seq(
  "org.tpolecat" %% "doobie-core"     % doobieVersion,
  "org.tpolecat" %% "doobie-postgres" % doobieVersion,
  "org.tpolecat" %% "doobie-specs2"   % doobieVersion
)

Upvotes: 2

Views: 961

Answers (4)

DenisNovac
DenisNovac

Reputation: 728

Thanks to all. This is what i use after all:

sql"SELECT * FROM books WHERE title LIKE ${title+"%"}".query[Book].option

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

The reason you got this error is you inscribed a string variable in a string itself i.e. title in your case. On usage, the string variables gets substituted to its value hence inscribing a string value within double quotes is again a syntax error. So, you should use title explicitely by concatenation of the title value to the select query string. 

    "select * from.... Where title like %" 
   +title+"%" ;

Upvotes: 1

pme
pme

Reputation: 14803

The why described already JGH.

A simple fix would be to add % to the parameter.

Request:

val s = sql"SELECT * FROM books WHERE title LIKE $title".query[Book].option

Examples:

findNamePref("Title")
findNamePref("Tit%")
findNamePref("%itle")
findNamePref("%")

Upvotes: 1

JGH
JGH

Reputation: 17866

The code will replace the content of $title with the value from the function parameter.

The query however contains a % after the parameter. Once replaced with a value, the SQL would look like ... WHERE title LIKE 'myTitle'%, which is invalid.

You can concatenate the % to the given parameter

val s = sql"SELECT * FROM books WHERE title LIKE $title || '%'".query[Book].option

which would translate to ... WHERE title LIKE 'myTitle' || '%' then to ... WHERE title LIKE 'myTitle%'

Upvotes: 2

Related Questions