Update doesn't work for SQLServer Driver (Slick 2.1.0)

[Ivan Grebenik]

Hello there!

Suddenly I ran into nasty situation. I couldn't update a record in a table under Microsoft SQL Server.

Table definition: 

TABLE "SLIDE" (
   "ID" BIGINT NOT NULL PRIMARY KEY IDENTITY,
   "TITLE" VARCHAR(254) NOT NULL,
   "BG_COLOR" VARCHAR(254),
   "BG_IMAGE" VARCHAR(254),
   etc...)

Here is a small test:

it should "update one existing record in the table" in {

    db.withSession { implicit session =>

      slides.ddl.create

      try {

        slides ++= Seq(

          SlideEntity(title = "Slide1", ...),

          SlideEntity(title = "Slide2", ...),

          SlideEntity(title = "Slide3", ...),

          SlideEntity(title = "Slide4", ...),

          SlideEntity(title = "Slide5", ...))

        val old = slides.filter(_.id === 3L).first

        slides.filter(_.id === 3L).update(old.copy(title = "Slide3 is modified"))

        val modified = slides.filter(_.id === 3L).first

        modified.title shouldEqual "Slide3 is modified"

      } finally {

        slides.ddl.drop

      }

    }

  }

 

 

Slick generates this update statement:

UPDATE "SLIDE" 
SET "ID" = ?, "TITLE" = ?, "BG_COLOR" = ?, "BG_IMAGE" = ?, etc...
WHERE "SLIDE"."ID" = 3

As you can see it tries update "id" as well but this is primary auto-incremented filed! It should be omitted in this case... 

And as the result I get the error like this one:

java.sql.SQLException: Cannot update identity column 'ID'.

How to get rid of it? Is the any workaround this issue?

[Jesús Martinez]

Ivan! I'm pretty sure we worked together some years ago, do you remember?

I just happened to run against same problem, and while searching for possible solutions found your question, did you manage to solve it nicely. We have some helper method that will filter and then update, but this solution requires to specify filed by field and we would like to simply give the update the whole entity as parameter. It will be something like this:

val old = slides.filter(_.id === 3L).first.map(s => (s.title, s.bgColor, s.bgImage))
old.update((newSlide.title, newSlide.bgColor, newSlide.bgImage))