Re: anorm with oracle sequences

[Srinivas Nandina]

I am running into a similar when trying to extract the inserted key values using Anorm for Oracle.

As a workaround I have created a class like below:

import anorm._

import anorm.ParameterValue

import anorm.SqlQuery

/**

 * Hack for Oracle to extract inserted id's

 */

object OracleAnorm {

  private def defaultParser: RowParser[Row] = RowParser(row => Success(row))

  implicit def sqlToOracleSimple(sql: SqlQuery): OracleSimpleSql[Row] = new OracleSimpleSql(sql, Nil, defaultParser)

  def OracleSQL(stmt: String) = Sql.sql(stmt)

}

case class OracleSimpleSql[T](sql: SqlQuery,

                              params: Seq[(String, ParameterValue[_])],

                              defaultParser: RowParser[T])

  extends Sql {

  def on1(args: (Any, ParameterValue[_])*): OracleSimpleSql[T] = this.copy(params = (this.params) ++ args.map {

    case (s: Symbol, v) => (s.name, v)

    case (k, v) => (k.toString, v)

  })

  def onParams(args: ParameterValue[_]*): OracleSimpleSql[T] = this.copy(params = (this.params) ++ sql.argsInitialOrder.zip(args))

  def list()(implicit connection: java.sql.Connection): Seq[T] = as(defaultParser*)

  def single()(implicit connection: java.sql.Connection): T = as(ResultSetParser.single(defaultParser))

  def singleOpt()(implicit connection: java.sql.Connection): Option[T] = as(ResultSetParser.singleOpt(defaultParser))

  def getFilledStatement(connection: java.sql.Connection, getGeneratedKeys: Boolean = false) = {

    // hack for oracle as it does not support Statement.RETURN_GENERATED_KEYS

    val s = if (getGeneratedKeys) connection.prepareStatement(sql.query, Array(1))

    else connection.prepareStatement(sql.query)

    sql.queryTimeout.foreach(timeout => s.setQueryTimeout(timeout))

    val argsMap = Map(params: _*)

    sql.argsInitialOrder.map(argsMap)

      .zipWithIndex

      .map(_.swap)

      .foldLeft(s)((s, e) => { e._2.set(s, e._1 + 1); s })

  }

  def using[U](p: RowParser[U]): OracleSimpleSql[U] = OracleSimpleSql(sql, params, p)

  def withQueryTimeout(seconds: Option[Int]): OracleSimpleSql[T] = this.copy(sql = sql.withQueryTimeout(seconds))

}

Usage

val insertQuery = "INSERT INTO region (region_id, name) VALUES (REGION_SEQ.nextval, {name}"

val regionId = OracleSQL(insertQuery).on1("name" -> name).executeInsert(scalar[java.math.BigDecimal].single).longValue()

Is there a better way to achieve this?

Thanks,

Srinivas

On Monday, 17 December 2012 19:15:22 UTC+5:30, stuart....@gmail.com wrote:

Hi,

I'm trying to migrate a proof-of-concept based on anorm 2.9.2 / 2.1-09142012 from H2 to Oracle.

However, I'm having some trouble coping with keys generated from a sequence. H2 works as expected, but Oracle throws this exception:

TypeDoesNotMatch(Cannot convert oracle.sql.ROWID@313170:class oracle.sql.ROWID to Long for column ColumnName(INSTRUMENT.ROWID,Some(ROWID)))

On investigation, the Oracle documentation at http://docs.oracle.com/cd/E11882_01/java.112/e16548/jdbcvers.htm states this:

When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when theStatement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns.

The result is that Oracle returns its internal identifier; the ROWID. Unfortunately I need the generated primary key.

This suggests to me that Anorm.scala needs enhancement to support not just "getGeneratedKeys: Boolean = false", but also the ability to provide the specific columns that must be returned.

So - 

• Am I using anorm correctly? 
  
• Is there a workaround for this behaviour?
  
• Should I raise a ticket?
  

Thanks,

Stu

[Kirill Nebogin]

More than 2 years have passed, but this issue doesn't seem to be fixed. Are there any other ways to retrieve Id's than forking anorm and hacking it?

[Kirill Nebogin]

Upgraded to Anorm 2.5.1, there are fixes of this issue. You just need to call executeInsert1("id")() to get generated id as Option[Long]. Or you can supply your own parser.

Just writing to tell anyone who'd google this issue up.

[Paul Porter]

Thank you Kirill, this solved my problem. I missed the "1" initially in execurtInsert1.