Slick MSSQL inserting object with auto increment

I've recently had to move a project over from MySQL to MSSQL. I'm using IDENTITY(1,1) on my id columns for my tables to match MySQL's auto-increment feature.

When I try to insert an object though, I'm getting this error:

[SQLServerException: Cannot insert explicit value for identity column in table 'categories' when IDENTITY_INSERT is set to OFF.]

Now after some research I found out that it's because I'm trying to insert a value for my id(0) on my tables. So for example I have an object Category

case class Category(
  id: Long = 0L,
  name: String
)
object Category extends Table[Category]("categories"){

  def name = column[String]("name", O.NotNull)
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def * = id ~ name <> (Category.apply _, Category.unapply _)

  def add(model:Category) = withSession{ implicit session =>
    Category.insert(model)
  }
  def remove(id:Long) = withSession{implicit session =>
    try{Some(Query(Category).filter(_.id === id).delete)}
    catch{case _ => None}
  }
}

Is there a way to insert my object into the database and ignoring the 0L without MSSQL throwing an SQLException? MySQL would just ignore the id's value and do the increment like it didn't receive an id. I'd really rather not create a new case class with everything but the id.

Answers


Try redefining your add method like this and see if it works for you:

def add(model:Category) =  withSession{ implicit session =>
  Category.name.insert(model.name)
}

If you had more columns then you could have added a forInsert projection to your Category table class that specified all fields except id, but since you don't, this should work instead.

EDIT

Now if you do have more than 2 fields on your table objects, then you can do something like this, which is described in the Lifted Embedding documentation here:

case class Category(
  id: Long = 0L,
  name: String,
  foo:String
)
object Category extends Table[Category]("categories"){
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name", O.NotNull)
  def foo = column[String]("foo", O.NotNull)

  def * = id ~ name ~ foo <> (Category.apply _, Category.unapply _)

  def forInsert = name ~ foo <> (t => Category(0L, t._1, t._2), {(c:Category) => Some(c.name, c.foo)})

  def add(model:Category) =  withSession{ implicit session =>
    Category.forInsert insert model
  }
  def remove(id:Long) = withSession{implicit session =>
    try{Some(Query(Category).filter(_.id === id).delete)}
    catch{case _ => None}
  }

  def withSession(f: Session => Unit){

  }
}

Need Your Help

Setting default parameters in jQuery plugin

jquery jquery-plugins

I'm looking for a way to set a default numerical value to my defaults parameters of my plugin or have the option to retrieve the default value via an ajax call. In other words:

nginx: Map single static URL to a PHP file

nginx fastcgi

I have a WordPress installation up and running without issue. There is an element on the page that is not part of WP, but our own custom PHP script. It's just there to handle form POST requests and

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.