Search code examples
scalatransactionsinsertscalaquery

How to insert autoincremented master/slave records using ScalaQuery?

Classic issue, new framework -- thus problem.

PostgreSQL + Scala + ScalaQuery. I have Master table with serial (autincrement) id and Slave table also with serial id.

I need to insert one master record and several slaves. I have to do it within transaction (to have ability to cancel all), so I cannot run a query after inserting master to find out id. As far as I see SQ "insert" method does not return any reference to inserted master record.

So how to do it?

SQ Examples cover this however without autoincremented field, so such solution (pre-set ids) is not applicable here.

Solution

  • If I understand it correctly this is not possible for now in automatic way. If one is not afraid, this can be done this way. Obtaining the id of last insert (per each master record insertion):

    postgreSQL function for last inserted ID

    Then using it in SQ:

    http://groups.google.com/group/scalaquery/browse_thread/thread/faa7d3e5842da82e

    This code shows the MySql way. I'm posting it to the list for posterity's sake.

    val scopeIdentity = SimpleFunction.nullaryLong

    val inserted = Actions.insert( "cat", "eats", "dog)

    //Print out the count of inserted records. println(inserted )

    //Print out the primary key for the last inserted record. println(Query(scopeIdentity).first)

    //Regards //Bryan

    But since for auto incremented fields you have to use projections excluding autoinc fields, and then inserting tuples instead of named record types, there is a question if it is not worth to hold breath until SQ will support this directly.

    Note I am SQ newbie, I might just misinform you.