使用Slick 3.0调用简单的数据库过程 [英] call simple database procedure using Slick 3.0

问题描述

我已经在mySQL中编写了一个简单的数据库过程，如下所示:

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 ;

现在，我想在使用Slick 3.0的Play Framework 2.4应用程序中调用此过程.这是一件很简单的事情，但是由于没有适当的文档，我真的很努力.非常令人沮丧.

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.

正如Google网上论坛中所述 https ://groups.google.com/forum/#！searchin/scalaquery/procedure/scalaquery/BUB2-ryR0bY/EFZGX663tRYJ

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.

此语句给出操作错误.

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)}")

或 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.

还不确定程序中是否需要COMMIT吗?

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

任何帮助，我们将不胜感激.

Any help, will be highly appreciated.

推荐答案

我已经设法使用旧的prepareCall方法调用存储过程.这是我的方法.希望对别人有帮助.

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()
    }
}

但是我仍然对使用sql"或sqlu"调用该过程感兴趣.

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

这篇关于使用Slick 3.0调用简单的数据库过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！