java.sql.BatchUpdateException using MSSql

[Robert Weissmann]

Hi,

getting the following Exception (sorry its in German, but the specific MSSQL part should be clear anyhow):

Exception in thread "main" java.sql.BatchUpdateException: Ein expliziter Wert für die Identitätsspalte kann nicht in der PERSONS-Tabelle eingefügt werden, wenn IDENTITY_INSERT auf OFF festgelegt ist.

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1160)

at scala.slick.driver.BasicInvokerComponent$InsertInvoker$$anonfun$insertAll$1$$anonfun$apply$4.apply(BasicInvokerComponent.scala:106)

at scala.slick.driver.BasicInvokerComponent$InsertInvoker$$anonfun$insertAll$1$$anonfun$apply$4.apply(BasicInvokerComponent.scala:99)

at scala.slick.session.Session$class.withPreparedStatement(Session.scala:68)

at scala.slick.session.BaseSession.withPreparedStatement(Session.scala:201)

at scala.slick.driver.BasicInvokerComponent$InsertInvoker.prepared(BasicInvokerComponent.scala:81)

at scala.slick.driver.BasicInvokerComponent$InsertInvoker$$anonfun$insertAll$1.apply(BasicInvokerComponent.scala:99)

at scala.slick.session.BaseSession.withTransaction(Session.scala:229)

at scala.slick.driver.BasicInvokerComponent$InsertInvoker.insertAll(BasicInvokerComponent.scala:95)

at org.rob.slicktests.ThirdExample$$anonfun$1.apply$mcV$sp(ThirdExample.scala:44)

at org.rob.slicktests.ThirdExample$$anonfun$1.apply(ThirdExample.scala:38)

at org.rob.slicktests.ThirdExample$$anonfun$1.apply(ThirdExample.scala:38)

at scala.slick.session.BaseSession.withTransaction(Session.scala:236)

at org.rob.slicktests.ThirdExample$delayedInit$body.apply(ThirdExample.scala:38)

I am using the follwing test code (works with h2):

import scala.slick.driver.SQLServerDriver.simple._

object ThirdExample extends App {

  case class Car(id: Option[Int], marke: String, kennzeichen: String)

  case class Person(id: Option[Int], name: String, age: Int, carId: Option[Int])

  object Cars extends Table[Car]("CARS") {

    def id = column[Int]("ID", O.PrimaryKey, O.AutoInc) // This is the primary key column

    def marke = column[String]("MARKE")

    def kennzeichen = column[String]("KENNZEICHEN")

    // Every table needs a * projection with the same type as the table's type parameter

    def * = id.? ~ marke ~ kennzeichen <> (Car, Car.unapply _)

  }

  // Definition of the SUPPLIERS table

  object Persons extends Table[Person]("PERSONS") {

    def id = column[Int]("ID", O.PrimaryKey, O.AutoInc) // This is the primary key column

    def name = column[String]("NAME")

    def age = column[Int]("AGE")

    def carId = column[Option[Int]]("CAR_ID")

    // Every table needs a * projection with the same type as the table's type parameter

    def * = id.? ~ name ~ age ~ carId <> (Person, Person.unapply _)

    def car = foreignKey("PERS_FK", carId, Cars)(_.id)

  }

//  implicit val session = Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver").createSession()

  implicit val session = Database.forURL("jdbc:sqlserver://localhost:1433;DatabaseName=playground",

                                          user = "playground",

                                          password = "playground",

                                          driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver").createSession()

  val ddl = Cars.ddl ++ Persons.ddl

  session.withTransaction {

    ddl.create

    val p1 = Person(None, "Rob", 44, None)

    val p2 = Person(None, "Sepp", 33, None)

    Persons.insertAll(p1, p2)

    println((for(p <- Persons) yield p).list.mkString(", "))

  }

}

Any ideas how to set this properly up ? Thanks.

Cheers, Rob.

[Shawn Downs]

Rob, did you ever get an answer to this? I have the same issue.

Thanks,
Shawn

[Dave]

IIUC it has to be

def id = column[Option[Int]]("ID", O.PrimaryKey, O.AutoInc)

otherwise slick is inserting null

Op woensdag 19 december 2012 17:33:14 UTC+1 schreef Shawn Downs het volgende:

[Dave]

Op woensdag 19 december 2012 20:17:14 UTC+1 schreef Dave het volgende:

IIUC it has to be

def id = column[Option[Int]]("ID", O.PrimaryKey, O.AutoInc)

otherwise slick is inserting null

 But the funny thing is that

the line

def * = id.? ~ name ~ age ~ carId <> (Person, Person.unapply _)

objects

[Dave]

Which solved by removing .?

def * = id ~ name ~ age ~ carId <> (Person, Person.unapply _)


But that is counter-intuitive

because ? means normally 0 (not present at all) or 1 (present)
but in this case probably null (present but null) or 1 (present not null)





 

[Shawn Downs]

Dave,

Thanks for the response but I'm getting "[info]   java.sql.SQLException: Cannot insert explicit value for identity column in table 'batch' when IDENTITY_INSERT is set to OFF." (so the same error) with:

object Batch extends Table[(Option[Int], String, Option[Date])]("batch") {

  def batch_id = column[Option[Int]]("batch_id", O.PrimaryKey, O.AutoInc)

  def status = column[String]("status", O.NotNull)

  def add_date = column[Option[Date]]("add_date", O.Nullable)

  def * = batch_id ~ status ~ add_date

  def create(status: String = "initiated") = db.withSession {this.insert((None, status, None))}

  

}

I've also followed typeafe's guide by doing with the same result:

object Batch extends Table[(Option[Int], String, Option[Date])]("batch") {

  def batch_id = column[Int]("batch_id", O.PrimaryKey, O.AutoInc)

  def status = column[String]("status", O.NotNull)

  def add_date = column[Date]("add_date", O.Nullable)

  def * = batch_id.? ~ status ~ add_date.?

  def create(status: String = "initiated") = db.withSession {this.insert((None, status, None))}

  

}

Any idea what i could be doing wrong?

Thanks again,

Shawn

[Dave]

I wouldn't turn it on. Maybe it is a bug in the sqlaquery/slick driver
With sqlite I have no problem with both ways.
Op woensdag 19 december 2012 21:29:18 UTC+1 schreef Shawn Downs het volgende:

[Dave]

Hmm, shouldn't it be

Persons.insertAll(List(p1, p2):_*)


Op woensdag 19 december 2012 21:29:18 UTC+1 schreef Shawn Downs het volgende:

[Shawn Downs]

Dave,

Sorry, it's not a bug... A friend of mine found this post which worked for me:

http://stackoverflow.com/questions/13199198/using-auto-incrementing-fields-with-postgresql-and-slick

Just wanted to share and thanks again for your time.

Shawn