Problem with insert & Oracle

[Kari Tuomainen]

Hi! I am new to Scala and O/R Broker, so this is probably just some silly misunderstanding. However, I don't seem to be able to insert a row into a table with my test program and the example programs and documentation don't seem to help me to get over this problem.

As far as I know, Oracle doesn't offer autoincremented primary key columns (without using triggers & sequences or some similar tricks), so I tried to create the primary key by myself, using hard coded integer for simplicity. The program just does some simple queries and then tries to insert a single row into the database. The queries seem to work, but when I try to insert a row, I get an exception.

What I am doing wrong?

Here are the statements for creating the table:

  CREATE TABLE MYTABLE (
    id          NUMBER PRIMARY KEY,
    identifier VARCHAR2(100) UNIQUE
  );

  INSERT INTO MYTABLE (id, identifier) VALUES (1, 'Test row 1');
  INSERT INTO MYTABLE (id, identifier) VALUES (2, 'Test row 2');

Contents of selectMytable.sql file:
  select * from MYTABLE

Contents of insertMytable.sql file:
  INSERT INTO Mytable (id, identifier) VALUES (:mytable.id, :mytable.identifier)

And here is the program:

package example

import java.sql.DriverManager
import org.orbroker._
import org.orbroker.config._
import org.orbroker.pimp._
import org.orbroker.conv._
import oracle.jdbc.driver._;

case class Mytable (
  id: Int,
  identifier: String
)

object MytableExtractor extends RowExtractor[Mytable] {
  def extract(row: Row) = {
    new Mytable(
      row.integer("ID").get,
      row.string("IDENTIFIER").get
    )
  }
}

object Tokens extends TokenSet(true) {
  var selectMytable = Token('selectMytable, MytableExtractor)
  val insertMytable = Token('insertMytable, BigDecimalConv)
}

object Main {

  def getDataSource(url:String, username:String, password:String) = {
    val result = new oracle.jdbc.pool.OracleDataSource();
    result.setURL(url)
    result.setUser(username)
    result.setPassword(password)
    result
  }
 
  val broker =  {
    DriverManager.registerDriver(new OracleDriver());
  
    val url = "DBURLHERE"
    val username = "USERNAMEHERE"
    val password = "PASSWORDHERE"
    val ds = getDataSource(url,username,password)
   
    val configFolder = new java.io.File("sql")
    val config = new BrokerConfig(ds)
    FileSystemRegistrant(configFolder).register(config)
    val tulos = config.verify(Tokens.idSet) // Verify that all tokens match a SQL file
    val broker = Broker(config)

    val count = broker.readOnly() { session =>
      session.selectOne[java.math.BigDecimal]("SELECT count(*) from MYTABLE").get
    }
    print("row count = ")
    println(count)
      
    val results = broker.readOnly() { session =>
      session.selectAll(Tokens.selectMytable)
    }  
    for (i <- results) {
      println(i.identifier)
    }
    
    val newItem = Mytable(3, "Test row 3")  
    val savedItem = broker.transaction() { txn =>
      val result = txn.execute(Tokens.insertMytable, "mytable"->newItem)
      newItem
    }
  }
 
  def main(args: Array[String]): Unit = {
    println("Just a test program")
  }
}

When I run the program, I get following output:

row count = 2
Test row 1
Test row 2
Exception in thread "main" java.lang.ExceptionInInitializerError
    at example.Main.main(Main.scala)
Caused by: org.orbroker.exception.ConfigurationException: Failed to prepare statement:
INSERT INTO Mytable (id, identifier) VALUES (?, ?)
    at org.orbroker.ParsedSQL.preparationError(ParsedSQL.scala:57)
    at org.orbroker.ParsedSQL.prepareUpdate(ParsedSQL.scala:32)
    at org.orbroker.ModifyStatement$class.execute(ModifyStatement.scala:16)
    at org.orbroker.config.BrokerConfig$$anon$1.execute(BrokerConfig.scala:110)
    at org.orbroker.Executable$class.execute(Executable.scala:27)
    at org.orbroker.Executable$class.execute(Executable.scala:17)
    at org.orbroker.Transaction.execute(Transactional.scala:11)
    at example.Main$$anonfun$4.apply(Main.scala:68)
    at example.Main$$anonfun$4.apply(Main.scala:67)
    at org.orbroker.Broker$$anonfun$transaction$1.apply(Broker.scala:157)
    at org.orbroker.Broker$$anonfun$transaction$1.apply(Broker.scala:156)
    at org.orbroker.Broker.runSession(Broker.scala:165)
    at org.orbroker.Broker.transactional(Broker.scala:121)
    at org.orbroker.Broker.transactional(Broker.scala:114)
    at org.orbroker.Broker.transaction(Broker.scala:156)
    at example.Main$.<init>(Main.scala:67)
    at example.Main$.<clinit>(Main.scala)
    ... 1 more
Caused by: java.sql.SQLException: Unsupported feature
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
    at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
    at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:3360)
    at org.orbroker.adapt.DefaultStatementAdapter$class.prepareUpdate(StatementAdapter.scala:13)
    at org.orbroker.adapt.DefaultAdapter.prepareUpdate(BrokerAdapter.scala:24)
    at org.orbroker.ParsedSQL.prepareUpdate(ParsedSQL.scala:30)
    ... 16 more
Java Result: 1

[Nils]

Does this help?

https://forums.oracle.com/forums/thread.jspa?threadID=586975 

[Kari Tuomainen]

On Thursday, March 1, 2012 5:59:34 PM UTC+2, Nils wrote:

Does this help?

https://forums.oracle.com/forums/thread.jspa?threadID=586975 

Installing new Oracle JDBC driver seems to have fixed the problem. Thank you from the bottom of my heart.

BTW, is there some recommended way of using Oracle sequences for creating primary keys when using O/R Broker?

As far as I know, there is at least three possible ways:

1. By creating trigger which fetches the value from sequence when rows are inserted.

2. By inlining: "insert into MYTABLE (id, identifier) VALUES (MYSEQ.NEXTVAL, :mytable.identifier)"

3. First fetching the value from sequence: "select MYSEQ.NEXTVAL from dual" and then

   in program making sure that primary key column is set to this value.

Best regards,

  Kari

[Nicholas Ustinov]

inlining is the best way.
trigger leads to one more context switch SQL - PL/SQL - SQL
fetching values - just one more query to server

Nicholas

2012/3/1 Kari Tuomainen <kari.tu...@gmail.com>