SQL syntax error with Derby and Circumflex ORM

Question

I am trying to use the Circumflex ORM (as suggested by StackOverflow - here , here and here ) to connect to the local (built-in) Apache Derby database through a JDBC from Scala project (built using a simple build tool). I carefully followed the instructions, but I have interesting problems.

Here are the driver and URL of the cx.properties file:

orm.connection.driver=org.apache.derby.jdbc.EmbeddedDriver
orm.connection.url=jdbc:derby:derbyDB

(They are compared to “instantiating a mirrored driver model and creating a connection” with raw JDBC or equivalents in persistence.xml - Circumflex uses a short and sweet properties file because, you know, this is not XML and this is a good thing.)

The dependencies that I added in my sbt project file that are directly related are as follows:

  "ru.circumflex" % "circumflex-orm" % "1.0",
  "org.apache.derby" % "derby" % "10.6.1.0"

I created a short sample model that defines a simplified version of the table that the documentation describes:

import java.sql.DriverManager
import ru.circumflex.orm._

class Country extends Record[Country] {
  val code = "code" VARCHAR(2)
  val name = "name" TEXT
}

object Country extends Table[Country]

This is like compiling, and I can create an instance of the Country object (using the Scala 2.8.0 RC5 wrapper invoked using the sbt console) and create an ActiveRecord-style object and then save it like this:

val c = new Country
c.code := "US"
c.name := "United States of America"
c.save

According to the documentation, this should do a check on the object and then insert it into the database. I get the following exception:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "public" at line 1, column 13.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement30...

I found this thread where someone has a similar problem with "Discovered" public and Apache Derby, t seems to offer a useful way forward.

Any ideas what could be causing this?

Answer 1

, Circumflex Derby, , , URL- JDBC.

. Hibernate ...

, , , "orm.defaultSchema" , "public", .

, , , Circumflex, , , Derby "APP" .

Answer 2

Circumflex ORM . Postgres, MySql Oracle . ru.circumflex.orm.Dialect, SQL Derby, cx.properties.

orm.dialect=com.magmanics.circumflex.orm.dialect.HsqldbDialect

, , ...

import ru.circumflex.orm._

/**
 * @author James Baxter <j.w.baxter(at)gmail>
 * @since 19-Jun-2010
 */
class HsqldbDialect extends Dialect {

  override def timestampType = "TIMESTAMP"
  override def createSchema(schema: Schema) = "CREATE SCHEMA " + schema.name + " AUTHORIZATION DBA"
  override def createTable(table: Table[_]) = "CREATE TABLE " + table.qualifiedName + " (" + table.fields.map(_.toSql).mkString(", ") + ")"
  override def columnDefinition(field: Field[_]): String = {
    var result = field.name + " " + field.sqlType
    field.default match {
      case Some(expr) => result += " " + expr
      case _ =>
    }
    if (!field.nullable_? && !result.contains("PRIMARY KEY")) result += " NOT NULL"
    return result
  }
  override def primaryKeyExpression(record: Record[_]) = "IDENTITY PRIMARY KEY"
  override def initializeRelation(relation: Relation[_]) = {}
  override def lastIdExpression(node: RelationNode[_]) = node.alias + "." + node.relation.primaryKey.name + " = IDENTITY()"
}

>