CODEWITHSUNDEEP
mysqlscalaplayframework
Maniyan
Maniyan

Reputation: 35

MySQL LAST_INSERT_ID() returns 0 instead of recently inserted ID

I need to insert values into a table B with the ID generated from table A. As table A has got primary key which is auto_increment I tried using select LAST_INSERT_ID() to get the id for the recently inserted value. But the LAST_INSERT_ID() returns 0.

This is the Scala function that I'm using to carry out the insertions

    def insertNewTagsAndMap(conceptCode : String, questionId : String, tagNames : List[String]) {
    
    for (tagName <- tagNames) {
      DB.withTransaction { implicit conn =>
      val tagInsertionQuery = SQL(""" 
        INSERT INTO tbl_tags(tag_name) values ({tag_name});
      """)
      .on("tag_name"->tagName).executeInsert()
      }
      
      DB.withConnection { implicit conn =>
      val getRecentTagIdQuery = SQL(
        "SELECT LAST_INSERT_ID() as id_for_tag;"
      )

      var tagId = getRecentTagIdQuery().head[Int]("id_for_tag")
      println(tagId.getClass)
      }
      mapTagToQuestion(conceptCode, questionId,  tagId)
    }
}

Upvotes: 0

Views: 537

Answers (1)

Kadet
Kadet

Reputation: 1409

According to documentation 0 is returned from LAST_INSERT_ID() if connection has not yet performed successful INSERT.

In Scala withConnection acquire and closes the connection every time it is called.

It looks like you are using first withTransaction and after that withConnection. Try to do both operations in one block of code.

Upvotes: 1

Related Questions