Slick insert into postgreSQL

Question

I am just trying to add a row from slick to my postgreSQL Database.

Here what I am trying to do :

 val dbConfig = DatabaseConfigProvider.get[JdbcProfile](Play.current)
 import dbConfig.driver.api._
 val query = Task += new TaskRow(5, "taskName", status = "other")
 println(Task.insertStatement)
 val resultingQuery = dbConfig.db.run(query).map(res => "Task successfully added").recover {
      case ex: Exception => ex.getCause.getMessage
    }

Here the result of println :

insert into "task" ("taskname","description","status","datetask","pediocitynumber","periodicitytype") values (?,?,?,?,?,?)

I don't have any result exception or success from the resulting query.

Code generate by slick-codegen 3.1.1 :

case class TaskRow(taskid: Int, taskname: String, description: Option[String] = None, status: String, datetask: Option[java.sql.Timestamp] = None, pediocitynumber: Option[Int] = None, periodicitytype: Option[String] = None)
  /** GetResult implicit for fetching TaskRow objects using plain SQL queries */
  implicit def GetResultTaskRow(implicit e0: GR[Int], e1: GR[String], e2: GR[Option[String]], e3: GR[Option[java.sql.Timestamp]], e4: GR[Option[Int]]): GR[TaskRow] = GR{
    prs => import prs._
    TaskRow.tupled((<<[Int], <<[String], <<?[String], <<[String], <<?[java.sql.Timestamp], <<?[Int], <<?[String]))
  }
  /** Table description of table task. Objects of this class serve as prototypes for rows in queries. */
  class Task(_tableTag: Tag) extends Table[TaskRow](_tableTag, "task") {
    def * = (taskid, taskname, description, status, datetask, pediocitynumber, periodicitytype) <> (TaskRow.tupled, TaskRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (Rep.Some(taskid), Rep.Some(taskname), description, Rep.Some(status), datetask, pediocitynumber, periodicitytype).shaped.<>({r=>import r._; _1.map(_=> TaskRow.tupled((_1.get, _2.get, _3, _4.get, _5, _6, _7)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column taskid SqlType(serial), AutoInc, PrimaryKey */
    val taskid: Rep[Int] = column[Int]("taskid", O.AutoInc, O.PrimaryKey)
    /** Database column taskname SqlType(text) */
    val taskname: Rep[String] = column[String]("taskname")
    /** Database column description SqlType(text), Default(None) */
    val description: Rep[Option[String]] = column[Option[String]]("description", O.Default(None))
    /** Database column status SqlType(statustask) */
    val status: Rep[String] = column[String]("status")
    /** Database column datetask SqlType(timestamp), Default(None) */
    val datetask: Rep[Option[java.sql.Timestamp]] = column[Option[java.sql.Timestamp]]("datetask", O.Default(None))
    /** Database column pediocitynumber SqlType(int4), Default(None) */
    val pediocitynumber: Rep[Option[Int]] = column[Option[Int]]("pediocitynumber", O.Default(None))
    /** Database column periodicitytype SqlType(periodicitytype), Default(None) */
    val periodicitytype: Rep[Option[String]] = column[Option[String]]("periodicitytype", O.Default(None))
  }
  /** Collection-like TableQuery object for table Task */
  lazy val Task = new TableQuery(tag => new Task(tag))

Could someone explain what I am doing wrong?

EDIT:

To clarify my question :

The row is not added to the table, it seems that nothing happen. I can't see any exception or error thrown. I think it could come from the sql statement and the questions marks (values (?,?,?,?,?,?)). It should be the actual values of the fields, right?

More code:

class Application @Inject()(dbConfigProvider: DatabaseConfigProvider) extends Controller {

      def index = Action {
        Ok(views.html.main())
      }


      def taskSave = Action.async { implicit request =>
        println(request.body.asJson)
        val dbConfig = DatabaseConfigProvider.get[JdbcProfile](Play.current)
        import dbConfig.driver.api._
        val query = Task += new TaskRow(5, "taskName", status = "other")
        println(Task.insertStatement)
        println(query)
        val resultingQuery = dbConfig.db.run(query).map(res => "TAsk successfully added").recover {
          case ex: Exception => ex.getCause.getMessage
        }
        resultingQuery.map(r => println("result : " + r))
        Future(Ok(""))
      }
}

route :

PUT   /task-save     controllers.Application.taskSave

mmm, what's exactly the question? The statement looks correct, do you have any error logged out? — Gabriele Petronella
No I don't have any error or log. I thought the statement was wrong because of the values (?,?,?,?,?,?). Is this normal? Should the question mark be replaced by the actual value of the field? — GermainGum
"I don't have any result exception or success from the resulting query." - How did you check that? Can you show some code? The question marks are fine, they are placeholders for the actual values in the query. What you see printed here is called a prepared statement. The database gets the query with placeholders, compiles it and then inserts the values into the query and finally executes it. — alextsc
Thank you for the link. I just add the code that I run. I run my application with "sbt run" in the terminal, so I check the output in the terminal. "How did you check that?" I print my val resultingQuery and neither the "Task succesfully.." or the Exception is printed in the terminal. — GermainGum

Som Bhattacharyya Som Bhattacharyya · Accepted Answer · 2016-04-20T17:30:13

The statement is not wrong because of the values (?,?,? ...). The lazy val Task is of type TableQuery[...] . When you call insertStatement on this object you get back a string that represents the template SQL string that runs in the background. That is expected. So that is not the indicator of the problem.

Try something like this :

val db = Database.forConfig("h2mem1")
try {

Await.result(db.run(DBIO.seq(
  // create the schema
  Task.schema.create,

  // insert two User instances
  Task += (2, "abcd", "201-01-01"),

  // print the users (select * from USERS)
  Task.result.map(println))), Duration.Inf)
 } finally db.close

Slick insert into postgreSQL

1 Answers