postgres: sql error while using string interpolation (scala + scalikejdbc)

Question

Here is the query I execute on Postgres from scala using scalikejdbc library which works without any issue

import scalikejdbc._
import java.time.LocalDate
def getRange(startDate: LocalDate, endDate: LocalDate): Unit =  {
  implicit val session: AutoSession.type = AutoSession
  log.info(s"startDate = $startDate, endDate = $endDate")
  log.info(sql"""select * from $startDate""".statement)
  val query =
    sql"""select generate_series(
      |timestamp without time zone '2021-05-01',
      |timestamp without time zone '2021-05-03',
      |'1 day'
      |) as tstamp
      |""".stripMargin
  log.info(s"query = ${query.statement}")
  log.info(s"Parameters = ${query.parameters}")
  query.map(rs => rs.string("tstamp")).list().apply().foreach(x => log.info(s"Date = $x"))
}

//-- Output --
Date = 2021-05-01 00:00:00
Date = 2021-05-02 00:00:00
Date = 2021-05-03 00:00:00

But I get error when using string interpolation for date. Here in this case I used ${startDate.toString} instead of the string literal 2021-05-01

...
def getRange(startDate: LocalDate, endDate: LocalDate): Unit =  {
...
      |timestamp without time zone ${startDate.toString},
      |timestamp without time zone '2021-05-03',
...

and the error I get is,

SQL execution failed (Reason: ERROR: syntax error at or near "$1" Position: 53): 
select generate_series( timestamp without time zone '2021-05-01', 
timestamp without time zone '2021-05-03', '1 day' ) as tstamp

What am I missing here?... as the sql statement shown in error is indeed a valid one

Well `Position: 53` is immediately before `'2021-05-01'` so I'm going to guess there is some sort of hidden character there. You should probably inspect what `${startDate.toString}` is actually outputting? — Adrian Klaver, Feb 04 '22 at 22:12
@AdrianKlaver thank you! Tried using variable like this `val dt = "2021-05-01"`... `timestamp without time zone ${dt}`. Does not work. Get the same error — Raj, Feb 04 '22 at 22:31

score 0 · Answer 1 · answered Feb 07 '22 at 21:59

This probably looks like a string interpolation bug in scalikejdbc. Modified the query like this, which did work

...
def getRange(startDate: LocalDate, endDate: LocalDate): Unit =  {
...
      |${startDate.toString}::timestamp,
      |${endDate.toString}::timestamp,
...

postgres: sql error while using string interpolation (scala + scalikejdbc)

1 Answers1