Problem with PostgreSQL driver

1,035 views
Skip to first unread message

Jacobus

unread,
Jul 16, 2012, 1:56:34 PM7/16/12
to scala...@googlegroups.com
Hi there,

I changed the FirstExample.scala file to use the PostgreSQL driver, and now I get an error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: type "double" does not exist
  Position: 104
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:360)
    at scala.slick.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1$$anonfun$apply$1.apply(DDL.scala:23)
    at scala.slick.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1$$anonfun$apply$1.apply(DDL.scala:23)
    at scala.slick.session.Session$class.withPreparedStatement(Session.scala:55)
    at scala.slick.session.BaseSession.withPreparedStatement(Session.scala:94)
    at scala.slick.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1.apply(DDL.scala:23)
    at scala.slick.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1.apply(DDL.scala:22)
    at scala.collection.Iterator$class.foreach(Iterator.scala:724)
    at scala.collection.AbstractIterator.foreach(Iterator.scala:1152)
    at scala.slick.ql.DDL$$anonfun$create$1.apply$mcV$sp(DDL.scala:22)
    at scala.slick.ql.DDL$$anonfun$create$1.apply(DDL.scala:22)
    at scala.slick.ql.DDL$$anonfun$create$1.apply(DDL.scala:22)
    at scala.slick.session.BaseSession.withTransaction(Session.scala:129)
    at scala.slick.ql.DDL$class.create(DDL.scala:21)
    at scala.slick.ql.DDL$$anon$1.create(DDL.scala:42)
    at scala.slick.examples.ql.FirstExample$$anonfun$1.apply$mcV$sp(FirstExample.scala:51)
    at scala.slick.examples.ql.FirstExample$$anonfun$1.apply(FirstExample.scala:46)
    at scala.slick.examples.ql.FirstExample$$anonfun$1.apply(FirstExample.scala:46)
    at scala.util.DynamicVariable.withValue(DynamicVariable.scala:57)
    at scala.slick.session.Database$$anonfun$withSession$1.apply(Database.scala:46)
    at scala.slick.session.Database$$anonfun$withSession$1.apply(Database.scala:46)
    at scala.slick.session.Database.withSession(Database.scala:38)
    at scala.slick.session.Database.withSession(Database.scala:46)
    at scala.slick.examples.ql.FirstExample$delayedInit$body.apply(FirstExample.scala:46)
    at scala.Function0$class.apply$mcV$sp(Function0.scala:40)
    at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    at scala.App$$anonfun$main$1.apply(App.scala:61)
    at scala.App$$anonfun$main$1.apply(App.scala:61)
    at scala.collection.LinearSeqOptimized$class.foreach(LinearSeqOptimized.scala:59)
    at scala.collection.immutable.List.foreach(List.scala:77)
    at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:32)
    at scala.collection.mutable.ListBuffer.foreach(ListBuffer.scala:45)
    at scala.App$class.main(App.scala:61)
    at scala.slick.examples.ql.FirstExample$.main(FirstExample.scala:15)
    at scala.slick.examples.ql.FirstExample.main(FirstExample.scala)

Any ideas as to why I'm getting this? I know PostgreSQL uses 'double precision' and not 'double', so this may be a mapping issue.

Regards

Stefan Zeiger

unread,
Jul 16, 2012, 3:03:48 PM7/16/12
to scala...@googlegroups.com
On 2012-07-16 19:56, Jacobus wrote:
Hi there,

I changed the FirstExample.scala file to use the PostgreSQL driver, and now I get an error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: type "double" does not exist

Any ideas as to why I'm getting this? I know PostgreSQL uses 'double precision' and not 'double', so this may be a mapping issue.

It looks like you're using the PostgreSQL JDBC driver with the H2 SLICK driver. You need to change the SLICK driver import, too: https://github.com/slick/slick-examples/blob/master/src/main/scala/scala/slick/examples/ql/FirstExample.scala#L4

This separation makes it possible to use the same database with different JDBC drivers (relevant e.g. for MS Access where you get the very limited JdbcOdbcBridge driver for free with the JDK but can buy better 3rd-party drivers). We will have to rethink this approach when we go beyond JDBC.

--
Stefan Zeiger
Typesafe - The software stack for applications that scale
Twitter: @StefanZeiger

Jacobus

unread,
Jul 16, 2012, 3:42:23 PM7/16/12
to scala...@googlegroups.com

It looks like you're using the PostgreSQL JDBC driver with the H2 SLICK driver. You need to change the SLICK driver import, too: https://github.com/slick/slick-examples/blob/master/src/main/scala/scala/slick/examples/ql/FirstExample.scala#L4


Hi,

Nope, I'm using the correct driver (I think):
import scala.slick.driver.PostgresDriver.simple._

Full code below:

package scala.slick.examples.ql

// Use H2Driver to connect to an H2 database
import scala.slick.driver.PostgresDriver.simple._

// Use the implicit threadLocalSession
import Database.threadLocalSession

/**
 * A simple example that uses statically typed queries against an in-memory
 * H2 database. The example data comes from Oracle's JDBC tutorial at
 * http://download.oracle.com/javase/tutorial/jdbc/basics/tables.html.
 */
object FirstExample extends App {

  // Definition of the SUPPLIERS table
  val Suppliers = new Table[(Int, String, String, String, String, String)]("SUPPLIERS") {
    def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
    def name = column[String]("SUP_NAME")
    def street = column[String]("STREET")
    def city = column[String]("CITY")
    def state = column[String]("STATE")
    def zip = column[String]("ZIP")
    // Every table needs a * projection with the same type as the table's type parameter
    def * = id ~ name ~ street ~ city ~ state ~ zip
  }

  // Definition of the COFFEES table
  val Coffees = new Table[(String, Int, Double, Int, Int)]("COFFEES") {
    def name = column[String]("COF_NAME", O.PrimaryKey)
    def supID = column[Int]("SUP_ID")
    def price = column[Double]("PRICE")
    def sales = column[Int]("SALES")
    def total = column[Int]("TOTAL")
    def * = name ~ supID ~ price ~ sales ~ total
    // A reified foreign key relation that can be navigated to create a join
    def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)
  }

  // Connect to the database and execute the following block within a session

  val db = Database.forURL("jdbc:postgresql:test:test1",
                           driver="org.postgresql.Driver",
                           user="postgres",
                           password="pass") withSession {
 
    // The session is never named explicitly. It is bound to the current
    // thread as the threadLocalSession that we imported

    // Create the tables, including primary and foreign keys
    (Suppliers.ddl ++ Coffees.ddl).create

    // Insert some suppliers
    Suppliers.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
    Suppliers.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
    Suppliers.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")

    // Insert some coffees (using JDBC's batch insert feature, if supported by the DB)
    Coffees.insertAll(
      ("Colombian",         101, 7.99, 0, 0),
      ("French_Roast",       49, 8.99, 0, 0),
      ("Espresso",          150, 9.99, 0, 0),
      ("Colombian_Decaf",   101, 8.99, 0, 0),
      ("French_Roast_Decaf", 49, 9.99, 0, 0)
    )

    // Iterate through all coffees and output them
    println("Coffees:")
    Query(Coffees) foreach { case (name, supID, price, sales, total) =>
      println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
    }

    // Why not let the database do the string conversion and concatenation?
    println("Coffees (concatenated by DB):")
    val q1 = for(c <- Coffees) // Coffees lifted automatically to a Query
      yield ConstColumn("  ") ++ c.name ++ "\t" ++ c.supID.asColumnOf[String] ++
        "\t" ++ c.price.asColumnOf[String] ++ "\t" ++ c.sales.asColumnOf[String] ++
        "\t" ++ c.total.asColumnOf[String]
    // The first string constant needs to be lifted manually to a ConstColumn
    // so that the proper ++ operator is found
    q1 foreach println

    // Perform a join to retrieve coffee names and supplier names for
    // all coffees costing less than $9.00
    println("Manual join:")
    val q2 = for {
      c <- Coffees if c.price < 9.0
      s <- Suppliers if s.id === c.supID
    } yield c.name ~ s.name
    for(t <- q2) println("  " + t._1 + " supplied by " + t._2)

    // Do the same thing using the navigable foreign key
    println("Join by foreign key:")
    val q3 = for {
      c <- Coffees if c.price < 9.0
      s <- c.supplier
    } yield c.name ~ s.name
    // This time we read the result set into a List
    val l3: List[(String, String)] = q3.list
    for((s1, s2) <- l3) println("  " + s1 + " supplied by " + s2)

    // Check the SELECT statement for that query
    println(q3.selectStatement)

    // Compute the number of coffees by each supplier
    println("Coffees per supplier:")
    val q4 = (for {
      c <- Coffees
      s <- c.supplier
    } yield (c, s)).groupBy(_._2.id).map {
      case (_, q) => (q.map(_._2.name).min.get, q.length)
    }
    // .get is needed because SLICK cannot enforce statically that
    // the supplier is always available (being a non-nullable foreign key),
    // thus wrapping it in an Option
    q4 foreach { case (name, count) =>
      println("  " + name + ": " + count)
    }
  }
}
 

Stefan Zeiger

unread,
Jul 17, 2012, 5:30:31 AM7/17/12
to scala...@googlegroups.com
On 2012-07-16 21:42, Jacobus wrote:

It looks like you're using the PostgreSQL JDBC driver with the H2 SLICK driver. You need to change the SLICK driver import, too: https://github.com/slick/slick-examples/blob/master/src/main/scala/scala/slick/examples/ql/FirstExample.scala#L4


Hi,

Nope, I'm using the correct driver (I think):
import scala.slick.driver.PostgresDriver.simple._

It turns out there were no tests for Float and Double, and the implementations for PostgreSQL and SQL Server were broken. Fixed in https://github.com/slick/slick/commit/040c9cc8ac074ee7e4d7c0f03e415fecf8cabba8

Jacobus Reyneke

unread,
Jul 17, 2012, 5:47:54 AM7/17/12
to scala...@googlegroups.com
Thanks, I thought I was going nuts there for a while :-p
Reply all
Reply to author
Forward
0 new messages