call simple database procedure using Slick 3.0

I have written a simple database procedure in mySQL as follows:

DROP PROCEDURE IF EXISTS sp_doSomething;  
DELIMITER //
CREATE PROCEDURE sp_doSomething    
(IN pVal1 varchar(100),
 IN pVal2 int(15)
)
BEGIN
  DECLARE vCnt int(5)  DEFAULT 0;   
  DECLARE vID int(15)  DEFAULT 0;
  DECLARE vTempID int(15)  DEFAULT 0;

  -- get ID
  SELECT id INTO vID FROM T1
  WHERE name = pVal1;

  -- get count
  SELECT count(*) INTO vCnt FROM T1
  WHERE owner = vID;

  -- get the log
  INSERT INTO log select CONCAT('-v1-:', pVal1, ':-v2-:', pVal2);

  -- Create basic stuff if it doesn't exist
  IF vFolderCnt = 0 THEN 
    INSERT INTO T1 (`id`, `col1`, `col2`, `col3`)  
    SELECT null, vID, 'some value', CONCAT(vID,'^1') FROM T1
    WHERE owner = 0;
  END IF;

  commit;

END //
DELIMITER ;

Now, I want to call this procedure in my Play Framework 2.4 application which uses Slick 3.0. It is such a simple thing but I am really struggling with it as there is no proper documentation available. It's very frustrating.

As it's mentioned on Google Group here https://groups.google.com/forum/#!searchin/scalaquery/procedure/scalaquery/BUB2-ryR0bY/EFZGX663tRYJ

I tried calling the procedure by different way. The code compiles but the procedure does not get called at all.

This statement gives an action error.

db.run(sql"{call sp_doSomething('${st.val1}', 1)}")

The following statement compiles fine but does not invoke the procedure.

db.run(sql"{call sp_doSomething('${st.val1}', 1)}".as[Int])

The following statement compiles fine but does not invoke the procedure.

db.run(sqlu"{call sp_doSomething('${st.val1}', 1)}")

Or db.run(sqlu"{?=call sp_doSomething('${st.val1}', 1)}")

I have granted the Execute permission on the procedure to my DB user and verified it.

Also, I am not sure, whether the COMMIT is required in the procedure or not?

Any help, will be highly appreciated.

回答1:

I have managed to invoke the stored procedure using old prepareCall method. Here's how I have done it. Hope, it might help someone.

db.withSession {
    implicit session => {
        val cs = session.conn.prepareCall("{call sp_doSomething(?, ?)}")
        cs.setString(1, st.val1)
        cs.setLong(2, 1L)
        val result = cs.executeUpdate()
    }
}

But I would be still interested in invoking the procedure using sql"" or sqlu"".

回答2:

For me the following works in Slick 3.2:

delimiter //
create procedure Try1()
begin
    select userid from TBL_USER where id = "4";
end //
delimiter ;

Then

  def runProcByRawSql() : Future[Vector[String]] = {
    def runproc = sql"""call Try1()""".as[String]

    db.run(runproc)
  }