1

I'm using Scalaquery and have run into a problem when I attempt to limit my query based on a date field. I'm using Scala 2.9.2, ScalaQuery 2.9.1:0.10.0-M1 Consider the code below:

case class MyCase (opr_date: Date)       

object MyClass extends BasicTable[MyCase]("MYTABLE") {

    def opr_date = column[Date]("OPR_DATE")
    def * = opr_date <> (MyCase, MyCase.unapply _)

    def test(date: Date) = db.withSession {

        logDebug("test date:  " + date)
        val qry = for {
                d <- MyClass if (d.opr_date === date)
        } yield d.opr_date
        logDebug(qry.selectStatement)
        qry.list
    }
}

This query never returns any rows. Here is the calling code:

"The data" should {
    "be available " in {

        val testDate = CommonFormat.parseDate("2012-10-27", CommonFormat.EURO_SHORT).getTime
        val records = MyClass.test2(new java.sql.Date(testDate))
        records.size must be_>(0)
    }
}

The query returns 0 rows and produces the following SQL when I print the select:

SELECT "t1"."OPR_DATE" FROM "MYTABLE" "t1" WHERE ("t1"."OPR_DATE"={d '2012-10-27'})

I have data available for the test date. If I paste the SQL into a SQL editor and edit the date so that its not the JDBC template format ('27-Oct-2012') the query returns the expected rows. Can anyone tell me what I'm doing wrong? Shouldn't this work?

myio561
  • 23
  • 4
  • Firstly - what database do you use? And second - if you paste that "following sql", does it return the needed rows? – Rogach Nov 08 '12 at 16:57
  • I'm using an Oracle database and yes I do get the expected rows when I paste the SQL into a client (PL/SQL Developer). – myio561 Nov 08 '12 at 17:31
  • Unfortunately I don't have Oracle to test with, but it may be a mismatch between your date formats. Try your exact code with something simple like an int instead of a date. The last paragraph in your question is probably a hint to the answer. Should be worth the trouble to swop to an in-memory database like h2 and see if the problem has to do with Oracle or not. I know that Slick's (new ScalaQuery) support for Oracle is not yet complete. This may be the case with ScalaQuery also... – Jack Nov 09 '12 at 08:08

1 Answers1

0

I found out this morning that this was a data problem. The query works fine. It turns out I was connecting to the wrong server. We have a confusing setup of multiple environments and back-up systems that share the same database name. After connecting to the correct server the query works as expected. I saw different results between my code and the editor-tool because they were pointing at different servers (same database name ugh).Thank you to all who took time to look into this for me. I appreciate your efforts.

myio561
  • 23
  • 4