Slick update table with auto-generated primary key-CodePudding

I have a simple case class in Scala that I map to a DB2 table with an auto-generated primary key (a sequence number). The Slick DAO works fine for INSERT and SELECT, but throws an exception for UPDATE.

Slick shows the generated SQL, and it seems that the issue is that it tries to (also) update the primary key column, and DB2 does not allow that.

This is the SQL statement generated by Slick:

slick.basic.BasicBackend.action - [update "SOME_THING" set "RECORD_ID" = ?, "STATUS" = ? where "RECORD_ID" = ...]

Since I am only trying to update the STATUS, this is what I wanted (and should work):

slick.basic.BasicBackend.action - [update "SOME_THING" set                  "STATUS" = ? where "RECORD_ID" = ...]

How can I modify my code to make the UPDATE work? I can replace the UPDATE with DELETE INSERT, but is there a better way?

Using Slick 3.3.3, Scala 2.12.

import slick.jdbc.DB2Profile.api._
import slick.lifted.ProvenShape

class SomethingTbl(tag: Tag, schemaName: String) extends Table[SomethingRec](tag, _schemaName = Some(schemaName), _tableName = "SOME_THING") {
  def record_id: Rep[Long] = column[Long]("RECORD_ID", O.PrimaryKey, O.AutoInc)

  def status: Rep[String] = column[String]("STATUS")

  override def * : ProvenShape[SomethingRec] = (record_id, status ) <> (SomethingRec.tupled, SomethingRec.unapply)
}

import akka.actor.ActorSystem
import slick.jdbc
import slick.jdbc.DB2Profile
import slick.sql.FixedSqlAction

import scala.concurrent.duration._
import scala.concurrent.{Await, Future}

class SomethingDAO(schemaName: String)(implicit val system: ActorSystem) {

  import slick.jdbc.DB2Profile.api._

  lazy val someThings: TableQuery[SomethingTbl] =
    TableQuery[SomethingTbl](tag => new SomethingTbl(tag, schemaName))

  // Capture the DB-generated sequence number and return it
  private lazy val insertSomething: jdbc.DB2Profile.IntoInsertActionComposer[SomethingRec, SomethingRec] =
    someThings returning someThings.map(_.record_id) into ((rec, record_id) => rec.copy(record_id = record_id))

  private implicit val db: DB2Profile.backend.Database = Database.forConfig("slick-db2")

  def update(e: SomethingRec): Int = {
    val recordFut = db.run(updateSomething(e))
    val record = Await.result(recordFut, Duration.Inf)
    record
  }
  
  private def updateSomething(e: SomethingRec): FixedSqlAction[Int, NoStream, Effect.Write] = {
    someThings
      .filter(_.record_id === e.record_id)
      .update(e)
      // com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-798, SQLSTATE=428C9, SQLERRMC=RECORD_ID, DRIVER=4.19.26
  }
}

CodePudding user response：

Modified the code as shown below, and it works

private def updateSomething(e: SomethingRec): FixedSqlAction[Int, NoStream, Effect.Write] = {
    val q = for {c <- someThings if c.record_id === e.record_id} yield c.status
    q.update(e.status)
}